PHP quotes in SQL

Question

Why does this code produce an error

  $sql = "INSERT INTO accountlist VALUES (\"\", \"$user\", \"$pwd\", \"$mail\", \"$date\")";

and this one doesn't?

  $sql = "INSERT INTO accountlist VALUES ('', '$user', '$pwd', '$mail', '$date')";

I know that double quotes process variables while single quotes doesn't, so the first option should be the right one, but it is the opposite!

Because in the fist one you forget to concatenate. BUT WAIT.... — Jay Blanchard, Feb 13 '20 at 18:52
....[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Feb 13 '20 at 18:52
So you should always use prepared statements, that way you ***never*** have to worry about quotes in queries ever again! — Jay Blanchard, Feb 13 '20 at 18:54
@JayBlanchard I've already prevented Injection using mysqli_real_escape_string() when declaring variables — Simo Pelle, Feb 13 '20 at 18:55
You don't? You said, *"Why does this code produce an error"* — Jay Blanchard, Feb 13 '20 at 18:55
`mysqli_escape_string()` ***will not*** prevent SQL injection. There are ways around it. — Jay Blanchard, Feb 13 '20 at 18:56
@JayBlanchardI'm studying PHP from a tutorial and to be honest I haven't arrived to PDO yet... — Simo Pelle, Feb 13 '20 at 18:56
@JayBlanchard the first one produces an error, while the second one doesn't — Simo Pelle, Feb 13 '20 at 18:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207789/discussion-between-temp-and-jay-blanchard). — Simo Pelle, Feb 13 '20 at 18:58
@JayBlanchard Unfortunately, as a result of our charity we now must wade through the waves of questions like this one, and those asking how to vertically center a div in css. — Stephen M Irving, Feb 13 '20 at 19:49
@StephenMIrving & SalmanA yeah, I've just started learning PHP so I don't know the language basics, but I can't see how that could make my question a bad one... It's just a question made from a beginner — Simo Pelle, Feb 13 '20 at 19:59
@Temp I did not mean to offend you, so I apologize if I did. It is OK to be a beginner, we all were once. It was a more a comment about the overall state of things here, because beginner questions used to be very much frowned upon and quickly closed. — Stephen M Irving, Feb 13 '20 at 20:02
I did not get offended, the fact is that I don't undersand why a beginner question should be "quickly closed"... Someone who is trying to understand a language for the first time is deprived from the possibility to clarify his doubts — Simo Pelle, Feb 13 '20 at 20:07
For example take this question (which I asked from anothed account that got banned) https://stackoverflow.com/questions/60213796/unique-signup-method... I didn't get any answer because it was considered opinion based, so I just maintain my doubts or I have to go on another forum, until some moderators just ban my account — Simo Pelle, Feb 13 '20 at 20:12
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Feb 13 '20 at 23:25

score 3 · Answer 1 · answered Feb 13 '20 at 18:55

Because in the first one you forget to concatenate, causing an error. BUT WAIT....

....you should always use prepared statements, that way you never have to worry about quotes in queries ever again!

In the second query PHP will interpolate variables in single quotes because the whole query is surrounded by double-quotes.

Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe!

score 1 · Answer 2 · answered Feb 13 '20 at 19:58

What they're saying is dead-on-the-money ... and considerably easier, too!

Your query becomes:

INSERT INTO accountlist VALUES ("", ?, ?, ?, ?)

The ? symbols (which, notice, are not enclosed in quotes) are the parameters.

And now, each time you execute the query, you provide an array with four values in it, to be substituted left-to-right in the statement. Those values can be anything, and you don't have to care about quote-marks and such, because they are not part of the SQL. Instead, the parameters are inputs.

And if you have to do "a whole lot of this," say thousands or millions of times, you prepare the statement just once, then execute the prepared statement as many times as necessary, providing a different array of values as inputs each time.

There are also plenty of libraries out there that let you specify parameters by name, giving a hash of named values, e.g.

INSERT INTO accountlist VALUES ("", :user:, :pwd:, :mail:, :date:)

{ 'user' => 'fred', 'pwd' => 'secret', 'mail' => 'foo@bar.com', 'date' => today() }

... and the library turns it into a valid SQL statement like the one shown above.

Much more secure, much less headache, and noticeably more efficient.

score 1 · Accepted Answer · answered Feb 13 '20 at 20:10

1

To answer your question instead of going on tangents about query parameters...

https://www.php.net/manual/en/language.types.string.php explains the mechanics of using quote characters inside quoted strings.

Basically, if your PHP string is delimited by ", then the next " character is the end of the string.

But you might want to use a literal double-quote character inside the string, but not to end the string. To do this, you can put a backslash in front of it like this:

$sql = "INSERT INTO accountlist VALUES (\"\", \"$user\", \"$pwd\", \"$mail\", \"$date\")";

Then the backslashed double-quote characters become part of the string content, not the delimiter for the end of the string.

But single-quote characters inside a double-quoted string won't cause the same ambiguity, so they don't need to be backslashed. Therefore the following works without error:

$sql = "INSERT INTO accountlist VALUES ('', '$user', '$pwd', '$mail', '$date')";

The parser can tell that single-quote is not the character it's looking for to end the double-quoted string. So those single-quotes are parsed as literal characters.

This works the same way in many other programming languages, like Java, C, C++, Ruby, Python, Perl, and even in SQL itself.

This is why some people may sound impatient that you asked this question. It's a very beginner-level question that indicates that you haven't done enough reading of programming languages, and you're expecting the community to give you personalized tutoring for concepts that you should get on your own.

answered Feb 13 '20 at 20:10

Bill Karwin

538,548
86
673
828

Furthermore, it is the first time that I approach the server-side world (I'm a bit disoriented at the moment) and the first time that I run a command in this way, so here why I made that question or questions like this https://stackoverflow.com/questions/60213796/unique-signup-method – Simo Pelle Feb 13 '20 at 20:22
Anyway thanks for the answer, now it's more clear! – Simo Pelle Feb 13 '20 at 20:22
@temp Even in C++ you need to match an opening `"` with a closing `"`. – Salman A Feb 13 '20 at 20:30
@SalamanA without having single and double close you never even face up to the problem of a quote inside another quote, and in C++ I've never got into something like *running a command for another language" (I don't even know if you can do something like that) – Simo Pelle Feb 13 '20 at 20:38
@Temp, Here's some reading for you: https://en.cppreference.com/w/cpp/language/escape – Bill Karwin Feb 13 '20 at 20:55
I already knew what they are from C++, the problem was that I didn't know what does PHP single and double quotes actually mean – Simo Pelle Feb 13 '20 at 21:14
PHP is mimicking some semantics from other languages (bash and Perl for example, but others too), in which variable expansion is allowed inside double-quoted strings, but not inside single-quoted strings. – Bill Karwin Feb 13 '20 at 21:36

PHP quotes in SQL

3 Answers3