String variable in SQL query within WordPress getting an error when not using single quotes

This code is in a custom WordPress plugin.

if (isset($_POST['send_user'])) {
    $username = sanitize_text_field( $_POST['username'] );
    global $wpdb;
	$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = $username");

This code does not work properly and it gets an error message with provides a NULL value within $customer_id.

When I manually change the code to:

$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = 'username'");

This works perfectly and provides the user_id I am looking for. Why? What am I missing here?

You need to use placeholders for passing data to the query, instead of directly injecting variables in the query string.

Your query should look like this:

$customer_id = $wpdb->get_var(
  $wpdb->prepare(
    "SELECT user_id FROM wp_wc_customer_lookup WHERE username = %s",
    $username
  )
);

It's also best practice to pull the database prefix from class properties:

$customer_id = $wpdb->get_var(
  $wpdb->prepare(
    "SELECT user_id FROM {$wpdb->prefix}wc_customer_lookup WHERE username = %s",
    $username
  )
);

$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = $username");

does not work because the string variable $username once added into this line of code will look like this:

$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = username");

This breaks because the WHERE clause is looking for a string format such as "username". In order to make this work within the SQL statement, you cannot add "username" into the SQL because it will break it - once its runs it will look like this:

$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = "$username"");

The first double quote exits the SQL statement and the $username is never added. In order to make this work you have to put single quotes around the string variable - like this:

$customer_id = $wpdb->get_var("SELECT user_id FROM wp_wc_customer_lookup WHERE username = '$username'");

This satisfies the parameters needed.

Many get confused (which happened to me) because this code works:

if (isset($_POST['send_user'])) {
$username = sanitize_text_field( $_POST['username'] );
$tablename = $wpdb->prefix."wc_customer_lookup";
global $wpdb;
$customer_id = $wpdb->get_var("SELECT user_id FROM $tablename WHERE username = 'username'");

and when using an int variable - it works as well. Like this:

if (isset($_POST['send_user'])) {
$username = sanitize_text_field( $_POST['username'] );
$tablename = $wpdb->prefix."wc_customer_lookup";
$number = 5;
global $wpdb;
$customer_id = $wpdb->get_var("SELECT user_id FROM $tablename WHERE int = $number");

So without thinking about it, you may conclude that using a string variable would work as well. Not the case, and it's because the string in the SQL argument must include quotes.

When using something like:

 $wpdb->prepare(
 "SELECT user_id FROM $tablename WHERE username = %s",
 $username

this will work just fine because the prepare class adds the single quotes to the SQL statement so you don't have to worry about it.