Passing array of strings to a SQL statement in a WordPress plugin

Question

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!

Answer 1

$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.' );