STRING_ESCAPE (Transact-SQL)
Экранирует специальные символы в тексте и возвращает текст с экранированными символами. STRING_ESCAPE — это детерминированная функция, впервые представленная в SQL Server 2016.
Синтаксис
STRING_ESCAPE( text , type ) Аргументы
text
Выражениеnvarchar, представляющее объект, который следует экранировать.
type
Правила экранирования, которые будут применены. В настоящее время поддерживается значение ‘json’ .
Типы возвращаемых данных
Текст nvarchar(max) с экранированными специальными и управляющими символами. В настоящее время с помощью функции STRING_ESCAPE можно экранировать только специальные символы JSON, представленные в таблицах ниже.
| Специальный символ | Закодированная последовательность |
|---|---|
| Кавычки («») | \» |
| Обратная косая черта (\) | \\ |
| Солидус (/) | \/ |
| Отмена | \b |
| Перевод страницы | \f |
| Новая строка | \n |
| Возврат каретки | \r |
| Горизонтальная табуляция | \t |
| Управляющий символ | Закодированная последовательность |
|---|---|
| CHAR(0) | \u0000 |
| CHAR(1) | \u0001 |
| . | . |
| CHAR(31) | \u001f |
Remarks
Примеры
A. Экранирование текста согласно правилам форматирования JSON
Приведенный ниже запрос экранирует специальные символы с использованием правил JSON и возвращает экранированный текст.
SELECT STRING_ESCAPE('\ / \\ " ', 'json') AS escapedText; escapedText ------------------------------------------------------------- \\\t\/\n\\\\\t\"\t Б. Форматирование объекта JSON
Приведенный ниже запрос создает текст в формате JSON на основе числовых и строковых переменных, а затем экранирует все специальные символы JSON в переменных.
SET @json = FORMATMESSAGE('< "id": %d,"name": "%s", "surname": "%s" >', 17, STRING_ESCAPE(@name,'json'), STRING_ESCAPE(@surname,'json') ); См. также:
- CONCAT (Transact-SQL)
- CONCAT_WS (Transact-SQL)
- FORMATMESSAGE (Transact-SQL)
- QUOTENAME (Transact-SQL)
- REPLACE (Transact-SQL)
- REVERSE (Transact-SQL)
- STRING_AGG (Transact-SQL)
- STUFF (Transact-SQL)
- TRANSLATE (Transact-SQL)
- Строковые функции (Transact-SQL)
Обратная связь
Были ли сведения на этой странице полезными?
Обратная связь
Отправить и просмотреть отзыв по
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 символов. Это просто еще одно препятствие, с которым вы столкнетесь, строя пуленепробиваемый генератор кода.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Как экранировать одинарные кавычки?
Необходимо удваивать кавычки, как верно замечено в комментариях к вопросу:
SELECT ' for=''213'' '; Или же escape string (требует явного указания на возможное содержание escape последовательностей)
SELECT E' for=\'213\' '; Так же есть возможность вовсе не извращаться с кавычками, а использовать синтаксис строковых литералов Dollar-quoted String (там же в мануале)
SELECT $$ for='213' $$; SELECT $anystring$ for='213' $anystring$; Если же данные приходят от пользователя через приложение — то используйте prepared statements (или его эмуляцию вашей библиотекой доступа) и тогда вас экранирование не беспокоит вовсе.
Экранирование спецсимволов в 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-инъекций.