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.
$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?
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
Your first query indicates that your asking to avoid SQL Injections. Your application logic should handle the prevention of injections, not your SQL (PDO).
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.
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.
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.
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/showthread.php?1075553-PDO-API-concerns&p=5449254&viewfull=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.
No, a failed query is not the worst. Try it again when $bad is set to...
' OR '' = '
The resulting query will be
SELECT * FROM table WHERE id = '' OR '' = ''
Or try it when $bad is set to...
'; DROP TABLE table_name; '
This topic is now closed. New replies are no longer allowed.