[psql] string escaping quirk in like clauses

[Burak Arslan]

Hi,

First, some background:

psql (9.2.4)
Type "help" for help.

somedb=# create table a(a varchar(5));
CREATE TABLE
somedb=# insert into a values (E'\\');
INSERT 0 1
somedb=# select * from a where a = '\';
a
---
\
(1 row)

somedb=# select * from a where a like '\';
ERROR: LIKE pattern must not end with escape character
somedb=# select * from a where a like '\\';
a
---
\
(1 row)

somedb=# select * from a where a like E'\\\\';
a
---
\
(1 row)

Here's the relevant part of the documentation:

http://www.postgresql.org/docs/9.2/static/functions-matching.html
Section 9.7.1

So I guess we can safely say that it's a known and documented behaviour
and won't be considered to be a bug by the postgres team.

http://www.postgresql.org/docs/9.1/static/release-9-1.html Section
E.10.2.1 could also be slightly (as it says nothing about LIKE) relevant.

Here's what happens with sqlalchemy:

Python 3.3.2 (default, May 23 2013, 10:38:22)
[GCC 4.6.3] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.8.1'
>>> from sqlalchemy import MetaData, create_engine
>>> e = create_engine("postgres://postgres:@localhost:5432/somedb")
>>> meta = MetaData(bind=e)
>>> meta.reflect()
>>> t=meta.tables['a']
>>> e.execute(t.select(t.c.a.like('\\')))
Traceback (most recent call last):
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 867, in _execute_context
context)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/default.py",
line 326, in do_execute
cursor.execute(statement, parameters)
psycopg2.DataError: LIKE pattern must not end with escape character


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 1614, in execute
return connection.execute(statement, *multiparams, **params)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 662, in execute
params)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 761, in _execute_clauseelement
compiled_sql, distilled_params
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 874, in _execute_context
context)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 1024, in _handle_dbapi_exception
exc_info
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/util/compat.py",
line 155, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/util/compat.py",
line 150, in reraise
raise value.with_traceback(tb)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/base.py",
line 867, in _execute_context
context)
File
"/home/plq/.local/lib64/python3.3/site-packages/SQLAlchemy-0.8.1-py3.3.egg/sqlalchemy/engine/default.py",
line 326, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (DataError) LIKE pattern must not end with
escape character
'SELECT a.a \nFROM a \nWHERE a.a LIKE %(a_1)s' {'a_1': '\\'}
>>>

Anything else I can do to help?

Best,
Burak

[Simon King]

Remember that Python also has its own string escaping. When you write
a literal '\\' in Python, you are creating a string containing a
single backslash. If you want to pass 2 backslashes to PG, you either
need to use 4 slashes in Python ("\\\\"), or use a raw string (r"\\")

>>> print '\\'
\
>>> print '\\\\'
\\
>>> print r'\\'
\\

Hope that helps,

Simon

[Simon King]

On Fri, Jun 28, 2013 at 2:11 AM, Burak Arslan
<burak....@arskom.com.tr> wrote:

> On 06/27/13 13:41, Simon King wrote:
>> Remember that Python also has its own string escaping. When you write
>> a literal '\\' in Python, you are creating a string containing a
>> single backslash.
>
>

> Hi Simon,
>
> I'm aware of that. My issue is that SQLAlchemy produces an invalid query
> for .like('\\') in postgresql.
>
> Best,
> Burak

I don't think I understand. From your own example, "LIKE" with a
single backslash is invalid in PG:

somedb=# select * from a where a like '\';
ERROR: LIKE pattern must not end with escape character

When you write this:

>>> e.execute(t.select(t.c.a.like('\\')))

...the pattern that you are sending to SA is a single backslash, and
SA is forwarding that directly to PG. What do you think the behaviour
should be in this case?

Simon

[Burak Arslan]

On 06/28/13 11:55, Simon King wrote:
> When you write this:
>>>> e.execute(t.select(t.c.a.like('\\')))
> ...the pattern that you are sending to SA is a single backslash, and
> SA is forwarding that directly to PG. What do you think the behaviour
> should be in this case?
>

Well, I'd prefer sqlalchemy did not leak such quirks and escape strings
sent to .like() accordingly.

In other words, I want the two to be equivalent:

>>> e.execute(t.select(t.c.a.like('\\')))
>>> e.execute(t.select(t.c.a == '\\'))

Otherwise, I'll have to implement a psql_escape_for_like function and
make it run like so:

>>> e.execute(t.select(t.c.a.like(psql_escape_for_like(whatever))))

I wouldn't really prefer to go down this route -- it's ugly!..

Best,
Burak

[Simon King]

Ah, OK, I see what you mean now. I'm not sure this could be changed
without breaking backwards compatibility. I wonder if you could do
something with the compiler module
(http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html).

Simon

[Mike Conley]

You shouldn't need to write special code for this, have you tried changing the escape character?

e.execute(t.select(t.c.a.like('\\', escape="~"))

where ~ could be any substitute escape character. 

I don't have Postgres currently available, but their docs also state that and empty string will disable escaping; don't know if the Python modules support that feature.

http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html?highlight=.like#sqlalchemy.sql.operators.ColumnOperators.like

--
Mike Conley

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Burak Arslan]

On 06/27/13 13:41, Simon King wrote:

> Remember that Python also has its own string escaping. When you write
> a literal '\\' in Python, you are creating a string containing a
> single backslash.

[Michael Bayer]

do you mean to say r'\\' there ?

[Michael Bayer]

here's a test, passes for me (even that it uses terrible names for the bound parameters in this case):

from sqlalchemy import create_engine
from sqlalchemy.sql import literal_column, select

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

assert e.scalar(select([literal_column(r"'hello \\ world'").like(r'% \\ world', escape='^')]))
assert e.scalar(select([literal_column(r"'hello \ world'").like(r'% \ world', escape='^')]))