wp-hackers Digest, Vol 102, Issue 16

d***@gmail.com

2013-07-16 07:08:42 UTC

message from the dark side

-----Original Message-----
From: wp-hackers-***@lists.automattic.com
Sender: "wp-hackers" <wp-hackers-***@lists.automattic.com>Date: Tue, 16 Jul 2013 06:59:25
To: <wp-***@lists.automattic.com>
Reply-To: wp-***@lists.automattic.com
Subject: wp-hackers Digest, Vol 102, Issue 16

Send wp-hackers mailing list submissions to
wp-***@lists.automattic.com

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.automattic.com/mailman/listinfo/wp-hackers
or, via email, send a message with subject or body 'help' to
wp-hackers-***@lists.automattic.com

You can reach the person managing the list at
wp-hackers-***@lists.automattic.com

When replying, please edit your Subject line so it is more specific
than "Re: Contents of wp-hackers digest..."

Today's Topics:

1. WP 3.5.2/multisite: How to use NOT IN in $wpdb->prepare()?
(Micky Hulse)
2. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Micky Hulse)
3. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Jerry Milo Johnson)
4. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Ryan McCue)
5. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Ryan McCue)
6. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Micky Hulse)
7. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Micky Hulse)
8. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Shea Bunge)
9. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Micky Hulse)
10. Re: WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()? (Nicholas Ciske)

----------------------------------------------------------------------

Message: 1
Date: Mon, 15 Jul 2013 19:36:21 -0700
From: Micky Hulse <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CALri7M=***@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

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. :)

------------------------------

Message: 2
Date: Mon, 15 Jul 2013 19:43:01 -0700
From: Micky Hulse <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CALri7MmK3ddK+1E_vUuetGqz_TyCtDh4rLh9cB+***@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

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?

------------------------------

Message: 3
Date: Mon, 15 Jul 2013 22:51:42 -0400
From: Jerry Milo Johnson <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CADYBncaYDdAnZbZXQUX-***@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

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

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)?

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

------------------------------

Message: 4
Date: Tue, 16 Jul 2013 14:21:17 +1000
From: Ryan McCue <***@rotorised.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID: <***@rotorised.com>
Content-Type: text/plain; charset=ISO-8859-1

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/>

------------------------------

Message: 5
Date: Tue, 16 Jul 2013 14:24:41 +1000
From: Ryan McCue <***@rotorised.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID: <***@rotorised.com>
Content-Type: text/plain; charset=ISO-8859-1

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

Forgot to include output: 'NOT IN (1,2,3)'
--
Ryan McCue
<http://ryanmccue.info/>

------------------------------

Message: 6
Date: Mon, 15 Jul 2013 22:28:31 -0700
From: Micky Hulse <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CALri7MnQzUi+OzKA0Fp2HQyRS8dASmypzK9xroSYzmhAf=d-***@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

Hi Jerry! Thanks for the help! :)

from what I can see from this trac discussion, they decided that prepare
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. ;)

------------------------------

Message: 7
Date: Mon, 15 Jul 2013 22:39:57 -0700
From: Micky Hulse <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CALri7Mm1-***@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

... 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

------------------------------

Message: 8
Date: Tue, 16 Jul 2013 16:28:15 +1000
From: Shea Bunge <***@bungeshea.com>
To: "wp-***@lists.automattic.com"
<wp-***@lists.automattic.com>
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID: <BLU177-***@phx.gbl>
Content-Type: text/plain; charset="iso-8859-1"

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

Date: Mon, 15 Jul 2013 19:43:01 -0700
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in $wpdb->prepare()?

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)?

------------------------------

Message: 9
Date: Mon, 15 Jul 2013 23:55:07 -0700
From: Micky Hulse <***@gmail.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID:
<CALri7M=rUfPLxt1G3oKnq3VhEjVKBA=FKfndD78-***@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8

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.

------------------------------

Message: 10
Date: Tue, 16 Jul 2013 01:59:21 -0500
From: Nicholas Ciske <***@thoughtrefinery.com>
To: wp-***@lists.automattic.com
Subject: Re: [wp-hackers] WP 3.5.2/multisite: How to use NOT IN in
$wpdb->prepare()?
Message-ID: <75CFCF9F-FB86-4DB5-9A61-***@thoughtrefinery.com>
Content-Type: text/plain; charset=windows-1252

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

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. :)

------------------------------

Subject: Digest Footer

_______________________________________________
wp-hackers mailing list
wp-***@lists.automattic.com
http://lists.automattic.com/mailman/listinfo/wp-hackers

------------------------------

End of wp-hackers Digest, Vol 102, Issue 16
*******************************************