转义 SQL“LIKE” Postgres 与 psycopg2 的值

发布于 2024-08-18 18:37:48 字数 514 浏览 13 评论 0原文

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),但我在那里找不到答案。)

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(13

后eg是否自 2024-08-25 18:37:48

是的,这真是一团糟。默认情况下,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 标准。叹...

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

冷血 2024-08-25 18:37:48

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

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

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

sql_query = "select * from mytable where website like '%%.com'"
cursor.fetchall(sql_query)
战皆罪 2024-08-25 18:37:48

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

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

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

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)})
柠檬色的秋千 2024-08-25 18:37:48

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

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

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'})
不及他 2024-08-25 18:37:48

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

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

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+'%'))
微凉 2024-08-25 18:37:48

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

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

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)
烟火散人牵绊 2024-08-25 18:37:48

您可以使用 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 的水。

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.

鸠魁 2024-08-25 18:37:48

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

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

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('_', '\\_')
萌酱 2024-08-25 18:37:48

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

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

删除会话 2024-08-25 18:37:48

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

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)

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)
埋葬我深情 2024-08-25 18:37:48

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

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

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 || '%%'
"""
就此别过 2024-08-25 18:37:48

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

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

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}%')
你又不是我 2024-08-25 18:37:48

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

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

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)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文