Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #201 (closed defect: wontfix)

Opened 13 years ago

Last modified 12 years ago

SQLObject does not catch SQL keywords used as names in models

Reported by: anonymous Owned by: anonymous
Priority: normal Milestone: 0.9
Component: SQLObject Version: 0.9a6
Severity: critical Keywords:
Cc: joeysmith@…

Description

I've attached a model file for which the "tg-admin sql create" command cannot succeed. It results in a traceback originating in the _mysql module, which gives this message:

_mysql_exceptions.ProgrammingError?: (1064, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order INT\n)' at line 4")

The MySQL error is just about useless, even if I look at the generated sql with tg-admin sql sql. I've attached that too, just in case.

My connection URI is mysql://jim:PASSWORD@localhost/teeth_test?unix_socket=/MYSQLSOCKETPATH (capitalized bits changed, of course)

This is with turbogears installed via ez_setup this afternoon.

Attachments

model.py Download (1.9 KB) - added by jim-tg@… 13 years ago.
Model file
sql-create-transcript Download (2.8 KB) - added by jim-tg@… 13 years ago.
shell transcript of running tg-admin sql create, including traceback
sql Download (2.2 KB) - added by jim-tg@… 13 years ago.
The sql as produced by tg-admin sql sql

Change History

Changed 13 years ago by jim-tg@…

Model file

Changed 13 years ago by jim-tg@…

shell transcript of running tg-admin sql create, including traceback

Changed 13 years ago by jim-tg@…

  • attachment sql Download added

The sql as produced by tg-admin sql sql

comment:1 Changed 13 years ago by jim-tg@…

(Adding a comment with my email address, as I forgot to put it in the issue)

comment:2 Changed 13 years ago by jim-tg@…

  • Summary changed from Invalid SQL generation to SQLObject does not catch SQL keywords used as names in models

After speaking on #turbogears with TML, it looks like the problem is in my model file. I was using a field named "order," which is an SQL reserved word.

MySQL's error was useless, but SQLObject really should have caught this before passing it on.

I've changed the title of this issue to reflect this more serious flaw.

comment:3 Changed 13 years ago by anonymous

  • Milestone set to 0.9

comment:4 Changed 13 years ago by anonymous

Shouldn't all table and column names be quoted by SQLObject?

comment:5 Changed 13 years ago by anonymous

Actually, this bug as it currently stands has nothing to do with reserved words in the SQL, but rather the way SQLObject builds its dynamic queries. If you look closely at the SQL output, you'll notice many of the commands are missing their terminating semilcolon (';'). I have a couple of patches put together that might solve it, but I think the problem may be fixed upstream once we move away from the 0.7-bugfix branch. If not, I'll still have my patches.

comment:6 Changed 13 years ago by joeysmith@…

Previous comment made by me (joeysmith@… / TML on freenode)

comment:7 Changed 13 years ago by godoy

  • Cc joeysmith@… added

Joey, due to the time it is taking for SQLObject to move on, I believe your patches could help a lot. Can you attach them to this ticket?

comment:8 Changed 13 years ago by godoy

  • Severity changed from critical to minor

Thinking better, this isn't "critical" but it is definitely annoying (the use of reserved SQL keywords). Another problem is also related to database extensions (i.e. some reserved keyword a database has that other doesn't have and isn't in the SQL standard...). I don't think it is feasible to check all tables that would be created (either through direct name mungling or through sqlmeta class) against a table of "forbidden names" and emmiting an error message. It is, IMHO, the developer's responsability to avoid using reserved names or defining some unique naming scheme to avoid that.

With regards to using quotes all the time, this might solve the problem but might lead to other problems when integrating your application with legacy databases or with reusing the new database with other external applications.

comment:9 Changed 13 years ago by joeysmith@…

  • Severity changed from minor to critical

Well, as I mentioned in my last comment, this bug is really two bugs. There's a problem with SQL keywords which godoy has just addressed, but there's also a problem with SQLObject sometimes failing to put a trailing ; on the queries. I don't recall them being related in my investigation. Once I've found my patches for this latter issue, I'll put them here.

comment:10 Changed 13 years ago by jorge.vargas

  • Status changed from new to closed
  • Resolution set to wontfix

for the keywords this is no easy task, and they change from db to db and even from version to version. about the quotes that doesn't works on all databases

conclusion was 1- anyone with a experience knows that tables can't be call keywords 2- tracking everything down was a problem a big problem 3- the ammount of people having this is not worth 2 4- we are going to have docs saying this "issue"

read the mailing list for discussions related to it.

if this is still happening please review #1010

if not reopen.

comment:11 Changed 13 years ago by joey

My patches to sqlobject should be obsoleted by upstream patch #1774

Note: See TracTickets for help on using tickets.