转义 SQL“LIKE” Postgres 与 psycopg2 的值

Question

psycopg2 是否具有转义 Postgres 的 LIKE 操作数值的函数？

例如，我可能想要匹配以字符串“20% of all”开头的字符串，因此我想编写如下内容：

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

是否有一个现有的 escape_sql_like 函数可以插入这里？

（类似的问题 如何显式引用字符串值（Python DB API/Psycopg2），但我在那里找不到答案。）

Answer 1

是的，这真是一团糟。默认情况下，MySQL 和 PostgreSQL 都使用反斜杠转义符。如果您还使用反斜杠而不是使用参数化再次转义字符串，这将是一个可怕的痛苦，并且根据 ANSI SQL:1992，它也是不正确的，它表示默认情况下在正常字符串转义之上没有额外的转义字符，并且因此无法包含文字 % 或 _。

如果您使用 MySQL 中的 NO_BACKSLASH_ESCAPE sql_mode 或 standard_conforming_strings 关闭反斜杠转义符（它们本身不符合 ANSI SQL），我认为简单的反斜杠替换方法也会出错。 /code> PostgreSQL 中的conf（PostgreSQL 开发人员现在已经威胁要在几个版本中这样做）。

唯一真正的解决方案是使用鲜为人知的 LIKE...ESCAPE 语法为 LIKE 模式指定显式转义字符。在 MySQL 和 PostgreSQL 中，它被用来代替反斜杠转义，使它们符合其他人的做法，并提供一种有保证的方式来包含带外字符。例如，使用 = 符号作为转义：

# 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+'%'))

这适用于 PostgreSQL、MySQL 和 ANSI SQL 兼容的数据库（当然对 paramstyle 取模，该参数在不同的数据库模块上会发生变化）。

MS SQL Server/Sybase 可能仍然存在问题，它显然也允许在 LIKE 表达式中使用 [az] 样式的字符组。在这种情况下，您还需要使用 .replace('[', '=[') 转义文字 [ 字符。然而，根据 ANSI SQL，转义不需要转义的字符是无效的！ （啊！）因此，尽管它可能仍然可以在真实的 DBMS 上运行，但您仍然不符合 ANSI 标准。叹...

Answer 2

我可以通过在 LIKE 操作数中使用 %% 来转义 %。

sql_query = "select * from mytable where website like '%%.com'"
cursor.fetchall(sql_query)

Answer 3

如果您使用准备好的语句，则输入将被包装在 '' 中以防止 sql 注入。这很棒，但也阻止了输入 + sql 连接。

解决此问题的最佳且最安全的方法是将 %(s) 作为输入的一部分传递。

cursor.execute('SELECT * FROM goats WHERE name LIKE %(name)s', { 'name': '%{}%'.format(name)})

Answer 4

你也可以从不同的角度来看待这个问题。你想要什么？您希望查询对于任何字符串参数都通过在参数后附加“%”来执行 LIKE。表达这一点的一个好方法是，无需借助函数和 psycopg2 扩展：

sql = "... WHERE ... LIKE %(myvalue)s||'%'"
cursor.execute(sql, { 'myvalue': '20% of all'})

Answer 5

我找到了一个更好的技巧。只需将“%”附加到您的搜索查询文本中即可。

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+'%'))

Answer 6

我想知道以上所有内容是否真的需要。我正在使用 psycopg2 并且能够简单地使用：

data_dict['like'] = psycopg2.Binary('%'+ match_string +'%')
cursor.execute("SELECT * FROM some_table WHERE description ILIKE %(like)s;", data_dict)

Answer 7

您可以使用 PostgreSQL 的正则表达式实现，而不是转义百分号字符。

例如，针对系统目录的以下查询将提供不是来自自动清理子系统的活动查询列表：

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;

由于此查询语法不使用“LIKE”关键字，因此您可以执行您想要的操作...并且不要混淆 python 和 psycopg2 的水。

Answer 8

到目前为止还没有找到内置函数，我写的函数非常简单：

def escape_sql_like(s):
    return s.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')

Answer 9

您可以创建一个 Like 类子类化 str 和 为其注册一个适配器，使其以正确的语法进行转换（例如使用您编写的escape_sql_like()）。

Answer 10

我对上面的代码做了一些修改以执行以下操作：

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)

Answer 11

只需要在其前后连接两个 % 即可。使用“ilike”而不是“like”使其不区分大小写。

query = """
    select 
        * 
    from 
        table 
    where 
        text_field ilike '%%' || %(search_text)s || '%%'
"""

Answer 12

从 2023 年开始，以下是我使用 psycopg3 的方法

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

Answer 13

我认为使用 f 字符串会更简单且更具可读性。

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