An Investigation into SQL Injection by axe11963


									An Investigation into SQL Injection
            April 11, 2005

         CS4235 Group Project
            Anthony Brown
             Matt Collins
              Seth Levy
            Jason Wallace
April 11, 2005                                       An Investigation into SQL Injection


       While developing any application, developers must keep in mind the

language’s inherent vulnerabilities. For an SQL database, SQL injection is that

ever-present risk on a good developer’s mind. SQL injection is a serious

security vulnerability used to retrieve or modify unauthorized information. It

exploits the same flexibility that makes the databases so powerful, especially in

versatile web applications. However, due to recent awareness of the problem

and better industry-wide coding practices, most forms of the attack are easy to

protect against.

       This paper will progress through the development of a SQL injection

attack and will include the detection of vulnerabilities. It will then introduce

various forms of the attack, retrieving and editing specific desirable information.

With a basic understanding of the threat, the paper will debrief the user on the

current solutions and preventative measures to secure a SQL Database.

Background and Evolution of SQL Injection

       Whether you are aware or not, you have most likely utilized a large

database powered computer system today. In the beginning, massive,

centralized database systems were used to build the most complex mission

critical systems, whether they be used for banking, finance, government, or

medical related tasks. The fast moving PC revolution, which is rooted in the late

70s and early 80s, introduced a movement away from large servers and towards

distributed power on every desktop. While this trend has grown over the past 25

                                                                            Pg. 2/21
April 11, 2005                                     An Investigation into SQL Injection

years (enabling numerous new creative and entertainment applications), it barely

took a few years as databases re-entered the forefront of the industry throughout

the 1980s. The increasing amount of available information, coupled with the

need to thoroughly process that information in a very short amount of time, and

enforce complex security settings, has made the database the central

component of most modern information systems.

       As the modern database emerged, in the years prior to the internet boom,

it took a very different form than the original mainframe systems. Modern

databases are relational; one way to understand this is thinking of a database as

a collection of hundreds or thousands of spreadsheets, containing different kinds

of information, where you can arbitrarily draw connections between columns on

different sheets to gain knowledge.

       The key to harnessing the power of a modern database is the ability to

access it from virtually any computing platform, and from inside virtually any

programming language. SQL as a language was designed from inception to

serve this purpose. SQL statements are semantically much less strict than

statements in most other programming languages because they were designed

first and foremost to be English like, so “pseudo-technical people” could use

them, and second to easily pass between wide ranges of hardware and software

as strings.

       SQL Injection is the practice of intercepting SQL query strings that

incorporate run-time obtained user input and modifying them to execute

                                                                          Pg. 3/21
April 11, 2005                                      An Investigation into SQL Injection

commands against the database that the application designers did not intend.

There is a wide range of complexity with respect to SQL injection hacking, and

the degree of knowledge and effort required for someone to successfully exploit

this technique will depend on both the underlying database platform and the

application level countermeasures developers have incorporated. The simplest

SQL injection attack exploits the manner in which SQL handles numeric


 User Search Application

 Enter SSN:

                                Click to search
                         Sample Web Application Search Page

       Before performing the injection attack, it may be useful to see if details of

the underlying query can be obtained. To do this, attackers can guess column

names in the search field (this may or may not work depending on how the

search form is validated both client and server side before the database server

receives the query). For example, type “name” and click the search link.

       Error Type:
       Microsoft OLE DB Provider for SQL Server (0x80040E37)
       Invalid column name 'name'.
       /sqltest.asp, line 7

The preceding error would be returned (on a Microsoft SQL Server database), if

the column specified on the form did not exist in the table being queried. After a

few guesses, an attacker may surmise that the query being passed to the

database from the web application server looks something like the following:

                                                                           Pg. 4/21
April 11, 2005                                      An Investigation into SQL Injection

       SELECT lastName FROM userData WHERE SSN = {InputSSN}

The above hypothetical query finds a person’s last name based on a social

security number, presumably typed in on a form in a web application. Since the

