After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 663608 - Incorrect escaping of " in SQL IDs
Incorrect escaping of " in SQL IDs
Status: RESOLVED FIXED
Product: libgda
Classification: Other
Component: Client library
5.0.x
Other Linux
: Normal normal
: ---
Assigned To: malerba
gnome-db Maintainers
Depends on:
Blocks:
 
 
Reported: 2011-11-08 10:14 UTC by Murray Cumming
Modified: 2013-11-06 09:57 UTC
See Also:
GNOME target: ---
GNOME version: ---

Attachments
test_libgda_postgresql_id_escaping.c (1.51 KB, text/plain)
2011-11-08 10:14 UTC, Murray Cumming 		Details

Description Murray Cumming 2011-11-08 10:14:10 UTC 
Created attachment 200969 [details]
test_libgda_postgresql_id_escaping.c

This test case shows that gda_connection_quote_sql_identifier() incorrectly escapes " as \" instead of "" as specified here:
  http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html
  "To include a double quote, write two double quotes."

The SQL query is then rejected by PostgreSQL like so:

** WARNING **: gda_connection_statement_execute() failed: syntax error at or near "inmiddle"
LINE 1: CREATE TABLE "tablenamewith\"inmiddle" (somefield numeric PR...
Comment 1 malerba 2011-11-08 22:15:25 UTC 
See correction in commit http://git.gnome.org/browse/libgda/commit/?id=ad1976252faaae4386c9429d065c48cd456a1e57
Can you check it's Ok?
Vivien
Comment 2 Murray Cumming 2011-11-08 23:22:14 UTC 
Thanks, yes, that fixes my Glom test case for PostgreSQL.


It's apparently also wrong for SQLite. I'm seeing this similar error:
  ConnectionError: near "quote": syntax error
for
  CREATE TABLE "sometablewith\"quote" ("sometablewith\"quote_id" numeric NOT NULL PRIMARY KEY, "description" varchar, "comments" varchar, "glom_lock" varchar)

But I have not investigated what syntax SQLite officially requires.
Comment 3 Murray Cumming 2011-11-09 14:22:54 UTC 
(In reply to comment #2)
> But I have not investigated what syntax SQLite officially requires.

This suggests that it should be the same as in PostgreSQL:
  http://www.sqlite.org/lang_expr.html
  "A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported"
Comment 4 Murray Cumming 2013-11-06 09:57:01 UTC 
This seems to be fixed now.