Causes and Prevention of SQL Injection Attacks in Web Applications

Abstract

SQL injection is one of the major threats to the security of the web applications. Attackers try to gain unauthorized access to the database, which has vital and private information of the users. Many researchers have provided various techniques and practices to protect the web applications from attackers. There is a plethora of techniques available to perform SQL injection and usually not everyone is familiar with every attack. Hence, this kind of attack is still the most prevalent. In this paper, we have presented the types of SQL injections attacks and most dominant ways to prevent them.

Full text

Causes and Prevention of SQL Injection Attacks in Web
Applications
Stephanos Mavromoustakos
University of Windsor
401 Sunset Ave
Windsor, ON N9B 3P4, Canada
stephano@uwindsor.ca
Aakash Patel
University of Windsor
401 Sunset Ave
Windsor, ON N9B 3P4, Canada
patel1bu@uwindsor.ca
Kinjal Chaudhary
University of Windsor
401 Sunset Ave
Windsor, ON N9B 3P4, Canada
chaudhak@uwindsor.ca
Parth Chokshi
University of Windsor
401 Sunset Ave
Windsor, ON N9B 3P4, Canada
chokship@uwindsor.ca
Shaili Patel
University of Windsor
401 Sunset Ave
Windsor, ON N9B 3P4, Canada
patel1ab@uwindsor.ca
ABSTRACT
SQL injection is one of the major threats to the security of the web
applications. Attackers try to gain unauthorized access to the
database, which has vital and private information of the users.
Many researchers have provided various techniques and practices
to protect the web applications from attackers. There is a plethora
of techniques available to perform SQL injection and usually not
everyone is familiar with every attack. Hence, this kind of attack is
still the most prevalent. In this paper, we have presented the types
of SQL injections attacks and most dominant ways to prevent them.
CCS Concepts
•Security and Privacy➝ Software and application security➝
Web application security.
Keywords
SQL-Injection, SQLi, Web Security, Security Causes, Security
Prevention.
1. INTRODUCTION
SQL injection is one type of web application security indebtedness
in which an attacker submits a database SQL command that is
carried out by a web application, which may expose all the
information of a back-end database. SQL injection attack can occur
when user use the SQL command and queries without validation
and encoding. Data tricks made by user allows the application to
execute unintended commands or data change or intercept the data.
An SQL injection (SQLi) allows an attacker to create, read, update,
change or remove data stored in the back-end database.
SQL injections form one of the most familiar class of attacks. An
SQLi attack inserts malicious SQL statements into an entry field
for execution to extract data, to bypass authentication, or to execute
remote commands [6].
The paper is organized as follows: Section 2, briefly explains SQLi
attacks in dynamic queries, while Section 3 provides a review of
the various types of SQLi attacks. Section 4 describes techniques
for preventing SQLi attacks and Section 5 draws the concluding
remarks.
2. SQLI ATTACK IN DYNAMIC QUERIES
SQL injections occur when data entered by users is directly
intercept by any unauthorised person as a SQL query. Attackers
trick the SQL interpreter by giving manipulated data input which
the interpreter cannot distinguish if it is vulnerable to the database,
hence executing unintended commands[5].
The SQLi often occurs when SQL is dynamically generated within
the front-end application. These attacks are most common with
Active Server Pages (ASP) and Hypertext Pre-processor (PHP)
applications. The core reason behind an SQLi narrows down to
poor coding practices and within the database stored procedures [16].
Let us assume that the front-end web application creates a dynamic
SQL Script that ends up executing an SQL Script similar to that
shown in Example 1. [16]
Example 1: A simple dynamic SQL statement as expected from the
application.
SELECT * FROM Accounts WHERE acct_num=14
This SQL Script is created when the customer goes to the customer
login of a bank’s website. The value passed in as the account
num is taken from the query string in the URL, so the query shown
above is created when the customer goes to the:
URL http://www.bankname.com/customer/login.a
spx?acct_num=14.
Within the .NET code, a simple string concatenation is done to put
together the SQL query. So any value that is put at the end of the
query string is passed to the database at the end of the select
statement. If the attacker were to change the query string to
something like:
Permission to make digital or hard copies of all or part of this work for
personal or classroom use is granted without fee provided that copies are
not made or distributed for profit or commercial advantage and that copies
bear this notice and the full citation on the first page. To copy otherwise,
or republish, to post on servers or to redistribute to lists, requires prior
specific permission and/or a fee.
ICINS '16, December 28 - 31, 2016, Kuala Lumpur, Malaysia
© 2016 ACM .ISBN978-1-4503-4796-9/16/12 …$15.00.
DOI:http://dx.doi.org/10.1145/3026724.3026742
55

