python - Escape SQL "LIKE" value for Postgres with psycopg2

Question

Does psycopg2 have a function for escaping the value of a LIKE operand for Postgres?

For example I may want to match strings that start with the string "20% of all", so I want to write something like this:

sql = '... WHERE ... LIKE %(myvalue)s'
cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' }

Is there an existing escape_sql_like function that I could plug in here?

(Similar question to How to quote a string value explicitly (Python DB API/Psycopg2), but I couldn't find an answer there.)

Answer 1

Yeah, this is a real mess. Both MySQL and PostgreSQL use backslash-escapes for this by default. This is a terrible pain if you're also escaping the string again with backslashes instead of using parameterisation, and it's also incorrect according to ANSI SQL:1992, which says there are by default no extra escape characters on top of normal string escaping, and hence no way to include a literal % or _.

I would presume the simple backslash-replace method also goes wrong if you turn off the backslash-escapes (which are themselves non-compliant with ANSI SQL), using NO_BACKSLASH_ESCAPE sql_mode in MySQL or standard_conforming_strings conf in PostgreSQL (which the PostgreSQL devs have been threatening to do for a couple of versions now).

The only real solution is to use the little-known LIKE...ESCAPE syntax to specify an explicit escape character for the LIKE-pattern. This gets used instead of the backslash-escape in MySQL and PostgreSQL, making them conform to what everyone else does and giving a guaranteed way to include the out-of-band characters. For example with the = sign as an escape:

# look for term anywhere within title
term= term.replace('=', '==').replace('%', '=%').replace('_', '=_')
sql= "SELECT * FROM things WHERE description LIKE %(like)s ESCAPE '='"
cursor.execute(sql, dict(like= '%'+term+'%'))

This works on PostgreSQL, MySQL, and ANSI SQL-compliant databases (modulo the paramstyle of course which changes on different db modules).

There may still be a problem with MS SQL Server/Sybase, which apparently also allows [a-z]-style character groups in LIKE expressions. In this case you would want to also escape the literal [ character with .replace('[', '=['). However according to ANSI SQL escaping a character that doesn't need escaping is invalid! (Argh!) So though it will probably still work across real DBMSs, you'd still not be ANSI-compliant. sigh...