Home arrow MySQL arrow Page 2 - SQL Injection Attacks: Are You Safe?
MYSQL

SQL Injection Attacks: Are You Safe?


Is your database safe from SQL injection attacks? In this article Mitchell tells us exactly what they are and shows us how to prevent them from occuring.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 58
May 29, 2002
TABLE OF CONTENTS:
  1. · SQL Injection Attacks: Are You Safe?
  2. · What is an SQL injection attack?
  3. · Injection attack examples
  4. · Preventing SQL injection attacks
  5. · Conclusion

print this article
SEARCH DEVARTICLES

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')

blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials