#224 SQLObject quotes improperly with PostgreSQL

closed-fixed
Oleg Broytman
Postgres (36)
3
2007-07-25
2007-07-24
Anonymous
No

Attempting to create a new object where a string field contains a single quote (') in a PostgreSQL 8.2.3 database fails due to improper quoting. PostgreSQL expects single quotes to be quoted using a single quote (e.g., 'Ba''r'), not a backslash. This problem occurred with both SQLObject 0.8.1 and 0.10dev_r2787.

[mejis ~]$ uname -a
Darwin mejis.local 8.10.1 Darwin Kernel Version 8.10.1: Wed May 23 16:33:00 PDT 2007; root:xnu-792.22.5~1/RELEASE_I386 i386 i386
[mejis ~]$ psql --version
psql (PostgreSQL) 8.2.3
contains support for command-line editing
[mejis ~]$ python -V
Python 2.4.4
[mejis ~]$ python
Python 2.4.4 (#1, Oct 18 2006, 10:34:39)
[GCC 4.0.1 (Apple Computer, Inc. build 5341)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlobject import *
>>> conn = connectionForURI('postgres://postgres@localhost/scratch')
>>> class Foo(SQLObject):
... name = StringCol()
...
>>> Foo.createTable()
[]
>>> Foo(name='Bar')
<Foo 1L name='Bar'>
>>> Foo(name="Ba'r")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/declarative.py", line 98, in _wrapper
return fn(self, *args, **kwargs)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/main.py", line 1218, in __init__
self._create(id, **kw)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/main.py", line 1249, in _create
self._SO_finishCreate(id)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/main.py", line 1273, in _SO_finishCreate
id, names, values)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/dbconnection.py", line 396, in queryInsertID
return self._runWithConnection(self._queryInsertID, soInstance, id, names, values)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/dbconnection.py", line 255, in _runWithConnection
val = meth(conn, *args)
File "/Library/Python/lib/python2.4/SQLObject-0.10dev_r2787-py2.4.egg/sqlobject/postgres/pgconnection.py", line 136, in _queryInsertID
c.execute(q)
psycopg2.ProgrammingError: syntax error at or near "c"
LINE 1: INSERT INTO foo (id, name) VALUES (7, 'Ba\'r')
^

>>>

Discussion

  • Oleg Broytman
    Oleg Broytman
    2007-07-25

    • priority: 5 --> 3
    • assigned_to: nobody --> phd
    • status: open --> open-postponed
     
  • Oleg Broytman
    Oleg Broytman
    2007-07-25

    Logged In: YES
    user_id=4799
    Originator: NO

    It is not a bug in SQLObject but a problem with PostgreSQL that at version 8.0 changed quoting style from \' to ''. It is impossible for SQLObject to support both old and new styles simultaneously. So you have to wait while SQLObject stops supporting Postgres 7. Meanwhile reconfigure PostgreSQL to support \' quoting style.

     
  • Oleg Broytman
    Oleg Broytman
    2007-07-25

    • status: open-postponed --> closed-fixed
     
  • Oleg Broytman
    Oleg Broytman
    2007-07-25

    Logged In: YES
    user_id=4799
    Originator: NO

    Fixed in the revisions 2795-2799 (branches 0.7, 0.8, the trunk and docs.) Will be in the next round of releases.