Экранирование значения SQL LIKE для Postgres с помощью psycopg2

Да, это настоящий бардак. И MySQL, и PostgreSQL по умолчанию используют для этого обратную косую черту. Это ужасная боль, если вы также снова экранируете строку с помощью обратной косой черты вместо использования параметризации, и это также неверно в соответствии с ANSI SQL: 1992, в котором говорится, что по умолчанию нет дополнительных escape-символов поверх обычного экранирования строки, и следовательно, нет возможности включить литерал % или _.

Я предполагаю, что простой метод замены обратной косой черты также пойдет не так, если вы отключите экранирование обратной косой черты (которые сами по себе несовместимы с ANSI SQL), используя NO_BACKSLASH_ESCAPE sql_mode в MySQL или standard_conforming_strings conf в PostgreSQL (которым разработчики 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, который меняется в разных модулях db).

Все еще может быть проблема с MS SQL Server/Sybase, который, по-видимому, также допускает группы символов в стиле [a-z] в выражениях LIKE. В этом случае вы также захотите экранировать буквальный символ [ с помощью .replace('[', '=['). Однако, согласно ANSI SQL, экранирование символа, который не требует экранирования, недопустимо! (Аргх!) Таким образом, хотя он, вероятно, по-прежнему будет работать в реальных СУБД, вы все равно не будете соответствовать стандарту ANSI. вздох...

Мне удалось избежать %, используя %% в операнде LIKE.

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

Можно также посмотреть на эту проблему под другим углом. Что ты хочешь? Вам нужен запрос, который для любого строкового аргумента выполняет LIKE, добавляя '%' к аргументу. Хороший способ выразить это, не прибегая к функциям и расширениям psycopg2, мог бы быть:

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

Интересно, действительно ли нужно все вышеперечисленное? Я использую psycopg2 и просто смог использовать:

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

Вместо экранирования символа процента вы можете использовать реализацию регулярных выражений 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.

Если вы используете подготовленный оператор, то ввод будет заключен в '', чтобы предотвратить внедрение sql. Это здорово, но также предотвращает конкатенацию input + sql.

Лучшим и безопасным способом обойти это было бы передать %(s) как часть ввода.

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

Пока не удалось найти встроенную функцию, я написал довольно простую:

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

Я нашел лучший хак. Просто добавьте «%» к вашему поисковому запросу 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+'%'))

Вы можете создать подкласс Like класса str и зарегистрировать для него адаптер чтобы преобразовать его в правильный синтаксис (например, используя escape_sql_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)

Я думаю, что было бы проще и читабельнее использовать f-строки.

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