SQL Injection Attacks: Are You Safe? - What is an SQL injection attack?
(Page 2 of 5 )
As you may know, SQL stands for Structured Query Language. It comes in many different dialects, however most are based on the SQL-92 ANSI standard. An SQL query comprises one/more SQL commands, such as SELECT, UPDATE or INSERT. For SELECT queries, each query typically has a clause by which it returns data, for example:
SELECT * FROM Users WHERE userName = 'justin'; The clause in the SQL query above is WHERE
username = 'justin', meaning that we only want the rows from the Users table returned where the userName field is equal to the string value of
Justin.
It's these types of queries that make the SQL language so popular and flexible... it's also what makes it open to SQL injection attacks. As the name suggests, an SQL injection attack "injects" or manipulates SQL code. By adding unexpected SQL to a query, it is possible to manipulate a database in ways initially unthought of by the database administrator/developer.
One of the most popular ways to validate a user on a web site is by providing them with a HTML form through which they can enter their username and password. Let's assume that we have the following simple HTML form:
<form name="frmLogin" action="login.asp" method="post">
Username: <input type="text" name="userName">
Password: <input type="text" name="password">
<input type="submit">
</form> When the form is submitted, the contents of the username and password fields are passed to the login.asp script and are available to that script through the Request.Form collection. The easiest way to validate this user would be to build an SQL query and then check that query against the database to see if that user exists. We could create a login.asp script like this:
<%
dim userName, password, query
dim conn, rS
userName = Request.Form("userName")
password = Request.Form("password")
set conn = server.createObject("ADODB.Connection")
set rs = server.createObject("ADODB.Recordset")
query = "select count(*) 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"
else
response.write "Bad Credentials"
end if
%> In the example above, the user either sees "Logged In" if their credentials matched a record in the database, or "Bad Credentials" if they didn't. Before we continue, let's create the database that we have queried in the sample code. 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 is happening 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, meaning 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 spitting 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 result in the following query being executed 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 by comparing their details against the table and retrieving 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')Next: Injection attack examples >>
More MySQL Articles
More By Mitchell Harper
