I have an issue with passing an array of strings to a SQL statement in a WordPress plugin, because the prepare method adds a backslash before each apostrophe.
// I have an array of strings.
$the_array = ['red', 'green', 'blue'];
// I convert the array into a single string containing comma separated values surrounded by apostrophes.
$the_string = "'" . implode("', '", $the_array) . "'";
// And I pass the string to the query, using prepare (necessary to avoid SQL injections).
$db_options = $wpdb->get_col(
$wpdb->prepare(
"SELECT option_name
FROM $wpdb->options
WHERE option_name NOT IN (%s)",
$the_string
)
);
The SQL statement generated by this code will be this, with added backslashes:
SELECT option_name
FROM wp_options
WHERE option_name NOT IN ('\'red\', \'green\', \'blue\'')
but this returns all values from the tables, ignoring the NOT IN part!
How can I generate from the PHP code, instead, the correct statement below
(while also keeping the prepare method)?
I need this:
SELECT option_name
FROM wp_options
WHERE option_name NOT IN ('red', 'green', 'blue')
Thank you!
1 Answer
$wpdb->prepare()
accepts a single array of arguments as the second parameter, e.g.
$wpdb->prepare(
"SELECT * FROM table WHERE field1 = %s AND field2 = %d AND field3 = %s",
array( 'foo-bar', 123, '[email protected]' )
)
Therefore you can use an array of placeholders with the SQL command (the 1st parameter), like so:
// Create an array of placeholders, e.g. array( %s, %s, %s ) in your case.
$placeholders = implode( ', ', array_fill( 0, count( $the_array ), '%s' ) );
$db_options = $wpdb->get_col(
$wpdb->prepare(
// 1st parameter: The SQL command.
"SELECT option_name
FROM $wpdb->options
WHERE option_name NOT IN ($placeholders)",
// 2nd parameter: A single array of arguments.
$the_array
)
);
Additionally, note that if you pass an array like the $the_array
above, then there must be no 3rd, 4th, 5th, etc. parameters; otherwise, you’ll get an error. So pass either individual arguments or a single array of all arguments, but never both!
// Good
$wpdb->prepare( "...", 'foo', 123, 'etc.' );
$wpdb->prepare( "...", array( 'foo', 123, 'etc.' ) );
// Bad - there is a 3rd parameter ('etc.')!
$wpdb->prepare( "...", array( 'foo', 123 ), 'etc.' );