SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Any issues when using single quotes on numbers?

    I have some PHP classes I have written that do various queries on MySQL. Some are designed to work around unique IDs which can be an int or a string (E.g. some I have use URL slugs for the query). To make them work with both an auto-increment ID and a string ID I always put single quotation marks around the ID value. So you get things like WHERE `id` = '45'. I know it always works and the MySQL server is smart enough not to throw it back as an error but are there any issues I should be aware of? Is it bad practice for me to work in this manner?

    Am I right in saying if you aren't using prepared statements then, although lazy, adding quotes to ints is safer? E.g.

    PHP Code:
    $bad "' OR 1'";

    $query "SELECT * FROM table WHERE id = $bad"// Injection
    $query "SELECT * FROM table WHERE id = '$bad'"// Failed query, result returns false
    $query "SELECT * FROM table WHERE id = '" $db->escape($bad) . "'"// No results 
    If you use quotes, the worst you get is a failed query. I'm not suggesting this as good practice but am I right?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    ... are there any issues I should be aware of?
    cannot now remember where i saw it, but somebody benchmarked it and there is a performance problem

    also, it makes the sql non-portable, as other database systems may barf on the datatype mismatch
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Your first query indicates that your asking to avoid SQL Injections. Your application logic should handle the prevention of injections, not your SQL (PDO).

  4. #4
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I have looked into this a bit more and I don't think there is much overhead in letting MySQL cast the string to an int, as you would expect. However, I have read that doing this can prevent MySQL from using the index. I haven't tested this myself yet but will do.

  5. #5
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Injections were just part of my question, I was asking about any issues, not just injections. I don't think you should use prepared statements just for injection prevention as that's not what it's for; that's just a nice by-product of how prepared statements work. Sometimes a normal query is better.

    Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement.
    From http://php.net/manual/en/mysqli.quic...statements.php

  6. #6
    SitePoint Evangelist
    Join Date
    May 2006
    Posts
    423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I can confirm there is no performance difference when using quotes or not—and the index is used regardless. Tested with a primary key column with a million records and SQL_NO_CACHE on MyISAM and InnoDB. So, if you decide to use quotes, escape the data, and if not use intval or (int) or whatever.

  7. #7
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    I can confirm there is no performance difference when using quotes or not—and the index is used regardless.
    That is not always the case. Yes, if you are sending simple queries which select by PK then the difference is so small that you won't be able to benchmark it. However, mysql needs to cast the quoted number to the proper data type so if you are using joins and subqueries this may begin to matter - but still, not always.

    I used to do benchmarks to specifically test this and I was able to spot a 2.5 vs 3 second difference in one query - see http://www.sitepoint.com/forums/show...=1#post5449254. So there is a potential for performance penalty but it's hard to tell in which cases you will experience this penalty. It's always safest to keep numbers unquoted. For small to medium sized databases this may not matter at all but on a large busy site I think this shouldn't be ignored.

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,149
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DrQuincy View Post
    Am I right in saying if you aren't using prepared statements then, although lazy, adding quotes to ints is safer? E.g.

    PHP Code:
    $bad "' OR 1'";

    $query "SELECT * FROM table WHERE id = '$bad'"// Failed query, result returns false 
    If you use quotes, the worst you get is a failed query. I'm not suggesting this as good practice but am I right?
    No, a failed query is not the worst. Try it again when $bad is set to...

    Code:
    ' OR '' = '
    The resulting query will be

    Code:
    SELECT * FROM table WHERE id = '' OR '' = ''
    Or try it when $bad is set to...

    Code:
    '; DROP TABLE table_name; '
    Oops.
    "First make it work. Then make it better."

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Well played


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •