Search code examples
phpmysqlsql-serverdatabasesql-injection

Using Nested SQL Sub-queries in SQL Injection

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?

Solution

  • 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.

    1. 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.

    2. 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.