Securing Web Application from Structured Query Language Injection Attacks: A Four-Tier Approach

(1)

International Journal of Emerging Technology and Advanced Engineering

Website: www.ijetae.com (ISSN 2250-2459,ISO 9001:2008 Certified Journal, Volume 4, Issue 10, October 2014)

660

Securing Web Application from Structured Query Language

Injection Attacks: A Four-Tier Approach

Odunayo Esther Oduntan

1

, Temitope Sunday Aluko

2 1

Department of Computer Science, Federal Polytechnic Ilaro, Ogun State, Nigeria

2_{Department of Computer Science, Ogun State Institute of Technology Igbesa, Ogun State} Abstract--With the emergence of Web 2.0, information

sharing through social networking have increased and the business adoption of the Web as a means of doing business and delivering service has greatly increased, which has led to the attack of websites directly. As a result, industry is paying increased attention to the security of the web applications themselves in addition to the security of the underlying computer network and operating systems. This paper aims at ensuring security at the operating system layer, application webserver, the database layer and the server-side layer of a web application by using a Four-tier approach which involves applying PHP Web Preprocessor Language codes at the server-side, to develop a blogging Web Application on a Linux platform, the expected result is secured web application from Structured Query Language Injection Attack.

Keywords-- social networking, computer network, security, operating system, blogging, web application, linux platform, webserver, database layers, Structured Query Language Injection Attack

I. INTRODUCTION

A complex combination of curiosity, competition, classic boredom and espionage lead humans to use computer applications in ways unintended, leading (in some cases) to serious security breaches and the need to secure compromised applications from further attacks.

Computer applications in general and web applications in particular are coming under continuous digital barrage of attacks; the following statement from Imperva Data Security Blog [1] sums it all:

“…SQL injection is the most pernicious vulnerability in human computer history. From 2005 through today, SQL injection has been responsible for 83% of successful hacking-related data breaches. Using data from Privacyrights.org, we checked the data breaches from 2005 to today. There were 312,437,487 data records lost due to hacking with about 262 million records from various breaches including TJMax, RockYou and Heartland, all of which were SQL injection attacks. We found, since July, the observed Web applications suffered on average 71 SQLi

attempts an hour.

Specific applications were occasionally under aggressive attacks and at their peak, were attacked

800-1300 times per hour” [1]

Surprisingly, diverse organizations (software development, supermarkets chains, bank credit card payment processors, universities, security firms etc) had one time or other fallen victims of SQLi attacks.[2]

· In February 2002, Jeremiah Jacks discovered that Guess.com was vulnerable to an SQL injection attack, permitting anyone able to construct a properly-crafted URL to pull down 200,000+ names, credit card numbers and expiration dates in the site’s customer database. · On November 1, 2005, a teenage hacker used SQL

injection to break into the site of a Taiwanese information security magazine from the Tech Target group and steal customers’ information.

· On January 13, 2006, Russian computer criminals broke into a Rhode Island government web site and allegedly stole credit card data from individuals who have done business online with state agencies. · On March 29, 2006, a hacker discovered an SQL

injection flaw in an official Indian government’s tourism site.

· On June 29, 2007, a computer criminal defaced the Microsoft UK website using SQL injection. UK website The Register quoted a Microsoft spokesperson acknowledging the problem.

· In January 2008, tens of thousands of PCs were infected by an automated SQL injection attack that exploited a vulnerability in application code that uses Microsoft SQL Server as the database store.

· In July 2008, Kaspersky’s Malaysian site was hacked by a Turkish hacker going by the handle of “m0sted”, who said to have used an SQL injection.

(2)

International Journal of Emerging Technology and Advanced Engineering

661 “m0sted” is known to have carried out similar attacks

on a number of other Web sites in the past including against a site maintained by Internet security company Kaspersky Lab.

· On April 13, 2008, the Sexual and Violent Offender Registry of Oklahoma shut down its website for “routine maintenance” after being informed that 10,597 Social Security numbers belonging to sex offenders had been downloaded via an SQL injection attack

· In May 2008, a server farm inside China used automated queries to Google’s search engine to identify SQL server websites which were vulnerable to the attack of an automated SQL injection tool.

