SQL Injection (PDF)

ABSTRACT
This eBook explains SQL Injection attacks and how
they work.

Ibrahim Najmi
For: TeamOS

UNDERSTANDING SQL
INJECTION ATTACKS &
HOW THEY WORK.
IDENTIFY SQL INJECTION
CODE & PHP CAVEATS

Contents
Introduction .................................................................................................................................................. 2
What is a SQL Injection? ............................................................................................................................... 2
How Does a SQL Injection Work? ................................................................................................................. 3
What Does a SQL Injection Look Like? .......................................................................................................... 4
Ensuring Code is Not Vulnerable to SQL Injection Vulnerabilities ................................................................ 5
SQL Injection is The Most Widely Exploited Vulnerability ............................................................................ 5

Introduction
SQL Injections have been keeping security experts busy for over a decade now as they continue
to be one of the most common type of attacks against webservers, websites and web
application servers. In this article, we explain what a SQL injection is, show you SQL injection
examples and analyse how these type of attacks manages to exploit web applications and
webservers, providing hackers access to sensitive data.

What is a SQL Injection?
Websites operate typically with two sides to them: the frontend and backend. The frontend is
the element we see, the rendered HTML, images, and so forth. On the backend however, there
are layers upon layers of systems rendering the elements for the frontend. One such layer, the
database, most commonly uses a database language called SQL, or Structured Query Language.
This standardized language provides a logical, human-readable sentence to perform definition,
manipulation, or control instructions on relational data in tabular form. The problem, however, is
while this provides a structure for human readability, it also opens up a major problem for security.
Typically, when data is provided from the frontend to the backend of a website – e.g. an HTML form with
username and password fields – this data is inserted into the sentence of a SQL query. This is because
rather than assign that data to some object or via a set() function, the data has to be concatenated into
the middle of a string. As if you were printing out a concatenated string of debug text and a variable’s
value, SQL queries work in much the same way. The problem, however, is because the database server,
such as MySQL or PostgreSQL, must be able to lexically analyze and understand the sentence’s grammar
and parse variable=value definitions. There must exist certain specific requirements, such as wrapping
string values in quotes. A SQL injection vulnerability, therefore, is where unsanitized frontend data, such
as quotation marks, can disrupt the intended sentence of a SQL query.

How Does a SQL Injection Work?
So what does “disrupt the intended sentence of a SQL query” mean? A SQL query reads like an
English sentence:
Take
variable
foo
and
set
it
to
‘bar’
in
table
foobar.
Notice the single-quotes around the intended value, bar. But if we take that value, add a single
quote and some additional text, we can disrupt the intended sentence, creating two sentences that
change the entire effect. So long as the database server can lexically understand the sentence, it is
none the wiser and will happily complete its task. So what would this look like?
If we take that value bar and change it to something more complex – bar’ in table foobar. Delete
all values not equal to ‘ – it completely disrupts everything. The sentence is thus changed as
follows:
Take variable foo and set it to ‘bar’ in table foobar. Delete all values not equal to ‘’ in table
foobar.
Notice how dramatically this disrupts the intended sentence? By injecting additional information,
including syntax, into the sentence, the entire intended function and result has been disrupted to
effectively delete everything in the table, rather than just change a value.

What Does a SQL Injection Look Like?
In code form, a SQL injection can find itself in effectively any place a SQL query can be altered by the user
of a web application. This means things like query strings e.g: example.com/?this=query_string, form
content (such as a comments section on a blog or even a username & password input fields on a login
page), cookie values, HTTP headers (e.g. X-FORWARDED-FOR), or practically anything else. For this
example, consider a simple query string in PHP:

First, we will break this down a bit.
On line #1, we set the value of the username field in the query string to the variable $user.
On line #2, we insert that variable’s value into the query string’s sentence. Substituting the variable
for the value admin in the URI, the database query would ultimately be parsed as follows by
MySQL:
UPDATE tbl_users SET admin=1 WHERE username='admin'

However, a lack of basic sanitization opens this query string up to serious consequences. All an
attacker must do is put a single quote character in the username query string field in order to alter
this sentence and inject whatever additional data he or she would like.
Here is an example of what this would look like:

Now, with this altered data, here is what MySQL would see and attempt to evaluate:
UPDATE tbl_users SET admin=1 WHERE username='admin' OR 'a'='a'
Notice, now, that if the letter A equals the letter A (basically true=true), all users will be set to admin
status.

Ensuring Code is Not Vulnerable to SQL Injection Vulnerabilities
If we were to add a function, mysql_real_escape_string() for example, on line #1, that would
prevent this particular variable from being vulnerable to a SQL injection. In practice, it would look
like this:

This function escapes certain characters dangerous to MySQL queries, by prefixing those characters with
backslashes. Rather than evaluate the single quote character literally, MySQL understands this prefixing
backslash to mean do not evaluate the single quote. Instead, MySQL treats it as part of the whole value
and keeps going. The string, to MySQL, would therefore look like this:
UPDATE tbl_users SET admin=1 WHERE username='admin\' OR \'a\'=\'a'

Because each single quote is escaped, MySQL considers it part of the whole username value, rather
than evaluating it as multiple components of the SQL syntax. The SQL injection is thus avoided,
and the intention of the SQL sentence is thus undisrupted.
Caveat: For these examples, we used older, deprecated functions like mysql_query() and
mysql_real_escape_string() for two reasons:
1.
2.

Most PHP code still actively running on websites uses these deprecated functions;
It allows us to provide simple examples easier for users to understand.

However, the right way to do it is to use prepared SQL statements. For example, the prepare() functions
of the MySQLi and PDO_MySQL PHP extensions allow you to format and assemble a SQL statement using
directive symbols very much like a sprintf() function does. This prevents any possibility of user input
injecting additional SQL syntax into a database query, as all input provided during the execution phase of
a prepared statement is sanitized. Of course, this all assumes you are using PHP, but the idea still applies
to any other web language.

SQL Injection is The Most Widely Exploited Vulnerability
Even though it has been more than sixteen years since the first documented attack of SQL Injection, it is
still a very popular vulnerability with attackers and is widely exploited. In fact SQL Injection has always
topped the OWASP Top 10 list of most exploited vulnerabilities.