SQL – Single quotes and backticks for query

  • Resolved ace0930

    (@ace0930)


    3 years, 7 months ago

    I have referred to Stack Overflow, sorry because it might be a stupid question because I’m learning SQL.

    Let’s say I have the below code:

    
function get_data ( $name ) {
   global $wpdb;

   $wpdb -> get_var (
      $wpdb -> prepare (
         "SELECT value FROM $table_name WHERE name = '$name'"
      )
   );
}

    1) May I know why I have to use the single quotes around the $name? Wouldn’t it become a string that prints $name? Like the below:

    
$test = 'Hii';

echo "My friend, '$test'";

    Which prints My friend, $test, instead it should be:

    
$test = 'Hii';

echo "My friend, $test";

    Which prints My friend, Hii.
    2) For good practice, should I use backticks for table and column names always like the below ( I don’t know how to escape the backtick here, I’ll imagine the backtick as ! ):
    $wpdb -> prepare ( "SELECT !value! FROM $table_name WHERE !name!= '$name'" )

Viewing 10 replies - 1 through 10 (of 10 total)
  • Moderator bcworkz

    (@bcworkz)

    3 years, 7 months ago

    In PHP, vars in double quoted strings are automatically replaced with their assigned values. So the single quotes within are just characters, they are not parsed as quotes per se. Once the var is replaced with it’s value, then the entire thing becomes one string value. PHP doesn’t “see” the single quotes as having any special meaning.

    In any case, you’re using prepare() incorrectly πŸ™‚
    You need to use sprintf() style syntax. Any vars in the string are specified by placeholders like %s, and the actual vars are passed as subsequent args.
    $wpdb -> prepare ( "SELECT !value! FROM %s WHERE !name!= '%s';", $table_name, $name )

    Yes, table and other DB element names should be quoted with backticks. You just cannot use them as such in the forums because they are code delimiters.

    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz You’re right and I should look at the doc πŸ™‚

    But the doc said: “Arguments may be passed as individual arguments to the method, or as a single array containing all arguments”

    1) In your answer, there are two arguments, shouldn’t we use the array to contain the two arguments?
    2) I store the table name in a constant, does it need with the backtick too? But how?

    
define ( 'table_name', 'wp_table_name' );

$wpdb -> prepare ( "SELECT !value! FROM " . table_name . " WHERE !name! = %s", $name )

    3) Why use the sprintf()-like syntax? Variables in double quotes can be parsed within it in PHP already, why do we need sprintf to make placeholders and replace them with arguments? Very weird to me…

    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz Not sure if this is a bug, the below code won’t work:

    
$current_status = $wpdb -> get_var (
   $wpdb -> prepare (
      "SELECT !value! FROM %s WHERE !name! = %s", $table_name, $name
   )
);

    But the below code work:

    
$current_status = $wpdb -> get_var (
   $wpdb -> prepare (
      "SELECT !value! FROM !$table_name! WHERE !name! = %s", $name
   )
);

    P.S, I use ! to replace the backticks here. Notice the backtick around $table_name, not sure if it is needed but it works

    Moderator bcworkz

    (@bcworkz)

    3 years, 7 months ago

    You’re right about passing vars in an array. I had the actual sprintf() syntax in my head. Sorry for any confusion I caused.

    Even though including a variable in the first string arg works, it’s incorrect. All vars should be passed in the second arg’s array.

    $wpdb -> prepare ( "SELECT !value! FROM %s WHERE !name!= '%s';", array( $table_name, $name ,) )

    Why do this when a double quoted string alone would work? The prepare method needs the vars broken out individually so it can properly escape anything that needs it. While it could have parsed the vars out of a single string arg, it’s not a very efficient process. Having it all separated out just works better.

    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz Sorry but your code still doesn’t work πŸ™

    $wpdb -> prepare ( "SELECT !value! FROM %s WHERE !name! = %s", array ( $table_name, $name ) )

    And Your original code:
    $wpdb -> prepare ( "SELECT !value! FROM %s WHERE !name!= '%s';", array( $table_name, $name ,) )

    Until I insert the !$table_name! inside then it works. Why??????
    $wpdb -> prepare ( "SELECT !value! FROM !$table_name! WHERE !name! = %s", $name )

    Below is the full code and you can try:

    
function get_data ( $name ) {
   global $wpdb;

   $table_name = $wpdb -> prefix . testing;

   $current_status = $wpdb -> get_var (
      $wpdb -> prepare (
         "SELECT !value! FROM %s WHERE !name! = %s", array ( $table_name, $name )
      )
   );

   return $current_status;
}
    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz I found a relevant question although I don’t understand what the answer is talking about: https://wordpress.stackexchange.com/questions/25764/cant-pass-table-to-wpdb-prepare

    quotation marks are added to the table name

    So $table_name = $wpdb -> prefix . testing; will become "wp_testing" according to the answer? Why is that so?

    And you said:

    Even though including a variable in the first string arg works, it’s incorrect. All vars should be passed in the second arg’s array.

    πŸ™‚

    Moderator bcworkz

    (@bcworkz)

    3 years, 7 months ago

    Β―\_(ツ)_/Β―
    I think it’s because $wpdb properties such as table names are essentially static values, so needn’t be escaped. I suggest your try echoing out the returned string from $wpdb->prepare() after trying different variations so you can see first hand what it does and doesn’t do. And I apparently need to take my own advice πŸ™‚

    Also note from the prepare() docs page:

    All placeholders MUST be left unquoted in the query string

    !name!= '%s' would be incorrect.

    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz Is that mean the $table_name shouldn’t be replace by %slike the below:
    $wpdb -> prepare ( "SELECT !value! FROM !$table_name! WHERE !name! = %s", $name )

    Instead and although the below matches the principle of sprintf but it won’t work with $wpdb -> prepare:
    $wpdb -> prepare ( "SELECT !value! FROM %s WHERE !name! = %s", array ( $table_name, $name ) )

    Moderator bcworkz

    (@bcworkz)

    3 years, 7 months ago

    Looking at the examples on the prepare() docs page, they don’t %s table names. I assume prepare() does something with values destined for %s that’s inappropriate for table names. Have you tried looking at what prepare() returns when we do %s table names? It ought to be clear why it’s not done.

    I may do so myself when I have more time. I’m not that clear on what prepare() literally does. Regardless, $table_name is essentially a static value, so needn’t be escaped anyway. The entire point of prepare() is to escape (and quote?) truly variable data that could be suspect.

    Thread Starter ace0930

    (@ace0930)

    3 years, 7 months ago

    @bcworkz It returns NULL when I echo prepare () if I %s the table name.

    And I found this line in the source code:
    $query = preg_replace( '/(?<!%)%s/', "'%s'", $query ); // Quote the strings, avoiding escaped strings like %%s.

    I believe it becomes 'table_name' ( note the quotes ) if %s the table name, and 'table_name' is different from table_name.

Viewing 10 replies - 1 through 10 (of 10 total)

The topic ‘SQL – Single quotes and backticks for query’ is closed to new replies.