· In 2008, at least April through August, a sweep of attacks began exploiting the SQL injection vulnerabilities of Microsoft’s IIS web server and SQL Server database server. The attack does not require guessing the name of a table or column, and corrupts all text columns in all tables in a single request. A HTML string that references a malware JavaScript file is appended to each value. When that database value is later displayed to a website visitor, the script attempts several approaches at gaining control over a visitor’s system. The number of exploited web pages is estimated at 500,000.

· On August 17, 2009, the United States Department of Justice charged an American citizen, Albert Gonzalez, and two unnamed Russians with the theft of 130 million credit card numbers using an SQL injection attack. Inreportedly “the biggest case of identity theft in American history”, the man stole cards from a number of corporate victims after researching their payment processing systems. Among the companies hit were credit card processor Heartland Payment Systems, convenience store chain 7 Eleven, and supermarket chain Hannaford Brothers.

Finally, the paper fully embraced open source technology (Linux operating system, Apache Web Application Server, MySQL Database Server and PHP Web Application Pre-processor) to demonstrate four-tier protection from SQLi attacks - using a blogging application specifically developed to demonstrate the points raised in the paper.

II. RELATED WORK

Many researchers have worked on web application security, using various tier-approaches, some of these approaches will be discussed in this section.

Two Tier Approach

[image:2.612.334.573.355.501.2]

In the 1980s, the arrival of inexpensive network-connected PCs produced the popular two-tier client-server architecture. In this architecture, there is an application running in the client machine which interacts with the server—most commonly, a database management system as illustrated in figure 2.1. Typically, the client application, also known as a fat client, contained some or all of the presentation logic (user interface), the application navigation, the business rules and the database access. Every time the business rules were modified, the client application had to be changed, tested and redistributed, even when the user interface remained intact.

Fig. 2.1 Two-tier Architecture[3] (Robert R. Perkoski, 2011)

Three-tier Architecture

(3)

International Journal of Emerging Technology and Advanced Engineering

662 Fig 2.2 Three-tier Architecture(Robert R. Perkoski, 2011)

Limitation of Previous Works

Before delving into the innovative four-tier approach adopted by this research, I will like to briefly examine prior research work in the research field:

1.In their research paper titled “A Classification of SQL Injection Attacks and Countermeasures” William G.J. Halfond, Jeremy Viegas, and Alessandro Orso of College of Computing, Georgia Institute of Technology focused their efforts on identifying and trying to resolve SQLiA via commercially available products, this implies a single vulnerability in any of the commercial products they recommended will equally compromise the database queries they are meant to protect.

2.A Security blog posting by Microsoft Corporation [4] focused on two-tiered security, namely the web application and a passing remark on ensuring the application database is properly restricted.

3.Diallo Abdoulaye Kindy and Al-Sakib Khan Pathan in their paper titled “A Detailed Survey on Various Aspects of SQL Injection: Vulnerabilities, Innovative Attacks, and Remedies”[6] focused on how Web Applications can be tested for vulnerabilities and how such vulnerabilities can be prevented at Web Application level. However, in the real world, programmers are always improving their application, therefore the chances of creating exploitable query will always be open.

III. METHODOLOGY

SQLi attacks relies on injecting unexpected SQL statements into data entry fields or as part of a URL (uniform resource locator eg. www.yahoo.com or www.google.com), this article on four-tier technique, which deals with sanitizing and neutering rogue SQL statements. The four-tier approach entails securing the following:

1.Operating System: This paper uses Linux operating system to demonstrate the first-tier of securing a web application. Linux is widely used by Google, Wikipedia, CERN (The European Organization for Nuclear Research), NASA and the US White House (amongst

others).[1]

2.Application WebServer: Apache’s popular Apache HTTP Server is adopted to demonstrate the second-tier of the web application security

3.Database: The third-tier of web application security is implemented using MySQL database. MySQL DB is higly popular amongst opensource developers

4.Server-Side Scripting Language: The final security tier is implement using PHP. PHP is a server-side scripting language designed for web development but also used as a general-purpose programming language.

In a nut-shell, this paper also implements security at the following layers:

