Question 9975 in Wordpress Question posted in
The official Wordpress documentation can be found here.

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    $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.


  2. 0

    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
  )
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search