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

admin管理员组

文章数量:1289497

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!

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!

• plugin-development
• mysql
• array
• query-string
• query-variable

Share Improve this question asked Jul 28, 2021 at 19:14 Ramona MoreaRamona Morea 4355 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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

本文标签： mysqlPassing array of strings to a SQL statement in a WordPress plugin