/login.aspx?acct_num=14; delete from acct_num
then the query sent to the SQL Server will be a little more
dangerous to run as shown in Example 2[16].
Example 2: A dynamic SQL String that has had a delete statement
concatenated to the end of it.
SELECT * FROM Accounts WHERE acct_num=14;
DELETE FROM Accounts;
The SQL database understands that the statement ending via the
semicolon “;” means there is next statement to be executed and
hence the next statement could be vulnerable such as DELETE or
DROP[16].
While the initial query is run as normal and without any errors
being generated, the Accounts table, shows no records in the
because the second query in that batch will have executed against
the database as well. Even if the attacker omits the value that the
query is expecting, he can pass in “; delete from
Accounts;” and while the first query attempting to return the
data from the Accounts table will fail, the batch will continue
moving on to the next statement, which will delete all the records
in the Accounts table [16].
3. TYPES OF SQLI ATTACKS
The different SQL injections attacks are briefly explained below:
3.1 Client-side regulation
If input message is carried out in client-side scripts only, then
security functions of those scripts can be alter using cross-site
scripting. Therefore, attackers can ignore input validation and send
invalidated input to the server-side.
3.2 Error message displayed
Suppose you have this wrong code:
SELECT last name FROM student WHERE id =8454\'
From the message error we can find out table name and its fields:
last name; student; id. By the gained information an attacker can
organize more strict attacks.
3.3 Impair input
This is almost the most common requirement on performing a SQLi
attack. There are some specifications in the web application which
are used in SQL queries. If there is no any error checking for queries
then it can violate in SQLi attacks. These limitations may contain
SQL keywords, e.g. INSERT, UPDATE or SQL control characters
such as, quotation marks and semicolons.
3.4 Generous privileges
Generally in a database, the privileges are defined as the protocol
to state which database subject has access to which object and what
operation are associated with user to be allowed to perform on the
objects. These privileges include allowing execution of actions, e.g.
SELECT, INSERT, UPDATE, DELETE, DROP, on certain objects.
Web applications open database connections using the specific
profile for accessing the database. An attacker or interceptor, who
bypasses authentication gains privileges equal to the profiles when
more privileges are given to the profile, the number of available
attack methods and affected objects increases. The worst case
happens if an account can connect to a system that is associated
with the system admin account because normally it has all
privileges.
3.5 Uncontrolled variable size
If variables allow storage of data to be greater than expected
consequently this allows an attacker or interceptor to enter
modified or faked SQL statements. Scripts that do not control
variable length may even open the way for attacks, such as buffer
overflow.
3.6 Tautologies
This kind of attack injects code in one or more conditional
statements which always evaluate to be true. The effect of this
attack turn to how the end results of the query are being used in the
application. Tautologies are used to bypass the authentication page
and extract the data. In this type of injection, an attacker exploits
an effected field that is used in a query’s WHERE conditional.
Transforming the conditional into a tautology causes all of the rows
in the database table addressed by the query to be returned [7].
Example : In this example attack, an attacker or interceptor submits
“ ’ or 1=1 - ” for the signin input field (the input submitted for
the other fields is irrelevant). The resulting query is:
SELECT profiles FROM customer WHERE signin=’’
or 1=1
The code injected in the conditional (OR 1=1) change the entire
WHERE clause into a tautology. As the conditional is a tautology,
the query evaluates to true for each row in the table and retrieve all
of them [8].
3.7 Illegal/Logically Incorrected Queries
This attack publish the back-end structure of the database to the
attackers or interceptors. The attack is considered fundamental,
information-collecting step for other attacks. Most of the times, the
error page shown by the application servers are over descriptive
which helps the attackers to gain more information. Usually, error
messages are kept descriptive so as to help the programmers debug
their application fast and easily, but this also helps the attackers
getting more information about the web application [12].
Example : In this example, the attacker’s aim is to cause a type
conversion error that can expose relevant data and information. To
do this, the attacker injects (applies) the following text into insert
field pin:
“convert (int, (select top 1 name from
sysobjects where xtype=’c’))”.
The resulting query is:
SELECT profiles FROM customers WHERE
signin=’’ AND pass=’’ AND pin= convert (int,
(select top 1 name from sysobjects where
xtype=’c’))
In the attack string, the injected select query experiments to extract
the first customer table (xtype=’c’) from the database’s
metadata table; assume the application is using Microsoft SQL
Server, for which the metadata table is named as sysobjects.
The query then tries to change this table name into an integer. As a
result, this is not a legal type change, the database throws an error.
3.8 Piggy-backed Queries
In this type of attack, the attacker injects an additional query into
the original query. This query is easily identifiable because attacker
56

