1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'PHP' started by Ian08, Sep 20, 2017.

  1. #1
    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:

     
    Solved! View solution.
    Ian08, Sep 20, 2017 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,537
    Likes Received:
    4,464
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I'm not sure once you get down to the mysql but while you're still in PHP single quotes are faster - however doubles make life much easier.

    I would, however, write that second example like this - faster and safer.
    mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '{$full_name}'");
    Code (markup):
    In reality, the images on your page are going to slow down your site way, way more than single versus double quotes ever will.
     
    sarahk, Sep 20, 2017 IP
    Ian08 likes this.
  3. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #3
    @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?
     
    Ian08, Sep 20, 2017 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,537
    Likes Received:
    4,464
    Best Answers:
    123
    Trophy Points:
    665
    #4
    the curly quotes tells PHP where the variable starts and stops

    consider this:
    <?php
    $fire = 'hot';
    $fireman = 'Steve';
    echo "<li>who was the $fireman";
    echo "<li>who was the {$fireman}";
    echo "<li>who was the {$fire}man";
    PHP:
    Arrays can make it even messier.
     
    sarahk, Sep 20, 2017 IP
    Ian08 likes this.
  5. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #5
    @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.
     
    Ian08, Sep 20, 2017 IP
  6. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    197
    Likes Received:
    28
    Best Answers:
    2
    Trophy Points:
    155
    #6
    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!
     
    SpacePhoenix, Sep 20, 2017 IP
    deathshadow and sarahk like this.
  7. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #7
    Thanks. Is using mysqli_real_escape_string() sufficient?
     
    Ian08, Sep 20, 2017 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #8
    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.
     
    PoPSiCLe, Sep 21, 2017 IP
    Ian08 likes this.
  9. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #9
    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?
     
    Last edited: Sep 21, 2017
    Ian08, Sep 21, 2017 IP
  10. #10
    chuck your code into something like this - and keep increasing $max until you start getting meaningful results. Let us know how you get on. I'd be interested to know what $max you ended up with.

    
    <?php
    function testme($test){
    
      $max = 10000;
      $full_name = 'James Brown';
    
      $starttime = time();
      for ($i = 0; $i < $max; $i++)
        switch ($test){
          case 1:
          mysqli_query($conn, "SELECT * FROM `mytable` WHERE `full_name` = '{$full_name}'");
          break;
          case 2:
          mysqli_query($conn, 'SELECT * FROM `mytable` WHERE `full_name` = "' . $full_name . '"');
          break;
          case 3:
          $stmt = mysqli_prepare($link, "SELECT * FROM `mytable` WHERE `full_name`=?");
          mysqli_stmt_bind_param($stmt, "s", $full_name);
          mysqli_stmt_execute($stmt);
          mysqli_stmt_bind_result($stmt, $result);
          mysqli_stmt_fetch($stmt);
          break;
        }
      }
      echo "<li>Time: ".time() - $starttime;
    }
    
    testme(1);
    testme(2);
    testme(3);
    
    PHP:
     
    sarahk, Sep 21, 2017 IP
    Ian08 likes this.
  11. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #11
    @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.
     
    Ian08, Sep 21, 2017 IP
  12. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #12
    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, Sep 22, 2017 IP
    Ian08 likes this.
  13. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #13
    @Rukbat Thanks for reminding me.
     
    Ian08, Sep 22, 2017 IP
  14. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #14
    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, Sep 25, 2017 IP
    SpacePhoenix likes this.
  15. sarahk

    sarahk iTamer Staff

    Messages:
    28,537
    Likes Received:
    4,464
    Best Answers:
    123
    Trophy Points:
    665
    #15
    shows how long it's been since I tried to benchmark strings :)
     
    sarahk, Sep 25, 2017 IP
  16. Ian08

    Ian08 Well-Known Member

    Messages:
    93
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #16
    @deathshadow Thank you for your suggestion. After seeing so many recommendations of prepared statement, I will give it more considerations.
     
    Ian08, Sep 25, 2017 IP
  17. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    197
    Likes Received:
    28
    Best Answers:
    2
    Trophy Points:
    155
    #17
    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
     
    SpacePhoenix, Sep 27, 2017 IP
  18. phpmillion

    phpmillion Member

    Messages:
    145
    Likes Received:
    11
    Best Answers:
    4
    Trophy Points:
    45
    #18
    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...
     
    phpmillion, Sep 29, 2017 IP
  19. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #19
    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.
     
    Last edited by a moderator: Oct 11, 2017
    deathshadow, Oct 11, 2017 IP
  20. Harshal shah

    Harshal shah Active Member

    Messages:
    126
    Likes Received:
    5
    Best Answers:
    1
    Trophy Points:
    65
    #20
    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.
     
    Harshal shah, Oct 14, 2017 IP