[wp-trac] [WordPress Trac] #12819: wpdb::prepare support for null

[WordPress Trac]

#12819: wpdb::prepare support for null
-------------------------+-----------------------------
 Reporter:  roxaz        |       Owner:  ryan
     Type:  enhancement  |      Status:  reopened
 Priority:  normal       |   Milestone:  Future Release
Component:  Database     |     Version:
 Severity:  normal       |  Resolution:
 Keywords:  needs-patch  |
-------------------------+-----------------------------

Comment (by jbutkus):

 Replying to [comment:10 miqrogroove]:
 > That syntax is neither used nor supported by wpdb::prepare, thankfully.
 What we're discussing is a simple matter of replacing the three supported
 placeholders with a literal NULL.  The only syntactical complication is
 that this operation must avoid the regexp quoting of strings for the null
 args.

 Well, I may have missed the documentation part on this one, but, actually,
 it works and gives expectable results:
 {{{
 $wpdb->prepare(
     '
         INSERT INTO foo ( val1, val2, val3 )
         VALUES ( x\'%2$s\', %3$d, \'%1$s\' )
         ON DUPLICATE KEY UPDATE SET val2 = %3$d
     ',
     'foo-\'bar\'-baz',
     'ae4f',
     12345
 );
 }}}
 gives
 {{{
 INSERT INTO foo ( val1, val2, val3 )
 VALUES ( x'ae4f', 12345, 'foo-\'bar\'-baz' )
 ON DUPLICATE KEY UPDATE SET val2 = 12345
 }}}

 Of course, the wpdb::prepare() does not support quoting of positionally-
 referenced strings, thus one has to add quotes on it's own.


 Replying to [comment:10 miqrogroove]:
 > Random hashes are not needed here, because in a worst-case replacement
 strategy it would be adequate to use a static placeholder string that is
 invalid in SQL.

 It is hard to say, what is "invalid in SQL".

 What if I would write such statement:
 {{{
 $wpdb->prepare(
     'INSERT INTO foo ( val1, val2 ) VALUES ( %s, %s )',
     'this is `"invalid"` SQL `DROP TABLE students` statement',
     NULL
 );
 }}}

 What value would you have to choose, as invalid SQL, which would correctly
 match only actual NULLs, and not anything else, that user may input?

 The key may not be purely random - it may be fixed for every replacement
 (i.e. 1kb long string based on curent NONCE_SALT, etc.).
 Just that I would think about it being "long", more than "invalid in SQL".
 And by long - anything, that might only be generated as correct input to
 statement, as 1 in 100 billions, or something. I.e. more likely user must
 target that intentionally, than have it as a valid input (by writing
 documentation, for example).

 Of course, a patch may include modifying "escape_by_ref", to NOT target
 specific values, i.e. {{{"this is 'NULL' placeholder"}}}. But that may
 cause an error, if one is to write **just** this value.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/12819#comment:11>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software