is not intending to modify the original query, instead he is trying to
include new and distinct queries which results, in receiving
multiple SQL queries to the database. The first query is the intended
query which is normally executed and the subsequent ones are the
injected ones which are executed afterwards which results in
harmful attacks. If the attack is successful one can insert virtually
any type of SQL command including stored procedures and can
execute along with the original query [9].
The result of executing the queries after the intended one, would be
to drop the table users, which will most likely destroy valuable
information. On the other hand, this can also lead to insertion of
new users and execute stored procedures.
3.9 Union Query
In this type of attack, the attacker writes SQL queries cross table
which means he can return records from another table. Also a
UNION statement allows column of all data types to be selected.
The outcome of this attack is that database returns a dataset that is
union of results of original query with the results of injected query.
For example an attacker could perform UNION query by injecting
the text:
“ UNION SELECT acct_num from Accounts where
acct_num=2345 –“
which would result in the following query:
SELECT user FROM Users WHERE username=’’
UNION
SELECT acct_num FROM Accounts where
acct_num=2345 -- AND password =
Assuming that the login is empty string the original query results in
null set and injected query results data from the Accounts table.
3.10 Stored Procedures
In this type of attack, the attacker is trying to execute stored
procedures present in the database. Most of the database vendor
ships databases with standard set of stored procedures which
extends functionality of database and allows to interact with
operating system. Hence, after determining the back-end database
in use, SQLIA’s can be manipulated according to that specific
database [13].
There is a common misconception that using stored procedures in
a web application makes it SQLIA’s proof but often developers are
surprised to see that using stored procedures in a web application is
as vulnerable to attacks as their normal applications.
Example : The following example shows that if an attacker tries to
execute a PHP script with the input username as Lisa’ -- and
password as any random password the query would look like
SELECT * FROM Users WHERE Username = ‘Lisa’ -
- ‘ AND Password = ‘any password’ which results in
SQL Injection attack. In order to prevent this attack the best advice
is to not concatenate user input with SQL queries and using
prepared statements.
3.11 Inference
In this type of attack, variation of the query is done so as to recast
it in the form of an action that executes on the basis of true/false
question about data values in the database. As attackers are trying
to attack the secure website. When the attack is successful there is
no correct feedback from database error messages. Since database
error messages are not able to provide the attacker with feedback,
attackers must use a different method of obtaining a response from
the database. Attacker constructs conditional attack which uses
specific instruction to see database behavior depending which
branch of condition was executed. By carefully observing when the
site perform the equivalent but when its behavior changes, the
attacker can conclude that not only certain parameters are
accessible, but also additional information about the values in the
database. There are two vulnerable techniques:
Blind Injection: This attacks result from insufficient cure for SQL
Injection. It is a form of attack that overcomes the lack of error
messages. Based on query execution attacker can examine that his
query was successful or not. For example an attacker might insert
in to query such as ‘lastname’ AND 1=1; -- in an input
field and if it returns the same username then attacker knows that
application is vulnerable to attacks. Similarly, an attacker can
extract table names from the database.
Time Based Attacks: This type of attack allows an attacker to have
valuable information from database by keeping time wait in the
response of the database. To implement a timing attack, attackers
structure their injected query in the pattern of an if/then statement,
whose branch signify resembles to an unknown about the contents
of the database. Onward one of the section, the attacker uses a SQL
design that takes an accepted amount of time to execute, (e.g.
the WAITFOR password, which matter the database to delay in its
response by a specified time). By measuring the increase or
decrease in response time of the database, the attacker can alter
which branch was taken in his injection and therefore the answer to
the injected question [10].
Example: We explain two ways in which Inference based attacks
can be used. The first of these is identifying injectable parameters
using blind injection. Considering two possible injections into the
login field. The first being “validuser” and 1=0 - -” and
for second “validuser’ and 1=1 - -”. These injections
result in the following two queries:
SELECT details FROM users WHERE
login=’validuser’
and 1=0 -- ’ AND pass=’’ AND pin=0
SELECT details FROM users WHERE
login=’validuser’
and 1=1 -- ’ AND pass=’’ AND pin=0
Now, let us consider the two sides. In the first phase, we have a
protected application, and the input for login is approved correctly.
In this case, both injections would return login error message, and
the attacker would know that the login parameter is not vulnerable.
In the second scenario, we have an insecure application and the
login specification is vulnerable to injection. The attacker
acknowledges the first injection and, as it always evaluates to false,
the application returns a login error information. At this point
however, the attacker does not know if this is because the
application validated the input perfectly and stop the attack attempt
or due to the attack itself caused the login error. The attacker then
submits the second query, which always evaluates to true. If in this
case there is no login error message, then the attacker will get to
know about the attack that went through and also that the login
parameter is vulnerable to injection.
3.12 Alternate Encodings
This attack is used to avoid defensive coding techniques and many
other preventive measures by injecting modified text. Apparently,
57

