Экранирование специальных символов в инструкции SQL LIKE с использованием параметров sql

У меня есть таблица, содержащая продукты. Мне нужно сделать запрос, чтобы найти все совпадающие результаты со значением, введенным пользователем. Я использую SqlParameter для вставки входных данных.

SqlCommand findProcutsByPattern = new SqlCommand(
    "SELECT *" +
    " FROM [Products]" +
    " WHERE ProductName LIKE @pattern", connection);

findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + pattern + '%');

Проблема возникает, когда строка пользовательского ввода содержит «_» или «%», поскольку они интерпретируются как специальные символы. С другой стороны, учитывая это:

Командные объекты используют параметры для передачи значений операторам SQL или хранимым процедурам, обеспечивая проверку и проверку типов. В отличие от текста команды, ввод параметра рассматривается как буквальное значение, а не как исполняемый код.

У меня не должно быть таких проблем. Нужно ли мне заменять / экранировать все «_» и «%» во входной строке или есть более элегантное решение. Я хочу, чтобы ввод рассматривался как литерал.

У меня есть несколько записей в таблице, которые содержат специальные символы в имени (N_EW, N\EW, N%EW, N"EW, N'EW). Указание \, "< /strong> и ', поскольку ввод работает нормально (рассматривает их как литералы).


sql c# sql-server escaping sqlparameter
person zhulien    schedule 06.09.2014    source источник
comment
Ваш запрос объединен с SELECT *FROM [Products]WHERE ProductName LIKE @pattern Не хватает пробелов   -  person juergen d    schedule 06.09.2014
comment
Не беспокойтесь о пробелах, запрос работает отлично. Кроме спецсимволов.. :)   -  person zhulien    schedule 06.09.2014
comment
Этот конкретный запрос действительно работает. Но если вы замените * именем столбца, этого не произойдет. Но это всего лишь подсказка для будущих запросов   -  person juergen d    schedule 07.09.2014
comment
О, да, это правильно. Я знаю об этом, это был просто пример кода, объясняющий проблему. Спасибо!   -  person zhulien    schedule 09.09.2014

Ответы (4)


arrow_upward
12
arrow_downward

У вас есть два варианта:

  • заключите их в [ и ]. Так:

    where pattern like '[%]'

    Ищет символ процента. Полный список символов для экранирования - '_', '%', '[', ']' с соответствующими заменами '[_]', '[%]', '[[]', '[]]'. Пример кода можно найти в Escapeing the escape-символ не работает — оператор SQL LIKE

  • используйте escape-символ, который вряд ли будет в строке, например, обратную кавычку:

    where pattern like '`%' escape '`'

    (См. синтаксис в MSDN — LIKE (Transact-SQL).)

В обоих случаях я бы посоветовал вам сделать замену на прикладном уровне, но вы также можете сделать это в SQL, если хотите:

where pattern like replace(@pattern, '%', '[%]')

И предоставление конечному пользователю доступа к подстановочным знакам может быть полезным с точки зрения пользовательского интерфейса.

Примечание: в запросе LIKE есть еще пара специальных символов '-' и '^', но их не нужно экранировать, если вы уже экранируете '[' и ']'.

person Gordon Linoff    schedule 06.09.2014
comment
замена скобками не работает для скобок. Можешь помочь. rextester.com/MJST70317 - person Anonymous Creator; 11.06.2018
comment
@ХардикВирадия . . . Я не говорил, что это работает для квадратных скобок, но с ними достаточно просто обращаться, например, с col1 LIKE '%$[$_$]%' ESCAPE '$'. - person Gordon Linoff; 12.06.2018
comment
Хм. Это произошло с изменениями @alexei Levenkov. как указано в '', '%', '[', ']' с соответствующими заменами '[]', '[%]', '[[]', '[]]' в отвечать. - person Anonymous Creator; 12.06.2018

arrow_upward
2
arrow_downward

Вы можете сделать это следующим образом: укажите явный escape-символ в строке SQL, а затем поместите этот escape-символ перед всеми символами % и _ внутри строки, которую вводит пользователь:

SqlCommand findProcutsByPattern = new SqlCommand(
    @"SELECT *
    FROM [Products]
    WHERE ProductName LIKE @pattern", connection) ESCAPE '_'"

При установке параметра замените все экземпляры _ и % на __ и _%:

var escapedPattern = Regex.Replace(pattern, "[%_]", "_$0");

Демонстрация

person Sergey Kalinichenko    schedule 06.09.2014

arrow_upward
1
arrow_downward

Вообще говоря, ручное экранирование значений в SQL считается плохой практикой, поскольку использование параметров является предпочтительным (и более безопасным) решением.

Однако в вашем примере вы уже используете параметры и хотите использовать оператор LIKE, поэтому ручное экранирование символов должно быть в порядке. Не забудьте также экранировать escape-символы - см. https://stackoverflow.com/a/13861567/232175 для какой-то код.

person Matthias    schedule 06.09.2014

arrow_upward
1
arrow_downward

Итак, в основном, я вручную экранировал (заменил) все символы подстановки, которые, похоже, теперь работают нормально. Вот окончательный код.

        SqlCommand findProcutsByPattern = new SqlCommand(
            "SELECT *" +
            "FROM [Products]" +
            "WHERE ProductName LIKE @pattern", connection);

        string patternEscaped = pattern.Replace("_", "[_]");
        patternEscaped = patternEscaped.Replace("%", "[%]");
        patternEscaped = patternEscaped.Replace("[", "[[]");

        findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + patternEscaped + '%');

Спасибо за поддержку!

ОБНОВЛЕНИЕ: я вижу...

escape_character Символ, который помещается перед подстановочным знаком, чтобы указать, что подстановочный знак следует интерпретировать как обычный символ, а не как подстановочный знак. escape_character — это символьное выражение, которое не имеет значения по умолчанию и должно оцениваться только как один символ.

Так что вам все равно придется делать побег самостоятельно.

person zhulien    schedule 06.09.2014
comment
Приведенный выше код не будет работать идеально, так как он сначала заменит % на [%], а затем заменит [ на [[], в результате чего получится строка [[]%]. Если вы измените порядок вызовов .Replace, сначала поставьте замену [, так будет лучше. - person Adam B; 24.08.2016