#736 Insert Row - column names are not quoted / escaped

3.0
open
Colin Bell
Core (461)
5
2012-12-29
2009-03-24
Chris Hubick
No

SQuirreL does not appear to properly escape column names when inserting data.

I am using SQuirreL 3.0.1 with PostgreSQL 8.3.6 and the 8.3.603 JDBC driver. I have a table with an "order" column.

If I right click on a table and "Make Editable" and then "Insert Row", enter the data, and hit "Insert" - I get an error dialog with the message:

> Exception seen during check on DB. Exception was:
> ERROR: syntax error at or near "order"
> Insert was probably not completed correctly. DB may be corrupted!

If I dismiss the dialog and then click on the "Press to view last log entry" button at the bottom, it shows me:

> Logged by net.sourceforge.squirrel_sql.client.session.DataSetUpdateableTableModelImpl at 3/24/09 12:27 AM:
>
> insertRow: pstmt sql = INSERT INTO "public"."page_group" ( page_group_id,order,properties_id,access_control_id,owner_user_id) VALUES ( ?, ?, ?, ?, ?)

Such a statement would not work unless the "order" column name is quoted similarly to the schema and table names.

Discussion

  • Rob Manning
    Rob Manning
    2009-03-24

    Chris,

    This is a tricky problem to fix generically. The PG jdbc driver reports the column names to us and unhelpfully tells us that the column name in this case is order and not "order". Also, many databases have modes for uppercase/lowercase identifiers that are locally altered when you quote an identifier like a column name. However, sadly again, many JDBC drivers seem to not report the proper case for identifiers such as column names. When I look at the column definitions as they appear in the data dictionary in PG I see nothing special indicating that the column name should be quoted:

    SELECT * FROM information_schema.columns where table_name = 'page_group';

    You can run that query for yourself and look at the records for your table and see that there isn't anything that distinguishes such a column from any other. It would be fantastic to find something in the PG data dictionary that tells us this column name is a keyword and therefore needs to be quoted. Probably the only thing in that could be done in light of this is to have a configuration that says "quote all columns" or some such thing. But I have a feeling it would break things on other databases so it would have to default to "off". Any other thoughts?

    Rob

     
  • Chris Hubick
    Chris Hubick
    2009-03-24

    Some good reading at: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

    I appreciate that this is tricky. Obviously, I think there are a few ways you could go here.

    Short term, could you simply escape based on a hard-coded list of reserved words? Section 5.2 of the SQL 92 spec (at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ) lists them all. If a column name (or whatever) is in that list, quote it, otherwise don't?

    Long term, if you look at the link I posted, it appears that in order to ultimately support the situations which are valid according to the SQL standard (a DB with both "test_case" and "TEST_CASE" tables ), you will probably be required to quote all identifiers? The question would then really be one of, given the problems with doing that in reality, how to get there?

    You could have a global setting, but it might be better to also have a per-driver setting with knowledge of that DB's behavior, which could toggle the default quoting setting for aliases created against that driver? You could then allow users to change that default for each created alias via the alias properties dialog. My choice would be to at least attempt to default to the correct behavior globally, and disable that when required - perhaps defaulting all drivers to set an "off" override initially - but then changing that case-by-case as you verify the DB and JDBC driver operation.

     
  • Rob Manning
    Rob Manning
    2009-03-25

    Indeed, we have already begun to allow global settings for quoting identifiers (see Global Preferences -> Refactoring -> Quote identifiers in scripts). We have a dialect system (borrowed from Hibernate) where we code the logic for crafting database-specific SQL. Thanks for the links and suggestions. We will evaluate them to determine the best short-term and long-term solutions.

    Rob

     
  • Rob Manning
    Rob Manning
    2009-03-25

    I just noticed near the bottom of http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

    Conclusion

    No wonders that case sensitivity of identifiers causes so much confusion! Since every DMBS seems to implement the SQL standards in a different way, you can't expect an uniform behavior. The safest choice if you care about portability and peace of mind is not to quote the identifiers when you create your tables/fields and when you run your queries.

    I hope now it's clear why the quote_identifier option in MDB2 is disabled by default. Now, I can hear the complaints coming... "But I *need* to quote my table/field name, because I'm using a reserved keyword...". Let me answer with another question: why on earth do you use a reserved keyword as identifier? ;-)

    Rob

     
  • Rob Manning
    Rob Manning
    2009-03-25

    I couldn't have said it better myself :)

    Rob