dynamically supplied data is placed at the end of the preconfigured query, this

query is especially vulnerable (assuming that the InputSSN is not pre-screened

for injection techniques). Instead of typing a nine digit social security number

into the web form, the user can type “123456789 UNION (SELECT lastName

FROM userData WHERE 1=1)”. This results in the following query being

passed to the database system:

       SELECT lastName FROM userData WHERE SSN=123456789
       UNION (SELECT lastName FROM userData WHERE 1=1)

The first half of the query will likely return no rows since 123456789 is not a valid

Social Security Number, but the second half of the query, which is attached with

a union, will return every row (and thus every last name) in the userData table.

In reality, this is not a practical example since queries in modern systems are

likely to be more complex. Although countermeasures are often inadequate,

rarely does a production system exist that does not prevent the simple kind of

attack illustrated above.

       Earlier SQL injection methodology generally relied on error information

from the application server, building on the very simple example displayed

above. In other words, as an attacker attempts various injection attacks and

consequently obtains a progressively more accurate picture of the structure of

the underlying database, including its vulnerability to injection. In the best-case

                                                                           Pg. 5/21
April 11, 2005                                       An Investigation into SQL Injection

scenario from the hacker’s point of view, the original query being targeted for

attack can be completely reconstructed. This makes it easy to construct

injection text to precisely obtain the desired information (or possibly execute

desired commands) from the server. Even if the original query cannot be

reconstructed, database error messages will almost certainly reveal the name of

one or more tables in the database, which is highly valuable in constructing

injection text.

       In response to this technique, one of the earlier reactions from the

security community was to simply suppress all error messages in production

applications. While this is certainly a good idea (and one that has been

embraced by traditional developers for a long time in the sense of a “debug”

mode), it turns out that it is basically ineffective for preventing SQL injection.

The term “BLIND SQL injection” refers to performing SQL injection attacks

without the benefit of server error messages. The easiest demonstration of this

technique utilizes a web application search page, because it is designed to

return from the database and display an arbitrary number of records. Consider

that a backend database has the easily guessable table name “Customers” with

the following structure:

    String name         String address            Long ZIP           Long IDNumber
 Note that the data-types differ here as they vary slightly
              among the major database systems.

                                                                            Pg. 6/21
April 11, 2005                                      An Investigation into SQL Injection

        Suppose the user appends the following to the existing query (to which

users are “blind”), via the dynamic search field read from the web application


        ‘ UNION SELECT ‘x’,’y’, ‘1’, ‘z’

Nothing is going to happen, because this will return an error (which cannot be

seen because error messages have been suppressed). However, the query can

simply be submitted again with the following:

        ‘ UNION SELECT ‘x’,’y’, ‘1’, ‘2’

This is going to return the following table:

 x                     y                       1                   2

Basically, the application is returning debug data in the context of its normal

interface, although as far as it is concerned an error never occurred. More

importantly, the attackers discovers that there is a table “customers” with 4

columns of type text, text, number, number, in that order. That is all that is

necessary to UNION any other table in the system, selecting columns that match

those types. This process is likely to take longer, especially if the SELECT

clause includes many columns, because it is iterative and thus the number of

possible orderings of columns and their respective types will increase

dramatically. However, considering that this type of attack could easily be

automated with a script, it becomes clear that hiding error messages is hardly a

long-term solution.

                                                                           Pg. 7/21
April 11, 2005                                       An Investigation into SQL Injection

       Another more sophisticated type of attack relies on executing what are

known as stored procedures. Stored procedures are effectively short programs

stored inside the database server that are executed via a call from a SQL query.

Stored procedures are not part of all database systems; simpler systems like

Access/Microsoft JET and MySQL do not offer them. Most heavy duty web

application systems have been moving towards stored procedures, primarily

since they offer better security and performance compared to passing query

strings from the web server. In fact, web applications that exclusively use stored

procedures are not really vulnerable to the kind of injection illustrated at all.

