Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: SQL: Quotes around the values of numeric columns?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dimalta
    Do you know if this syntax is accepted by all the databases?
    yes i do, and no it's not

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    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 )

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mysql and sql server support it (with implicit conversion)

    microsoft access doesn't

    i can't test any others at the moment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Oracle doesn't.

    PostgreSQL (& EnterpriseDB) don't.

    MySQL is pretty much on its own with that particular interpretation of ANSI sql.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    3
    For Oracle this comes for the documentation:
    Character and Number Values
    Example When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '200' to 200:

    SELECT last_name
    FROM employees
    WHERE employee_id = '200';
    -> Oracle Datatype Comparison Rules

    For PostgreSQL I tested it myself and it seems to work

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937
    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?
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by r937
    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?
    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.

  10. #10
    Join Date
    May 2009
    Posts
    3
    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.

  11. #11
    Join Date
    May 2009
    Posts
    3
    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.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by dillydadally
    so if you have more to expound on your opinion, please do.
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2009
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •