WP 3.5.2/multisite: How to use NOT IN in $wpdb->prepare()?

[Micky Hulse]

Situation and example code:

[code]

$ignore = implode(',', array('1', '19', '21',));
echo '<pre>';
$rows = $wpdb->get_results($wpdb->prepare("SELECT blog_id FROM
$wpdb->blogs WHERE blog_id NOT IN (%s) AND public = '1' AND archived =
'0' AND mature = '0' AND spam = '0' AND deleted = '0'", $ignore),
ARRAY_A);
print_r($rows);
echo '</pre>';

[/code]

Problem:

The "NOT IN ()" filter fails to ignore blog ids.

Reason:

I'm not certain, but I assume my code fails because $ignore isn't of
the %s, %d or %f types.

Solution:

Move $ignore into the statement like so:

... blog_id NOT IN ($ignore) ...

Problem:

I get this PHP notice:

PHP Notice: wpdb::prepare was called <strong>incorrectly</strong>.
wpdb::prepare() requires at least two arguments. Please see <a
href="http://codex.wordpress.org/Debugging_in_WordPress">Debugging in
WordPress</a> for more information. (This message was added in version
3.5.) in /rgblog/html/wp-includes/functions.php on line 2962

Reason for the notice:

"PHP Warning: Missing argument 2 for wpdb::prepare()"
<http://make.wordpress.org/core/2012/12/12/php-warning-missing-argument-2-for-wpdb-prepare/>

Specifically:

[quote]

you’re passing $id directly into the query, unprepared. And this,
right here, is why $wpdb->prepare() now issues a warning if it isn’t
called with more than one argument. Because you can’t prepare a query
without more than one argument.

[/quote]

With that said, how can I accomplish my goal of passing several blog
IDs into a query that uses wpdb::prepare() (like my example above)?

Any tips would be appreciated. :)

[Micky Hulse]

On Mon, Jul 15, 2013 at 7:36 PM, Micky Hulse <***@gmail.com> wrote:
> With that said, how can I accomplish my goal of passing several blog
> IDs into a query that uses wpdb::prepare() (like my example above)?

I should say:

What's the most practical and secure way (I want to avoid little Bobby
Tables http://xkcd.com/327/) to pass a list of blog IDs into the NOT
IN clause of wpdb::prepare() SQL statement?

[Jerry Milo Johnson]

from what I can see from this trac discussion, they decided that prepare
won't handle this case:

http://core.trac.wordpress.org/ticket/11102

hth,
Jerry Milo Johnson


On Mon, Jul 15, 2013 at 10:43 PM, Micky Hulse <***@gmail.com>wrote:

> On Mon, Jul 15, 2013 at 7:36 PM, Micky Hulse <***@gmail.com>
> wrote:
> > With that said, how can I accomplish my goal of passing several blog
> > IDs into a query that uses wpdb::prepare() (like my example above)?
>
> I should say:
>
> What's the most practical and secure way (I want to avoid little Bobby
> Tables http://xkcd.com/327/) to pass a list of blog IDs into the NOT
> IN clause of wpdb::prepare() SQL statement?
> _______________________________________________
> wp-hackers mailing list
> wp-***@lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

[Micky Hulse]

Hi Jerry! Thanks for the help! :)

On Mon, Jul 15, 2013 at 7:51 PM, Jerry Milo Johnson <***@gmail.com> wrote:
> from what I can see from this trac discussion, they decided that prepare
> won't handle this case:
> http://core.trac.wordpress.org/ticket/11102

Ahh, that does help. Thank you for the tip off. :)

Unfortunately, it looks like there's not a good alternative presented.

Either way, I appreciate the linkage! Definitely nice to see that
others have been in same boat. ;)

[Ryan McCue]

Micky Hulse wrote:
> What's the most practical and secure way (I want to avoid little Bobby
> Tables http://xkcd.com/327/) to pass a list of blog IDs into the NOT
> IN clause of wpdb::prepare() SQL statement?

$ids = array(1, '2', '3q', 'b');

// Ensure all IDs are positive integers
$ids = array_map('absint', $ids);

// Filter anything that converted to 0 (i.e. non-integers)
$ids = array_filter();

// Add to your SQL
$sql .= ' NOT IN (' . implode(',', $ids) . ')';

--
Ryan McCue
<http://ryanmccue.info/>

[Ryan McCue]

Ryan McCue wrote:
> $ids = array(1, '2', '3q', 'b');

Forgot to include output: 'NOT IN (1,2,3)'

--
Ryan McCue
<http://ryanmccue.info/>

[Micky Hulse]

