[patch] Names in SQL should be quoted

In both PostgreSQL and SQLite, identifiers such as table and field names are quoted using the " (double-quote) character. In MySQL, identifiers are quoted using the ` (backtick) character. A helper function will be needed to quote names appropriately for each database engine.

The above patch adds the necessary helper function. Now it's "just" a case of going through the Django source and finding all the places where field names need to be quoted. Not exactly non-trivial, but doable.

I meant "Not exactly trivial", of course.

After working on quoting the field names for a short while, I discovered that quote_identifier() is far too long to type, especially when in some places it needs to be repeated four times for a single SQL statement! So I renamed it to quote_name(). I also added a little extra "smarts" to the function, so that if an identifier has already been quoted it won't get quoted again. Please ignore the quote_identifier.patch above and replace it with the following quote_name.patch:

Patch to add quote_name() to all db backends

And finally, here's the (very large) patch to correctly quote all identifiers (table and field names) in the SQL of all of Django. Phew!

Patch to quote all SQL identifiers with db.quote_name()

Tagging summary with [patch] to flag it for developers' attention. Also noticed that I uploaded a broken version of the quote_name.patch; fixed version of that patch coming up.

Fixed patch to add quote_name() to all db backends - previous patch had wrong linecounts

related comment

It's worth noting Ian Sparks' comment at http://www.djangoproject.com/weblog/2005/jul/21/sqlite3/#c250 that if we quote all field names in the SQL, then those field names become case-sensitive. This shouldn't have any effect on the generated SQL, since we always use code like [f.name for f in self._meta.fields] and so the names being sent to the database are already case-consistent. But if we end up with strange errors immediately on applying this patch, one troubleshooting approach will be to look for any case-inconsistencies in field names throughout the Django code.that's one possible place to look

I think this really should go in, especially in the light of more db backends coming up. The problem is, more backends means more reserved words, means people get short on column names - or problems with incompatibilities over different databases are bound to happen, as people tend to use short names that might be reserved by some database.

I set the milestone to 1.0 because I think this really should go in, as it doesn't change djangos functionality at all, but makes portability of applications much simpler.

(In [1039]) Added quote_name hook for each database backend. Refs #121. Thanks, Robin Munn -- we miss you.

I have a recent version of the quote-names patch which works like a charm here. Any interest in merging this soon? I don't feel like spamming the list of attached files with an updated patch for every change in d.c.meta which touches the SQL :)

Sure, go ahead and attach the patch to this ticket. I'm planning on integrating this shortly -- hopefully on Sunday, if I have the time.

(In [1224]) Fixed #121 -- Django now quotes all names in SQL queries. Also added unit tests to confirm. Thanks, Robin Munn and Sune.

Milestone Version 1.0 deleted