wpdb prepare syntax

7 Views
$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]

Read More

"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

Post Views: 7

Related posts

1 comment

  1. 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);";

Comments are closed.