SQL-Ex blog
Одной из моих любимых команд SQL являлась QUOTENAME. При программировании генерации кода обычно возникает необходимость заключать строковое значение в кавычки и экранировать любые символы, совпадающие с теми, которыми вы ограничиваете строку, их удвоением. (А если у вас пара таких символов в строке, вам их потребуется уже четыре.) Например, чтобы взять следующую строку в одинарные кавычки (‘):
Чтобы иметь возможность использовать её в динамическом операторе или объявлении переменной, потребуется удвоить одинарную кавычку в строке:
Или, если вы Rob Volk (@sql_r на Twitter), и хотите создать раздражающую базу данных на вашем лучшем заклятом SQL Server, то, чтобы включить скобки в имя базы типа:
вам придется сделать так:
Удваивается закрывающая скобка, но не открывающая. Для экранирования можно использовать QUOTENAME. Параметрами этой функции являются строка и разделитель. По умолчанию удваивается скобка как у большинства имён SQL Server, хотя вы можете использовать любой символ для удвоения. Так для нашей строки:
Этот код вернет
| objectName | string | |
|---|---|---|
| Mr. O’Malley | [Mr. O’Malley] | ‘Mr. O»Malley’ |
Кажется, работает отлично, поэтому вы чувствуете, что, если вам потребуется нагенерировать некоторый код, вы сможете поступить так:
Выполните этот запрос в Management Studio. Вы увидите на вкладке результата строку, начинающуюся с приведенного выше текста. Проблема состоит в том, что QUOTENAME предназначена для закавычивания значений имен SQL Server, а поскольку в SQL Server имена не могут быть длиннее, чем 128 символов. на входе должно быть не более 128 символов (ниже покажем, что может быть и больше). Итак:
Когда вход превышает 128 символов, возвращается NULL без каких либо предупреждений. Это, мягко говоря, не то, что вы хотели. В моем случае я разработал генератор скрипта расширенных свойств, который принимает значение типа sql_variant и преобразует его к nvarchar(max). Я не тестировал входы, превышающие 128 символов, но коллега любезно предоставил мне строку размером порядка 8000 символов. К счастью, это не был рабочий сервер, где бы безостановочно звонил телефон поддержки.
Итак, я могу предложить использование QUOTENAME в рабочем коде только для квотирования реальных имен операторов SQL, и использовать более неуклюжий метод для других целей:
Этот код демонстирует один и тот же результат для обоих вариантов.
Следует заметить, что это (как и любая простая повторно исполняемая скалярная функция) просится для использования в качестве пользовательской функции. В версиях, предшествующих 2019, всегда однозначно советовали избегать их, т.к. они, мягко говоря, ограничивали производительность. В одних случаях падение производительности было незначительным, в других — существенными провалами. В SQL Server 2019 Microsoft изменила механизм выполнения некоторых скалярных функций, который будет «встраивать» код функции в план запроса, использующего эту функцию. В результате производительность, полученная для перекодируемого оператора и при использовании функции соизмеримы (даже для довольно сложных функций!).
Построим, например, в базе данных WideWorldImporters следующую функцию:
Выполнить функцию можно так:
И вы можете проверить, встраивается ли она, таким образом:
Этот запрос для нашей новой функции возвращает 1.
Встраиваемая или нет, вы не увидите каких либо изменений в этом простом примере — я просто хотел указать на это как на более ценную возможность в будущем. Подробнее о встраиваемых функциях смотрите в публикации Brent Ozar’а.
Наконец, давайте взглянем на немного туповатый трюк, который в целом полезен для определения того, наколько длинной может быть заковыченная строка. Меня интересует, какого максимального размера может быть вывод функции QUOTENAME. Если вы выполните следующий оператор:
вернет []]]]], что на 2 символа больше, чем исходная строка. Если у нас 128 символов ], мы должны получить SELECT 127*2+4 или 258 символов, которые должен вывести следующий оператор.
Мы могли бы это просто прочитать в документации о QUOTENAME, где говорится о результате nvarchar(258)! Следовательно, вот какого размера должна быть переменная/столбец, чтобы ее можно было обработать с помощью функции EscapeString:
Здесь максимальная длина — это не 2 миллиарда как у varchar(max), а максимальная длина, которую может иметь ваш источник данных. Конечно, когда вы используете это значение со всеми экранированными строками, оно будет выглядеть больше, чем результирующее значение, но просто потому, что нам требуется пространство для 258 символов на имя, что не означает, что нам требуется именно 258 символов. Это просто еще одно препятствие, с которым вы столкнетесь, строя пуленепробиваемый генератор кода.
Как правильно писать mysql запрос?
В интернете, в примерах, в различных учебниках можно найти незначительно отличающийся синтаксис mysql запроса. А именно кавычки ‘ ` или их отсутствие.
Пример кода из php файла в кодировке utf-8 без bom
Методом научного тыка выяснено, что можно писать и с кавычками `user_settings` и без user_settings.
И так `user_id`=$user_id и так `user_id`=’$user_id’. Но это не точно (может связь с кодировкой?).
(Если что про двойные-одинарные кавычки в php всё знаю)
Скажите, пожалуйста, а как правильно писать mysql запрос и зачем нужны эти кавычки?
- Вопрос задан более трёх лет назад
- 8263 просмотра
- Вконтакте
Кавычки экранируют символы.
`обратные кавычки` используются тогда, когда тебе необходимо экранировать название столбца, если оно совпадает с зарезервированным ключевым словом.
‘Одинарные кавычки’ следует использовать, когда ты пишешь даты и строки.
«Двойные кавычки» следует использовать для написания непосредственного запроса.
Напоминаю. При запросе в базу данных из скрипта, особенно если ты используешь любые данные полученные от пользователя (от сессий и куки, до полей ввода), следует либо использовать подготовленные запросы в режиме эмуляции, либо перед вставкой данных в запрос осуществлять экранирование строки.
- Вконтакте
То есть в общем и целом можно прийти к такому выводу:
1) Всегда использовать `обратные кавычки` для названий объектов БД — баз, таблиц, столбцов.
Ведь проблемы с зарезервированными словами никому не нужны. Хотя называть таблицы или что-то ещё зарезервированными словами я, разумеется, не буду.
+Повышение безопасности от возможных sql инъекций (хотя вопрос безопасности отдельная тема) и нужно всегда осуществлять фильтрацию данных.
2) Всегда использовать ‘одинарные кавычки’ для значений объектов БД, например значений столбца. Ведь в переменной может быть строка.
Я прав?
Экранирующая строка в MySQL
База данных
Строковые данные необходимо экранировать перед использованием в запросе выбора, чтобы вернуть набор результатов с одинарной кавычкой («), обратной косой чертой (\), ASCII NULL и т. д. Для выполнения этой задачи используется функция MySQL QUOTE(). Он генерирует строковое значение, которое правильно экранирует данные запроса. В этом руководстве описано использование функции QUOTE() для экранирования строки в MySQL.
Синтаксис:
Функция QUOTE() принимает строковое значение в качестве аргумента, который будет экранирован, и возвращает экранированную строку после выполнения запроса. Синтаксис этой функции приведен ниже.
Символы управляющей последовательности
Использование различных символов управляющей последовательности объясняется ниже.
| Character | Description |
| \’ | Он используется для печати символа одинарной кавычки (‘). |
| \” | Он используется для печати символа двойной кавычки («). |
| \0 | Он используется для печати символа ASCII NULL. |
| \б | Он используется для печати символа возврата. |
| \n | Он используется для печати символа новой строки. |
| \р | Он используется для печати символа возврата каретки. |
| \т | Он используется для печати символа табуляции. |
| \Z | Он используется для печати символов ASCII 26 (Ctrl+Z). |
| \\ | Он используется для печати символа обратной косой черты (\). |
| \% | Он используется для печати символа ‘%’. |
| \_ | Он используется для печати символа «_». |
Использование функции QUOTE() для строкового значения
В этой части руководства показано использование функции QUOTE() с различными типами символов управляющей последовательности.
Пример 1: Использование функции QUOTE() для печати простой строки
Когда строковое значение печатается без функции QUOTE() с помощью оператора SELECT, строковое значение будет напечатано без кавычек. Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать простую строку с одинарной кавычкой.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 2: Использование функции QUOTE() для печати одинарной кавычки внутри строки
Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать строку с одинарными кавычками.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 3: Использование функции QUOTE() для печати двойной кавычки внутри строки
Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать строку, заключенную в двойные кавычки.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 4: Использование функции QUOTE() для печати строки с новой строкой
Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать строку с новой строкой.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 5: Использование функции QUOTE() для печати строки с пробелом табуляции
Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать строку с символом ’\t’.
Следующий вывод появится после выполнения вышеуказанного запроса. Пространство табуляции было создано между «Hello» и «World» в выходных данных для использования символа «\t».
Пример 6: Использование функции QUOTE() для печати строки с пробелом
Запустите следующую инструкцию SELECT с функцией QUOTE(), чтобы напечатать строку после применения символа ’\b’.
Следующий вывод появится после выполнения вышеуказанного запроса. Символ «x» был удален из основной строки с помощью символа «\b» внутри строки.
Использование функции QUOTE() для данных таблицы
Вы должны создать таблицу с данными в базе данных MySQL, чтобы проверить с помощью функции QUOTE() данные таблицы. Откройте терминал и подключитесь к серверу MySQL, выполнив следующую команду.
Выполните следующую команду, чтобы создать базу данных с именем test_db.
Выполните следующую команду, чтобы выбрать базу данных.
Выполните следующий запрос, чтобы создать таблицу с именем client и пятью полями.
CREATETABLEclients (
id INTNOT NULL PRIMARY KEY ,
name VARCHAR ( 30 ) NOT NULL ,
email VARCHAR ( 50 ) ,
address TEXT ,
contact_noVARCHAR ( 15 ) ) ;
Выполните следующий запрос INSERT, чтобы вставить 4 записи в таблицу клиентов.
INSERT INTO `clients` ( `id` , `name` , `email` , `address` , `contact _ no` ) VALUES
( ‘4001’ , ‘Laboni Sarkar’ , ‘laboni@gmail.com’ , ’34, Dhanmondi 9/A, Dhaka.’ , ‘01844767234’ ) ,
( ‘4002’ , ‘Tahsin Ahmed’ , ‘tahsin@gmail.com’ , ‘123/1, Jigatola, Dhaka.’ , ‘015993487812’ ) ,
( ‘4003’ , ‘Hasina Pervin’ , ‘hasina@gmail.com’ , ‘280, Shantibagh, Dhaka.’ , ‘01600487812’ ) ,
( ‘4004’ , ‘Mehrab Ali’ , ‘ali@yahoo.com’ , ’78, Cox’s Bazar, Chottogram.’ , ‘01727863459’ ) ;
Выполните следующий запрос, чтобы распечатать все записи таблицы клиентов.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 1: Использование функции QUOTE() в одном поле таблицы
Когда строковое значение таблицы печатается с использованием запроса SELECT, на выходе будет отображаться строковое значение без каких-либо кавычек. Следующий запрос SELECT напечатает все записи таблицы клиентов и еще один дополнительный столбец QUOTE (электронная почта), заключив поле электронной почты таблицы клиентов в одинарную кавычку.
Следующий вывод появится после выполнения вышеуказанного запроса.
Пример 2: Использование функции QUOTE() в нескольких полях таблицы
Следующий запрос SELECT напечатает исходные значения полей имени и адреса, а также значения в одинарных кавычках полей электронной почты и contact_no таблицы клиентов.
SELECT name as Name , QUOTE ( email ) as Email , address as Address , QUOTE ( contact_no ) as `Contact No`
FROM clients ;
Следующий вывод появится после выполнения вышеуказанного запроса.
Заключение
Функция QUOTE() используется в запросе SELECT для форматирования строковых данных с использованием различных escape-символов перед печатью. Способы использования функции QUOTE() для простых строковых данных и табличных данных были показаны в этом руководстве, чтобы помочь пользователям MySQL узнать, как экранировать строку в MySQL.
How do I escape a single quote in SQL Server?
I am trying to insert some text data into a table in SQL Server 9.
The text includes a single quote ‘ .
How do I escape that?
I tried using two single quotes, but it threw me some errors.
eg. insert into my_table values(‘hi, my name»s tim.’);
14 Answers 14
Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:
Results
If escaping your single quote with another single quote isn’t working for you (like it didn’t for one of my recent REPLACE() queries), you can use SET QUOTED_IDENTIFIER OFF before your query, then SET QUOTED_IDENTIFIER ON after your query.
Many of us know that the Popular Method of Escaping Single Quotes is by Doubling them up easily like below.
we are going to look on some other alternate ways of escaping the single quotes.
1. UNICODE Characters
39 is the UNICODE character of Single Quote. So we can use it like below.
2. QUOTED_IDENTIFIER
Another simple and best alternate solution is to use QUOTED_IDENTIFIER. When QUOTED_IDENTIFIER is set to OFF, the strings can be enclosed in double quotes. In this scenario, we don’t need to escape single quotes. So,this way would be very helpful while using lot of string values with single quotes. It will be very much helpful while using so many lines of INSERT/UPDATE scripts where column values having single quotes.
The above mentioned methods are applicable to both AZURE and On Premises .
2 ways to work around this:
for ‘ you can simply double it in the string, e.g. select ‘I»m happpy’ — will get: I’m happy
For any charactor you are not sure of: in sql server you can get any char’s unicode by select unicode(‘:’) (you keep the number)
So this case you can also select ‘I’+nchar(39)+’m happpy’
The doubling up of the quote should have worked, so it’s peculiar that it didn’t work for you; however, an alternative is using double quote characters, instead of single ones, around the string. I.e.,
insert into my_table values(«hi, my name’s tim.» );
Also another thing to be careful of is whether or not it is really stored as a classic ASCII ‘ (ASCII 27) or Unicode 2019 (which looks similar, but not the same).
This isn’t a big deal on inserts, but it can mean the world on selects and updates.
If it’s the unicode value then escaping the ‘ in a WHERE clause (e.g where blah = ‘Workers»s Comp’) will return like the value you are searching for isn’t there if the ‘ in «Worker’s Comp» is actually the unicode value.
If your client application supports free-key, as well as copy and paste based input, it could be Unicode in some rows, and ASCII in others!
A simple way to confirm this is by doing some kind of open ended query that will bring back the value you are searching for, and then copy and paste that into notepad++ or some other unicode supporting editor.
The differing appearance between the ascii value and the unicode one should be obvious to the eyes, but if you lean towards the anal, it will show up as 27 (ascii) or 92 (unicode) in a hex editor.