Alternate Encoding digs to exploit vulnerabilities that otherwise
cannot be exploited with defensive coding techniques and other
preventive measures. What makes this technique necessary is to
avoid common defensive coding practices which scans for known
bad characters such as single quotes and comment operators [13].
To evade this defense, attackers have employed alternate methods
of encoding their attack strings (e.g., using hexadecimal, ASCII,
and Unicode character encoding). Normal exploitation prevention
techniques usually ignore specially encoded strings, which allows
Alternate Encoding attacks to go undetected. Moreover, different
application layer deals with alternate coding differently. So
practically it becomes impossible for this attack to be secured by
developer as this attack can target different layers in the application.
So, if an attacker tries to add an encoded character using char()
function which takes parameter as integer or hexadecimal encoding
then the query would become:
SELECT users FROM Users WHERE username =
‘username’; exec(char(0x73687574646f776e)) -
- AND password =
4. PREVENTING SQLI ATTACKS
There are many techniques to prevent SQL Injection attacks. These
techniques could be best development techniques or fully
automated frameworks for prevention and detection of attack at
early stage.
4.1 Defensive Coding Practices
Improper input validation is the main cause for this kind of SQLi
vulnerabilities. Therefore, the direct solution for removing these
vulnerabilities is to apply suitable defensive coding practices.
Below are few prevention methods to protect from SQLi
vulnerabilities.
Input type examination: The simplest way to protect your
application is to check inputs and prevent attacks. For example, in
the case of numeric inputs, the developer can simply refuse any
input that includes characters other than numerical. Many
developers ignore this kind of check by accident because the data
inserted by user is always entitled by string, regardless of its content
or intended use [7].
Encoding of inputs: Attacker uses meta-characters into string
parameter which tricks the SQL parser into interpreting user input
as SQL tokens. It is possible to formally forbid any usage of these
meta-characters, doing so would restrict a non-malicious user’s
skills to identify clearly and definitely legal inputs that include such
characters. A better solution is to use functions that encode a whole
string in such a way that all meta-characters are specially encrypted
and interpreted by the database as general characters [11].
Positive pattern matching: Proper input validation routine should
be established by developers which checks good inputs against bad
inputs. This approach is generally entitled as positive validation, as
opposed to negative validation, which searches input for forbidden
patterns or SQL tokens. It is impossible for developers to check for
every invalid input but on the other hand it is safer to specify all the
inputs that are legal [14].
Identification of all input sources: Developers must check all input
to their application. There are many possible resources of insertion
data to an application. If used to construct a query, these insertion
data resources can be a path for an attacker or interceptor to
introduce a SQLi attack.
Applications still remain problematic in practice even after
implementing defensive coding practices which is considered as a
best measure. Defensive coding is prone to human error and is not
as precisely and completely applied as automated techniques. Even
though most of the developers code safely, it is difficult to
encompass all sources of input in defensive coding practices.
4.2 Automated Exploitation
Manipulating an SQL injection has become relatively easy because
of some magnificent automated tools such
as SQLNinja and SQLMap. Among these automated tools some
are highly configurable and sometimes allow you to circumvent
blacklists/filters or even provide you with an OS shell amongst
many other features. SQLMap contains an impressive collection of
payloads to detect and exploit SQL vulnerabilities. SQLMap
currently contains specific payloads for boolean-based blind, time-
based blind, error-based, UNION query, stacked queries and out-
of-band. There are very rare SQL injection vulnerabilities that are
not exploitable after some tweaking with SQLMap or one of its
competitors.
4.3 Other Techniques for Detection and
Prevention
Black-box Testing: In a Web Application, a Web crawler can be
used to identify all points that can be used to inject SQLIAs. This
Web Crawler can be configured to use and authenticate valid user
credentials. It would also avoid visiting spammed links that would
damage current session and logoff the crawler.
Static-Code Tester: This technique checks for type mismatches in
dynamically generated query string. JDBC-Checker is a tool that
detects and helps developers which allows attackers to exploit type
mismatch. However, this technique would not catch other types of
SQLIAs which consist of syntactically and type correct queries.
The primary drawback of this technique is that its scope is limited
to detecting and preventing tautologies and cannot reveal other
types of attacks [4].
Taint-Based Approach: This type of approach would require
modification of the PHP interpreter to track taint information about
user input and should intelligently can detect and reject queries if
any untrusted input is used to create types of SQL tokens. The
primary drawbacks of this technique are that it assumes that
satisfactory provision for susceptible functions can be accurately
expressed using their typing system and that forcing input to pass
through certain types of filters is sufficient to consider it not trusted
enough [5].
Proxy filters: Security gateway is a proxy filtering system that
enforces input validation rules on the data flow to the web
applications [17]. Developers can provide constraints and apply
special transformations which are applied as they flow from web
page to application server using Security Policy Descriptor
Language (SPDL). As SPDL is highly expressive, developers have
freedom to express their own policy. The main drawback with this
approach is that it is human-based and also developers are likely to
know which data needs to be filtered and what patterns and filters
they need to apply to the data.
New Query Development Paradigms: One of the safe and reliable
way to access the database is to encapsulate the database queries
which comes under the approaches known as SQL DOM and Safe
Query Objects. These techniques offer an effective path to bypass
the SQLIA problem by alternating the query-building process from
an unregulated one that uses string concatenation to a systematic
one that uses a type-checked API. Within their API, they are able
58

