SQL Injection – The Complete Overview

This page is intentionally left blank.

0|Page

SQL Injection – The Complete Overview

SQL injection is the most popular technique used by hackers to get down a website. This material will hold some information to understand this attack and defend yourselves from being hacked! It will also be an exhaustive introduction for beginners. People reading this material are expected to have some basic knowledge over computers since this could be a little quick walk through. In this tutorial we will learn different methods to exploit SQL injection vulnerabilities with a live example. We are going to attack a web page and also going to discuss a set of tools to work with which would automate our tasks. Firstly I’ll talk about how to identify a SQL injection (from now I will be using the abbreviation SQLI) vulnerable link then we are going to take a look on how to find these vulnerable links further, we’ll make this automated using a tool. Once we have found a SQLI vulnerable site then we are going look at how attackers inject malicious SQL queries to retrieve the sensitive information. Once we are done with discussing the attack process, we will get a little deep into the coding part. We will take a look at how the coding is generally done and I’ll introduce you what is like bad programming. Then I will show you to program in PHP from the very basics and we’ll move to database connection. Finally, we will see how secure programming is done. SQL injection is technique used by hackers to gain the confidential data from the database. If SQL injection vulnerability exists in your database, it can compromise your restricted information in the hands of an attacker. Generally, in a database driven webpage (dynamic webpage) the user cannot communicate with the database (MySQL in this case) directly from the web browser. All those interactivity done on the webpage is handled by web server 1|Page

SQL Injection – The Complete Overview

(here, I am using Apache server running PHP on a Windows machine, that is, WAMP). So in our case, PHP is going to take our request and process that accordingly. If a user requests for some information that is stored in the database, PHP analyses the request then connects to the MySQL database. Once the MySQL responds back to PHP, it is extracted (from the array) and displayed in simple HTML format. Therefore, the user is not able view the PHP code neither he/she is capable of executing SQL commands from the browser. But, if vulnerability such as SQL injection exists on that webpage, then the attacker might be able to do that! When the user queries are not filtered properly or not handled appropriately, like the user input string is directly passed into SQL statement then this may leave your entire database compromised. This tutorial will help you understanding this vulnerability and debugging it. If you are not able to understand what I’m trying to convey then its fine for now, you may gradually understand when you move further reading this tutorial. In this tutorial, I will inject some SQL queries into the vulnerable link and help you understand about how this technique works. Although there are many automated tools that can automate this technique, manually injecting the SQL commands gives a good understanding on how it really happens. Even if manual injection takes time and effort, don’t always depend on automated tools, at least till you understand it perfectly. This tutorial is divided into two major sections namely, Attack demo and PHP/MySQL. The first section will deal with the demonstration on how the SQLI is done and in the second we will explore the PHP and MySQL programming. This is not an advanced guide to SQLI but will be a good start for the beginners. Note: Hacking is illegal and may not be misused. This demonstration is for educational purposes only. Any harm caused to the website is at your own risk!

2|Page

SQL Injection – The Complete Overview

In this demonstration we will be testing a website, http://shapes.aimatshape.net. After scanning the website using a web vulnerability scanner, I’ve found a URL that is vulnerable to SQL injection and that is,

http://shapes.aimatshape.net/view.php?id=783 To check if the URL is vulnerable or not, just add a “ ‘ “ (single quote) at the end of the URL and look whether an error is thrown or not! So, what we do now is we modify the URL to

http://shapes.aimatshape.net/view.php?id=783’ As you could see, there is an error thrown on your screen and it reads out to be, You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\' LIMIT 1' at line 1. Take a look at the error:

You will notice that this text is similar to the text that is displayed when you have some syntactical mistake in your MySQL command while you are working with your MySQL database in console.

3|Page

SQL Injection – The Complete Overview

So, let us do a quick ‘order by’ to know the number of columns present in the database. Here, you have to use hit and trial method to figure out the number of columns. The number above which you start getting the error denotes the number of columns.

