--- Begin Message ---
Package: python-sqlobject
Version: 0.12.4-2.1
Severity: important
Tags: patch
Dear Maintainer,
Postgresql 9.1 changed the default value of standard_conforming_strings
to on. This disables treating \ as escape characters by default and
control characters now need to be explicitly escaping using postgresql's
E'' syntax. SQLObject only added support for E'' escapes in version
1.2.0, so older versions (such as Debian's 0.12.4) do the wrong thing
when used against wheezy's postgresql server. This results in rather
unexpected behaviour when using sqlobject and postgres.
The attached patch backports the relevant changes from SQLObject 1.2.0
to 0.12.4. I've tested this with my application against both postgres
8.4 and 9.1, and, with the patch, it works correctly against both
versions while it fails against 9.1 without the patch.
The postgresql 9.1 release notes
(http://www.postgresql.org/docs/9.1/static/release-9-1.html) do mention
that escaping strings incorectly could lead to security issues, altough
I'm not certain if this will apply to any software in Debian.
"This change can break applications that are not expecting it and do
their own string escaping according to the old rules. The consequences
could be as severe as introducing SQL-injection security holes. Be sure
to test applications that are exposed to untrusted input, to ensure that
they correctly handle single quotes and backslashes in text strings."
The patch probably breaks support for sqlobject and postgresql 7 - I
haven't tested that and I don't think that is a significant concern.
-- System Information:
Debian Release: wheezy/sid
APT prefers testing
APT policy: (500, 'testing')
Architecture: amd64 (x86_64)
Foreign Architectures: i386
Kernel: Linux 3.2.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=en_ZA.UTF-8, LC_CTYPE=en_ZA.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Versions of packages python-sqlobject depends on:
ii python 2.7.3~rc2-1
ii python-formencode 1.2.4-2
ii python-pkg-resources 0.6.24-1
ii python-support 1.0.15
python-sqlobject recommends no packages.
Versions of packages python-sqlobject suggests:
pn python-kinterbasdb <none>
pn python-maxdb <none>
ii python-mysqldb 1.2.3-1+b1
ii python-psycopg2 2.4.5-1
pn python-sqlite <none>
-- no debconf information
Index: sqlobject/converters.py
===================================================================
--- sqlobject/converters.py (revision 4567)
+++ sqlobject/converters.py (working copy)
@@ -1,6 +1,11 @@
+from array import array
+import datetime
+from decimal import Decimal
import sys
-from array import array
+import time
+from types import ClassType, InstanceType, NoneType
+
try:
import mx.DateTime.ISO
origISOStr = mx.DateTime.ISO.strGMT
@@ -15,17 +20,12 @@
DateTimeType = None
DateTimeDeltaType = None
-import time
-import datetime
-
try:
import Sybase
NumericType=Sybase.NumericType
except ImportError:
NumericType = None
-from decimal import Decimal
-from types import ClassType, InstanceType, NoneType
########################################
## Quoting
@@ -90,6 +90,8 @@
value = value.replace("'", "''")
else:
assert 0, "Database %s unknown" % db
+ if db in ('postgres', 'rdbhost') and ('\\' in value):
+ return "E'%s'" % value
return "'%s'" % value
registerConverter(str, StringLikeConverter)
@@ -198,3 +200,17 @@
return converter(obj, db)
else:
return reprFunc(db)
+
+
+def quote_str(s, db):
+ if db in ('postgres', 'rdbhost') and ('\\' in s):
+ return "E'%s'" % s
+ return "'%s'" % s
+
+def unquote_str(s):
+ if s.upper().startswith("E'") and s.endswith("'"):
+ return s[2:-1]
+ elif s.startswith("'") and s.endswith("'"):
+ return s[1:-1]
+ else:
+ return s
Index: sqlobject/sqlbuilder.py
===================================================================
--- sqlobject/sqlbuilder.py (revision 4567)
+++ sqlobject/sqlbuilder.py (working copy)
@@ -70,7 +70,7 @@
import weakref
import classregistry
-from converters import sqlrepr, registerConverter
+from converters import registerConverter, sqlrepr, quote_str, unquote_str
class VersionError(Exception):
@@ -896,18 +896,18 @@
if isinstance(s, SQLExpression):
values = []
if self.prefix:
- values.append("'%s'" % self.prefix)
+ values.append(quote_str(self.prefix, db))
s = _quote_like_special(sqlrepr(s, db), db)
values.append(s)
if self.postfix:
- values.append("'%s'" % self.postfix)
+ values.append(quote_str(self.postfix, db))
if db == "mysql":
return "CONCAT(%s)" % ", ".join(values)
else:
return " || ".join(values)
elif isinstance(s, basestring):
- s = _quote_like_special(sqlrepr(s, db)[1:-1], db)
- return "'%s%s%s'" % (self.prefix, s, self.postfix)
+ s = _quote_like_special(unquote_str(sqlrepr(s, db)), db)
+ return quote_str("%s%s%s" % (self.prefix, s, self.postfix), db)
else:
raise TypeError, "expected str, unicode or SQLExpression, got %s" % type(s)
Index: sqlobject/tests/test_converters.py
===================================================================
--- sqlobject/tests/test_converters.py (revision 4567)
+++ sqlobject/tests/test_converters.py (working copy)
@@ -1,9 +1,11 @@
import sys
from sqlobject.sqlbuilder import sqlrepr
+from sqlobject.converters import registerConverter, sqlrepr, \
+ quote_str, unquote_str
from sqlobject.sqlbuilder import SQLExpression, SQLObjectField, \
Select, Insert, Update, Delete, Replace, \
- SQLTrueClauseClass, SQLConstant, SQLPrefix, SQLCall, SQLOp
-from sqlobject.converters import registerConverter
+ SQLTrueClauseClass, SQLConstant, SQLPrefix, SQLCall, SQLOp, \
+ _LikeQuoted
class TestClass:
@@ -40,23 +42,23 @@
assert sqlrepr('A String', 'firebird') == "'A String'"
def test_string_newline():
- assert sqlrepr('A String\nAnother', 'postgres') == "'A String\\nAnother'"
+ assert sqlrepr('A String\nAnother', 'postgres') == "E'A String\\nAnother'"
assert sqlrepr('A String\nAnother', 'sqlite') == "'A String\nAnother'"
def test_string_tab():
- assert sqlrepr('A String\tAnother', 'postgres') == "'A String\\tAnother'"
+ assert sqlrepr('A String\tAnother', 'postgres') == "E'A String\\tAnother'"
def test_string_r():
- assert sqlrepr('A String\rAnother', 'postgres') == "'A String\\rAnother'"
+ assert sqlrepr('A String\rAnother', 'postgres') == "E'A String\\rAnother'"
def test_string_b():
- assert sqlrepr('A String\bAnother', 'postgres') == "'A String\\bAnother'"
+ assert sqlrepr('A String\bAnother', 'postgres') == "E'A String\\bAnother'"
def test_string_000():
- assert sqlrepr('A String\000Another', 'postgres') == "'A String\\0Another'"
+ assert sqlrepr('A String\000Another', 'postgres') == "E'A String\\0Another'"
def test_string_():
- assert sqlrepr('A String\tAnother', 'postgres') == "'A String\\tAnother'"
+ assert sqlrepr('A String\tAnother', 'postgres') == "E'A String\\tAnother'"
assert sqlrepr('A String\'Another', 'firebird') == "'A String''Another'"
def test_simple_unicode():
@@ -195,3 +197,18 @@
pass
else:
assert sqlrepr(Set([1])) == "(1)"
+
+def test_quote_unquote_str():
+ assert quote_str('test%', 'postgres') == "'test%'"
+ assert quote_str('test%', 'sqlite') == "'test%'"
+ assert quote_str('test\%', 'postgres') == "E'test\\%'"
+ assert quote_str('test\\%', 'sqlite') == "'test\%'"
+ assert unquote_str("'test%'") == 'test%'
+ assert unquote_str("'test\\%'") == 'test\\%'
+ assert unquote_str("E'test\\%'") == 'test\\%'
+
+def test_like_quoted():
+ assert sqlrepr(_LikeQuoted('test'), 'postgres') == "'test'"
+ assert sqlrepr(_LikeQuoted('test'), 'sqlite') == "'test'"
+ assert sqlrepr(_LikeQuoted('test%'), 'postgres') == r"E'test\\%'"
+ assert sqlrepr(_LikeQuoted('test%'), 'sqlite') == r"'test\%'"
--- End Message ---
