1
$arr = [];
array_push( $arr, 'wp_table', 1, '2013-12-24', 3, 'NULL');
$sql_prepare = "INSERT INTO %s (id, datea, one, two) VALUES (%d, %s, %d, %s) ON DUPLICATE KEY UPDATE one = VALUES(one), two = VALUES(two);";

$wpdb->query( 
      $wpdb->prepare( $sql_prepare,
                      $arr )
);

The above code seems to result in an issue with the datea value 2013-12-24.

[You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wp_table' (id, datea, one, two) VALUES (1, '201' at line 1]

"INSERT INTO 'wp_table' (id, datea, one, two) VALUES (1, '2013-12-24', 3, 'NULL') ON DUPLICATE KEY UPDATE one = VALUES(one), two = VALUES(two);"

Without $wpdb->prepare (...) the query works just fine.

$wpdb->query( "INSERT INTO $table_name (id, datea, one, two) VALUES (1, '2013-12-24', 3, NULL) ON DUPLICATE KEY UPDATE one = VALUES(one), two = VALUES(two);");

Any advice on how to handle the date value? What am I doing wrong? Thank you for further explanation.

/edit I see I might be overusing prepare but at least datea, one, two are user-submitted input

  • +1 was having the same problem yesterday. Ended up removing prepare from my code :/ You can't use prepare on all places. Specially for table names. – Sisir Jan 3 '14 at 5:06
0

Just look at the generated string:

INSERT INTO 'wp_table' (id, datea, one, two) VALUES (1, '2013-12-24', 3, 'NULL') ON DUPLICATE KEY UPDATE one = VALUES(one), two = VALUES(two);

The problem is the quotes around the table name. (Your hand-written query has no quotes around the table name, and works.) prepare is intended for dynamic user supplied, content, and it is intended for use with strings and numbers. The table name is neither-- not in the same way that your content is. It is going to supply quotes, which is going to break your query.

You should be able to simply write the table name into the query.

$arr = array();
array_push( $arr, 1, '2013-12-24', 3, 'NULL');
$sql_prepare = "INSERT INTO {$wpdb->prefix}table (id, datea, one, two) VALUES (%d, %s, %d, %s) ON DUPLICATE KEY UPDATE one = VALUES(one), two = VALUES(two);";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.