1. System Layer 2. Web Server Layer 3. Database Layer 4. Application Layer

Furthermore, in order to demonstrate SQL Injection attacks and how to resolve them, this paper involves the development of a blogging web application and gradually implements the four-tier security layer on the application to demonstrate the effect of increasing security layer.

IV. IMPLEMENTATION

(4)

International Journal of Emerging Technology and Advanced Engineering

663 By introducing security at each tier of the web application interaction with the host system, we hope to nip the success rate of SQLiA and related attacks in the bud.

The First-Tier: Securing the Application-tier

The first tier in a web application worth protecting is the application itself. The general principle is simple, “don't

trust users of your web application”. In an idea world, web application users would supply expected data in the appropriate data entry fields, however, this is not always the case. For example, instead of supplying a valid user name and password, malicious users can inject SQL (Structured Query Language) code into the application entry fields, which when executed can compromise the security of both the web application and the server on which it is hosted.

The web application below demonstrates the effect of malicious manipulation of data entry fields with injected SQL codes.

Fig 4. 1 Demostration of the effect of malicious manipulation of data entry field with SQL Code

In the example PHP application above the user injected SQL code into the data entry fields for both Name and Password. The injected SQL code is shown below:

' or '1'='1' -- '

The code above when executed will force the database to return the login details for the first name in the list (see below):

Fig 4.2: Output of SQL Code injected in Fig. 4.1

[image:4.612.323.587.158.294.2]

An explanation of the source code and how to sanitize it follows:

Fig. 4.3: A Snapshot showing the Segment of the Source Code

Line 1 contains the php opening tag (<?php), this tag tells the php preprocessor that the file contains php codes which the preprocessor must process.

Lines 2, 3, 4 and 5 contain variable declaration, the following variables are declared:

1. The database host (in this case localhost) 2. The database user (root in this example)

3. The database password (rootpassowrd)

[image:4.612.333.585.373.584.2]

4. A variable to hold login success value, 0 connotes invalid login while 1 connotes successful login

Fig. 4.4: A Window demonstrating various commands through which the application connect to the MySQL server backend

Lines 7 to 15 contain the various commands which allow the application to connect to the MySQL server backend. Observe that line 7 made use of the variables we declared in line 2-5 and assigns the result of the mysql_connect

command to a new variable called $link.

(5)

International Journal of Emerging Technology and Advanced Engineering

[image:5.612.49.301.171.431.2]

664 Lines 14-15 attempts to connect to the sqlia database, no message is displayed if the connection is successful, else an error is displayed if the connection did not succeed.

Fig. 4.5: A window demonstrating Web Application Logic

Lines 17 to 42 above contains the bulk of the web application logic, line 17 determines if the application has successfully loaded by examining the value of the HTML form object called myaction. Form object myaction is initiated with a default value of „submitted‟, this implies the code block above will only work when the web application is loaded and ready to accept user data.

Line 19 determines if the user clicked on the Submit

button, the button is named buttonlogin and will only be set to true if it was clicked by the user. Clicking on the

Submit button will trigger the execution of the code block

from line 20 to 37.

Lines 20 and 21 stores the data collected from the user in two variables namely $duser (contains the login name) and

$dpass (which contains the login password).

Thereafter, line 24 constructs a database query which is passed for execution to MySQL. The result of the execution is captured in lines 27 to 36.

Line 27 attempts to execute the database query, if successful, line 29 attempts to fetch a record from the table mentioned in the database, if successful, it will set the variable called $loginsuccess to 1 in line 30.

Failure to retrieve a valid record will lead to the execution of the code block in lines 31 to 35 (which sets

$loginsuccess to 0 and displays a message telling the user

that username or password is invalid).

The remaining part of the code deals with displaying the login form or displaying the welcome message (please see appendix A for the complete application listing).

The key to sanitizing the first-tier of this application involves adding sanitizing codes after lines 20 and 21. These two lines naively trusted the user and hence did not sanitize the user input before passing them to the web application database. To resolve the problem, we simply rewrite the two lines thus:

Fig. 4.6: A window demonstrating codes used in sanitizing the First-tier