http://shapes.aimatshape.net/view.php?id=783 order by 204-In the query above you could see that it ends with “--“, that typically is to comment out the rest of the SQL query so that the web server only considers the injected query and there by ignoring the rest. Let us take a quick look at how it looks,

This means, there is no 204th column in the table. Now let’s try a number less than 204 that is 203. You’ll notice that the page is opened properly without throwing any error. So there are 203 columns in the database. To view the affected columns we insert the UNION ALL SELECT command.

http://shapes.aimatshape.net/view.php?id=783 UNION ALL SELECT 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20, ..., 203-Note: Do not copy and paste the URL above directly, you have to mention all the column numbers up to 203. Here, I’m skipping the numbers to avoid the unnecessary wastage of time.

The vulnerable column numbers will be popped out, as in, the column numbers will be displayed on the webpage. You just have to note down the vulnerable columns so that you can use them while extracting the sensitive data. Note: In this tutorial we are not going to attack the database to gain the sensitive information but we will look at the how attacker does that by taking an example! Here, we assume that column 2 and 19 are the vulnerable columns (though may not be). Next, we will extract some information related to database like version and database name. So we do some modifications in the above URL. Since the 2nd and 19th columns are vulnerable, we replace those particular column numbers in the URL with the SQL commands, 4|Page

SQL Injection – The Complete Overview

version() to find the MySQL version and database() to find the name of the database. It will look something like this.

http://shapes.aimatshape.net/view.php?id=783 UNION ALL SELECT 1,version(),3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,database(),20, ..., 203-An attacker would note the database name and the MySQL version then use a sophisticated method to further inject the formulated code depending on the version and name. Further the attacker would inject some malicious SQL commands in the vulnerable column number’s area and would extract the sensitive data. As such is already done in the above case by replacing the vulnerable column numbers with the SQL query. Generally, the MySQL versions 5.0 and above can be exploited by SQL injection whereas the earlier versions are vulnerable to blind SQL injection attacks. This type of manual injection can be done easily over the versions 5.0 and above. The blind injection is quite complex and needs some sort of guessing to exploit and retrieve the data within the database. Coming back to our attack, now if you want to get the list of tables in the database that corresponds to this website then you can insert the following SQL query into URL. And it may look similar to this:

http://shapes.aimatshape.net/view.php?id=783 UNION ALL SELECT 1,GROUP_CONCAT(table_name) ,3,4,5,6,7,8,9,10,11,12,13,14,15, ..., 203 FROM information_schema.tables WHERE table_schema=database()-The above query will extract the list of the tables in the database that corresponds to the website. Further you can note the table names of the tables whose data you want to have access to. Similarly, you can extract the list of columns in a particular table using an injected query as shown below:

http://shapes.aimatshape.net/view.php?id=783 UNION ALL SELECT 1,GROUP_CONCAT(column_name) ,3,4,5,6,7,8,9,10,11,12,13,14,15, ..., 203 FROM information_schema.columns WHERE table_schema=database()-An attacker can use a tool such as SQLI - Helper to automate this attack. This tool is available on various file sharing sites (Torrents), you can download it and start using. From an attacker point of view, these types of tools make the complex task of attacks to be dealt with an ease. There are various tools on which you can rely on, to automate the SQL injection attack mechanism. 5|Page

SQL Injection – The Complete Overview

Here is the snapshot of the tool trying to inject into the above URL.

As you could notice that the tool itself figured out the number of columns and started injection. There are other tools such as SQL Poizon and Havij which automate our tasks of find and attacking SQL vulnerable websites. This is the end of the first section, in the next section we are going to understand some basic PHP/MySQL coding.

6|Page

SQL Injection – The Complete Overview

