Last update Febbraio 14, 2024 1:49 PM



Personal Website

Articoli / Articles

Pagina Iniziale Home Page

Informazioni About Me

Curriculum Vitae Resume
Didattica Teaching
Progetto Jara Jara Project
Guide Tutorials
Articoli Articles
Libri Books

Contatti Contacts

SQL Injection Attack Technique
by Roberto Saia

In this information age, web-based applications are becoming more and more important for people and at the same time they are becoming more sophisticated. On one hand it is positive but on the other hand this exceptional growth is directly connected to an increase in security breaches. It is well known that when a new server is set up and connected to the Internet, it suddenly become a potential target. The risk is very high, data servers (that are the core of web-based applications) are one of the favourite targets for the internet attackers. The SQL Injection is a method used in order to attack, alter or retrieve information from these servers. It is based on a particular technique which enables an attacker to execute unauthorized SQL commands against a remote database. Although this technique has been known for many years, day by day, the incessant successful exploits against the systems show us that we have to reconsider the problem in more detail.

The aim of the article
This article should be useful for those responsibles for the management and development of online services but it is also useful to anybody who wants to know something about a particular attackers technique. It aims to suggest possible ways to fix a dangerous vulnerability and prevent data loss. Web sites that use dynamic server-side technologies in order to interact with their users are the main target of the attackers and for this reason alone they have plenty of targets to choose from.

A false sense of security
In these times, most firms that have a web site on the Internet base their security on a firewall. The task of this device is to filter all network traffic, inbound and outbound. Its aim is crystal clear: stop unauthorized network activities. Within the framework of a company security policy, a firewall is the most important element. It operates at the levels 3, 4 and 5 of the ISO/OSI model: “Network”, “Transport” and “Session” layers. This means that a firewall is not able to look into higher layers of the OSI model: one of the higher-level protocols is HTTP (Hypertext Transfer Protocol). To sum up everything, we can say that a firewall allows certain kinds of attacks against higher-levels of the OSI model. SQL Injection is one of these because it is directed to the OSI layer 7. An attack against a remote database based on SQL Injection technique could be originated by different reasons such as the possibility to obtain a general unauthorized access to a database or, specifically, to steal data that should be normally unavailable. The mechanism of the SQL Injections is based upon a well known SQL code vulnerability that allows the attackers to inject and execute arbitrary SQL commands on the target system and this usually happens through a standard HTML form. Attackers can perform these operations without particular difficulty because they do not need any special or complicated tools. However, despite the fact that this technique is very simple to apply, the result is very powerful. Fortunately, at the same time, it is possible to defend a system against such attacks with a little effort. I would like to point out that the vulnerabilities mentioned in this article are not specific to a particular server-side technology and moreover they are not reflective of any significant SQL weakness in fact this kind of vulnerability can be found in any server-side application that interacts with an SQL server.

Introduction to SQL
If someone tries to unfold the SQL (Structured Query Language) history by Internet searches, he rapidly discovers that it is not so simple to do, in fact, there are numerous Web pages about this language and it is really difficult to get exhaustive information in a short time. This is because the SQL language has a lot of history. Unfortunately, there is not enough space in this article to fully address all aspects of it, therefore I must try to summarize the most important information in a few rows. SQL was designed over 30 years ago to create, modify and query databases. It was born from the ashes of another language named “SEQUEL” (Structured English Query Language). SEQUEL was developed by IBM and later became SQL due to trademark conflicts. At the end of seventies the Relational Software Inc (today Oracle Corporation) issued the first commercial implementation of SQL language and, subsequently, it was admitted as a standard by ANSI (American National Standards Institute) and ISO (International Organization for Standardization). During this time it has gone through various revisions and although it was defined as a standard by both ANSI and ISO, today, there are versions with some proprietary extensions. Fortunately, despite there being several SQL versions, all of them usually support the same major keywords such as SELECT, UPDATE, DELETE, INSERT and WHERE, in compliance with the issued standards.

