[wp-trac] [WordPress Trac] #11608: wpdb->prepare() is broken

[WordPress Trac]

#11608: wpdb->prepare() is broken
--------------------------+-------------------------------------------------
 Reporter:  hakre         |       Owner:  ryan                  
     Type:  defect (bug)  |      Status:  new                   
 Priority:  normal        |   Milestone:  3.0                   
Component:  Database      |     Version:  2.9                   
 Severity:  normal        |    Keywords:  has-patch dev-feedback
--------------------------+-------------------------------------------------

Comment(by hakre):

 New patch with a function that does what is announced: To simply replace
 ''%s'' with '''%s''' while the three tokens ''%'', ''d'' and ''s'' are
 allowed. Works syntactically correct.

 Naturally this does NOT work for LIKE queries and other functions unless
 you use the full parameter because it will substitue %s with a single
 quoted value (so for this tickets scope not all of [otherwise valid] hints
 by miqrogroove do correctly apply here). The user still needs to take care
 to build valid SQL on it's own. But this time the function does what it
 said it will do, enabling proper use for various MYSQL functions that need
 to have parameters containing %'s.

 The new function provides a strict mode when configured which means that
 it would be even possible to snytactically validate the $query throwed
 into the public function ''wpdb->prepare()''. In case you like it hot, you
 should not use that feature for shure :). (WPDEBUG maybe to signal stuff
 here?).

 Please keep in mind that this ticket is about the actual data passed by
 the user as query, not the parameters (so not an escaping issue here for
 the acutal data in parameters, see comment in last paragraph as well).

 Since it was criticised that I moved out the child-safety fuzz-logic to
 handle "double quotings by ''accident'' (???)", I moved them after the
 single-quote insertion which makes that more stable as well.

 Tests (from the attached testcase, the new function only ''not'' the
 prepare function):

 {{{
    %         -> %          (Syntax: Error)
    %%        -> %%         (Syntax: Ok)
    %s        -> '%s'       (Syntax: Ok)
    %%s       -> %%s        (Syntax: Ok)
    %%%s      -> %%'%s'     (Syntax: Ok)
    %d        -> %d         (Syntax: Ok)
    %-        -> %-         (Syntax: Error)
    %d%s%%    -> %d'%s'%%   (Syntax: Ok)
    %%d%s%%   -> %%d'%s'%%  (Syntax: Ok)
    %%%d%s%%  -> %%%d'%s'%% (Syntax: Ok)
    %%%d%%s%% -> %%%d%%s%%  (Syntax: Ok)
    %%d%%s%   -> %%d%%s%    (Syntax: Error)
    SELECT FROM t1 WHERE a LIKE (%s) -> SELECT FROM t1 WHERE a LIKE ('%s')
 (Syntax: Ok)
    SELECT FROM t1 WHERE a = %s -> SELECT FROM t1 WHERE a = '%s' (Syntax:
 Ok)
    SELECT 1 WHERE table.row LIKE '%stupid' AND othertable.row = %s ->
 SELECT 1 WHERE table.row LIKE ''%s'tupid' AND othertable.row = %s (Syntax:
 Ok)
    SELECT 1 WHERE table.row LIKE '%stupid' AND othertable.row = %s ->
 SELECT 1 WHERE table.row LIKE ''%s'tupid' AND othertable.row = %s (Syntax:
 Ok)
    SELECT 1 WHERE table.row LIKE '%%stupid' AND othertable.row = %s ->
 SELECT 1 WHERE table.row LIKE '%%stupid' AND othertable.row = '%s'
 (Syntax: Ok)
    SELECT 1 WHERE table.row LIKE '%%stupidisas%%stupiddoes' AND
 othertable.row = %s -> SELECT 1 WHERE table.row LIKE
 '%%stupidisas%%stupiddoes' AND othertable.row = '%s' (Syntax: Ok)
 }}}

 Have fun.

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