Thursday, 2 February 2012

SQL Injection in INSERT Query

SQL injection is being one of the mostly exploited issues in web application security and has found a place in OWASP Top 10 since 2004. There are many blog posts, papers available on SELECT query injection exploiting WHERE or HAVING clauses. Today I’m going to discuss SQL injection in INSERT query.

The Basics:

INSERT query followed by VALUES inserts rows into an existing table based on explicitly specified values.  The syntax of INSERT query is: (source:

    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
        [, col_name=expr] ... ]

tbl_name is the table into which rows should be inserted. A comma-separated list of column names can be provided following the table name. In this case, a value for each named column must be provided by the VALUES list.

To insert a record in a table, following query will be used:

INSERT INTO tbl_name (a,b,c) VALUES(‘data’,’data’,’data’);

I hope this is enough to introduce INSERT query.

The Injection:
One of the examples of usage of INSERT query in web application is comment page.

The page requests for name, email address and the comment and inserts this data into database using following query:

INSERT INTO comments (name, email, comment) VALUES (‘lol’,’lol’,’lol’);

In this query, an attacker can inject arbitrary data if the inputs are not sanitized. Let’s check this by placing single quote (‘) in name field.

This results in SQL Error as expected:

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 'asd', 'asd')' at line 1

Now we can inject any data in all fields with comment string at the end.

To get the result of injected query, we need a place where the injected data is reflected back by the application. In this example, the comment details are printed back on the page.
So let’s start injecting something which will give information about the database server. We can insert sql subquery in place of parameter value. We will insert subquery ‘(select version())’ without single quotes in ‘email’ parameter.

We get the mysql version. In this way we can get the other database details as well like current user, current database, etc.

Let try to get the password of user ‘root’ from mysql.user table.

Injected Data:
test’,(select password from mysql.user where user=’root’),’test2’)-- -

This gives error:
Subquery returns more than 1 row

Hmm, only one row. We will use LIMIT to fetch 1 row at a time. Let’s craft the payload with LIMIT:
test’,(select password from mysql.user where user=’root’ limit 0,1),’test2’)-- -

This works and we now have password hash for user ‘root’:

In this way, we can mine the database with SQL injection in INSERT Query using sql subquery.

The INSERT query is common in user registration pages. Let’s analyze an example. This example is taken from “Mutillidae”, a well-known web application to learn security. A new user provides details such as username, password & signature in order to create an account with the forum/application. This data is then inserted into database using this query. The background SQL query looks like this:

INSERT INTO accounts (username, password, mysignature) VALUES ('data', 'data', 'data');

It’s same as the last example and we will be able to inject arbitrary values into database using single quote (‘) and comment string (-- -):

And the result is:

Great!! The user is added. Now we can use the same sql subquery technique to inject sql queries and to get the data. But the question is where will be the returned data?

There are 2 places where the injected data is being reflected by the app.

One place is “Account created” message as shown in above snapshot. Here ‘username’ value is being reflected. As we cannot control the first single quote (‘) for ‘username’ field, we will not be able to inject subquery which will successfully give us the returned data.

We need to look for other places to see if our injected data is being reflected back. As we have registered an account, let’s login and check if data from inserted SQL query reflected somewhere or not.

We can see ‘signature’ parameter is getting reflected in status message. So we need to inject subqueries into ‘signature’ parameter and we will get returned data in status message.

Let’s start with mysql version(). The payload in username field for this will be:

test’,’test’,(select version()))-- -

And user added successfully.

Now login with username & password as ‘test’ and check status. It should have mysql version info.

Yes, it’s there and the injection is successful. Let’s try to get password hash for user ‘root’.

test1’,’test1’,(select password from mysql.user where user=’root’))-- -

And here comes the error:

We again need to use LIMIT to get only one row.

test1’,’test1’,(select password from mysql.user where user=’root’ LIMIT 0,1))-- -

And we get password hash for user ‘root’.

That’s all. The same way other data can be mined from the database.

1. Identify the injection point.
2. Check where the injected data is visible.
3. Use subquery to insert sql queries.
4. Use LIMIT to get one row at a time.

Hope you like this post. Suggestions, queries are welcome.


  1. If i have:
    $var1 = $_POST['post1'];
    $var2 = $_POST['post2'];
    $var3 = $_POST['post3'];

    mysql_query("INSERT INTO table(col1, col2,col3) VALUES ('var1','var2','var3')

    Im Safe? If not, how can i keep safe?

  2. @Lumen, the example you mentioned is vulnerable to SQL injection as INSERT query is using user-supplied values without sanitization. This is not safe. You should look at OWASP SQL Injection Prevention Cheat Sheet:
    Always use parameterized queries for dynamic queries.

  3. Thanks men, actually i have troubles about "hacking" and i think is using sql inyection

    Thanks again.

  4. This comment has been removed by the author.

  5. Kindly let me know your views for a page where you dont get the inserted input in the response, for example, you insert something and the only response you get is - "Thanks for your input, we will get back to you" or may be nothing.. and your aim is to retrieve the data, but, not to execute additional query by appending it with the current... which most blogs have explained. Thanks in advance.

  6. Whould "mysql_real_escape_string" on every $_POST[] avoid the injection?

    1. Yeah that would help. Still its bypassable :p

    2. Either it helps or it's bypassable. (and no, it's not bypassable if done right)

  7. This comment has been removed by a blog administrator.

  8. Hi..

    Great tutorial..
    How can i test in my local pc? do u have a sample php coding for this?


    1. Hi,
      You can use "Mutillidae" on local pc with apache-php-mysql environment.

  9. Hi AMol NAik,

    Do u know how to use update query using SQL injection? Can we do using Mutillidae?

  10. Hey buddies, such a marvelous blog you have made I’m surprised to read such informative

  11. This text may be value everyone’s attention. How will I learn more? full coverage insurance on car

  12. Superb posts with lots of information!!! This is really the most miraculous blog site dude…. personal cash loans

  13. Superb posts with lots of information!!! This is really the most miraculous blog site dude…. pay day loan

  14. But what if we don't have anything to display the returned data like in above example?? Where we can show the data then returned by that INSERT query for attacks??

  15. But what if we don't have anything to display the returned data like in above example?? Where we can show the data then returned by that INSERT query for attacks??

  16. Love it! Thank you so much for sharing this one really well defined all peaceful info,well really like it. Thank you so much for sharing this one. - SQL Lite Database Integration Service

  17. Great Tutorial,

    Thanks for sharing this tutorial of sql injection attack

  18. Hello dear .You put really very helpful information.
    It’s pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the web will be a lot more useful than ever before.
    icc t20 world cup 2016
    t20 world cup 2016
    ipl live streaming
    ipl 2016 live streaming
    real madrid vs barcelona live streaming
    La Liga Live Streaming
    India vs Pakistan Live Streaming
    India vs Pakistan Live Scores
    wrestlemania 2016 live stream
    wrestlemania 2016 results

  19. The 5 star rating underneath the Facebook business pages is a reasonably new attribute which has been included. buy facebook fanpage reviews

  20. First You got a great blog.I will be interested in more similar topics.
    i like play games happy wheels online friv , girlsgogames , games2girls and play happy wheels games