Introduction to Scripting Languages
There are different technologies used for server-side processing but all of them perform similar functions. Two of the most popular scripting languages are ASP (Active Server Page) and PHP (recursive acronym for PHP: Hypertext Preprocessor). They are mainly used to serve database driven web sites such as e-commerce or informational sites. They work in a simple way: when a remote client, through a web browser, calls an ASP or a PHP file in a web server, the request is analyzed by the script and transformed in an SQL query. The query will be forwarded to the database. The result of this operation will be an HTML page displayed in the client web browser. The following diagram try to summarize the entire process described above:

Figure 1 - ASP/PHP working diagram

Through this mechanism web applications can interact with databases in order to build dynamically customized data views.

The Problem Overview
Recent statistical sources have reported that at least 92% of web applications are vulnerable to some form of attacks and, within this statistic, SQL injection has covered up to 62%. In this context, the only effective way to shield a system against this threat is to obtain exhaustive information about the elements involved in the attack process. Fortunately, there are a lot of well documented SQL Injection attacks and this allows us to deploy adequate defensive measures in order to be proactive rather than reactive: many organizations are following this direction but still not enough. The whole object of this article is to stress the importance of the prevention activities.

SQL Injection Attack in Practice
SQL Injection allows attackers to affect the construction of a SQL statement passed to a database in order to change its original aim. The examples given in the article are related to ASP scripting language but they also can be transpose to other languages with some little adjustments. There are a number of possible attacks based on this
vulnerability, here we will try to describe and analyze only the most popular of these. The first kind of attack is directed against a logon web form where there are two elements: a textbox for the user name and a password box for the password. The following lines show the HTML code of this form:

Listing 1 - HTML source code of a typical login web form

<form name="frmValidate" action="validate.asp" method="post">
Username: <input type="text" name="username">
Password: <input type="text" name="password">
<input type="submit" value="Send query">

The code above produced the following result:

Figure 2 - Login Web Form

When the user press the submit button on the form, the values “A” and “B” inserted, respectively, into the “username” and “password” fields are passed to the ASP script “validate.asp”. This script is likely written in this way:

Listing 2 - Validate.asp script

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 password='" & password & "'"
conn.Open "Provider=SQLOLEDB; Data Source=(local); 
Initial Catalog=UsersDB; User Id=sa; Password="
rs.activeConnection = conn query
if not rs.eof then
response.write "Welcome to the system"
response.write "Invalid Account"
end if

If we take a look at the above code, it does not seem to contain any particular problem: each user can be logged in only with a valid username and password. This means that is impossible to log in without a valid account but is it really true? No unfortunately, because the code mentioned above is vulnerable to SQL Injection attacks. The attack has became possible due to the absence of a specific control regarding the SQL query building that allows an attacker to modify the statement executed. In the script above, UsersDB (the accounts database) will be queried through a SQL query like this:

SELECT * FROM Users WHERE Username =’A’ AND Password=’B’

Where “A” and “B” are the values inserted into the fields “Username” and “Password” on the Web form. When we use this form in a “traditional” way, we insert a valid couple username and password and then the query will fetch data with regard to a specific user, differently, if we enter into the form a valid username only (for example: Smith) and instead of a password we insert ' OR 1=1 ---
the resultant query will become:

SELECT * FROM Users WHERE Username =’Smith’ AND Password=’’ OR 1=1 ---‘

In the first case the query checks for any user with a precise username and password. In the second case it checks for an empty password or the conditional expression 1=1. What does it means? It means that the condition is always valid, in fact, the field is empty and 1 is equal 1. The delimiter “—“ permits to avoid the ASP “unclosed quotations” error. This operation allows to fetch a valid row from the users table. So if the ASP script was created in order to authenticate the users, the attacker will log into the system. Another attack is oriented against a web application that manages goods. In this environment users can enter a good identifier in order to retrieve all details about the product. The request is dispatched to a remote database by the following part of the ASP code (goods.asp) that generates a SQL query:

sql_query= "SELECT GoodName, GoodDescription FROM Goods WHERE GoodNumber " & Request.QueryString("GoodID")

The function  “Request.QueryString("GoodID") extracts the value typed from the user on the web form variable (GoodID). For example, if the user typed the value “3456” the query generated by asp script will be:

