Database query where() value is a number

Question

As we know, any values that are defined in a database query should always be escaped in Joomla using $db->quote($value).

When defining numbers as a variable, one would normally use (int) to set it as an integer.

So what would one do when setting a number as a value in a where() clause for a database query? Would we only define it as an integer like so:

$id = (int)555;
->where($db->quoteName('id') . ' = ' . $id);

or would we only need to quote it like so:

$id = 555;
->where($db->quoteName('id') . ' = ' . $db->quote($id));

or would we do both?

$id = (int)555;
->where($db->quoteName('id') . ' = ' . $db->quote($id));

Could someone please shed some light just so that I know for future reference please?

Answer 1

Good question this one. I have been in this consideration many times as well. Not sure if there is one right answer and also not sure if my answer will be totally correct and clearly explained.

From what I have seen so far, the database will understand a query containing a number/ integer in a where statement, and will compare it with its records set, even if the field we are comparing is defined as an (INT), (TEXT), (VARCHAR) or (CHAR) type, with or without quotes.

However one of the critical points in such cases is to have consistency when querying the database with the right data types. So when checking for an INT field, the variable included in the query should always be transformed to an integer.

Doing so and passing an integer to the database, we are somewhat safe of what data we are sending... The database will understand and process the query.

From my tests with this case, my general conclusion is that quoting the Integer will not make any difference in the results and the whole stability. (Unless there are any specific cases that I can't think of at the moment, where quoting or not an integer will result in different or undesired results). So, it looks like that it comes down more to the coding style and the consistency of the code.

With the above in mind, it seems that is not actually required to quote an integer.

It will be interesting to investigate this throughout the Joomla core, to see extensively their practices, or even better have an input from someone with an extensive knowledge and experience on this topic.