Lines 22 to 23 and lines 27-28 effectively take care of sanitizing user input, ensuring single quoted injected SQL codes are not executed. The result of running the application is shown below:

Fig. 4.7(a): Result of the sanitization of the first-tier

[image:5.612.323.583.287.427.2]

(6)

International Journal of Emerging Technology and Advanced Engineering

665 Fig. 4.7(b): Result of the sanitization of the first-tier

This implies that the user input was successfully sanitized, and the web application will no longer fall prey to common SQL injection attacks. Hence only valid users can access the web applications (as shown below):

Fig. 4.7.(c): Result of the sanitization of the first-tier

When user yinka logs in above with the right password, the message below will be displayed:

Fig. 4.7(d): Result of the sanitization of the first-tier

The Second-Tier: Securing the Database-tier

The next second tier of the methodology involves securing the database layer from other forms of injected SQL codes.

It is important not to allow your web application to access the database as the root user. User root has unlimited privileges to the application database. He can create users, drop (remove) tables and delete records. Hence if a web application is accessing a database as user root, the hacker will have access to the whole database as soon as he/she compromise the application.

Therefore, the first step to securing a database involves creating less privilege users per web application. Indeed complex web application may have two or more database users having varying degree of privilege to interact with the database.

The steps below create the database used in the first tier and assign the database to a less privilege user:

Open a command prompt and run MySQL client as the root user (commands are shown in bold italics, command output in normal text while expected user interactions are underlined)

mysql –uroot –p mysql (press the Enter key to continue)

Enter password: (type the password and press enter to continue)

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 363

Server version: 5.5.31-0ubuntu0.12.04.1 (Ubuntu)

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database sqlia; (press Enter key to continue)

Query OK, 1 row affected (0.04 sec)

mysql> CREATE TABLE `sqlia`.`register` (`username` VARCHAR(10) NOT NULL, `password` VARCHAR(45) NOT NULL, `balance` double(32,2) NOT NULL) ENGINE = MyISAM DEFAULT CHARACTER SET =

latin1; (press Enter key to continue)

Query OK, 0 rows affected (0.10 sec)

mysql> grant select on sqlia.* to 'sqliauser'@'localhost'

(7)

International Journal of Emerging Technology and Advanced Engineering

666 Query OK, 0 rows affected (0.08 sec)

mysql> flush privileges; (press Enter key to continue)

Query OK, 0 rows affected (0.03 sec)

Explanation

The command line: create database sqlia; is responsible for creating the database.

While the command line CREATE TABLE

`sqlia`.`register` (`username` VARCHAR(10) NOT

NULL, `password` VARCHAR(45) NOT NULL, `balance` double(32,2) NOT NULL) ENGINE = MyISAM

DEFAULT CHARACTER SET = latin1; creates a table

call register in the sqlia database.

The next set of SQL commands (grant select on sqlia.*

to 'sqliauser'@'localhost' identified by 'tun421X';) creates

a user call sqliauser and grant him the right to select records stored in any sqlia table.

The final command line (flush privileges;) flushes the privileges table, this is necessary if we want the user and his privileges to be activated without restarting the MySQL database or restarting the system.

In the example above, we granted the user the privilege to SELECT records from a database, we deliberately refrain from granting the user the following privileges UPDATE, DELETE, DROP and INSERT, depending on the web application requirements, these privileges can be assigned to other database users.

[image:7.612.48.300.505.676.2]

The application developed in the first tier can be rewritten to use the new user (since it is bad practice to allow root to access MySQL database from a web application).

Fig. 4.8: A Snapshot showing a segment of the source code

The Third-Tier: Securing the Web Server-tier

Our web application is served via Apache HTTP Server; hence this layer is worth protecting. A secured web application and database server could be compromised by an unsecured Apache HTTP Server.

So far, we have developed three versions of a user authentication web application which we execute by calling each application’s url (universal resource locator).

The web application files are stored within the sqlia

folder or directory, which resides within the Apache HTTP Server root document directory. Hence calling each application is as simple as specifying both the domain name (in this case localhost), the application folder (sqlia) and the name of the web application file (either login.php

or login_ver2.php or login_ver3.php)

