Which kind of quote combination is preferred for MySQL queries in PHP?

Question

When inserting into MySQL with PHP, I can do either of the following:

$query = 'INSERT INTO tablename SET id = "4"';
$query = "INSERT INTO tablename SET id = '4'";
$query = 'INSERT INTO tablename SET id = \'4\'';
$query = "INSERT INTO tablename SET id = \"4\"";

Is there a reason (security, performance, code readability, ...) why I should prefer one of them?

You have a good answer here : http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Clément Andraud, Apr 29 '14 at 16:01
Whichever one you want, so long as you **PREPARE YOUR STATEMENTS**. — Martin Bean, Apr 29 '14 at 16:01
Personally, I always use the double quoted wrapped queries. There's less typing plus less room for error. *That's just me* ;-) — Funk Forty Niner, Apr 29 '14 at 16:24

score 0 · Answer 1 · 2014-04-29T16:14:51.383

0

That depends on situation, in your case it should be:

$query = 'INSERT INTO tablename (id) VALUES (4)';

In case that you would want to insert an integer and a string, it should be:

$query = "INSERT INTO tablename (id, name) VALUES (4, 'New York')";

In case that you would want to build query by directly inserting variables into the query, it should be:

$query = "INSERT INTO tablename (id, name) VALUES ($id, '$city')";

Building a query like this is prone to SQL injection.

In case that you would want to build query by using for example PDO library, it would be:

$query = 'INSERT INTO tablename (id, name) VALUES (:id, :city)';

edited Apr 29 '14 at 16:14

answered Apr 29 '14 at 16:07

I'll reserve the downvote in case you don't correct the answer, but you **can** do `INSERT INTO ... SET col = ...`. – Marcus Adams Apr 29 '14 at 16:10
@MarcusAdams Interesting, I have never ever seen such usage of `INSERT` statement. – Apr 29 '14 at 16:13
Why would use single quotes on the first and last statements? – Marcus Adams Apr 29 '14 at 16:16
@MarcusAdams Because in case that I would use `""` then PHP interpreter would look for variables inside the string. – Apr 29 '14 at 16:17
@MarcusAdams In case that I wrap string inside `""` and I don't use variables inside the string, then it is for readability purposes, `"Jake's mom"` is better readable then `'Jake\'s mom'`. – Apr 29 '14 at 16:19

score 0 · Answer 2 · answered Apr 29 '14 at 16:07

0

Like Martin Bean mentioned, it would be a good idea to use prepared statements:

$stmt = $con->prepare("INSERT INTO tablename SET id=?");
$stmt->bind_param(4);
$stmt->execute();
$stmt->close();

answered Apr 29 '14 at 16:07

rmcfrazier

444
4
8

Marcus Adams · Accepted Answer · 2014-04-29T16:40:38.607

I think that this is the most common format:

$query = "INSERT INTO tablename SET id = '4'";

So, you can easily come back and perform variable expansion like this:

$id = 4;
$table = "tablename";
$query = "INSERT INTO $table SET id = '$id'";

If the 4 is something passed in by the user, you would instead use parametrized queries and bind the parameter to avoid SQL injection:

$id = $_POST["id"];
$table = "tablename";
$stmt = $con->prepare("INSERT INTO $tablename SET id=:id");
$stmt->bindParam(":id", $id);
$stmt->execute();

Which kind of quote combination is preferred for MySQL queries in PHP?

3 Answers3