Quotations Around PHP String Variables That Hold Database Field Names

[mojtabaavahdati]

My question is a little out of curiosity. We should put quotes around php string variables that hold database field names when we are doing a query(from an object of mysqli class for example):
$this->connection->query(" SELECT * FROM a_table WHERE name=‘$name’ ");
so here, we wrap $name by quotes, because when the query is sent, what goes to database is only plain text, and database needs to parse it and so needs to see those ’ & ’ to realize that the thing between them is a string, but on the other hand, for php itself, we never need to do the same, example:
$_SESSION[‘name’]=$name;
php will find out that $name is a string,
now my question is, how can php understand that $name is a string if quotations don’t get saved inside the string variable. When all the code becomes plain text, how does php assign that string without quotations to that $_SESSION[‘name’] in this example? (Except that it happens before php starts its interpretation…?). Thank you for your help.

[John_Betong]

@mojtabaavahdati

$this->connection->query(" SELECT * FROM a_table WHERE name=‘$name’ ");

I have had trouble with quotes especially if there are spaces or hyphens in $name.

I have learnt through bitter experience to always surround $name with backticks which is what PhpMysql recommends:

$this->connection->query(" SELECT * FROM `a_table` WHERE name=`$name` ");

Further Reading:

Using backticks around field names

[mojtabaavahdati]

Okay, yes,thank you, I use backticks too, but my main question is about something else.

[John_Betong]

My understanding is that PHP accepts whatever the $name variable contains or gives an error.

MySql interprets the complete query string which may contain some odd characters which are best surrounded by backticks.

Can you supply examples which may clarify the post?

[spaceshiptrooper]

I believe anything wrapped inside double quotes and single quotes are automatically a string in PHP up until you specify what the data type is. For example

$var = 'true';

Is not a Boolean, but a string that has Boolean value.

$var = true;

Is a Boolean. But we can also do type casting as well.

$var = (bool) 'true';

This forcefully converts the data type string into a Boolean.

[mojtabaavahdati]

Thank you, but I think I’ve given an example already, if you remove those ’ 's or ` `s, that surround $name, the query won’t work.

[John_Betong]

Perhaps I did not make myself clear

I was wanting examples of $name contents.

I prefer setting a $query like this:

$sql = <<< ____TMP

  SELECT  * 
  FROM     `a_table`
  WHERE   name=`$name`

____TMP;
// DEBUG
   echo '<pre>'; echo $sql; echo '</pre>';

  $ok = $this->connection->query( $sql );

Edit:
Enclosed $sql in <pre> </pre> tags to prettify output.

When developing I have a function fred( $val=‘Yes we have no $val???’ ){…} which is easier to type and formats the large majority of variables and arrays. Usage: fred( $sql );


Output:

 SELECT  * 
  FROM   `a_table`
  WHERE   name=`Just testing`

The majority of coding time is spent debugging and time is reduced if one can quickly scan code rather than trying to ensure the syntax is correct especially on very long lines. Modern processors are fast and seldom are compact scripts required.

[spaceshiptrooper]

I would also like to add, you should be using Prepared Statements when dealing with user inputs. One should never trust any kind of user input regardless if the user doesn’t know what to do.

[Mittineague]

I didn’t find any documentation to back up my hypothesis, but my take is it has to do with what I call “pass through”. By that I mean how many times the code gets processed.

PHP treats strings inside single quotes as string literals. eg.

$var = 'foo';
$bar = "$var"; // $bar is the string foo
$baz = '$var'; // $baz is the string $var

When the query is assigned, PHP removes the single quotes and keeps the variable name.
Then when PHP passes the query to the database, that is when the value of the variable is substituted for its name.

You can see this type of thing with backslash escaping. some times \x is enough, but other times you need to escape the escape \\x or even double that many \\\\x

[benanamen]

Forget the backticks. You should NEVER EVER have variables in your query in the first place. You need to use Prepared Statements. (And dont name your DB columns using reserved words.)