to methodically apply coding best procedure such as input filtering
and traditional type checking of user input. By changing the
development paradigm in which SQL queries are created, these
techniques remove the coding practices that make most SQLIAs
possible. As this requires the developers to learn a new
programming paradigm or query development process it is
considered to be less effective [7].
Instruction Set Randomization: SQLrand is an approach based on
instruction-set randomization. It is a framework which allows
developers to create queries using randomized instruction instead
for normal SQL queries. A proxy filter seizes queries to the
database and de-randomizes the keywords and hence therefore SQL
code injected by an attacker would not have been constructed using
the randomized instruction set. Therefore, injected commands
would result in a syntactically incorrect query. Although this
technique could be effective it has drawbacks such as it uses secret
key to modify instructions and the security is dependent on the
attacker for not being able to discover the key and secondly, the
approach imposes a compelling infrastructure overhead because it
requires the integration of a proxy for the database in the system.
Parse Tree Validation Approach: This approach uses the parse tree
of a particular statement at execution time and its primary statement.
When there is a match then you stop the execution of the statement.
5. CONCLUSIONS
In this paper, we have presented a survey and comparison of cur-
rent techniques for detection and prevention of SQLIAs. In
contemplation to accomplish that, we have analysed various types
of SQLIAs and we have explained the various techniques in terms
of their capability to recognize and prohibit such attacks. To better
understand which techniques handle which mechanism we have
examined different SQLIAs in various examples.
Many of the techniques have problems handling attacks that take
recognition of poorly-coded stored process and cannot handle
attacks that disguise themselves using alternate encodings. There is
also a difference in ability of the preventive measures according to
the variations in prevention-focused and general detection and
prevention techniques.
6. REFERENCES
[1] A. S. Yeole, B. B. (2011). Analysis of different technique for
detection of SQL injection. Proceedings of the International
Conference & Workshop on Emerging Trends in Technology (pp.
963-966). New York: ACM.
[2] Angelo Ciampa, C. A. (2010). A heuristic-based approach for
detecting SQL-injection vulnerabilities in web applications.
International Conference on Software Engineering (pp. 43-49).
New York: ACM.
[3] Db Networks. (2016). SQL Injection Attacks. Db Networks.
[4] Dennis Appelt, C. D. (2014). Automated testing for SQL
injection vulnerabilities: an input mutation approach. International
Symposium on Software Testing and Analysis (pp. 259-269). New
York: ACM.
[5] DuPaul, N. (2016). SQL Injection Cheat Sheet. Retrieved May
12, 2016, from Veracode: http://www.veracode.com/security/sql-
injection
[6] Julian Thomé, A. G. (2014). Search-based security testing of
web applications. International Conference on Software
Engineering (pp. 5-14). New York: ACM.
[7] Kai-Xiang Zhang, C.-J. L.-J.-L.-H. (2011). TransSQL: A
Translation and Validation-based Solution for SQL-Injection
Attacks. First International Conference on Robot, Vision and
Signal Processing, 248-251.
[8] NunoAntunes, M. V. (2009). Comparing the Effectiveness of
Penetration Testing and Static Code Analysis on the Detection of
SQL Injection Vulnerabilities in Web Services. IEEE Pacific Rim
International Symposium on Dependable Computing, 310-306.
[9] Orlando Karam, S. P. (2009, March 19). Teaching with security
in mind. ACM Southeast Regional Conference.
[10] Shelly Rohilla, P. K. (2013, November 11). Database Security
by Preventing SQL Injection Attacks in Stored Procedures.
International Journal of Advanced Research in Computer Science
and Software Engineering, 3(11), 1-5.
[11]SupenoDjanali, F. A. (2014). SQL Injection Detection and
Prevention System with Raspberry Pi Honeypot Cluster for
Trapping Attacker. (pp. 1-4). Bandung: International Symposium
on Technology Management and Emerging Technologies.
[12] Weiss, A. (2012, August 16). How to Prevent SQL Injection
Attacks. Retrieved May 12, 2016, from eSecurity Planet:
http://www.esecurityplanet.com/hackers/how-to-prevent-sql-
injection-attacks.html
[13] Wikipedia. (2016, April 30). SQL Injection. Retrieved May 12,
2016, from Wikipedia:
https://en.wikipedia.org/wiki/SQL_injection
[14] William G. J. Halfond, A. O. (2006). Using positive tainting
and syntax-aware evaluation to counter SQL injection attacks.
Foundations of Software Engineering (pp. 175-185). New York:
ACM.
[15] William G.J. Halfond, J. V. (2006). A Classification of SQL
Injection Attacks and Countermeasures. (pp. 1-11). Geogia:
Georgia Institute of Technology.
[16] Cherry, D. (2011, July 01). How to stop SQL injection and
prevent data compromises. Retrieved May 29, 2016, from
ComputerWeekly.com:
http://www.computerweekly.com/tip/How-to-stop-SQL-injection-
and-prevent-data-compromises
59