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?
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.