zondag 22 september 2013

Quoting integers in SQL, yes or no?

If your database let's you quote numeric values then handle them just like you would handle a string.

The single biggest reason why you should: Risk management.

Not quoting numeric values means each and every numeric value that you give to the database must be validated. Every one, every where, every time. That may sound easy but it's almost impossible to do. validations are a nuissance to programmers, they are added last, and if there is a deadline looming then validations are the first thing to be skipped because "well, what are the chances..."

Escaping and quoting on the other hand can be automated. Most software, especially frameworks, already have classes and methods to run all the queries in the appication. for example:

$objDatabase->query("INSERT INTO foo (bar) VALUES ($1)", array("hello"));

With this setup you can just loop over the array of values and escape and quote them all.
That is a 100% thing, none of the values are left open to injection.

"But developers can bypass this routine!"
True, they can decide to write their own database access code, just like they can decide not to do any validation.
But what is more likely; completely ignoring the existing framework, or forgetting to validate a variable?

"But quotes define strings, not numbers!"
In a query the quotes only define data boundries. The parser uses quotes to work out where the values begin and end, in case the values contain text that could be misinterpreted as part of the query grammer, it has nothing to do with datatypes.

"If you quote a number the database will read it as a string and has to re-cast it to a number"
The database doesn't cast anything, anywhere. It parses the query, picks up the values and verifies that whatever the value represent is valid for the target column or operation.

The only thing that happens that resembles casting is that the characters from the query are transformed into a binary value that the database stores in the actual table, and that happens for all the values in the query, quoted or not.

The only legit argument I've found against quoting numbers is that it requires escaping and escaping may require a roundtrip to the database. It's a small price to pay for your safety, but if you want to bitch about something; this is it.