Single quotation and double quotation


#1

I have a text like the above.
Let’s make the text above as a variable like the below.

code1

$myVar='singleQuote(') and doubleQuote{"}';

The code1 above produces syntax error, unexpected ‘)’

code2

$myVar="singleQuote(') and doubleQuote{"}";

The code2 above produces same error, unexpected “}”

So I change it like the below.

code3

$singleQuote="'";
$myVar='singleQuote('.$singleQuote.') and doubleQuote{"}';

The code3 above is fine.

I like to save $myVar in database.

mysql_query("UPDATE myTable SET say='$myVar'
WHERE ID=1");

However, it seems not to be updated.

If $myVar has no any quotation mark, it is well updated.
But with single or double quotation mark, it is not updated.

How can I update the record with single and double quotation marks?


#2

What you are seeing is the exact reason why you should not insert variables via sql without escaping them first.

This is how SQL injection works. Because the quotes are parsed by the query other people can modify the statement and do all sorts of nasty things.

First off you need to stop using mysql_query as it is depreciated http://php.net/manual/en/function.mysql-query.php. You can instead use mysqli_query http://php.net/manual/en/mysqli.query.php which is pretty similar in syntax. There are a few differences though so you’ll need to pay careful attention in particular to the connection variable which needs to be used in a few places in the script.

then you need to escape the variables before inserting http://php.net/manual/en/mysqli.real-escape-string.php there are some examples on this page of procedural style.

in your case it will look something like this though…

$myVar = mysqli_real_escape_string($connection, $myVar);

Or ideally switch to prepared statements as it is much safer as it is more difficult to forget to escape a variable. Once you get the hang of it it is pretty easy.

hth


#3

As @Noppy has suggested, use prepared statements. Your sql reduces to:

$sql = 'UPDATE myTable SET say=? WHERE ID=?';

Needless to say, but I’ll say it anyways, the mysql functions are not longer in the latest supported pgp versions. You need to move at least to mysqli functions. If you are following a tutorial then stop and try to find something written in the last 5 years or so.

Going back to first part of your question, the nowdoc syntax can help out.

$myVar = <<<'EOT'
'singleQuote(') and doubleQuote{"}'
EOT;

echo $myVar . "\n";

But again, use prepared statements for all of this nonsense.


#4

I completely agree with the suggested use of prepared statements. Your code example is just a minimal example of the difficulty that can happen while trying to differentiate enclosure quotation marks from quotation marks in the content. Believe me, code can get a lot messier than that example.

Yes, the problem can be solved by alternating between single and double, escaping, entitizing, escaping escapes, string concatenation etc. Though the code may end up “working”, it will be difficult for a programmer to read it. IMHO, “Why care, I will never see this code again” is very often a delusional assumption. Do your future self a favor and strive to write readable code.

True, you will still have times when strings need to be “massaged” but by using prepared statements - which deal with this problem “automagically” - your query code will not only be easier to read but perhaps more importantly much safer against malicious use and unintended errors.