Thread: [SQLObject] backslash escaping in postgresql 8.3
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Nick M. <nmu...@lo...> - 2008-06-09 10:42:53
|
Hi everyone, I'm having trouble with backslash escapes in PostgreSQL 8.3 and SQLObject. I currently have a Resource class that looks like: class Resource(SQLObject): resource = UnicodeCol(unique=True) @classmethod def autocomplete(cls, input): """ Controller for the autocomplete widget. """ return cls.select(cls.q.resource.startswith(input)) If I call Resource.autocomplete('blah'), the following statement is generated: SELECT collateral.id, collateral.resource, collateral.description, collateral.icon FROM collateral WHERE ((collateral.resource) LIKE ('blah%')) That works great, but if I call it with r'\', like so, I get an error from postgres: 2008-06-09 11:43:21 BST WARNING: nonstandard use of \\ in a string literal at character 135 2008-06-09 11:43:21 BST HINT: Use the escape string syntax for backslashes, e.g., E'\\'. 2008-06-09 11:43:21 BST LOG: statement: SELECT collateral.id, collateral.resource, collateral.description, collateral.icon FROM collateral WHERE ((collateral.resource) LIKE ('\\%')) and no records are returned, despite there being records in the table starting with '\'. Currently I'm working around this by duplicating the backslash in python, which generates the same error from postgres but actually returns records beginning with a single backslash. It looks like postgres has a special E'' syntax for escaping which I don't entirely understand, but would it make sense for SQLObject to be using that in the future? Thanks, Nick |
From: Diez B. R. <de...@we...> - 2008-06-09 10:55:01
|
> Currently I'm working around this by duplicating the > backslash in python, which generates the same error from postgres but > actually returns records beginning with a single backslash. It looks like > postgres has a special E'' syntax for escaping which I don't entirely > understand, but would it make sense for SQLObject to be using that in the > future? I'm actually less than satisfied with the way SO currently deals with parameters - because it essentially does the escaping itself, and then creates full SQL-statements. The better way to go would of course be to use the parametrized version of cursor.execute, which should shift the problem (if it persisted) to the db-adapter itself. However, this would mean a rather major rewrite of sqlbuilder. Yet I do believe it's worth it, because otherwise we open up for SQL injection attacks and similar problems. Diez |
From: Oleg B. <ph...@ph...> - 2008-06-22 11:10:46
|
On Mon, Jun 09, 2008 at 12:54:09PM +0200, Diez B. Roggisch wrote: > I'm actually less than satisfied with the way SO currently deals with > parameters - because it essentially does the escaping itself, and then > creates full SQL-statements. > > The better way to go would of course be to use the parametrized version of > cursor.execute, which should shift the problem (if it persisted) to the > db-adapter itself. > > However, this would mean a rather major rewrite of sqlbuilder. Yet I do > believe it's worth it, because otherwise we open up for SQL injection attacks > and similar problems. Not only sqlbuilder - dbconnection and derivatives. I tried to do this in the private branch http://svn.colorstudy.com/home/phd/SQLObject/paramstyles (see dbconnection.py, class DBAPI, method _executeRetry; also see http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/sqlobject/include/DBSingleStyle.py). The work partially failed because different DB API drivers has issues with data types - PySQLite1 does interpolation itself simply by doing "%s" % param, PySQLite2 has (or at least had problems with mxDateTime), psycopg had problems with datetime. The second problem is that the work has to be resynchronised with the work of Luke Opperman in 0.10.0, and that's rather big job. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |