python - must - Escape el valor "LIKE" de SQL para Postgres con psycopg2

psycopg2 postgres 10

Question

¿Tiene psycopg2 una función para escapar del valor de un operando LIKE para Postgres?

Por ejemplo, es posible que desee unir cadenas que comiencen con la cadena "20% de todas", por lo que quiero escribir algo como esto:

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

¿Hay una función escape_sql_like existente que podría conectar aquí?

(Pregunta similar a Cómo citar un valor de cadena explícitamente (Python DB API / Psycopg2) , pero no pude encontrar una respuesta allí.)

Answer 1

Al no haber podido encontrar una función incorporada hasta ahora, la que escribí es bastante simple:

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

Answer 2

En lugar de escapar del carácter de porcentaje, podría utilizar la implementación de expresiones regulares de PostgreSQL.

Por ejemplo, la siguiente consulta en los catálogos del sistema proporcionará una lista de consultas activas que no pertenecen al subsistema de autoaprendizaje:

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;

Dado que esta sintaxis de consulta no utiliza la palabra clave ''LIKE'', puedes hacer lo que quieras ... y no enturbiar las aguas con respecto a python y psycopg2.

Answer 3

Encontré un mejor hack. Simplemente agregue ''%'' a su búsqueda 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+''%''))

Answer 4

Hice algunas modificaciones al código anterior para hacer lo siguiente:

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 5

Me pregunto si todo lo anterior es realmente necesario. Estoy usando psycopg2 y simplemente pude usar:

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

Answer 6

Puede crear una clase de subclasificación de clase Like y registrar un adaptador para que se convierta en la sintaxis correcta (p. Ej., Utilizando el escape_sql_like() que escribió).

Answer 7

Sí, esto es un verdadero desastre. Tanto MySQL como PostgreSQL usan backslash-escapes para esto por defecto. Este es un dolor terrible si también escapa la cadena nuevamente con barras invertidas en lugar de usar parametrización, y también es incorrecto según ANSI SQL: 1992, que dice que no hay caracteres de escape adicionales por encima de la cadena normal que se escapa, y por lo tanto, no hay manera de incluir un % literal o _ .

Supongo que el método simple de sustitución de barra invertida también funciona mal si desactiva la barra diagonal inversa (que a su vez no son compatibles con ANSI SQL), utilizando NO_BACKSLASH_ESCAPE sql_mode en MySQL o standard_conforming_strings en PostgreSQL (que los servicios PostgreSQL han sido amenazadores hacer por un par de versiones ahora).

La única solución real es usar la sintaxis LIKE...ESCAPE poco conocida para especificar un carácter de escape explícito para el patrón LIKE . Esto se usa en lugar de la barra invertida de escape en MySQL y PostgreSQL, lo que hace que se ajusten a lo que hacen los demás y ofrece una forma garantizada de incluir los caracteres fuera de banda. Por ejemplo, con el signo = como 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+''%''))

Esto funciona en las bases de datos compatibles con PostgreSQL, MySQL y ANSI SQL (módulo, por supuesto, el estilo de estilo que cambia en diferentes módulos de db).

Todavía puede haber un problema con MS SQL Server / Sybase, que aparentemente también permite grupos de caracteres de estilo [az] en expresiones LIKE . En este caso, también querrá escapar del literal [ carácter con .replace(''['', ''=['') . Sin embargo, de acuerdo con ANSI SQL, escapar de un personaje que no necesita escapar no es válido. (¡Argh!) Entonces, aunque probablemente aún funcione con DBMS reales, aún no sería compatible con ANSI. suspiro...

Answer 8

También puede ver este problema desde un ángulo diferente. ¿Qué deseas? Desea una consulta que para cualquier argumento de cadena ejecuta LIKE agregando un ''%'' al argumento. Una buena forma de expresar eso, sin tener que recurrir a funciones y extensiones psycopg2 podría ser:

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