Double quotes and single quotes around variables. Which one is faster in SQL query?

I have seen two different kinds of quotes around variables in SQL query like the following two. Which one executes faster?

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '" . $full_name. "'")

PHP:

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '$full_name'")

PHP:

 

@sarahk, Thank you.

I understand that single quotes in PHP is faster. I mainly want to know how it is in Mysql.

Could you please explain why wrapping variables with curly brackets is faster and safer?

 

@sarahk, Thank you for letting me know that curly quotes tells PHP where the variable starts and stops and therefore is faster. I didn't know that before.

I will try to find out if the situation is the same in Mysql.

 

If the value for $full_name is supplied by the user then you really should be using a prepared statement otherwise you're leaving yourself a sitting duck for SQL injection attacks. When you have any situation where data is being supplied to the query, try and get into the habit of ALWAYS using prepared statements, that way you don't accidentally open a security hole if you change the source of the data supplied to the query to data supplied by the user. NEVER trust user submitted data no matter how well you trust your users!

 

Thanks. Is using mysqli_real_escape_string() sufficient?

 

No, it's not. If you're already using mysqli_, then you have access to using prepared statements, so do it. Note, if the values used in the query is NOT provided by the user in any way, but comes from hardcoded values in the code, or from separate SQL-queries, you might not need to run a prepared statement.

As for the quoting, the single quotes are faster, if only by nanoseconds (if those queries add up, you will notice the difference), simply because the first example concats the query, while the second just quotes the variable - concating is ALWAYS slower, and shouldn't be used in query-building at all, since it MIGHT lead to unexpected results depending on what you are concatenating.

 

Thanks for pointing it out. That really makes sense. My two examples don't really have any difference in quote using. And my first example unnecessarily splits the query with concatenation so it is supposed to be slower.

And because @sarahk said that using curly brackets to tell PHP where the variable starts and stops is faster, it looks like her example is faster than my second example. So let's compare that one with yet another example which uses single quotes to wrap the whole query:

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '{$full_name}'");

PHP:

mysqli_query($conn, 'SELECT * FROM `mytable` WHERE `full_name` = "' . $full_name . '"');

PHP:

Is the last one even faster because it uses single quotes to wrap the whole query so that PHP does not have to figure out if there are variables inside the query?

 

@sarahk, Thank you.

Somehow I just couldn't get the prepared statement case working and it kept showing errors. Anyway, because prepared statement is supposed to be slower when it is repeatedly used for only one query like that, I gave it up and only tested the first two cases.

After the testing, I found that case 2 is a bit faster most of the time, as expected. And when the iteration increases to 30000, it can even be 1.00x second faster.

I will stick to the syntax of case 2 then.

 

Remember, though - if you have a variable in your string, and you want it to be evaluated, you have to use double quotes around the PHP string (forcing you to use single quotes around SQL strings).

If $thing is 5, "Insert $thing" will insert 5, but 'Insert $thing' will cause a SQL error (unquoted string and trying to insert a string to a numeric field).

 

@Rukbat Thanks for reminding me.

 

This, THIS, A THOUSAND TIMES THIS!!!

Generally speaking this is 2017 not 2007, unless it's something you cannot bind like a table name, you have ZERO damned business putting variables into your bloody query string in the first place, rendering the entire question moot.

IF you are even THINKING about doing this:

mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '{$full_name}'");

You've gone full Pakled and have no business working with mysqli! -- that SHOULD NEVER BE DONE!!! It's one of the entire reasons we've been told FOR NEARLY A DECADE NOT to use the old mysql_ functions!

... and for *** sake if you're worried about an overhead difference, put on the big boy pants and use the object model, not the pointless halfwit wrapping procedures that should NEVER have even existed in the first damned place!

Oh, and @sarahk your attempt at a bench is flawed as most SQL servers cache results... again though NOT that either of the first two "case" you coded should EVER be used; and really if you're going to query a ***ton of times the same query that's where POEM comes into play... which is why inside the loop the ONLY thing that would/should be done is the execute. EVERYTHING else goes outside the loop.

Hence why -- well, let's say you had an array of names you wanted to pull data for.

$users = [
	'John', 'River', 'Rory', 'Amy', 'Mel'
];
$stmt = $link->prepare('
	SELECT *
	FROM users
	WHERE full_name = ?
');
$stmt->bind_param('s', $name);

foreach ($users as $name) {
	$stmt->execute();
	$stmt->bind_result($result);
	$stmt->fetch($stmt);
	// do whatever it is you plan on with the result here
}

Code (markup):

This is also an excellent example of why I consider PDO superior to mysqli since you don't have to screw around with bindResult.

$users = [
	'John', 'River', 'Rory', 'Amy', 'Mel'
];
// assuming $db is connected PDO object
$stmt = $db->prepare('
	SELECT *
	FROM users
	WHERE full_name = ?
');
$stmt->bindParam(1, $name);
foreach ($users as $name) {
	$stmt->execute();
	if ($row = $stmt->fetch()) {
		do {
			// do whatever it is you plan on with the $row here
		} while ($row = $stmt->fetch());
	} else echo 'No matches found for "', htmlspecialchars($name), '"<br>';
}

Code (markup):

Again though, EITHER damned way that variable has ZERO damned business in that query string!

 

@deathshadow Thank you for your suggestion. After seeing so many recommendations of prepared statement, I will give it more considerations.

 

With PDO if you just want to grab the entire result set, to save having to mess around with a loop, you could just use the fetchall method instead of fetch. PDO also has the advantage of the ability to use named placeholders

 

Just a quick update - very often users may not have access to using prepared statements even if they have mysqli enabled. I can't even count how many times I saw users having problems with prepared statements (even if mysqli extension itself was working perfectly), especially on GoDaddy and HostGator servers. In short, a great deal of hosting providers enable mysqli, but "forget" (or just don't know how to) to enable prepared statements along with mysqli (mysqlnd driver). Hence, once some script executes a prepared statement, a fatal error Call to undefined method mysqli_stmt is displayed.

Many users experience this issue very often - https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result

To summarize, while user should always use prepared statements if he already uses mysqli, sometimes hosting provider doesn't allow him to do so...

 

Just be warned doing so GREATLY increases your memory footprint since you're doubling down on the entire data set being in PHP's memory at once ON TOP of it being in SQL's. That's why we USUALLY use the regular iterating and looping fetch model. Unless the data set is REALLY tiny, I would advise AGAINST using fetch_all for performance reasons alone.

Well, unless you're hosting a low traffic site on a dedicated server with batshit crazy amounts of RAM available to it. Even then you're wasting CPU time making an extra copy of the same values that you'll likely JUST copy again when you iterate the resultant array.

That's why you don't use get_result and bind_result after execute instead. Hence why in my examples I use bind_result and not get_result. The latter is USELESSLY BROKEN.

That or just use PDO which is VASTLY superior in most every way leading me to wonder why the **** mysqli is even a thing or why most people seem to choose it first. The mere frustration that mysql->query and mysqli->prepare return two different object types should be enough derp to make you go "Oh HELL no!" on choosing it.

Though I know the REAL reason people keep diving for mysqli is some folks are so batshit insanely scared of "objects" they should probably just do the world a favor and back the **** away from the keyboard

Even if those procedural wrappers are in fact THIS ***ing stupid implementation-wise.

function mysql_stmt_prepare($a, $b) { return $a->prepare($b); }

Code (markup):

THAT'S how ****ING stupid even having those procedural versions EXIST in the language is.

 

Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.The difference between single and double quotes in PHP is that double quotes are "intelligent" in that they will parse for variables when being read, while single quotes are "dumb" and will not try to parse any character in the string.