Almost 70% of the websites built today is vulnerable to SQL injection. That’s a true fact and if you know how to deface a website using SQL injection, then you will surely realize the fact.

Attention: This article only helps you to protect your site against SQL injection if you are a site owner or to know about the vulnerabilities in the web if you are a learner. Please, do not misuse this vulnerability against an individual, organisation or some other network without prior consent from them.

I am demonstrating SQL injection examples with PHP and MySQL. But, they are applicable to almost any sort of programming languages and are not restricted to these.

This article consists of the following topics

  1. Prerequisites for SQL injection
  2. Understanding SQL injection
  3. Using sqlmap tool
  4. Prevention of SQL injection in PHP

1. Prerequisites for SQL injection

First of all, this article about SQL injection needs you to be aware of some programming concepts in Web development. You should have some knowledge in PHP, MySQL and database connectivity. If you don’t have any of these prior knowledge, google it and there are always some great tutorials on the internet.

We should do some setup before we dive into experiments. I have installed XAMPP in my system and I’ve an index.php page in the htdocs folder. And it contains the following code:

<?php
// connect to database
$con = new mysqli("localhost", "root", "", "test");
if($con->connect_errno)
	die("Database connection failed");
// store the query parameter to a local variable
$id = $_GET["id"];
//querying the database with the id parameter and retrieve as array
$result = $con->query("select name, email from user where id=$id")->fetch_array();
//printing the name of the corresponding user
echo $result['name'];
?>
I have a database named test with the following structure
SQL injection database schema
Database test schema
And the output of the index.php with url http://localhost/index.php?id=1 is
SQL injection index page
username of user with id=1
The above result is fetched from a row in the table user that have the following data:

id: 1
name: dolly
password: password
email: [email protected]

2. Understanding SQL injection

Well, so finally we're into the tutorial. So, what is SQL injection? It is a technique where you can inject your malicious code into the url or cookie or with some other parameters to get the information about the database schema or to inject a file into the site's root directory or to even shutdown the system.

Please note that the following tutorials are only meant for beginners for easy understanding. There are many categories and types of SQL injection that can be done. For more information, see Wikipedia's article about SQL injection</div>

So, to do SQL injection we must first identify whether the site is vulnerable to SQL injection. You can find that by the simple way of appending ' to the end of the url that contains the get parameter. Considering our example, if I put the URL as http://localhost/index.php?id=1' then I will get the output as

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/index.php on line 7

As you can see it, if it reports the error like this then the site is vulnerable to SQL injection. Next we're going to discover the information about databases, tables and MySQL version using SQL injection

3.Using sqlmap tool

You can inject the code using the URL but, that will look a little bit tougher if you are a beginner. sqlmap is an opensource freeware that helps you to test and inject websites. Download the zip file and extract it to your computer.

Keep in mind that you will need Python to run sqlmap. You can use sqlmap on both Windows and *nix platforms.

After extracting sqlmap and installing python, in the command line browse to your sqlmap folder and then type the following command:

python sqlmap.py

python is not recognized as internal or external command

If you encounter the above error then you have not set your PATH variable or you have set that wrong. Please check this link on setting up PATH in Windows. After testing that sqlmap works as expected, its time to discover the databases in the target system. Use the command below to discover the databases.
python sqlmap.py -u http://localhost/index.php?id=1 --dbs
The -u flag stands for URL of the target. Enter python sqlmap.py -h to see the manual of using sqlmap. After entering the command, it discovers the databases in my localhost as shown in the image below:
SQL injection sqlmap database discovery
sqlmap database discovery
You can use some other flags in sqlmap to do some specified tasks. It is well documented in the table below:
Flag Description
--users Reveals all the users in the system
--passwords Reveals all the passwords of the users in the system
--tor Use tor as a proxy to hack into the system. Use --tor-type=SOCKS5 if you're using tor in SOCKS5 protocol.
--sql-shell Brings up SQL shell to you for manipulating SQL queries
--os-shell Brings up OS shell that will allow you to shutdown, read or write files, start or stop a service, etc.
--sql_query query Runs the query specified by the user.
--dump Dumps the specified database in csv format.

There are much more flags you can use with sqlmap. Read the man page for further hacks

Prevention of SQL injection in PHP

So, how can we protect ourselves from this type of SQL injection? The answer is quite simple. We can use prepared statements in PHP by escaping the characters in SQL query before we query it in the database. Using PDO, may be a little complex, but if you play around, it will be easy for you.

The index.php can be modified as follows:

<?php
	$con = new mysqli("localhost", "root", "", "test");
	if($con->connect_errno)
		die("Database connection failed");
	$id = $_GET["id"];
	$modified_id = $con->escape_string($id);	//escaping and storing $_GET["id"] in a variable
	$statment = $con->prepare("select name from user where id=?");	//using prepared statements. Note the '?' mark.
	$statment->bind_param('i',$modified_id);	//bind the variable to the ? mark. First paramter 'i' stands for integer and second one is the variable.
	$statment->execute();	//executing the query
	$result = $statment->get_result()->fetch_array(); //getting the result and storing it in array
	echo $result['name'];	//echoing out
?>

The first thing you should do is to replace all the input variables in the query using a ? mark. Then bind the value to the ?, by using bind_param() function which takes two arguments. The first one is the datatype - 'i' for integer, 's' for string, 'd' for double, etc. and the second parameter is the unsafe variable. Then you can execute and get the result and then use it according to your needs. Now, you can test with sqlmap for vulnerabilities in your code.

If there are any changes to be made please comment below