However, if care is not taken to limit the database user permissions of the web

application, there are still large security concerns. The major database systems

come with a variety of powerful, pre-installed stored procedures, enabling

everything down to command line level access on the server. If a hacker is able

to use techniques similar to those displayed above to execute such a stored

procedure (using the SQL keyword EXEC), the potential for damage is as great

or potentially greater than traditional SQL injection.

       A worthwhile question to ask is why SQL injection remains a serious

threat, despite the fact that it is well understood and seems easy enough to

prevent. For many applications, the answer is the same as many other areas of

computer security: security has not been taken as a top priority. However, for

today’s “best practices” web application, the answer is more the need for high

flexibility in web applications. Using regular expressions on all dynamically

                                                                            Pg. 8/21
April 11, 2005                                     An Investigation into SQL Injection

supplied data, before anything is ever passed to the database server, is likely to

be extremely effective for preventing many attacks. However, invariably, those

regular expressions will be forced to grow, and therefore become less strict, over

time. People using the application will complain that they cannot use a particular

symbol, which could be a major problem (for example, consider an email field in

an automated system, necessary for user registration/access). Combined with

the fact that various character sets and encoding formats, such as ASCII and

Unicode, are useable over http, it is virtually impossible to absolutely guarantee

an attacker will never be able to get the characters necessary to perform an

injection attack into a query string. These kinds of encoding issues are at the

forefront of SQL injection today, and protecting against them provides some very

interesting challenges.

Examples of SQL Injection

       There are several major database vendors commonly used today, and

each of them vary slightly with regard to the SQL injection techniques to which

they are susceptible. However, most of the basic principles of SQL injection

apply to all of them. As previously discussed, SQL injection involves adding

extra SQL to queries in a manner not intended by the application developers,

usually by escaping out of a string parameter in the query and/or commenting

out unwanted parts of the query. Below are a few examples of attacks against

Microsoft’s SQL Server database, Oracle, MySQL, and PostgreSQL databases.

                                                                          Pg. 9/21
April 11, 2005                                      An Investigation into SQL Injection

       Suppose for the following examples that there exist two tables: one called

CUSTOMERS to which a user has direct access and another called USERS to

which a user does not have direct access. Assume also that the USERS table

has information like usernames and passwords to the system and that users can

use the CUSTOMERS table to retrieve phone numbers of other customers by

inputting customer last names. Finally, it can be assumed that the example

application does no user input checking whatsoever (Finnigan).

       The first type of SQL injection to be discussed involves commenting out

parts of the SQL statement in the hopes of gaining access to extra information.

Consider a malicious person who knows a valid username for the system trying

to log in. Since the login is done against the USERS table, it can be surmised

that a query exists along these lines:

       select * from users where username = ‘{username}’ and

       password = ‘{password}’

where {username} and {password} are the user inputs. A malicious person

could get around having to use a password altogether by putting this into the

username field:

       user1’ --

where user1 is the valid username that the malicious attacker knew. The

dangerous part of this input is the “ ‘ -- “. The single quote closes the first quote

before {username} in the query. The double dash signifies the start of a

comment in SQL. The cracker has just logged into the system without a

                                                                          Pg. 10/21
April 11, 2005                                      An Investigation into SQL Injection

password. Consider also the case where the system has a superuser account

with a name that is easy to guess, like “admin” or “system”. The attacker could

log into the system with the above technique and have free reign to do whatever

he or she wants.

       The next example involves taking the previous example one step further

by adding a constraint that always evaluates to true with the aim of getting extra

information from the database. If the attacker in the previous example were to

insert this into the username field:

       x' or 1 = 1 --

the resulting query would be:

       select * from users where username = ‘x’ or 1 = 1 --'

       and password = {password}.

Here the password constraint is still commented and there is an extra constraint

in the “where” clause that always evaluates to true. Since an “or” is used, the

entire “where” clause always evaluates to true. Hence, this statement will return

every row in the table. Depending on how the application handles receiving

