WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 2 years ago

#20282 closed defect (bug) (duplicate)

$wpdb->insert incorrectly escapes numbers

Reported by: jontro Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.1
Component: Database Keywords:
Focuses: Cc:

Description

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.

Change History (1)

comment:1 duck_2 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #19016 which is already fixed in trunk.

Note: See TracTickets for help on using tickets.