I have some code that looks like this:

$stmt = $wpdb->prepare("SELECT * FROM <tablename> WHERE user_id = %d", $user_ID);

It seems to work fine...

However, I have some queries that are going to get quite lengthy so I wanted to do something more like this:

$query = ("SELECT * FROM <tablename> WHERE user_id = {$user_ID}"); // string variable
$stmt = $wpdb->prepare($query);

This is preferable way of writing it for scalability. I did not think there would be anything wrong with this. However, I get this error:

Notice: wpdb::prepare was called incorrectly. The query argument of wpdb::prepare() must have a placeholder.

I think I understand what this error is saying: it must have the query and variable(s) inside of the method so it can "prepare it itself".

Can you help me understand why this is? Security? Also, please help me find another way of pre-defining large query statements to better organize the code.

I have some code that looks like this:

$stmt = $wpdb->prepare("SELECT * FROM <tablename> WHERE user_id = %d", $user_ID);

It seems to work fine...

However, I have some queries that are going to get quite lengthy so I wanted to do something more like this:

$query = ("SELECT * FROM <tablename> WHERE user_id = {$user_ID}"); // string variable
$stmt = $wpdb->prepare($query);

This is preferable way of writing it for scalability. I did not think there would be anything wrong with this. However, I get this error:

Notice: wpdb::prepare was called incorrectly. The query argument of wpdb::prepare() must have a placeholder.

I think I understand what this error is saying: it must have the query and variable(s) inside of the method so it can "prepare it itself".

Can you help me understand why this is? Security? Also, please help me find another way of pre-defining large query statements to better organize the code.

• php
• wp-query
• mysql
• wpdb

• "This is preferable way of writing it for scalability" - what kind of scalability is it that makes you use variables directly in the query? And the error in question was just telling you that your $wpdb->prepare() syntax is wrong. See the documentation for more details, but the basic syntax is - 1st param: the SQL query with a placeholder like %s, and 2nd param: the replacement value for the placeholder in your query. – Sally CJ Commented Jan 24, 2022 at 9:23
• 1 @Sally CJ, I mean that not putting the variables directly in the query is preferable. Thanks for the documentation reference. – thisissparzo Commented Jan 24, 2022 at 16:13

1 Answer 1

$query = ("SELECT * FROM <tablename> WHERE user_id = {$user_ID}"); // string variable
$stmt = $wpdb->prepare($query);

Say your $user_ID is 9, this will get evaluated to:

$query = ("SELECT * FROM <tablename> WHERE user_id = 9");
$stmt = $wpdb->prepare($query);

and finally becomes

$stmt = $wpdb->prepare("SELECT * FROM <tablename> WHERE user_id = 9");

Now it is complaining that there is no placeholder (like %d was), which is correct. You're not using prepared statements anymore, which is bad. Because if $user_ID was '' OR 1=1 the statement becomes

$stmt = $wpdb->prepare("SELECT * FROM <tablename> WHERE user_id = '' OR 1=1");

and suddenly you've made yourself open to all kinds of SQL injections.

If you want to group your query and variables together more, I suggest using something like:

$query = [
    'sql' => 'SELECT * FROM <tablename> WHERE user_id = %d',
    'args' => [
        $user_Id,
    ],
];
$stmt = $wpdb->prepare($query['sql'], $query['args']);

Or use anything else. But do not remove the %d from the SQL query, otherwise you lose all benefits of prepare()!