multiple user objects when it is only expecting one, this will probably just result in

a system error and not a critical breach of security. It is apparent that this

technique may be used in other situations to gain more information than the

application intended the user to see (Harper).

                                                                          Pg. 11/21
April 11, 2005                                     An Investigation into SQL Injection

       A third kind of SQL injection to discuss involves retrieving extra rows from

the database by using the “union” keyword in SQL. The application will probably

have a database query that looks something like this:

       select customer_phone from customers where

       customer_last_name = ‘{name}'

where {name} is the user’s input. To retrieve the list of user names from the

user table, a user could input something like

       x’ union select username from users where ‘x’ = ’x’.

In reality the constraint in the “where” clause is somewhat superfluous. Most

databases would go ahead and spit out all the user names without it there, but it

was left in for emphasis and to help illustrate the example. This example would

work against just about any database if user input were not validated.

Furthermore, the exploit could potentially cause the database to return every

username in the table. Now just imagine what would happen if a user queried

for the passwords and threw an “order by username” into the statement. He or

she would then have a list of every username and password pair in the system.

       For the next example, imagine a scenario of a registered user of the

application trying to gain administrative access. Suppose a page in the program

allows users to reset their passwords. This would be the SQL for performing this


       update users set password = ‘{new password}’ where

       username = {username}.

                                                                         Pg. 12/21
April 11, 2005                                       An Investigation into SQL Injection

Usually an application will require users to enter their old password and their

new one, but the old password was left out of the “where” clause in this example

as its presence will end up not mattering in the end. A user wishing to gain

administrative access, supposing he could guess the some part of the

administrator’s user name, could use SQL injection to change the administrators

password by entering this into the password field:

       newpassword' where username like ‘%admin%’ --.

Assuming the administrator account is some variant of “admin,” this would reset

the password for the account to “newpassword.” Now all the user has to do is

log out and log back in as the administrator, and they have full access to the

application (Achour).

       The next two examples only apply to Microsoft SQL Server. One

difference between SQL Server and other databases is that SQL Server allows

multiple queries to be sent at one time. This can be exploited by using some of

the techniques above in conjunction with putting additional queries in with the

input. Consider again the case of one user looking up another user’s phone

number and what happens when the user inputs the following string:

       {anything}’; update users set password=’ez4me’ where

       username like ‘%admin%’; --.

With this input, the user can put whatever she wants in for {anything}. The

second SQL command will execute regardless of whether the original query

returns any rows. Once again, the system has been compromised, and a user

                                                                           Pg. 13/21
April 11, 2005                                     An Investigation into SQL Injection

has gained full access (Achour). One caveat to this method, however, is that the

attacker must have some knowledge of the structure of the database, i.e. table

and column names. This fact makes this particular method a little more difficult,

it would not take too much poking around and trying names before the attacker

was able to guess the correct ones. This exploit can be particularly dangerous if

SQL Server is still using the default settings, which includes a default database

user and set of privileges. This user has permission to execute whatever SQL

he wants, including dropping tables, inserting/updating/deleting rows, and even

shutting down and restarting the database (Harper). Needless to say, this could

cause serious problems for a database-powered application.

       The final, and especially devious, example also involves SQL Server’s

ability to execute multiple statements in one query. The default user also has

access to “extended stored procedures” which are basically C/C++ DLL files.

These stored procedures give the user access to such functions as reading and

writing to external files and directories and executing commands from the

command prompt. Assume that since the application uses Microsoft SQL Server

that the system also runs Microsoft IIS as the web server and Microsoft Windows

as the operating system. A malicious user could input the following into the

phone number lookup:

       '; exec master..xp_cmdshell 'iisreset'; --,

and this would restart the entire web server terminating all active sessions. This

is definitely not a command to which regular users should have access (Harper).

                                                                         Pg. 14/21
April 11, 2005                                     An Investigation into SQL Injection

Preventing SQL Injection

       With all the dangers of SQL injection, developers and IT professionals

