This article looks at SQL Injection:
What it is & How you can prevent it.
What Is SQL Injection ?
How Can I Prevent SQL Injection ?
What Is SQL Injection
Before you can know how to prevent SQL
Injection, you need to know what it is and how it can be
used.
The first thing to know is that websites
commonly use a database server to build webpages
dynamically.
This data server, which is separate to the
website 'page' server, contains 'Tables' of information
which are stored in rows and columns similar to a
spreadsheet setup for example.
The illustration below shows how data
interaction occurs between the web user, the page server
and the data server.
For example when the web user enters a
search query on the website, the website typically
connects to the data server and retrieves the required
info, which is then used to construct the web page being
requested, in this case the web page would be the
'Search Results' page.
So What is SQL you ask? Well SQL which
stands for 'Structured Query Language' is in
layman's terms the language used by the data server to
communicate with the web server.
So next you need to know how the data
server communicates with the web server.
This communication process starts as
mentioned when a web user enters a search query onto the
website.
Say for example the user is looking for a
CD or record by Madonna. They would simply enter MADONNA
into the search field on the website.
Next, the website would construct an
'Ad-Hoc' SQL statement using the data entered (MADONNA).
This is what a very basic SQL query sent to
the data server would typically look like in script
form.
<% SELECT * FROM
dbo.PRODUCT_TABLE WHERE artist_name like 'MADONNA'
%>
As you can see, the query which is pretty
self explanatory is simply searching the 'artist_name'
column of the PRODUCT_TABLE for any entries containing
MADONNA.
If the data server finds any entries, it
will select them all (*), and return the data to the
'Search Results' page on the website.
Now you know how SQL is supposed to be
used, we can look at how it can be manipulated for malicious
purposes, of course we are referring to sites where good programming techniques have not
been used.
As you can see from the example above,
the data entered by the user is actually used to build
the SQL statement, so in theory with a bit of knowledge
you could enter some data into the search field that would
enable you to run your own SQL statements.
For example, if you entered
MADONNA' or '1' = '1
The data server would in-fact return
every item contained in the database table.
To understand why, lets look at what
actual SQL
statement the data server would have been trying to
execute.
<% SELECT * FROM
dbo.PRODUCT_TABLE WHERE artist_name like 'MADONNA'
or '1' = '1' %>
As you can see, we have manipulated the
statement to select everything in the database as 1 is
equal to 1.
At this point you may be
thinking 'Big Deal' they have selected everything in the
product table, but if you imagine that the table being
injected into was the CUSTOMER table, and the injection
target being the EMAIL and PASSWORD fields, then you
soon realize the potential of SQL injection.
To add to that, there are
SQL statements such as DROP TABLE which would delete the
entire table.
Therefore you should
exercise a strict data backup policy on even the most
secure sites.
How Do I Prevent
SQL Injection
Well now you know what SQL Injection is,
you are half way to preventing it.
Good security files [FireFiles]
will stop the vast majority of Injection attempts before
they get a chance to execute.
But this does not defeat the root
cause, which is poor programming techniques.
Stored Procedures are a good defence
against Injection, particularly when the correct
validation is applied to any executed variables.
If you are not using stored procedures you have to simply validate and
check all data being used in the construction of
run-time / ad hoc SQL statements.
Another good defence against this type of
attack is to use limited database access rights, whereby
each 'database use' (ie the web application) is only
given certain rights to use the database.
For example a products database which
would normally be used by the search results pages,
should only be given read access to the database.
What Is Validation
Validation is the checking of user
entered data (usually querystring GETS or form POSTS).
Validation can be done 'client side' and 'server
side'.
|
|
|
Client side validation can be as simple
as a few javascript form checking scripts, that don't
allow certain characters to be passed to the server, but
it is important that you also operate 'server side'
validation since a user could disable javascript on
their local machine for example.
This is where the 'server side
validation' would come into play. This can be in the
form of special 'cleansing' functions, stored
procedures, regular expressions, blacklists, white lists
and various other forms of validation. These are one of
the most important aspects of online security.
We are still regularly amazed at the
astonishing lack of security on many websites.
Below you can see examples of basic
server side validation.
<%
Dim ThisVar
ThisVar=replace(request.querystring("id"),";","_")
%>
This replace function would replace any
semi-colons (;) with an underscore (_)
<%
ThisVar=Cint(replace(ThisVar,"'","''"))
%>
Next, we have swapped a single apostrophe
for two apostrophes. We have also attempted to convert
the querystring data type to an integer since we know
that we are only expecting a fairly small number (less
than 10,000), we do this so that if the data contains an
apostrophe we know this will cause the page to error and
not execute any further.
Similar procedures can be applied to
client side validation. Using javascript forms.
Below you can see a simple example of
javascript client side validation.
The first part of this script is placed
inside the HEAD tags of the page to which it is applied.
This example shows a validation script
for a search form.
<script
language="JavaScript">
<!--
function Validate_Search_Form ( )
{
valid = true;
if ( document.searchform.artist.value == "" )
{
alert ( "Type the name of the artist in the artist
field" );
valid = false;
}
if ( document.searchform.label.value == "" )
{
alert ( "Type the name of the label in the label field"
);
valid = false;
}
return valid;
}
//-->
</script>
This script is checking that the form
cannot be submitted without any data in the field, any
empty field would trigger an alert box to appear asking
the user to enter data into the required field.
The javascript function can be called to
validate a form by using the following red line of code
within your form.
<form action="yourdomain" method="post"
name="searchform"
onsubmit="return Validate_Search_Form()">
These are only very basic,
simple validation examples to show you the principal
, they provide relatively low levels of protection
against modern hackers.
If you require more information about
securing your website
click here.
If you are interested in having FireStorm
secure your site or simply review your current site,
please
contact us here
FireFile Website Protection
ASP Version
Download Now
Protect your website
against SQL Injection, XSS, And Malicious Code
< go back
Top
|
