[wp-trac] [WordPress Trac] #20282: $wpdb->insert incorrectly escapes numbers

[WordPress Trac]

#20282: $wpdb->insert incorrectly escapes numbers
--------------------------+-----------------------------
 Reporter:  jontro        |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  3.1
 Severity:  normal        |   Keywords:
--------------------------+-----------------------------
 When using $wpdb->insert with a format string of "%d" the generated sql
 code is escaped using
 {{{
 ''
 }}}
 .

 $wpdb->update works in a different way passing it as an exact number. When
 using binary fields in mysql this will make a big difference as mysql does
 a string to binary conversion when passed as a string.

 Example:

 {{{

 $wpdb->insert(
         $this->relTable,
         array(
                 'contact_id' => $id,
                 'contact_is_employee' => $contact_is_employee
         ),
         '%d'
 );

 var_dump($wpdb->last_query);
 Gives the output:  "INSERT INTO `wp_5_reltable`
 (`contact_id`,`contact_is_employee`)
  VALUES ('288','0')"

 }}}

 However

 {{{

 $wpdb->update(
         $this->relTable,
         array('contact_is_employee' => $contact_is_employee),
         array(
                 'contact_id' => $id
         ),
         '%d'
 );
 var_dump($wpdb->last_query);
 gives
 "UPDATE `wp_5_reltable` SET `contact_is_employee` = 0 WHERE `contact_id` =
 '289'


 }}}

 When looking at the affected code in _insert_replace_helper in wp-db.php I
 found the following

 {{{
 $sql = "{$type} INTO `$table` (`" . implode( '`,`', $fields ) . "`) VALUES
 ('" . implode( "','", $formatted_fields ) . "')";
 }}}



 {{{
 implode( "','", $formatted_fields )
 }}}
  Will always escape all fields with ''

 The solution to me would be to do the same thing that wpdb->update does:
 No escaping the $formatted_fields array. Let wpdb->prepare take care of it
 instead.

 This would change the code to

 {{{
 $sql = "{$type} INTO `$table` (`" . implode( '`,`', $fields ) . "`) VALUES
 (" . implode( ",", $formatted_fields ) . ")";
 }}}

 When using this change insert works as one would expect.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/20282>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software