Hello,

I must modify an application build with MySQL to make it work with other databases.

This application works perfectly but use quotes in all the requests even for numeric columns.

For example:
SELECT * FROM table WHERE id='1'

(where id is declared as INTEGER)

Do you know if this syntax is accepted by all the databases?

yes i do, and no it's not

do you know which ones support it and which ones don't?

(if you can just mention those you're familiar with and sure about, pleaseeee )

mysql and sql server support it (with implicit conversion)

microsoft access doesn't

i can't test any others at the moment

Oracle doesn't.

PostgreSQL (& EnterpriseDB) don't.

MySQL is pretty much on its own with that particular interpretation of ANSI sql.

gee that's funny, lou

dimalta reported here that oracle and postgresql do allow quotes around numeric values

pity i can't be bothered to download either of them to test for myself, eh

could you test it for us in either of them please?

For Oracle this comes for the documentation:
-> Oracle Datatype Comparison Rules

For PostgreSQL I tested it myself and it seems to work

D'Oh !

My bad. That's what I get for reading too fast. I thought dimalta was referring to the MySQL's goofy non-apostrophe text delimiters... (` instead of ') Apostrophe's around numeric data is implicitly converted with both Oracle and PostgreSQL.

PostgreSQL will accept numeric_column = '1' but will not accept character_column = 1 (this is since 8.3 and the reasons are explained in the release notes)

Oracle will convert it as well, but will fail to use an index on the column because of the implicit conversion. So there are potential performance problems.

Nobody would try to use numeric values within quotes in a regular programming language like Java, C, Pascal, PHP or VisualBasic (not sure about the last two though, as I have never used them)
Why are people trying to do that with SQL?


My recommendation is: never ever use single quotes around numeric values. Even if it is "accepted" by the DBMS.

Using the correct format also adds to the readability and maintainability of the query.

Just an interesting note here. The MySQL documentation actually recommends that you DO use single quotes around numeric values. Why? Because it stops some hacking attempts. From the documentation under 5.3.1. General Security Guidelines:

"A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it."

My thoughts on this is there are way too many accepted policies in how to make your sql queries that have no real basis other than that's what's accepted - another example is capitalization of key words (another topic right there). As long as the database allows it, then as far as I know it's mostly just an individual's opinion as to whether or not single quotes around numeric values should be used (barring cases such as Oracle which possibly create performance issues as noted by shammat). I usually do it, but just because I think it's easier to not have to think about whether a value needs quotes are not - I just type them and keep going. Other than syntax highlighting, I don't see a difference in readability (which is all in the eye of the beholder) and SQL is something altogether different than a standard programming language. Also, if I'm dynamically creating a query from variable values, it's nice not to have to check if the variable is numeric and make a separate case. If it solves a problem with your database conversion, I'd say go ahead.

By the way, just wanted to say I respect your opinion shammat and am guessing you have a lot more experience in databases than me, so if you have more to expound on your opinion, please do. I'd like to learn. I wasn't trying to attack you or anything (I hate forum wars), just giving my view point.

One of the most important things that immediately come to mind is performance. Oracle (and Postgres) will fail to use an index on the numeric column if you provide the wrong data type to compare against.

So SELECT * FROM my_table WHERE numeric_column = 1 will use an index on numeric_column whereas WHERE numeric_column = '1' will not use an index due to type conversion. It's like writing WHERE to_char(numeric_column) = '1'.

Just imagine the performance of the statement on a multi-million row table where each select for a single row needs to use a full table scan.

The second reason is stability against the environment.

The parsing of a "numeric" literal can be correct in one environment but fails in another. Just think about locale settings and what happens if you run WHERE numeric_column > '3.14'. Now if you run that on my computer it would fail because of german localisation settings. The database would need WHERE numeric_column > '3,14' to be able to parse that as a numeric value (note the comma instead of the dot).

It get's even more scary if you mix both characters inside an update statement because the meaning of the literal completely changes.

Consider

UPDATE employees SET salary = '3,000.00' WHERE emp_id = 42

vs.

UPDATE employees SET salary = '3.000,00' WHERE emp_id = 42

in different locale environments. The first statement with a german locale will happily set the salary to 3, the second to 3000. I'm pretty sure the employee would not like the outcome of the first statement. In an environment with american locale it would be the other way round. If your application runs in different environments a bugs like this will be very hard to track.

The rules for number constants (non-quoted) are pretty clear, it's always a dot and a comma is not allowed.

I don't really buy the MySQL argument though. This kind of attack can safely be prevented by using prepared statements which will have the added benefit of increased performance as well. Not sure about MySQL though, I don't know it good enough to know what their recommendation means in terms of performance. I could imagine that they simpley don't have such a strict type checking as the others and thus are not affected by this kind of hidden performance problems.

Another point is documentation. Using the correct data types is a matter of proper application design and adds to the readability of the code.

If I see a WHERE some_column = '1' somewhere in an application I would assume that some_column is a character column and would probably be surprised when WHERE some_column = 'A' does not work.

nice explanation, shammat, and i'm totally on board with you that a good programmer will distinguish between numeric and string values, and not leave it to the database engine

i don't buy the mysql explanation either, it's a crock

i really love mysql, but they did some things early on that are egregiously wrong and they have been back-pedalling ever since

Good points shammat. Still, if you aren't using PostgreSQL or Oracle and you're not developing for something that might change environments (which has always been the case with me), quotes are still a viable option.

However, I can see two reasons still to follow your advice - first, I think it's good to develop good programming habits, and an individual might have to use Oracle and Postgre or a different environment in the future even if they aren't now. It would be hard to switch if you're used to putting quotes on numeric values. It could cause some bugs or performance issues when you're not paying attention or aren't knowledgeable about your environment.

Second, I particularly like your last point. I didn't quite understand what you were referring to with readability of the code in your first post, but now I do. It's nice to be able to look at a value and know if it is a string or a number in the database. Again, it could avoid bugs.

So, I agree with you and have learned something new. The only situation I might personally still use the single quotes is when I'm using MySQL and I'm dynamically creating a query with variable values that I'm unsure whether they're numeric or string based. Just placing the variable in quotes is a much quicker solution than checking the type and creating two different cases for numeric and string. Anyway, thanks for the explanation shammat.