Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#108 Columns with keyword names are problematic for some queries

Unstable_(example)
closed-rejected
Oleg Broytman
None
5
2013-04-28
2012-11-20
Darren Janeczek
No

Assume you create a SQLObject out of an existing table that happens to have a column named "group".
This crude patch will protect most queries that involve columns with names like "group" etc, by using a function that wraps the names with back quotes: `group`.
NOT THOROUGHLY TESTED.

Discussion

1 2 > >> (Page 1 of 2)
  • Oleg Broytman
    Oleg Broytman
    2012-11-20

    Thanks. Alas, the patch quotes special names with backticks which AFAIK are only valid on MySQL. Any idea if there are other backends that use backticks to quote names?

    Postgres, as well as most other databases use double quotes (") to quote names. Can you rework the patch so it takes the quote character from the DB Connection?

     
  • Oleg Broytman
    Oleg Broytman
    2012-11-20

    • assigned_to: nobody --> phd
    • status: open --> open-invalid
     
  • This patch actually causes another problem:
    `table_name.field_name` is produced instead of `table_name`.`field_name`

    My work around was to change the method to:

    def escape_keywords(keyword):
    #Use this function to allow for the potential of field items with SQL syntax
    if '.' in keyword:
    #The dot implies that this item contains the table name -- making it legal
    return keyword
    return "`%s`" % (keyword)

     
  • Another possibly more generally compatible solution would be to explicitly state table_name.field_name for each.

    Note however that the use of "`" is coded in a central function (escape_keywords) and could easily be changed to get the quotes from the db-specific abstraction. I have a few deadlines to fight and don't know the inner workings to do it efficiently at this time.

     
  • (Removed invalid patch. Will try again shortly)

     
  • I wrote a simplistic test which covers some of the functionality.
    Unfortunately, I wasn't able to invoke the testing suite, and I won't have time to dive too deeply into the docs for a while.
    The new solution should be more generally compatible.

     
  • Oleg Broytman
    Oleg Broytman
    2012-11-23

    Interesting approach. I'll have a deeper look into into it and fix tests
    if possible.

     
  • Oleg Broytman
    Oleg Broytman
    2012-11-23

    • status: open-invalid --> open
     
  • Thanks.
    How do you invoke the tests anyway?

     
1 2 > >> (Page 1 of 2)