What would be more professional and error proof approach with simple mysql not PDO or anything
I usually do like this
$sql_request = "SELECT *
FROM myusers
WHERE user_id = {$user_id}
AND email = '{$email_address}'
LIMIT 0,1";
However should i quote {$user_id} as well?
Even when i get user input i dont quote numbers, however i do check ctype_digit() before processing them.
No, there's no need to place quotes around numeric literals.
With MySQL it's fine if the numeric literals are enclosed in single quotes; I think the reason we see it done so often is that it's convenient for programmers not to care about whether it's character or numeric, and just put quotes around all the literals.
There is a difference in behavior when a value is quoted and not quoted when the value is not a numeric literal. For example, given an id column of type INTEGER, in this context
... WHERE id = '1X'
The '1X' here will be interpreted as a literal numeric with a value of 1, but in this context
... WHERE id = 1X
The 1X here will be interpreted as a column name, rather than as a numeric literal. This will likely cause MySQL to throw an 'Unknown column' exception.
Consider the difference in how these will be interpreted...
... WHERE id = 'id' -- 'id' will be interpreted as numeric literal value 0
... WHERE id = id -- id will be interpreted as a column name
So it really boils down to which behavior is best for your application, when what you expect to be interpreted as a numeric literal is something other than numeric.
My personal preference is to NOT quote the numeric literals. This is probably due to my experience with other DBMSs and the need to avoid issues caused by implicit data conversions. My personal preference is also to use prepared statements, and avoid my statements including values as literals in the SQL text. (With MySQL that point is mostly moot, since a prepared statement with bind variables gets converted into plain SQL text when it's sent to the database... but that's done by the MySQL library, not my code. And again, this is preference is probably most informed by my longer experience using other RDBMSs (Oracle, Teradata, DB2, SQL Server) rather than MySQL itself.
I would strongly suggest to be carefull when quoting numerical values - here is what happened to me: An optimized, often-run query produced insane amounts of IO and quite some CPU laod:
SELECT blah FROM foo WHERE intcolumn='17';
with the selectivity being some 100 rows out of millions. I checked the execution plan: lo and behold, full table scan on the driving table. I checked the index on foo(intcolumn) again and again, even dropped and recreated it, no luck. Query time was in the minutes.
SELECT blah FROM foo WHERE intcolumn=17;
took less than 0.1 seconds. For some reason, MySQL had chosen to cast all foo.intcolumn to VARCHAR and then do a string compare to '17'. Ofcourse this included ignoring the index.
I don't know, if I hit an exotic bug in an old version of MySQL, bu I surely took away one thing: Make sure, the parser knows, what data type I intend to use. This ofocurse can be tricky with quoted numerals.
What would be more professional and error proof approach with simple mysql not PDO or anything
I'd say it is sprintf and writing readable code. You normally do not want to place the variable names into the strings but add them later based on order. That decouples it a bit more and is easier to read.
Also you can specify numeric data as %d so that you ensure it's an integer value and not prone to string injection. Numeric literals normally do not need quotes. So sprintf and SQL team up well here. %s needs quotes, %d does not. However quotes do not hurt either:
$sql_request = "SELECT *
FROM myusers
WHERE user_id = '%d'
AND email = '%s'
LIMIT 0,1";
$query = sprintf($sql_request, $user_id, mysql_real_escape_string($email_address, $link));
However I read in your question about not mysqli or PDO. If you can, switch to PDO and use prepared statements / parametrized queries. If this is an old application, prefix the mysql_* functions with something and implement them your own based on PDO and slowly but steadily replace old with new code.
Also take a look at this question that should help you as well to start porting code, it works relatively quickly:
And for how to switch to PDO:
The common consensus is that it's not a good idea (i.e., not professional nor error-proof) to be writing SQL statements as an interpolated (your example) or concatenated string. You should use prepared statements instead.
In the dark ages, I used a function like:
// This is obsolete!!
function escape_input($s) {
if (!ctype_digit($s))
return "'" . mysql_real_escape_string($s) . "'";
else
return $s;
}
As you can see, I quoted string inputs, but allowed integer inputs to pass through without quotes. (Though you could quote integer inputs without any ill effects.) I used this function on every single MySQL input, regardless of whether I knew the input was an integer or not. By forcing myself to follow this pattern, I don't think I ever opened myself up to SQL injection attacks. But again, I would not do things this way anymore. I would use prepared statements from PDO or mysqli_*.
User_id column should not be quoted as it is numerical. But you have to make sure that numerical value is passed.
You don't need to quote $user_id as long as you make sure the variable contains a number.
If it doesn't it may break your query and even damage your database!
The better solution would be to use prepared statements using PDO or another library.
I would recommend on quoting. You can't never be too sure! (And you'd let MySQL handle the possible errors). Also, getting used to add quotes around your variables helps you to remember to always put them once you get used to it and makes do you less errors. (Don't forget to escape your inputs as well)
Or, use PDO or MySQLi with prepared statements! You won't even have to quote anymore. (Yeah, sorry. Answering to a question that really wants to use something that is deprecated makes me give better alternatives)