SQL reads out to be Structured Query Language. When we store or retrieve data in a database, the data is provided in a particular way so that it makes easy for the database to store by relating by one another. The language we use to communicate with the database is SQL or generally known as SQL commands. MySQL is a database application that stores data those are related which in turn form a collection of similar data. In such application you can have many databases and can hold tables, and further, tables contain columns and rows. Now, let’s have a quick look at PHP. PHP stands for Hypertext Pre-Processor. Now, you might be wondering how is that supposed to make the abbreviation, well, earlier it used to be Personal Home Page. It’s a scripting language which we’ll be using to connect with MySQL database to generate a dynamic web page. We all know that in a dynamic webpage the information that is provided on the page or taken from the user is retrieved or stored in a database and this can be achieved by connecting your web page with your database. So that’s the reason why we use PHP in a dynamic page. The PHP code is responsible for the dynamic content of the webpage. If a PHP code is embedded in a HTML webpage, it is readable only to the webserver whereas the user can only see the HTML code that is echoed by the PHP. I assume that you have some understanding about variables, data types, control statements and functions in C or some other similar language that will help you grasp the concept. If you don’t have any prior knowledge then you can refer to some PHP programming material that I have suggested at the Webpages section of this e-book.

7|Page

SQL Injection – The Complete Overview

There isn’t a much difference between C and PHP syntax. Well, in PHP, you don’t have to declare any variables with their data types. Since it’s a scripting language you don’t have to compile it. The variables in PHP are preceded with a ‘$’ symbol. In PHP, ‘.’ (Dot) operator can be used for concatenation of two strings. You will understand this better with this example,

The ‘’ indicates the end of the PHP script. As you can see we did not declare the variables $string1 and $string2 with any data types, here, when we assign a variable with some text or number

with single or double quotes around it, its data type is assumed to be string. The variable $final_string is assigned with a concatenated string, i.e. it contains ‘Hello World!’ as the

string. Now in the next statement we print the string that is in the $final_string variable. We will understand some predefined functions in the next section. Now let us move on to PHP and MySQL connection. Firstly we’ll be taking a look on the working code that will help you grasp the concept better. Here is the PHP code for connecting to database: 8|Page

SQL Injection – The Complete Overview

In the above code, mysqli_connect($dbhost, $dbuser, $dbpass) is a predefined function in PHP used to connect with database. As we can see, it takes three arguments ($dbhost, $dbuser, $dbpass) where the first argument is host address (it will be localhost only if you are running a server on your local system), second argument is the username (root is the default username) that you use to login into your MySQL and $dbpass is the password for your root account (by default its null). This function returns the connection resource. mysqli_select_db($conn, $db) is another predefined function used to select the database. It takes two arguments, one is the connection resource, that is the returned by mysqli_connect() function and the second one is variable holding database name. The die() function tells the interpreter to end the process by displaying the string that is provided as the argument. So now let us get to the main part of this article. We are going to create a basic HTML form where we are using ‘GET’ method to submit the form details to an action URL. The action attribute is set to a PHP page where the form details are retrieved and then stored in variables and finally we use it to extract the relevant information from the database. We will be having a ‘search box’ in the HTML page with a ‘submit button’, when the user clicks the submit button the information related to that keyword will be echoed by the PHP page that is given in the action attribute. Below is the HTML code to take the input:

The title of the HTML page is “SQL injection demo” (enclosed within HTML title tag) which is the part of head section in the above code. The first input tag, “” is the text field asking for the keyword given by user and the other input tag “” is the Submit button. Later, when the Submit button is clicked the data from the form is sent to the action URL, which is in this case is submit.php and processed accordingly. Note: This tutorial assumes that you have some prerequisite knowledge to understand working of HTML and webpages.

9|Page

SQL Injection – The Complete Overview

Here is the SQL injection vulnerable code to retrieve the search keyword from the form and extract the details from table (here, table_name is the table): /* include statements and validation code... */ $search_keyword = $_GET['keyword']; $sql_query = "SELECT * FROM table_name WHERE some_column LIKE \"%$search_keyword%\";"; $query = mysqli_query(dbConnect('database_name'),$sql_query); // dbConnect is a user defined function. if(mysqli_num_rows($query) == 0) { echo 'No results found'; } else { // extract the output using a while loop // and echo the HTML code! }

