College of Information Technology Lecturer: Dr. Hassan H.

Alrehamy
Department of Information Security Year(III)-Term(I)-Lecture(#3)

Web Security Vulnerabilities

The OWASP1 Top-10 is a standard awareness document for developers and web
application security. It represents a broad consensus about the most critical security
risks to web applications. In this lecture we discuss the most important vulnerability
on that list.
SQL Injection in web applications
The typical unit of execution of SQL is the 'query', which is a collection of statements
that typically return a single 'result set'. SQL statements can modify the structure of
databases (using Data Definition Language statements, or 'DDL') and manipulate the
contents of databases (using Data Manipulation Language statements, or 'DML'). In
the context of this lecture, we discuss Transact-SQL, the dialect of SQL used by
Microsoft SQL Server.
SQL Injection is a code injection technique used to attack data-driven applications,
in which malicious SQL statements are inserted into an entry field for execution (e.g.
to dump the database contents to the attacker). SQL injection must exploit a security
vulnerability in an application's software, for example, when user input is either
incorrectly filtered for string literal escape characters embedded in SQL statements
or user input is not strongly typed and unexpectedly executed. SQL injection is
mostly known as an attack vector for websites but can be used to attack any type of
SQL database.
SQL injection occurs when an attacker is able to insert a series of SQL statements
into a 'query' by manipulating data input into an application. A typical SQL
statement looks like this:
select id, forename, surname from authors
This statement will retrieve the 'id', 'forename' and 'surname' columns from the
'authors' table, returning all rows in the table. The 'result set' could be restricted to a
specific 'author' like this:
The Open Web Application Security Project (OWASP) is an online community that produces freely-
available articles, methodologies, documentation, tools, and technologies in the field of web application
security. The Open Web Application Security Project (OWASP) provides free and open resources. It is led
by a non-profit called The OWASP Foundation. The OWASP Top 10 - 2021 is the published result of
recent research based on comprehensive data compiled from over 40 partner organizations.
OWASP TOP 10 WAS: https://owasp.org/www-project-top-ten/

1
 College of Information Technology Lecturer: Dr. Hassan H. Alrehamy
Department of Information Security Year(III)-Term(I)-Lecture(#3)

select id, forename, surname from authors

where forename = 'john' and surname = 'smith'
An important point to note here is that the string literals 'john' and 'smith' are
delimited with single quotes. Presuming that the 'forename' and 'surname' fields are
being gathered from user-supplied input, an attacker might be able to 'inject' some
SQL into this query, by inputting values into the application like this:
Forename: jo'hn
Surname: smith
The 'query string' becomes this:
select id, forename, surname from authors
where forename = 'jo'hn' and surname = 'smith'
When the database attempts to run this query, it is likely to return an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'hn'.
The reason for this is that the insertion of the 'single quote' character 'breaks out' of
the single-quote delimited data. The database then tried to execute 'hn' and failed. If
the attacker specified input like this:
Forename: jo'; drop table authors--
Surname:
Then the authors table would be deleted. It would seem that some method of either
removing single quotes from the input, or 'escaping' them in some way would handle
this problem. This is true, but there are several difficulties with this method as a
solution. First, not all user-supplied data is in the form of strings. If our user input
could select an author by 'id' (presumably a number) for example, our query might
look like this:
select id, forename, surname from authors where id=1234
In this situation an attacker can simply append SQL statements on the end of the
numeric input. In other SQL dialects, various delimiters are used; in the Microsoft
Jet DBMS engine, for example, dates can be delimited with the '#' character. Second,
2
 College of Information Technology Lecturer: Dr. Hassan H. Alrehamy
Department of Information Security Year(III)-Term(I)-Lecture(#3)

'escaping' single quotes is not necessarily the simple cure it might initially seem.
This is the code for the 'form' page, into which the user types a username and
password:
<INPUT type=text name=username size=100% width=100></INPUT>
<INPUT type=password name=password size=100% width=100></INPUT>
In the code behind of this page, a statement like this would be found:
var sql = "select * from users where username = '" + username + "'
and password = '" + password + "'";
This is the critical part of the web page which creates the 'query string'. If the user
specifies the following:
Username: '; drop table users--
Password:
The 'users' table will be deleted, denying access to the application for all users. The
'--' character sequence is the 'single line comment' sequence in Transact-SQL, and
the ';' character denotes the end of one query and the beginning of another. The '--'
at the end of the username field is required in order for this particular query to
terminate without error. The attacker could log on as any user, given that they know
the users name, using the following input:
Username: admin'--
The attacker could log in as the first user in the 'users' table, with the following input:
Username: ' or 1=1--
And, strangely, the attacker can log in as an entirely fictional user with the following
input:
Username: ' union select 1, 'fictional_user', 'some_password', 1--
The reason this works is that the application believes that the 'constant' row that the
attacker specified was part of the recordset retrieved from the database. In order to
manipulate the data in the database, the attacker will have to determine the structure
of certain databases and tables. For example, our 'users' table might have been
created with the following command:

3
 College of Information Technology Lecturer: Dr. Hassan H. Alrehamy
Department of Information Security Year(III)-Term(I)-Lecture(#3)

create table users( id int, username varchar(255), password varchar(255), privs int )
And had the following users inserted:
insert into users values( 0, 'admin', 'r00tr0x!', 0xffff )
insert into users values( 0, 'guest', 'guest', 0x0000 )
insert into users values( 0, 'chris', 'password', 0x00ff )
insert into users values( 0, 'fred', 'sesame', 0x00ff )
Let's say our attacker wants to insert a user account for himself. Without knowing
the structure of the 'users' table, he is unlikely to be successful. Even if he gets lucky,
the significance of the 'privs' field is unclear. The attacker might insert a '1', and give
himself a low - privileged account in the application, when what he was after was
administrative access. Fortunately for the attacker, if error messages are returned
from the application (the default ASP behaviour) the attacker can determine the
entire structure of the database, and read any value that can be read by the account
the ASP application is using to connect to the SQL Server. First, the attacker wants
to establish the names of the tables that the query operates on, and the names of the
fields. To do this, the attacker uses the 'having' clause of the 'select' statement:
Username: ' having 1=1--
This provokes the following error:
Column 'users.id' is invalid in the select list because it is not contained in an
aggregate function and there is no GROUP BY clause.
So the attacker now knows the table name and column name of the first column in
the query. They can continue through the columns by introducing each field into a
'group by' clause, as follows:
Username: ' group by users.id having 1=1--
which produces the error:
Column 'users.username' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause. Eventually the attacker
arrives at the following 'username':
' group by users.id, users.username, users.password, users.privs having 1=1--

4
 College of Information Technology Lecturer: Dr. Hassan H. Alrehamy
Department of Information Security Year(III)-Term(I)-Lecture(#3)

which produces no error, and is functionally equivalent to:

select * from users where username = ''
So the attacker now knows that the query is referencing only the 'users' table, and is
using the columns 'id, username, password, privs', in that order. It would be useful
if he could determine the types of each column. This can be achieved using a 'type
conversion' error message, like this:
Username: ' union select sum(username) from users--
This takes advantage of the fact that SQL server attempts to apply the 'sum' clause
before determining whether the number of fields in the two rowsets is equal.
Attempting to calculate the 'sum' of a textual field results in this message:
The sum or average aggregate operation cannot take a varchar data type as an
argument.
which tells us that the 'username' field has type 'varchar'. If, on the other hand, we
attempt to calculate the sum() of a numeric type, we get an error message telling us
that the number of fields in the two rowsets don't match:
Username: ' union select sum(id) from users--
All queries in an SQL statement containing a UNION operator must have an equal
number of expressions in their target lists.
We can use this technique to approximately determine the type of any column of any
table in the database. This allows the attacker to create a well - formed 'insert' query,
like this:
Username: '; insert into users values( 666, 'attacker', 'foobar', 0xffff )--
However, the potential of the technique doesn't stop there, as there are way too many
forms of advanced injections to perform various tasks in order to take full control
over the application’s database.