Passing in MySQL prepare statement parameter separately throwing error

$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()!

Related Posts: