SQL Injection Attacks - Are You Safe?

Let's also create a users table with some dummy records:

create database myDB  
go  
 
use myDB  
go  
 
create table users  
(  
userId int identity(1,1) not null,  
userName varchar(50) not null,  
userPass varchar(20) not null  
)  
 
insert into users(userName, userPass) values('john', 'doe')  
insert into users(userName, userPass) values('admin', 'wwz04ff')  
insert into users(userName, userPass) values('fsmith', 'mypassword')

So if I entered a username of john and password of doe, then I would be presented with the text "Logged In". The query would look something like this:

select count(*) from users where userName='john' and userPass='doe'

There's nothing insecure or dangerous about this query... is there? Maybe not at first glance, but what about if I entered a username of john and a password of ' or 1=1 --

The resultant query would now look like this:

select count(*) from users where userName='john' and userPass=''  
or 1=1 --'

In the example above I've italicised the username and password so they are a bit easier to read, but basically what happens is that the query now only checks for any user with a username field of john. Instead of checking for a matching password, it now checks for an empty password, or the conditional equation of 1=1. This means that if the password field is empty OR 1 equals 1 (which it does), then a valid row has been found in the users table. Notice how the last quote is commented out with a single-line comment delimiter (--). This stops ASP from returning an error about any unclosed quotations.

So with the login.asp script we created above, one row would be returned, and the text "Logged In" would be displayed. We could take this a bit further by doing the same thing to the username field, like this:

Username: ' or 1=1 ---  
Password: [Empty]

This would execute the following query against the users table:

select count(*) from users where userName='' or 1=1 --' and userPass=''

The query above now returns a count of all rows in the user table. This is the perfect example of an SQL injection attack: adding code that manipulates the contents of a query to perform an undesired result.

Another popular way to validate a user against a table of logins is to compare their details against the table, and retrieve the valid username from the database, like this:

query = "select userName from users where userName='" &  
userName & "' and userPass='" & password & "'"  
 
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 "Logged In As " & rs.fields(0).value  
else  
response.write "Bad Credentials"  
end if

So, if we entered a username of john and a password of doe, then we would be presented with:

Logged In As john

However, if we used the following login credentials:

Username: ' or 1=1 ---  
Password: [Anything]

Then we would also be logged in as John, because the row whose username field is John comes first in the list, based on the insert queries we saw earlier:

insert into users(userName, userPass) values('john', 'doe')  
insert into users(userName, userPass) values('admin', 'wwz04ff')  
insert into users(userName, userPass) values('fsmith', 'mypassword')