must learn ways to protect their applications from vulnerabilities to SQL injection.

With so many different database management systems and technologies to

develop web applications especially, the volume of vulnerabilities becomes large

and protection can become very difficult. However, system administrators,

database administrators, and developers have several options for protecting

their web applications, including managing configuration options of the database

management system and controlling the means in which applications execute

queries against a database.

       The first means of protection addresses the access rights of the

applications in executing certain types of queries. Even if an attacker was able

to submit a dangerous query, such as a DROP or ALTER, against the database,

permissions should prevent the database from actually executing that portion of

the query. In a setup for an ASP web application connected to Microsoft SQL

Server, the application should never connect to the database as the system

administrator (“sa”) user. Such a connection gives a successful attacker the

ability to modify table structures and database schemas in many undesirable

ways, including the ability to alter files and control the web server. Web

applications should consider using a user account that is able to perform limited

query types, such as SELECT, UPDATE, or DELETE statements.

                                                                         Pg. 15/21
April 11, 2005                                      An Investigation into SQL Injection

       In the case of using MySQL, user administration is very easy to configure

incorrectly. Any user can be given any number of permissions that range from

global, database-specific, or table-specific for executing database queries.

Making the mistake of giving global permissions for even an UPDATE query can

open the MySQL configuration table to complete modification for the attacker,

which would then make the entire database completely vulnerable and insecure.

Alternatively, database administrators for a MySQL database can leverage the

drilled-down security features in terms of limiting users to database and even

table-specific privileges as the needs may arise.

       The other central means of protecting web applications from SQL injection

attacks involves controlling how queries are prepared and then executed against

the database. One such way to control the query is by protecting the string

value of a parameter being passed into the application to prevent additional

portions of SQL code to be executed. Consider the case of a MySQL and PHP

web application. A simple database query basing a parameter passed as part of

a query string could look like the following example:

       $sql = “SELECT * FROM Users WHERE ID = ‘” .
               $_GET[‘id’] . “’”;
       $result = mysql_query($sql);

The preceding query is extremely vulnerable to a SQL injection attack in that the

$_GET parameter of ‘id’ first can be modified by the user very easily just by

changing the parameter within the query string and second can modified to

include additional SQL code that may potentially be very dangerous to the

                                                                          Pg. 16/21
April 11, 2005                                    An Investigation into SQL Injection

database server based on the user permissions. PHP and MySQL each offer

several means of protection for this example. First, PHP offers a function called

“mysql_real_escape_string” which escapes certain characters within the query

string passed into the function that as a result removes the danger from the

string. The preceding example thus becomes:

       $sql = “SELECT * FROM Users WHERE ID =” .
       $result = mysql_query($sql);

Now an attack cannot occur by appending SQL code to the $_GET[‘id’]

parameter in the query. The worst case is that the id is modified by the user and

no user is found from the database.

       Other database management systems and web application languages

provide for a means to prepare a SQL statement to execute against a database.

Oracle is one such example that uses parameter placeholders and SQL

statement preparation to protect against SQL injection before the query is even

completed. Using the Oracle database management system, developers may

first build a query with placeholders. For example, “:today” may be a

placeholder for the current day which may be inserted later into the query string.

Additional placeholders may be added as necessary for each parameter in the

database query. Then the developer associates values with each placeholder

that check the data for potential problems about data types or errors. Next, the

developer issues the database management system a “PREPARE” statement to

parse the SQL statement and the host variables for the statement. Finally, the

                                                                        Pg. 17/21
April 11, 2005                                       An Investigation into SQL Injection

statement is executed against the database. Sybase databases employ a very

similar process of using placeholders, such as the “:today” parameter mentioned

earlier, associating those placeholders with a simple variable, and then

executing the SQL statement under the protection of using the parameters

straight from the host variables. This systematic process reduces risk to SQL

injection by utilizing the host variables with placeholders as a safety net against


       The next step to protect a web application from the risks of SQL injection

