|
From: SourceForge.net <no...@so...> - 2012-12-08 15:00:56
|
Patches item #3588789, was opened at 2012-11-20 12:30 Message generated for change (Comment added) made by phd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540674&aid=3588789&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None Status: Open >Resolution: Invalid Priority: 5 Private: No Submitted By: Darren Janeczek (darren_janeczek) Assigned to: Oleg Broytman (phd) Summary: Columns with keyword names are problematic for some queries Initial Comment: 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. ---------------------------------------------------------------------- >Comment By: Oleg Broytman (phd) Date: 2012-12-08 07:00 Message: Alas, doesn't work too. SQLite: cur.execute('INSERT INTO test (test.name) VALUES (?)', ('text',)) pysqlite2.dbapi2.OperationalError: near ".": syntax error PostgreSQL: cur.execute('INSERT INTO test VALUES (test.id, test.value)', (1, 'test')) psycopg2.ProgrammingError: invalid reference to FROM-clause entry for table "tes t" LINE 1: INSERT INTO test VALUES (test.id, test.value) ^ HINT: There is an entry for table "test", but it cannot be referenced from this part of the query. ---------------------------------------------------------------------- Comment By: Oleg Broytman (phd) Date: 2012-11-23 10:26 Message: Using a complex set of shell wrapper over py.test. The central part is like the following: for test in "$@"; do py.test "$test" -D "$TESTDB_URI" done If you are interested in all details I can send you an archive of all shell scripts I use to maintain SQLObject. Give me your email; if you don't want to publish it -- send it to me by a private message. I'm going to try nose. The usage should be something like python -c "import nose; nose.run_exit()" ---------------------------------------------------------------------- Comment By: Darren Janeczek (darren_janeczek) Date: 2012-11-23 09:48 Message: Thanks. How do you invoke the tests anyway? ---------------------------------------------------------------------- Comment By: Oleg Broytman (phd) Date: 2012-11-23 07:53 Message: Interesting approach. I'll have a deeper look into into it and fix tests if possible. ---------------------------------------------------------------------- Comment By: Darren Janeczek (darren_janeczek) Date: 2012-11-23 07:30 Message: 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. ---------------------------------------------------------------------- Comment By: Darren Janeczek (darren_janeczek) Date: 2012-11-21 09:24 Message: (Removed invalid patch. Will try again shortly) ---------------------------------------------------------------------- Comment By: Darren Janeczek (darren_janeczek) Date: 2012-11-20 17:33 Message: 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. ---------------------------------------------------------------------- Comment By: Darren Janeczek (darren_janeczek) Date: 2012-11-20 17:29 Message: 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) ---------------------------------------------------------------------- Comment By: Oleg Broytman (phd) Date: 2012-11-20 12:43 Message: 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? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540674&aid=3588789&group_id=74338 |
