Экранирование спецсимволов в SQL запросах
Обычно для экранирования спецсимволов в SQL-запросах используется символ обратной косой черты. Но символ апострофа можно экранировать вторым символом апострофа. Другими словами два символа апострофа подряд считаются одним экранированным апострофом. Примеры для MySQL:
INSERT INTO `text_table`(`text`) VALUES (‘as»df’);
Стандартный способ экранирования:
INSERT INTO `text_table`(`text`) VALUES (‘as\’df’);
INSERT INTO `text_table`(`text`) VALUES (‘as\\df’);
А вот экранировать слеш при помощи апострофа нельзя, такой запрос не сработает:
INSERT INTO `text_table`(`text`) VALUES (‘as’\df’);
В новых версиях Postgresql апостроф нельзя экранировать при помощи обратной косой черты.
При использовании SQL-запросов из программного кода лучше всего полагаться на подготовленные запросы. Это не только позволяет избавиться от мороки с экранированием символов но и защищает от SQL-инъекций.
Как экранировать кавычки в sql
← →
АлександрМ ( 2005-11-30 13:01 ) [0]
Используя компонент TQuery пытаюсь отправить SQL запрос вида:
UPDATE mytable SET field1=»Текст. \» — это кавычка. Опять текст», field2=»Текст» WHERE field3=1111
Для экранирования кавычек использую обратный слеш, в соответствии с документацией по MySQL. Запрос выдаёт ошибку (без кавычки внутри текста запрос проходит). Если этот же запрос передаю в базу например через MySQL Front то всё работает.
← →
Desdechado © ( 2005-11-30 13:09 ) [1]
а ты параметры не хочешь использовать, а?
← →
АлександрМ ( 2005-11-30 13:15 ) [2]
Если я буду использовать параметры, то TQuery сам разбедётся как кавычки экранировать? Сейчас попробую.
← →
Плохиш © ( 2005-11-30 13:51 ) [3]
quotedstr
← →
Anatoly Podgoretsky © ( 2005-11-30 13:56 ) [4]
АлександрМ (30.11.05 13:15) [2]
Если будешь использовать параметры, то экранировать нечего.
Во вторых экранирование не имеет никакого отношения к TQuery
← →
АлександрМ ( 2005-11-30 14:31 ) [5]
Anatoly Podgoretsky, если экранирование не имеет никакого отношения к Query, то почему не работате мой запрос? Как будто ошибка в синтаксисе.
Я бы с радостью не использовал бы параметры. Мне гораздо удобнее составить сразу SQL запрос, чем вводить кучу параметров, а потом каждому присваивать значение.
В общем-то проблему сейчас решил. Спасибо всем за советы. Но непонимание осталось. Почему всё-таки не сработало экранирование обратным слешом?
← →
Anatoly Podgoretsky © ( 2005-11-30 14:51 ) [6]
АлександрМ (30.11.05 14:31) [5]
Работает он потому, что это информация для сервера, что следующий символ данного литерала не ограничитель, а символ.
← →
АлександрМ ( 2005-11-30 15:09 ) [7]
Anatoly Podgoretsky, запрос не работает! То есть, до сервера не доходит, что кавычка должна быть символом. Сервер почему-то считает, что это ограничитель! В результате чего и возникает проблема!
← →
ANB © ( 2005-11-30 15:21 ) [8]
> АлександрМ (30.11.05 15:09) [7]
Может я чего то путаю, но вы не пробовали обрамляющие кавычки заменить на одинарные ?
← →
АлександрМ ( 2005-11-30 18:16 ) [9]
ANB, тогда двойная кавычка проходит. 😉
Только одинарные проходить перестают.
← →
Плохиш © ( 2005-11-30 18:36 ) [10]
> АлександрМ (30.11.05 18:16) [9]
Т.е. [3] совсем не помогает?
← →
ANB © ( 2005-12-01 09:31 ) [11]
> АлександрМ (30.11.05 18:16) [9]
А если у тебя в строках бывают разные кавычки — то не фиг парится, а переходи на параметры.
ЗЫ. В оракле \» и \» тоже не работает (другой синтаксис для этого). Обычно приходится использовать для маски в LIKE, например, когда нужно % искать.
SQL-Ex blog
Одной из моих любимых команд SQL являлась QUOTENAME. При программировании генерации кода обычно возникает необходимость заключать строковое значение в кавычки и экранировать любые символы, совпадающие с теми, которыми вы ограничиваете строку, их удвоением. (А если у вас пара таких символов в строке, вам их потребуется уже четыре.) Например, чтобы взять следующую строку в одинарные кавычки (‘):
Mr. O'Malley
Чтобы иметь возможность использовать её в динамическом операторе или объявлении переменной, потребуется удвоить одинарную кавычку в строке:
'Mr. O''Malley'
Или, если вы Rob Volk (@sql_r на Twitter), и хотите создать раздражающую базу данных на вашем лучшем заклятом SQL Server, то, чтобы включить скобки в имя базы типа:
This [database] Is Awesome
вам придется сделать так:
CREATE DATABASE [This [database]] Is Awesome];
Удваивается закрывающая скобка, но не открывающая. Для экранирования можно использовать QUOTENAME. Параметрами этой функции являются строка и разделитель. По умолчанию удваивается скобка как у большинства имён SQL Server, хотя вы можете использовать любой символ для удвоения. Так для нашей строки:
DECLARE @Value nvarchar(15) = 'Mr. O''Malley';
SELECT @Value, QUOTENAME(@value) AS objectName,
QUOTENAME(@value,'''') AS string;
Этот код вернет
| objectName | string | |
|---|---|---|
| Mr. O’Malley | [Mr. O’Malley] | ‘Mr. O»Malley’ |
Кажется, работает отлично, поэтому вы чувствуете, что, если вам потребуется нагенерировать некоторый код, вы сможете поступить так:
DECLARE @Value nvarchar(200) = 'Mr. O''Malley';
SELECT CONCAT('SELECT ',QUOTENAME(@Value,''''));
SELECT 'Mr. O''Malley'
Выполните этот запрос в Management Studio. Вы увидите на вкладке результата строку, начинающуюся с приведенного выше текста. Проблема состоит в том, что QUOTENAME предназначена для закавычивания значений имен SQL Server, а поскольку в SQL Server имена не могут быть длиннее, чем 128 символов. на входе должно быть не более 128 символов (ниже покажем, что может быть и больше). Итак:
SELECT QUOTENAME(REPLICATE('a',200));
Когда вход превышает 128 символов, возвращается NULL без каких либо предупреждений. Это, мягко говоря, не то, что вы хотели. В моем случае я разработал генератор скрипта расширенных свойств, который принимает значение типа sql_variant и преобразует его к nvarchar(max). Я не тестировал входы, превышающие 128 символов, но коллега любезно предоставил мне строку размером порядка 8000 символов. К счастью, это не был рабочий сервер, где бы безостановочно звонил телефон поддержки.
Итак, я могу предложить использование QUOTENAME в рабочем коде только для квотирования реальных имен операторов SQL, и использовать более неуклюжий метод для других целей:
DECLARE @Value nvarchar(15) = 'Mr. O''Malley'
SELECT @Value,
'''' + REPLACE(@value,'''','''''') + '''' AS AwkwardButRightWay,
QUOTENAME(@Value,'''') AS QUOTENAME;
Этот код демонстирует один и тот же результат для обоих вариантов.
Следует заметить, что это (как и любая простая повторно исполняемая скалярная функция) просится для использования в качестве пользовательской функции. В версиях, предшествующих 2019, всегда однозначно советовали избегать их, т.к. они, мягко говоря, ограничивали производительность. В одних случаях падение производительности было незначительным, в других — существенными провалами. В SQL Server 2019 Microsoft изменила механизм выполнения некоторых скалярных функций, который будет «встраивать» код функции в план запроса, использующего эту функцию. В результате производительность, полученная для перекодируемого оператора и при использовании функции соизмеримы (даже для довольно сложных функций!).
Построим, например, в базе данных WideWorldImporters следующую функцию:
USE WideWorldImporters
GO
CREATE SCHEMA Utility
GO
--всегда делаю одинарные кавычки для моей функции
CREATE OR ALTER FUNCTION Utility.EscapeString ( @string nvarchar(max) )
RETURNS nvarchar(max)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
--если раскомментировать эту строку, функция не будет встраиваемой
--DECLARE @datevalue date = GETDATE();
RETURN '''' + REPLACE(@string,'''','''''') + '''';
END;
Выполнить функцию можно так:
SELECT Utility.EscapeString(CityName)
FROM Application.Cities;
И вы можете проверить, встраивается ли она, таким образом:
SELECT is_inlineable
FROM sys.sql_modules
WHERE OBJECT_ID = OBJECT_ID('Utility.EscapeString');
Этот запрос для нашей новой функции возвращает 1.
Встраиваемая или нет, вы не увидите каких либо изменений в этом простом примере — я просто хотел указать на это как на более ценную возможность в будущем. Подробнее о встраиваемых функциях смотрите в публикации Brent Ozar’а.
Наконец, давайте взглянем на немного туповатый трюк, который в целом полезен для определения того, наколько длинной может быть заковыченная строка. Меня интересует, какого максимального размера может быть вывод функции QUOTENAME. Если вы выполните следующий оператор:
SELECT QUOTENAME(']')
SELECT QUOTENAME(']]')
вернет []]]]], что на 2 символа больше, чем исходная строка. Если у нас 128 символов ], мы должны получить SELECT 127*2+4 или 258 символов, которые должен вывести следующий оператор.
SELECT LEN(QUOTENAME(REPLICATE(']',128)))
Мы могли бы это просто прочитать в документации о QUOTENAME, где говорится о результате nvarchar(258)! Следовательно, вот какого размера должна быть переменная/столбец, чтобы ее можно было обработать с помощью функции EscapeString:
((максимальная длина вашего входа) - 1) * 2 + 4
Здесь максимальная длина — это не 2 миллиарда как у varchar(max), а максимальная длина, которую может иметь ваш источник данных. Конечно, когда вы используете это значение со всеми экранированными строками, оно будет выглядеть больше, чем результирующее значение, но просто потому, что нам требуется пространство для 258 символов на имя, что не означает, что нам требуется именно 258 символов. Это просто еще одно препятствие, с которым вы столкнетесь, строя пуленепробиваемый генератор кода.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Как экранировать кавычки в sql
Хороший способ разрабатывать на PL/pgSQL заключается в том, чтобы в одном окне с текстовым редактором по выбору создавать тексты функций, а в другом окне с psql загружать и тестировать эти функции. В таком случае удобно записывать функцию, используя CREATE OR REPLACE FUNCTION . Таким образом, можно легко загрузить файл для обновления определения функции. Например:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ . $$ LANGUAGE plpgsql;
В psql , можно загрузить или перезагрузить такой файл определения функции, выполнив:
\i filename.sql
а затем сразу выполнять команды SQL для тестирования функции.
Ещё один хороший способ разрабатывать на PL/pgSQL связан с использованием GUI инструментов, облегчающих разработку на процедурном языке. Один из примеров такого инструмента pgAdmin , хотя есть и другие. Такие инструменты часто предоставляют удобные возможности, такие как экранирование одинарных кавычек, отладка и повторное создание функций.
40.11.1. Обработка кавычек
Код функции на PL/pgSQL указывается в команде CREATE FUNCTION в виде строки. Если записывать строку как обычно, внутри одинарных кавычек, то любой символ одинарной кавычки должен дублироваться, так же как и должен дублироваться каждый знак обратной косой черты (если используется синтаксис с экранированием в строках). Дублирование кавычек в лучшем случае утомительно, а в более сложных случаях код может стать совершенно непонятным, так как легко может потребоваться четыре или более идущих подряд кавычек. Вместо этого при создании тела функции рекомендуется использовать знаки доллара в качестве кавычек (см. Подраздел 4.1.2.4). При таком подходе никогда не потребуется дублировать кавычки, но придётся позаботиться о том, чтобы иметь разные долларовые разделители для каждого уровня вложенности. Например, команду CREATE FUNCTION можно записать так:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ . $PROC$ LANGUAGE plpgsql;
Внутри можно использовать кавычки для простых текстовых строк и $$ для разграничения фрагментов SQL-команды, собираемой из отдельных строк. Если нужно взять в кавычки текст, который включает $$ , можно использовать $Q$ , и так далее.
Следующая таблица показывает, как применяются знаки кавычек, если не используется экранирование долларами. Это может быть полезно при переводе кода, не использующего экранирование знаками доллара, в нечто более понятное.
В начале и конце тела функции, например:
CREATE FUNCTION foo() RETURNS integer AS ' . ' LANGUAGE plpgsql;
Внутри такой функции любая кавычка должна дублироваться. 2 кавычки
Для строковых литералов внутри тела функции, например:
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
При использовании знаков доллара можно просто написать:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
и именно это увидит исполнитель PL/pgSQL в обоих случаях. 4 кавычки
Когда нужны одинарные кавычки в строковой константе внутри тела функции, например:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
К a_output будет добавлено: AND name LIKE ‘foobar’ AND xyz
При использовании знаков доллара это записывается так:
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
будьте внимательны, при этом не должно быть внешнего долларового разделителя $$ . 6 кавычек
Когда нужны одинарные кавычки в строковой константе внутри тела функции, при этом кавычки находятся в конце строковой константы. Например:
a_output := a_output || '' AND name LIKE ''''foobar''''''
К a_output будет добавлено: AND name LIKE ‘foobar’ .
При использовании знаков доллара это записывается так:
a_output := a_output || $$ AND name LIKE 'foobar'$$
10 кавычек
Когда нужны две одиночные кавычки в строковой константе (это уже 8 кавычек), примыкающие к концу строковой константы (ещё 2). Вероятно, такое может понадобиться при разработке функции, которая генерирует другие функции, как показано в Примере 40.9. Например:
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;'';
Значение a_output затем будет:
if v_. like ''. '' then return ''. ''; end if;
При использовании знаков доллара:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;
где предполагается, что нужны только одиночные кавычки в a_output , так как потребуется повторное взятие в кавычки перед использованием.
40.11.2. Дополнительные проверки во время компиляции
Чтобы помочь найти и предупредить простые, но часто встречающиеся проблемы, PL/PgSQL предоставляет дополнительные проверки . Если они включены в конфигурации, то во время компиляции функций будут выдаваться дополнительные сообщения WARNING или ошибки ERROR . Функция, при компиляции которой выдавалось WARNING , при последующем выполнении не будет выдавать это сообщение и её можно протестировать в отдельной среде разработки.
Для включения этих проверок используются параметры конфигурации plpgsql.extra_warnings для предупреждений и plpgsql.extra_errors для ошибок. Каждому из параметров можно присвоить список значений, разделённых запятыми, значение «none» или «all» . По умолчанию используется «none» . В настоящий момент доступна только одна проверка:
shadowed_variables
Проверяет, что объявление новой переменной не скрывает ранее объявленную переменную.
Следующий пример показывает эффект присвоения plpgsql.extra_warnings значения shadowed_variables :
SET plpgsql.extra_warnings TO 'shadowed_variables'; CREATE FUNCTION foo(f1 int) RETURNS int AS $$ DECLARE f1 int; BEGIN RETURN f1; END; $$ LANGUAGE plpgsql; WARNING: variable "f1" shadows a previously defined variable LINE 3: f1 int; ^ CREATE FUNCTION
| Пред. | Наверх | След. |
| 40.10. PL/pgSQL изнутри | Начало | 40.12. Портирование из Oracle PL/SQL |