用psycopg2为Postgres逃避SQL "LIKE "值

53 人关注

psycopg2是否有一个函数用于转义一个 LIKE Postgres的操作数？

例如，我可能想匹配以 "20%的人 "为开头的字符串，所以我想这样写。

sql = '... WHERE ... LIKE %(myvalue)s'
cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' }
是否有一个现有的escape_sql_like我可以在这里插入的功能？
(问题类似于如何明确地引用一个字符串值(Python DB API/Psycopg2)。，但我在那里找不到答案）。


         1
         
         个评论


           
            如果你想查询...WHERE ...LIKE %(myvalue)%，其中myvalue是字符串？


         python


         postgresql


         psycopg2


         python-db-api


        12
        
        个回答


          已采纳


         0
         
         人赞同


          
           是的，这确实是个麻烦。MySQL和PostgreSQL都默认使用反斜线转义来处理这个问题。如果你也用反斜线来转义字符串而不是使用参数化，这是一个可怕的痛苦，而且根据ANSI SQL:1992，这也是不正确的，它说在正常的字符串转义之上默认没有额外的转义字符，因此没有办法包括一个字面的
           
            %
           
           或
           
            _
           
           。
          
          
           我想，如果你关闭了反斜线escapes（其本身不符合ANSI SQL），使用MySQL中的
           
            NO_BACKSLASH_ESCAPE
           
           sql_mode或PostgreSQL中的
           
            standard_conforming_strings
           
           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表达式中使用[a-z]风格的字符组。在这种情况下，你会希望用.replace('[', '=[')来转义字面的[字符。然而，根据ANSI SQL的规定，转义一个不需要转义的字符是无效的。(因此，尽管它可能仍然可以在真正的DBMS中工作，但你仍然不符合ANSI标准。


           
            
             
              standard_conforming_strings
             
             in postgres does
             
              not
             
             在类似查询中打破反斜杠转义。至少在12.7中没有。


          
           
            我能够通过使用
            
             %%
            
            来逃避
            
             %
            
            。
            
             LIKE
            
            操作数。
           
           sql_query = "select * from mytable where website like '%%.com'"
cursor.fetchall(sql_query)


           
            
             
              这应该是解决方案。这有什么问题吗？


           
            
             
              也许这与PostgreSQL的版本有关？这个答案答案是在问题和目前的顶级答案发布几年后才出现的。


           
            
             
              为了解决这个问题，我费尽心思，但只有这个方法有效。非常感谢!


          
           
            
             你也可以从不同的角度来看待这个问题。你想要什么？你想要一个查询，对于
             
              任何
             
             字符串参数通过在参数上添加'%'来执行一个LIKE。一个很好的表达方式，不需要借助于函数和psycopg2的扩展，可以是。
            
            sql = "... WHERE ... LIKE %(myvalue)s||'%'"
cursor.execute(sql, { 'myvalue': '20% of all'})


           
            
             
              
               这将匹配像
               
                2001 had worst of all terrorism
               
               这样的字符串。


           
            
             
              
               不，它不会，因为论证中的
               
                %
               
               会被引用。


           
            
             
              
               没有任何东西告诉pscopg2，%需要特殊待遇。


           
            
             
              
               它是一个绑定的变量：默认情况下，它是带引号的。


           
            
             
              
               是的，这就是为什么它不能工作。你应该测试一下。


          
           
            
             
              我发现了一个更好的破解方法。只需在你的搜索查询文本中添加'%'。
             
             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+'%'))


          
           
            
             
              
               如果你使用一个准备好的语句，那么输入将被包裹在
               
                ''
               
               中，以防止sql注入。这很好，但也可以防止输入+sql串联。
              
              
               最好和最安全的方法是将
               
                %
               
               作为输入的一部分传入。
              
              cursor.execute('SELECT * FROM goats WHERE name LIKE %(name)s', { 'name': '%{}%'.format(name)})


          
           
            
             
              
               
                
                
                 Neha Chachra
                
               
               
                发布于
                
                2020-08-08


          
           
            
             
              
               
                我不知道是否真的需要上述所有的东西。我正在使用psycopg2，并且能够简单地使用。
               
               data_dict['like'] = psycopg2.Binary('%'+ match_string +'%')
cursor.execute("SELECT * FROM some_table WHERE description ILIKE %(like)s;", data_dict)


           
            
             
              
               
                
                 
                  它可以更简单。`游标.执行("SELECT * FROM some_table WHERE description LIKE %s;", ['foobar%'])。


          
           
            
             
              
               
                
                 你可以使用PostgreSQL的regex实现，而不是转义百分之一的字符。
                
                
                 例如，以下针对系统目录的查询将提供一个非来自自动吸尘子系统的活动查询列表。
                
                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的水搅浑。


          
           
            
             
              
               
                
                 
                  到目前为止还没有找到一个内置函数，我写的这个函数非常简单。
                 
                 def escape_sql_like(s):
    return s.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_')


           
            
             
              
               
                
                 
                  
                   
                    @JensTimmerman 这个函数只转义类似的标记，在查询中使用之前，要对结果使用正常的字符串转义。正确的字符串转义取决于猜测
                    
                     standard_conforming_stings
                    
                    ，因此最好使用库代码来完成。


           
            
             
              
               
                
                 
                  
                   
                    More concisely,
                    
                     re.sub(r'([%\\"\'_])', r'\\\1', s)


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


           
            
             
              
               
                
                 
                  
                   
                    一个有趣的想法，我还没有想到，但是你总是需要将转义的字符串与真正的
                    
                     LIKE
                    
                    操作符（%或_）结合起来，否则你还不如用
                    
                     =
                    
                    而不是
                    
                     LIKE
                    
                    。如果你这样做了，那么我不确定这种方法比直接调用转义函数这种更简单的方法有什么好处。


          
           
            
             
              
               
                
                 
                  
                   我对上面的代码做了一些修改，做到了以下几点。
                  
                  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)


          
           
            
             
              
               
                
                 
                  
                   
                    它只是要求在其前后串联双%。使用 "ilike "而不是 "like "使得它不区分大小写。
                   
                   query = """
    select 
        table 
    where 
        text_field ilike '%%' || %(search_text)s || '%%'