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

Question

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

Я использую PostgreSQL 9.1 и go-pgsql для Go.

Answer 1

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

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

Пример: ... WHERE field like 'john^%node1^^node2.uucp@%' ESCAPE '^' будет соответствовать john%node1^node2.uccp@, за которым следует что угодно.

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

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

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

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

Answer 2

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

SELECT * FROM users WHERE name LIKE replace(replace(user_input, '_', '\\_'), '%', '\\%');

Answer 3

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

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

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

Answer 4

Лучший ответ заключается в том, что вы вообще не должны интерполировать пользовательский ввод в ваш sql. Даже избежать sql все еще опасно.

Следующее, которое использует библиотеку go / 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 для нескольких запросов, что в некоторых случаях может быть более эффективным.