Как экранировать строку при сопоставлении с шаблоном в PostgreSQL

Я хочу найти строки, где текстовый столбец начинается с заданной пользователем строки, например,SELECT * FROM users WHERE name LIKE 'rob%' но "ограбь" не подтвержден пользовательский ввод. Если пользователь пишет строку, содержащую специальный символ шаблона, такой как «rob_», он будет соответствовать обоим «robert42». и "rob_the_man". Я должен быть уверен, что строка соответствует буквально, как бы я это сделал? Нужно ли обрабатывать выход на уровне приложения или это более красивый способ?

Я использую PostgreSQL 9.1 игоу-PgSQL для го.

Ответы на вопрос(4)

единственными специальными символами с оператором LIKE являются проценты и подчеркивание, и их можно легко избежать вручную, используя обратную косую черту. Это не очень красиво, но это работает.

SELECT * FROM users WHERE name LIKE
regexp_replace('rob', '(%|_)', '\\\1', 'g') || '%';

Мне странно, что в PostgreSQL нет таких функций. Кто хочет, чтобы их пользователи писали свои шаблоны?

 16 апр. 2012 г., 04:00
Два или триreplace() звонки, как правило, намного быстрее, чем одинregexp_replace() вызов. Вы должны пойти с Даниилом, очень хорошо продуманный ответ.
 04 янв. 2019 г., 21:31
Если вы идете по этому маршруту, вам также следует'\' так как входная строка может быть что-то вроде'rob\%'так что если вы замените'%' с'\%'вы в конечном итоге'rob\\%', который является точным / буквальным'rob\' затем'%' подстановочные
 15 апр. 2012 г., 16:05
Я не помню проблему, обсуждаемую в списках рассылки PostgreSQL, - возможно, она просто не поднималась раньше. Я подозреваю, что любому, кто отправляет патч, было бы целесообразно предоставить соответствующую функцию для экранирования данных, которые должны быть подключены к регулярному выражению, поскольку они, похоже, более популярны, чем LIKE, среди пользователей PostgreSQL (при этом оператором совпадение выражений). Если вы хотите отправить патч, я был бы рад помочь вам с процессом и предоставить обзор.

которые будут использоваться в шаблоне вlike выражения используют escape-символ:

SELECT * FROM users WHERE name LIKE replace(replace(user_input, '_', '\\_'), '%', '\\%');
 Betamos14 апр. 2012 г., 18:35
Спасибо. Я закончил тем же путем (см. Мой ответ)
 14 апр. 2012 г., 18:40
Я думаю, что это не обрабатывает случай, когда \ появляется в пользовательском вводе. Я отправил другой ответ, который, я надеюсь, охватывает все.

чтобы их можно было буквально сопоставить в операторе LIKE, но это никак не обойти. Выбор заключается в том, чтобы делать это на стороне клиента или на стороне сервера (обычно с помощью SQL replace (), см. Ниже). Кроме того, чтобы сделать его на 100% правильным в общем случае, есть несколько вещей, которые следует учитывать.

По умолчанию символ кавычки, который следует использовать перед _ или%, является обратной косой чертой (\), но его можно изменить с помощью предложения ESCAPE, следующего сразу за предложением LIKE. В любом случае символ кавычки должен повторяться дважды в шаблоне, чтобы буквально совпадать как один символ.

Пример:... WHERE field like 'john^%node1^^[email protected]%' ESCAPE '^' будет соответствоватьjohn%node1^[email protected] с чем угодно

Существует проблема с выбором по умолчанию обратной косой черты: она уже используется для других целей, когдаstandard_conforming_strings ВЫКЛЮЧЕН (PG 9.1 по умолчанию включен, но предыдущие версии все еще широко используются, об этом стоит подумать).

Также, если цитирование для подстановочного знака LIKE выполняется на стороне клиента в сценарии ввода данных пользователем, оно приходитin addition to к обычным кавычкам, уже необходимым при вводе пользователем.

Взгляд на пример go-pgsql говорит о том, что он использует заполнители в стиле $ N для переменных ... Поэтому здесь сделана попытка написать его каким-то универсальным образом: он работает со стандартными_конформными_строками, как ON, так и OFF, с использованием серверной стороны. замена [% _], альтернативного символа кавычки, кавычка символа кавычки, и избегает SQL инъекций:

   db.Query("SELECT * from USERS where name like replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'",
     variable_user_input);

что вы вообще не должны интерполировать пользовательский ввод в ваш sql. Даже выход из sql все еще опасен.

Следующее, которое использует библиотеку go-s db / sql, иллюстрирует гораздо более безопасный способ. Замените вызовы Prepare и Exec на те эквиваленты, которые у вас есть в go postgresql.

// The question mark tells the database server that we will provide
// the LIKE parameter later in the Exec call
sql := "SELECT * FROM users where name LIKE ?"
// no need to escape since this won't be interpolated into the sql string.
value := "%" + user_input
// prepare the completely safe sql string.
stmt, err := db.Prepare(sql)
// Now execute that sql with the values for every occurence of the question mark.
result, err := stmt.Exec(value)

Преимущества этого в том, что пользовательский ввод можно безопасно использовать, не опасаясь, что он введет sql в выполняемые вами операторы. Вы также получаете преимущество повторного использования подготовленного sql для нескольких запросов, что в некоторых случаях может быть более эффективным.

 20 янв. 2013 г., 17:01
Это решение не позволяет пользователям правильно искать значения, содержащие литерал & quot;% & quot; или & quot; _ & quot; персонажи. Например, если user_input равен «на 100% лучше», он будет ошибочно совпадать со строками, такими как «в 100 раз лучше».

Ваш ответ на вопрос