is to treat all input as being wrong and dangerous. Validate any interaction in

which the user may have access to control, whether that is data input from a

form or a web page with a query string using the variables as constraints against

a database. Validate each input in terms of type, size, format, and range. If

supported by the database management system, consider replacing single

quotes within a query with double quotes. This simple change rejects many SQL

injection attacks. If a specific input is supposed to be a date, validate it to be

sure. Never use a variable that has not been validated to the best of the

developer’s ability straight into a SQL statement. Such a mistake could become

very risky for developers.

       A final means of reducing the risks of SQL injection is to take advantage

of stored procedures if supported by the database management system. Stored

procedures provide the same safety measures of parameterized SQL

statements in that variables are not used directly in a statement, but rather are

                                                                           Pg. 18/21
April 11, 2005                                      An Investigation into SQL Injection

stored in a data type that explicitly checks for potential errors. Stored

procedures remove the element of direct access from variable to the SQL

statement. Only after validation are values used in SQL statements.

       IT professionals must be very careful and methodical when developing

web applications in terms of the permissions available to the application. Simple

configuration errors or unnecessary permissions can open a database up to any

number of risks making a substantial impact on the business and the associated

data involved.


       With such a popular technology as SQL, it is important to recognize that

the easiest cracks in the system will be hit first and most often. This paper

reviews the simple and destructive SQL injection as a major consideration in

SQL database design. In the formation of an attack, the hardest part of SQL

injection is identifying the structure of the underlying database through trial and

error. In this process, error messages can provide an attacker with column

names and field data-types. Displaying debug error messages can greatly cut

down the trouble an attacker must go through to discover structural details of the

database and should be hidden from all users.

       Once an attacker has identified the structure of the database, he or she is

free to access accounts without passwords, retrieve lists of protected information

from anywhere in the database, delete records, modify entries, and execute

malicious code. In a real world situation, this could potentially compromise

                                                                            Pg. 19/21
April 11, 2005                                      An Investigation into SQL Injection

thousands of individuals’ personal information, or erase the content of a target’s

bank or stock holdings.

       Database developers can eliminate most of the vulnerabilities by

rigorously checking all input for malicious SQL code. Validation causes

negligible system slowdown because inputs are only checked once for each

series of queries. Another security method is carefully restricting user access to

only the lowest level operations they have permission to use. The last common

security method is using stored procedures to store input queries as variables in

the system. This method may cause the system to use more resources for

storing and converting variables, so its drawbacks should be carefully taken into

consideration when designing a SQL database system.

       Overall, SQL databases are among the most powerful and versatile

systems on the market. Because SQL injection acts within proper SQL

parameters, simple firewalls and intrusion detection systems cannot catch it.

Currently, there is no completely sure way to evade the SQL injection threat, but

if all permissions are carefully configured and all input is considered potentially

malicious, we feel it is possible to avoid most attacks.

                                                                          Pg. 20/21
April 11, 2005                                    An Investigation into SQL Injection

Sources Cited:

Achour, Mehdi, Friedhelm Betz, Antony Dovgal, et al. "Chapter 27. Database

   Security." PHP Manual. 13 January 2005. PHP Documentation Group. 07

   Apr. 2005 <


Finnigan, Pete. " SQL Injection and Oracle, Part One ." SecurityFocus 21

   November 2002. 07 Apr 2005 <>.

Friedl, Stephen. "SQL Injection Attacks by Example." Steve Friedl's

   Tech Tips. 13 January 2005. 07 Apr. 2005


Harper, Mitchell. "SQL Injection Attacks: Are You Safe?." Dev Articles. 29 May.

   2002. 07 Apr. 2005 <


John, Anil. “SQL Injection Protection.” SecureCoder. 6 April 2005


“Methods of Using Dynamic SQL.” Oracle Dynamic SQL. 5 April 2005 <>

Otey, Michael. “Injection Protection.” SQL Server Magazine. 5 April 2005



                                                                        Pg. 21/21

To top