55

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.)

Community
  • 1
  • 1
EMP
  • 59,148
  • 53
  • 164
  • 220

13 Answers13

39

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...

bobince
  • 528,062
  • 107
  • 651
  • 834
  • `standard_conforming_strings` in postgres does _not_ break backslash escapes in like queries. At least not in 12.7. – 0x89 Jul 02 '21 at 12:06
35

I was able to escape % by using %% in the LIKE operand.

sql_query = "select * from mytable where website like '%%.com'"
cursor.fetchall(sql_query)
pandasuser
  • 450
  • 4
  • 5
  • 3
    This should imho be the solution. Is there anything wrong with it? – Dalbenn Mar 31 '20 at 10:32
  • 1
    Maybe this is PostgreSQL version-dependent? This answer answer came several years after the question and the current top answer was posted. – wfgeo Jul 08 '20 at 14:47
  • I moved heaven and Earths to solve the issue but only this method worked. Much thanks! – Asim Jan 01 '22 at 13:52
8

If you're using a prepared statement, then the input will be wrapped in '' to prevent sql injection. This is great, but also prevents input + sql concatenation.

The best and safest way around this would be to pass in the %(s) as part of the input.

cursor.execute('SELECT * FROM goats WHERE name LIKE %(name)s', { 'name': '%{}%'.format(name)})
meisinger
  • 3
  • 2
sdc
  • 2,603
  • 1
  • 27
  • 40
  • NOT what I was expecting to work, but all the other ways failed for me when I have other %s in the same query AND string substitution using `.replace()`. Thanks. Helped me a ton! – Marc Maxmeister Nov 10 '22 at 04:54
5

You can also look at this problem from a different angle. What do you want? You want a query that for any string argument executes a LIKE by appending a '%' to the argument. A nice way to express that, without resorting to functions and psycopg2 extensions could be:

sql = "... WHERE ... LIKE %(myvalue)s||'%'"
cursor.execute(sql, { 'myvalue': '20% of all'})
fog
  • 3,266
  • 1
  • 25
  • 31
5

I found a better hack. Just append '%' to your search query_text.

con, queryset_list = psycopg2.connect(**self.config), None
cur = con.cursor(cursor_factory=RealDictCursor)
query = "SELECT * "
query += " FROM questions WHERE  body LIKE %s OR title LIKE %s  "
query += " ORDER BY questions.created_at"
cur.execute(query, ('%'+self.q+'%', '%'+self.q+'%'))
p8ul
  • 2,212
  • 19
  • 19
3

I wonder if all of the above is really needed. I am using psycopg2 and was simply able to use:

data_dict['like'] = psycopg2.Binary('%'+ match_string +'%')
cursor.execute("SELECT * FROM some_table WHERE description ILIKE %(like)s;", data_dict)
Neha Chachra
  • 51
  • 1
  • 4
  • 6
    It can ve even easier: `cursor.execute("SELECT * FROM some_table WHERE description LIKE %s;", ['foobar%']); – jb. May 30 '12 at 10:47
2

Instead of escaping the percent character, you could instead make use of PostgreSQL's regex implementation.

For example, the following query against the system catalogs will provide a list of active queries which are not from the autovacuuming sub-system:

SELECT procpid, current_query FROM pg_stat_activity
WHERE (CURRENT_TIMESTAMP - query_start) >= '%s minute'::interval
AND current_query !~ '^autovacuum' ORDER BY (CURRENT_TIMESTAMP - query_start) DESC;

Since this query syntax doesn't utilize the 'LIKE' keyword, you're able to do what you want... and not muddy the waters with respect to python and psycopg2.

Uli Köhler
  • 13,012
  • 16
  • 70
  • 120
Brian
  • 21
  • 4
1

Having failed to find a built-in function so far, the one I wrote is pretty simple:

def escape_sql_like(s):
    return s.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')
EMP
  • 59,148
  • 53
  • 164
  • 220
  • @JensTimmerman this function only escape the like tokens, to use the normal string escaping on the result before using it in a query. correct string escaping depends on the sessing `standard_conforming_stings` and so is best done using the library code. – Jasen Jul 13 '15 at 23:36
  • More concisely, `re.sub(r'([%\\"\'_])', r'\\\1', s)` – jstaab Mar 09 '20 at 17:22
0

You can create a Like class subclassing str and register an adapter for it to have it converted in the right like syntax (e.g. using the escape_sql_like() you wrote).

piro
  • 13,378
  • 5
  • 34
  • 38
  • An interesting idea that I hadn't thought of, but you would invariably need to combine the escaped string with real `LIKE` operators (% or _), otherwise you might as well have used `=` instead of `LIKE`. If you do that then I'm not sure what the benefit of this approach is over the simpler approach of just calling the escape function. – EMP Feb 16 '10 at 22:20
0

I made some modifications to the code above to do the following:

def escape_sql_like(SQL):
    return SQL.replace("'%", 'PERCENTLEFT').replace("%'", 'PERCENTRIGHT')

def reescape_sql_like(SQL):
    return SQL.replace('PERCENTLEFT', "'%").replace('PERCENTRIGHT', "%'")

SQL = "SELECT blah LIKE '%OUCH%' FROM blah_tbl ... "
SQL = escape_sql_like(SQL)
tmpData = (LastDate,)
SQL = cur.mogrify(SQL, tmpData)
SQL = reescape_sql_like(SQL)
cur.execute(SQL)
wandarkaf
  • 1,839
  • 20
  • 30
0

It just requires to concatenate double % before and after it. Using "ilike" instead of "like" makes it case insensitive.

query = """
    select 
        * 
    from 
        table 
    where 
        text_field ilike '%%' || %(search_text)s || '%%'
"""
cem
  • 1,535
  • 19
  • 25
0

From 2023, Here is how I do it with psycopg3

query = f'''SELECT * FROM table where column like %s;'''
cursor.execute(query, f'%{my_value}%')
-3

I think it would be simpler and more readable to use f-strings.

query = f'''SELECT * FROM table where column like '%%{my_value}%%' '''
cursor.execute(query)
  • 4
    Never do this, this is insecure. Introduces a risk of an SQL Injection. – Eqzt111 Aug 05 '21 at 06:41
  • Well, it's just a example like anothers from this question who use the same approach. It's not abou security but how to escape % in query. And this is only a security risk if you use the raw input from the user or something like this. – Tama Wilson Nov 08 '21 at 14:10