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

[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 <mickyhulse.lists at 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-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>