SELECT GoodName, GoodDescription FROM Goods WHERE GoodNumber=3456

At the same way of the previous example, an attacker can forge parameter’s value to execute malicious SQL code such as the following line:          <-- regular OR 1=1   <-- malicious

In the example above the attacker has used non-standard parameters and he has directly inserted the value “3456 or 1=1” into the browser address bar. What is the result? In this case all GoodName and GoodDescription are fetched and displayed. Unfortunately the results can be more dangerous, in fact, an attacker can forge a destructive command like this:;DROP TABLE goods

In this case the attacker uses a semicolon in order to pass multiple SQL commands (obviously the command must be translated). The second command “DROP TABLE goods” destroyed the entire “goods” table.

SQL Injection Countermeasures
We have already seen that in spite of the protection of some security devices such as the firewalls, intruders still have a way to exploit a remote database. They can do this through the available applications. Every security policy has a weak point and in this case the weak point are the running web applications. We have discovered that the SQL Injection can be a very dangerous and destructive attack. The enormous diffusion of SQL language in addition to irresponsible behaviour of some DBAs (DataBase Administrators) that do not provide to defend adequately their systems are the main cause of the problem. This scenario allows attackers to obtain a great deal of success during their operations. Nobody can do anything to slow the SQL spread but anyone can follow some simple security guidelines. As it is written at the beginning of the article, SQL Injection attacks are very easy to reject, we only need to do some simple SQL code adjustments. The first operation to do is to shield SQL queries code, modality depends on the database in use: next example is about a MS-SQL Server:

Listing 3 - Shielded validate.asp script

dim username, password, query
dim conn, rS
username = Replace(Request.Form("username"), "'", "''")    <-- Fixed line
password = Replace(Request.Form("password"), "'", "''")    <-- Fixed line
set conn = server.createObject("ADODB.Connection")
set rs = server.createObject("ADODB.Recordset")
query = "select count(*) from Users where username='" & 
username & "' and password='" & password & "'"
conn.Open "Provider=SQLOLEDB; Data Source=(local); 
Initial Catalog=UsersDB; User Id=sa; Password="
rs.activeConnection = conn query
if not rs.eof then
response.write "Welcome to the system"
response.write "Invalid Account"
end if

The script “validate.asp” accepts two variables as input (username and password) and, without appropriate code adjustments, it jeopardizes our security when single quotes are used in a particular way. As we can see in the previous listing, the solution can be easy found by “Replace” function. Another good strategy to shield our system against SQL Injection attacks is based on the elimination of the standard error messages generated by the system: these messages sometimes can uncover some important details about the system in use and specifically, they can address an attacker to a valid point of access. The basic operation to counteract this kind of problem is based on replacement of the standard error messages (such as the error web pages in IIS) with custom messages. This operation aims to eliminate whole technical details that could be useful for an attacker.

So far we have dealt with only some of the numerous possible exploitations of the SQL injection technique. The length allowed for this article is not sufficient for an exhaustive description of all the aspects of the problem, but it believe it is adequate for at least some basic advice. I do no want this article to be a brief explanation of the problem, only that I want to emphasize the need for an increased awareness on these issues. I would give a special priority to database and system administrators as they are responsible for maintaining and operating, on private and public organization's databases, large volumes of critical data. As we all know, one of the most important risk is the violation of an individual's privacy right. I would like to conclude with a remark which I always mention at the end of my writings: there is no sense in deploying sophisticated instruments to defend us if we do not have already a semblance of common sense.

Glossary of used terms

Structured Query Language is the most popular computer language used to create, modify and query databases.

Active Server Pages is Microsoft's server-side technology for dynamically-generated web pages that is distributed as a part of Microsoft IIS (Internet Information Server).

PHP (a recursive acronym for "PHP: Hypertext Preprocessor") is an open-source programming language for server-side applications with dynamic web content.

Recursive Acronym
Abbreviations that refer humorously to themselves or to other acronyms or abbreviations, for example, the recursive acronym GNU stands for "GNU is Not Unix”.

Microsoft Internet Information Services. They are some Internet based services for Microsoft Windows machines.

Roberto Saia  -  All Rights Reserved