[wp-trac] Re: [WordPress Trac] #4553: Consider using local prepared-statement/sprintf()-like system for last-second SQL escaping

[WordPress Trac]

#4553: Consider using local prepared-statement/sprintf()-like system for last-
second SQL escaping
---------------------------------------------------------------+------------
 Reporter:  markjaquith                                        |        Owner:  markjaquith
     Type:  task                                               |       Status:  assigned   
 Priority:  normal                                             |    Milestone:  2.3 (trunk)
Component:  Security                                           |      Version:  2.3        
 Severity:  normal                                             |   Resolution:             
 Keywords:  sql prepared statement sprintf injection security  |  
---------------------------------------------------------------+------------
Comment (by markjaquith):

 Old:
 {{{
 $var = $wpdb->escape($var);
 $var2 = $wpdb->escape($var2);
 $limit = (int) $limit;
 $wpdb->query("UPDATE $wpdb->tablename SET foo = '$var' WHERE blah =
 '$var2' LIMIT $limit");
 }}}

 New
 {{{
 $wpdb->query($wpdb->prepare("UPDATE $wpdb->tablename SET foo = '%s' WHERE
 blah = '%s' LIMIT %d", $var, $var2, $limit));
 }}}

 Notes:

  * Variables passed to {{{$wpdb->prepare()}}} should '''not''' be pre-
 escaped.
  * All uses of '%s' should either be quoted (for values) or strictly
 filtered through a list of allowed values (for things like %s representing
 a column name for ORDER BY).  If the latter, this should be done as close
 to the SQL query as possible so that we can verify that it is not a SQL
 injection hole.
  * sprintf() takes care of int-casting %d variables.
  * As "%" is a special char, it is recommended that you concatenate that
 char into a string when doing LIKE queries instead of putting the "%"
 character into the query (as you will have to escape it with %% if you use
 it in the query).

 Questions, concerns?  Would be good to raise them before we start changing
 things.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/4553#comment:4>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software