Quotes and double quotes in SQL command with PHP

Question

I found a piece of code like this:

<?php
  class myClass {
      function myFunc(&$par1) {
          // [...]
          $val2 = $par1->field1;
          // [...]
          $val3_escaped = mysql_real_escape_string($someVar2);
          $cmdInsert = "insert into tab1(col1,col2,col3, col4) values(1,'$val2',\"$val3_escaped\",'val4')";
          $result = mysql_query($cmdInsert, $myConnection);
    }
  }
?>

I'm wondering what is the difference between '$val2' and \"$val3_escaped\"? Are both valid? I guess that should be correct only with single quote, but it seems to works fine only with \". What's the right sintax?

Thanks.

Use prepared statements. Please protect yourself against SQL injection. Then you don't even need to worry about quotes vs double quotes. — ctwheels, Jun 28 '17 at 15:29
The right syntax would be to bin all of it, stop using the defunct `mysql_` extension and move to `mysqli_` or `pdo` prepared statements with bound parameters... — CD001, Jun 28 '17 at 15:29
It's a very old existing CRM, I'm a Java programmer and I'm trying to fix a little bug on that application. Thanks anyway. — Alessandro, Jun 28 '17 at 15:43

score 3 · Accepted Answer · answered Jun 28 '17 at 15:31

3

The escape character is for the benefit of PHP (since the PHP string is delimited by " characters). It has nothing to do with what is sent to MySQL.

Single quotes delimit strings in SQL.

Double quotes have no meaning in standard SQL. In MySQL they can mean the same as ' or ` depending on the ANSI_QUOTES setting. Avoid them.

Warning: You are using an obsolete database API which has been removed entirely from the latest version of PHP. You should use a modern replacement.

With a modern replacement you could use prepared statements with placeholders instead of worrying about quoting variables.

answered Jun 28 '17 at 15:31

Quentin

914,110
126
1,211
1,335

Thanks for the answer, that's a very old existing CRM, I'm a Java programmer and I'm trying to fix a little bug on that application. Just one question... php substitutes any variable that start with $ inside a string? You don't need to close quotes and concatenate the variable? – Alessandro Jun 28 '17 at 15:49
Ok, I see... Thank you so much. – Alessandro Jun 28 '17 at 16:15

pro_cheats · Answer 2 · 2017-06-28T16:22:39.187

I agree to this answer by Quentin, but since you still have doubts, here is the explanation. Hope this helps.

'$val2'

Variables and escape sequences for special characters will not be expanded(to the actual values they represent) when they occur in single quoted strings. For Example -

// Outputs: This will not expand: \n a newline
echo 'This will not expand: \n a newline';

// Outputs: Variables do not $expand $either
echo 'Variables do not $expand $either';

So in your case, '$val2' will just add $val2 string to the query, not its value.

\"$val3_escaped\"

If the string is enclosed in double-quotes ("), PHP will interpret all the variables and escape sequences for special characters. (ex - \n, \t, etc.).

\" - will just print the double-quote when used inside a double quote. :) So in your case, PHP will get its actual value and add to the query (Due to the outer double quotes).

Example -

$juices = array("apple", "orange", "koolaid1" => "purple");
//Outputs : He drank some apple juice.
echo "He drank some $juices[0] juice."

You can refer to PHP:Strings Manual for further details.

That's exactly what I'm looking for. So the right way should be \"$val3_escaped\", otherwise it cannot replace the var with its value. Now it's clear to me. Thanks. — Alessandro, Jun 28 '17 at 16:14
I've already upvoted your answer, but sorry, now I realize that the entire string is enclosed in double quotes, so the other answer is the most appropriate one. Thanks. — Alessandro, Jun 29 '17 at 07:08

Quotes and double quotes in SQL command with PHP

2 Answers2