Suppose a user decides to enter localhost/sqlia into the address bar of the browser, what will he or she sees? The user will be presented with a list showing all web application files. This will enable the user to simply copy the source code, where he or she can figure out the database credentials.

Fig. 4.9: A snaphot showing a list of web application files

So how do we go about securing this security hole?

1.Locate the Apache HTTP Server configuration file using the command below (on Ubuntu Linux version 12.04):

locate 000-default (press Enter key to continue)

/etc/apache2/apache2.conf

/etc/apache2/apache2.conf~

(8)

International Journal of Emerging Technology and Advanced Engineering

667 2. The first file in the list above is the one we need to

edit, hence type the command below to continue

sudo nano /etc/apache2/sites-enabled/000-default

(press Enter key to continue)

3. The command above will load the default apache website configuration file for editing using the nano text editor. Search for the line containing Options

Indexes FollowSymLinks MultiViews and remove

or delete Indexes from the line.

4. Save the file and exit the editor (press Ctrl+O to save and Ctrl+X to exit)

5. Next type sudo service apache2 restart to activate the new security features

6. Finally try visiting localhost/sqlia from your browser to see if the security was implemented (it should display the page below):

Fig. 4.10: This Demonstrates the output of a secured Apache Server

The Fourth Tier: Securing the Operating System

The final tier of our four-tier model worth protecting is the Operating System. While Linux operating system is inherently secure, it is important to avoid some pitfalls while installing new applications.

Most Linux users who decide to install Apache or MySQL from source files end up installing the application as root! It is a terrible thing to install an application like Apache HTTP server as root. Why? Once the system is compromised, the hacker who takes over the system can remotely create and run any script which can further compromise the system. This is possible only when Apache HTTP server is installed as user root (who has 100% privilege over the entire system).

Therefore, the golden rule of thumb is to create users and groups with low level privileges and assign such users the right to run both MySQL and Apache HTTP Servers.

Finally, it is important to cross check the content of /etc/passwd file to ensure less privilege users cannot use the system shell.

Follow the steps below to cross check the content of /etc/passwd file (on Ubuntu 12.04 Linux)

1.Type sudo nano /etc/passwd (press Enter key to continue).

2.On Ubuntu, the low privilege user who owns Apache HTTP server process is called www-data, we simply search the file for this user and change the user’s ability to use the bash shell to false.

www-data:x:33:33:www-data:/var/www:/bin/sh

Change the above to:

www-data:x:33:33:www-data:/var/www:/bin/false

3.Repeat the same process for user mysql

4.Save and exit nano text editor

5.Restart Apache HTTP server and MySQL database server to effect the changes

Type sudo service apache2 restart to restart Apache HTTP Server and

sudo service mysql restart to restart MySQL Server

V. CONCLUSION

This paper have been able to study the various web application security vulnerabilities, looked into the rate of attack over years and their profound limitation in securing the web application, and have been able to affirm that the Structured Query Language Injection Attack commonly referred to as SQLiA have been a bug affecting almost all the sites on the globe, it has ended up in putting large organizations into challenges that led to the fall of the companies as discussed in this paper.

(9)

International Journal of Emerging Technology and Advanced Engineering

668 REFERENCES

[1] (http://blog.imperva.com/2011/09/sql-injection-by-the-numbers.html)

[2] en.wikipedia.org/wiki/SQL_injection#Examples):

[3] Robert R. Perkoski. "Introduction to Web Development". Retrieved 2011-09-19

[4] (http://blogs.technet.com/b/srd/archive/2008/05/29/sql-injection-attack.aspx, May 24, 2013)

[5] Kindy, D. A., Pathan, A. K. “A Detailed Survey on Various Aspects of SQL Injection: Vulnerabilities, Innovative Attacks, and Remedies”

[6] Arokia(2011) "3-Tier Web Architecture". http://www.arokait.com/3-tire-web-architecture.htm. Retrieved 2011-09-19.

[7] Arthur, Charles (2010). "Twitter users including Sarah Brown hit by malicious hacker attack" (http:/ / www. guardian. co. uk/technology/ blog/ 2010/ sep/ 21/ twitter-bug-malicious-exploit-xss). The Guardian. . Retrieved September 21, 2010.