So, let us first analyse what happens when we put these pages on the server. Here, we are retrieving the keyword and assigning it to the $search_keyword variable, then we are inserting it directly into the SQL query by concatenating it to the query string. Then we pass the query string into mysqli_query() function to executed the MySQL query. It takes two arguments, one is the connection resource, that is the returned by mysqli_connect() function in the dbConnect() function and the second one is query string. dbConnect()is a user defined function that connects to the MySQL server, selects the database and returns the connection resource. Further, we are checking whether there are some matching entries related to the keyword or not and if matched then it will generate the HTML code or else it will print “No results found”.

So now, the question is why does the SQL injection vulnerability occurred…??? Well, the answer is very simple, that is because we are inserting the user’s string into SQL query directly without validating it! If the user’s input contain some characters those found in SQL syntax then a SQL command can easily be misinterpreted and then any data can be retrieved by modifying query. So, now you might ask, what should we do to remove this vulnerability? We need to add a filter that enters an escape character before any keyword or character that corresponds to MySQL query syntax. So, how do we do it now? PHP provides a predefined 10 | P a g e

SQL Injection – The Complete Overview

function that is used for filtering the user input strings before passing it to the mysqli_query() function. mysql_real_escape_string() is the function used for

filtering the query string. Now, we can rewrite the above vulnerable code in this way, /* include statements and validation code... */ $search_keyword = mysql_real_escape_string($_GET['keyword']); $sql_query = "SELECT * FROM table_name WHERE some_column LIKE \"%$search_keyword%\";"; $query = mysqli_query(dbConnect('database_name'),$sql_query); // dbConnect is a user defined function. if(mysqli_num_rows($query) == 0) { echo 'No results found'; } else { // extract the output using a while loop // and echo the HTML code! }

The above code is the modified version which is to resist the SQL injection attacks to some extinct. This code can be further debugged by replacing mysql_real_escape_string() function by user defined function that is made to place escape character preceding to every special character. Therefore, making the code much secure and trust worthy. Remember, no code can be said to be perfectly debugged, there is always a bug unknown to the programmer!

11 | P a g e

SQL Injection – The Complete Overview

SQL injection is a hacking technique used by attackers to gain sensitive information by attacking the web application with the help of the malicious SQL commands. These SQL commands or statements are inserted into an entry field for execution. SQL injection is the most widely used technique by hackers to exploit web applications. Approximately 16% of websites are vulnerable to SQL injection attack on World Wide Web (WWW). In our “Attack demo” section we have seen how to exploit the SQLI vulnerability manually and also taken a look over how the automated tools such as SQLI Helper are helpful to ease our tasks. Generally, this type of vulnerability is occurred when the user input it not filtered properly. The developer while constructing the code for his web application must take proper care of user provided data. It must be filtered in a way that the special characters are treated as common text rather as a SQL statement or command. This can be achieved by use of appropriate encoding or filtering it by inserting escape characters before the special characters. Most of the websites over the WWW are build using PHP and MySQL as a backend because there are open source and efficient enough. There by, even I’ve chosen a WAMP environment to demonstrate the common coding mistakes that lead to SQL injection vulnerabilities. It is always highly advisable to use the latest version of the software to avoid bugs that would help an attacker to take advantage.

12 | P a g e

SQL Injection – The Complete Overview

Vijay Kumar is a blogger and a student holding his interest in IT Security and Ethical Hacking. He is currently pursuing his Bachelors Technology degree in Computer Science. He is also a part time web developer who loves to tweak the code. He also likes to blog about his journey towards a successful career in IT Security field where he writes about his current working subject or topic. He is a Python programming and likes to build security tools. He loves to pen-test Windows machines and prefers working on UNIX based systems. Personal blog: http://kumonit.tumblr.com

Webpages: Basic Hacking Skills http://basichackingskills.wordpress.com. Join the professional hacker hub and start learning the Information Security basics that will help you grow as a hacker. This blog will provide you a bunch of quality articles related to IT security written by Vijay Kumar and his team. Tutorials Point http://www.tutorialspoint.com This website provides an excellent set of material to many programming languages such as JavaScript, PHP, MySQL, Perl and what not. You can download the e-books and start learning today!

13 | P a g e