On Mon, Jul 15, 2013 at 9:24 PM, Ryan McCue <***@rotorised.com> wrote:
> ... snip ...
> Forgot to include output: 'NOT IN (1,2,3)'

That's awesome! Thanks for the code example Ryan! That's very helpful. :)

Based on your tips for working with my array of IDs, and the
limitation of wpdb::prepare() (must have at least one argument, and
arguments can only be %s (string), %d (int) or %f (float)), I could
fake things and do (not tested, but gets the point across):

$rows = $wpdb->get_results($wpdb->prepare("SELECT blog_id FROM
$wpdb->blogs WHERE blog_id NOT IN (' . implode(',', $ids) . ') AND
public = '%d' AND archived = '%d' AND mature = '%d' AND spam = '%d'
AND deleted = '%d'", 1, 0, 0, 0, 0), ARRAY_A);

I'd probably end up using easy to configure variables for the %ds above.

Using your tip on making sure the IDs are valid/secure, then I'd say
it's OK that I'm not actually using "prepare" to sanitize the input
array of IDs.

Thanks again Jerry and Ryan!

Cheers,
M

[Shea Bunge]

What format are the blog ids stored in, and where did they come from?

> From: ***@gmail.com
> Date: Mon, 15 Jul 2013 19:43:01 -0700
> To: wp-***@lists.automattic.com
> Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in $wpdb->prepare()?
>
> On Mon, Jul 15, 2013 at 7:36 PM, Micky Hulse <***@gmail.com> wrote:
> > With that said, how can I accomplish my goal of passing several blog
> > IDs into a query that uses wpdb::prepare() (like my example above)?
>
> I should say:
>
> What's the most practical and secure way (I want to avoid little Bobby
> Tables http://xkcd.com/327/) to pass a list of blog IDs into the NOT
> IN clause of wpdb::prepare() SQL statement?
> _______________________________________________
> wp-hackers mailing list
> wp-***@lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers

[Micky Hulse]

On Mon, Jul 15, 2013 at 11:28 PM, Shea Bunge <***@bungeshea.com> wrote:
> What format are the blog ids stored in, and where did they come from?

Great question. Sorry that I did not mention that before.

I have a couple of functions that allow me to get latest updated blogs
on our multisite. The IDs are controlled by me, the developer, and
I've allowed them to be overridden via function arguments. There will
be no forms or users (ones that I don't trust) that will have access
to this code, so I can be pretty certain the input will be legit.

Maybe I'm being overly cautious? I guess a part of me wants to just
play it safe even though I completely trust where the IDs are coming
from. :)

Thanks for asking! Let me know if you have feedback.

[Nicholas Ciske]

If you play it safe, then the other developers can’t grab something unsafe from a form or query string and open up a security hole via your code.

IDs are so easy to sanitize, there's no reason not to.

_________________________
Nick Ciske
http://thoughtrefinery.com/
@nciske

On Jul 16, 2013, at 1:55 AM, Micky Hulse wrote:
>
> There will
> be no forms or users (ones that I don't trust) that will have access
> to this code, so I can be pretty certain the input will be legit.
>
> Maybe I'm being overly cautious? I guess a part of me wants to just
> play it safe even though I completely trust where the IDs are coming
> from. :)

[Shea Bunge]

You should have a look at the absint (http://queryposts.com/function/absint) function - it will make sure that a variable is a positive integer.
If you're dealing with an array of blog IDs, use array_map( $blog_ids, 'absint' ); (http://www.php.net/array_map) to apply the absint function to all of the array elements.
> From: ***@gmail.com
> Date: Mon, 15 Jul 2013 23:55:07 -0700
> To: wp-***@lists.automattic.com
> Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in $wpdb->prepare()?
>
> On Mon, Jul 15, 2013 at 11:28 PM, Shea Bunge <***@bungeshea.com> wrote:
> > What format are the blog ids stored in, and where did they come from?
>
> Great question. Sorry that I did not mention that before.
>
> I have a couple of functions that allow me to get latest updated blogs
> on our multisite. The IDs are controlled by me, the developer, and
> I've allowed them to be overridden via function arguments. There will
> be no forms or users (ones that I don't trust) that will have access
> to this code, so I can be pretty certain the input will be legit.
>
> Maybe I'm being overly cautious? I guess a part of me wants to just
> play it safe even though I completely trust where the IDs are coming
> from. :)
>
> Thanks for asking! Let me know if you have feedback.
> _______________________________________________
> wp-hackers mailing list
> wp-***@lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers

[Andrew Nacin]

$wpdb->prepare() is only for preparing a query with %s, %d, or %f
placeholders.

If your query doesn't have a placeholder, then don't use prepare. As in:
$rows = $wpdb->get_results($wpdb->prepare("SELECT blog_id FROM
$wpdb->blogs WHERE blog_id NOT IN ($ignore) AND public = '1' AND archived =
'0' AND mature = '0' AND spam = '0' AND deleted = '0'", $ignore),
ARRAY_A);

Just make sure that $ignore is is secure! As in:
$ignore = esc_sql( array( $a, $b, $c ) );
$ignore = "'" . implode( "', '", $ignore ) . "'";

Since the ignored values are IDs, then you don't *actually* need to escape
it
with esc_sql() here. I would still use intval() or absint(), though.

$ignore = array_map( 'absint', array( $a, $b, $c ) );
$ignore = implode( ', ', $ignore );


On Mon, Jul 15, 2013 at 10:36 PM, Micky Hulse <***@gmail.com>wrote:

> Situation and example code:
>
> [code]
>
> $ignore = implode(',', array('1', '19', '21',));
> echo '<pre>';
> $rows = $wpdb->get_results($wpdb->prepare("SELECT blog_id FROM
> $wpdb->blogs WHERE blog_id NOT IN (%s) AND public = '1' AND archived =
> '0' AND mature = '0' AND spam = '0' AND deleted = '0'", $ignore),
> ARRAY_A);
> print_r($rows);
> echo '</pre>';
>
> [/code]
>
> Problem:
>
> The "NOT IN ()" filter fails to ignore blog ids.
>
> Reason:
>
> I'm not certain, but I assume my code fails because $ignore isn't of
> the %s, %d or %f types.
>
> Solution:
>
> Move $ignore into the statement like so:
>
> ... blog_id NOT IN ($ignore) ...
>
> Problem:
>
> I get this PHP notice:
>
> PHP Notice: wpdb::prepare was called <strong>incorrectly</strong>.
> wpdb::prepare() requires at least two arguments. Please see <a
> href="http://codex.wordpress.org/Debugging_in_WordPress">Debugging in
> WordPress</a> for more information. (This message was added in version
> 3.5.) in /rgblog/html/wp-includes/functions.php on line 2962
>
> Reason for the notice:
>
> "PHP Warning: Missing argument 2 for wpdb::prepare()"
> <
> http://make.wordpress.org/core/2012/12/12/php-warning-missing-argument-2-for-wpdb-prepare/
> >
>
> Specifically:
>
> [quote]
>
> you’re passing $id directly into the query, unprepared. And this,
> right here, is why $wpdb->prepare() now issues a warning if it isn’t
> called with more than one argument. Because you can’t prepare a query
> without more than one argument.
>
> [/quote]
>
> With that said, how can I accomplish my goal of passing several blog
> IDs into a query that uses wpdb::prepare() (like my example above)?
>
> Any tips would be appreciated. :)
> _______________________________________________
> wp-hackers mailing list
> wp-***@lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

[Micky Hulse]

Wow, you folks are so helpful!

Thanks to everyone (Jerry, Ryan, Shea, Nicholas and Andrew) for the
pro hacker tips and advice. It is much appreciated. :-)

Andrew, thanks for pointing me towards get_results() (and for the
additional details and example code).

Have a nice day!

Cheers,
M

[Micky Hulse]

On Tue, Jul 16, 2013 at 5:52 AM, Andrew Nacin <***@andrewnacin.com> wrote:
> If your query doesn't have a placeholder, then don't use prepare. As in:
> $rows = $wpdb->get_results($wpdb->prepare("SELECT blog_id FROM
> $wpdb->blogs WHERE blog_id NOT IN ($ignore) AND public = '1' AND archived =
> '0' AND mature = '0' AND spam = '0' AND deleted = '0'", $ignore),
> ARRAY_A)

On Tue, Jul 16, 2013 at 10:55 AM, Micky Hulse
<***@gmail.com> wrote:
> Andrew, thanks for pointing me towards get_results() (and for the
> additional details and example code).

For some reason, in the example code above, my eyes skimmed over the
"prepare()" bit .. When I read "don't use prepare", I saw
"get_results()" and assumed that I could use that without using
prepare(). Lol, it's still early and the coffee has yet to kick in. :D

Andrew, in your example above, I see that you have $ignore in both
spots. I assume you meant to delete the $ignore argument from the
prepare()? Or, to clarify, you must be saying to leave it there as an
unused argument (otherwise, prepare will throw that PHP notice)? Sorry
for me being so dense.

Thanks again Andrew and all!

Cheers,
M