What is SQL Injection?

SQL Injection is the process when someone executes one or more SQL statements in your database without your knowledge to harm the data in your database. In this technique, someone exploits a security vulnerability in database application layer. This happens often when you ask for input(s) from the user and they add extra SQL statement(s) with the valid input. We can illustrate this situation with two cases (1) user inputs are incorrectly filtered or (2) user submitted data is not strongly typed and one ore more of the inputs is executed unexpectedly.

This is possible because the user can submit any input and you may have no or weak user submitted input filtering coding, you can execute one more statements in your database at a time.
For example,
Case 1:

Executing one statement at a time is OK.

<?php
	$sqlStatement = "SELECT * FROM customers where username='james';";
?>

Case 2:

Executing more than one statement at a time is also OK.

<?php
	$sqlStatement = "DROP TABLE users; UPDATE customers SET age=0; DELETE FROM customers where id>0;";
?>

Now an intruder can combine Case 1 and Case 2, run all the queries at a time and then delete the users table and harmfully update the customers table.

As a result, a successful SQL Injection occurs and harms the database information.

Reasons behind SQL Injection

In the following situations, SQL Injection happens.

• Incorrectly filtered escape characters
• Incorrect type handling
• Vulnerabilities inside the database server
• Conditional Errors

Case 1: Incorrectly filtered escape characters

You have a module that asks for user’s email address to send a temporary password to her email address when she forgets her password. So, the usual SQL query will be like this in this case:

<?php
	$sqlStatement = "SELECT * FROM users WHERE username = '" + $username + "' AND email = '" + $email + "' ";
?>

But an intruder can extent this statement if they set the value for the $email variable in this way and delete the user table:

user@hostname.com’; DROP TABLE users; SELECT * FROM customers WHERE name LIKE ‘%

As a result, the final statement is something like:

<?php
	$sqlStatement = "SELECT * FROM users WHERE username = 'james' AND email = 'user@hostname.com'; DROP TABLE users; SELECT * FROM customers WHERE name LIKE '%'";
?>

So, you see the intruder is deleting the users table easily. And as a result, you lose your users table and your system crashes since no user will be able to log in from now on. If you do not have a database backup, you loose everything.

Case 2: Incorrect type handling

Sometimes you definitely know the type of data. For example, the age of a customer is a numeric value, gender of a user as male or female, total amount of bill as double value.

<?php
	$sqlStatement = "SELECT * FROM customers WHERE age = " + $ageValue + ";";
?>

Now what if someone submits the $ageValue value as 20; DROP TABLE users

The resulting sql is:

<?php
	$sqlStatement = "SELECT * FROM customers WHERE age = 20; DROP TABLE users;";
?>

You know for sure that the value of will be always an integer. If you do not check if the value is really an integer, the intruder can add one or more statements with the value of $ageValue variable and harm your database.

Case 3: Vulnerabilities inside the database server

Although some people think that they can avoid SQL Injection just by using mysql_real_escape_string() function in PHP, they are wrong unfortunately. Built-in functions supplied with language package to work on database is sometimes vulnerable to database and thus are not successful to avoid the attack all the times.

Case 4: Conditional Responses

Using SQL Injection the user can easily bypass the sign in or log in process in your system. Let us give an example:

<?php
	$sqlStatement = "SELECT * FROM users WHERE username = 'james' AND password = 'secret' OR 1=1;";
?>

Now you know satisfying only one condition is enough to enter the system. The condition OR 1=1 is always true. Thus, the intruder can fool the log in system.

SQL Injection Prevention and/or Protection

These are the techniques for preventing SQL Injection:

1. Use Parameterized Query
2. Use Stored Procedure whenever applicable
3. Apply Regular expression to discard invalid inputs
4. Write and use Quote blocking  function
5. Hide detailed error messages from the user
6. Create a database user with less privileged role
7. Set the limitation for maximum value in your HTML form

1. Use Parameterized Query

Rather than directly supplying the values in the SQL statement, let us supply the values in parameterized way as follows:

<?php
	$db_connection = new mysqli("localhost", "user", "pass", "db");
	$statement = $db_connection->prepare("SELECT * FROM customers WHERE id = ?");
	$statement->bind_param("i", $id);
	$statement->execute();
?>

“i” stands for integer type
“d” stands for double type
“s” stands for string type
“b” stands for a blob and will be send in packets

2. Use Stored Procedure whenever applicable

Using stored procedures can help your to reduce the attack risk too.

<?php
	$sqlStatement = "
		CREATE PROCEDURE HUGEORDER
		(
			id INT ,
			quantity INT,
			price DECIMAL(6,2)
		)
		BEGIN
			DECLARE discount_percent DECIMAL(6,2);
			DECLARE discounted_price DECIMAL(6,2);
			SET discount_percent  =  10;
			SET discounted_price = price – discount_percent/100*price;
			IF quantity > 500 THEN
				SET discounted_price = discounted_price - 0.25 * quantity;
			END  IF;
			UPDATE fashion_products
			SET product_price = discounted_price WHERE product_id = id;
			Select * from fashion_products;
		END;
	";
?>

3. Apply Regular expression to discard invalid inputs

Regular expression is very powerful process to find out the validity of the inputs. We can check whether the input is given in proper format. For example, here we validate data for a valid numeric value for customer age and reject any chance for SQL Injection.

<?php
	if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email))
	{
		echo 'INVALID Email Address!';
		return;
	}
?>

You can also user the built in PHP is_array(), is_bool(), is_double(), is_float(), is_int(), is_integer(), is_integer() etc functions to check if the user provided information is in proper format.

4. Write and use Quote blocking  function

If you are using PHP, mysql_real_escape_string function for each and every user given inputs. For example,

<?php
	$username = mysql_real_escape_string($username, $ dbLink);
?>

This is a very powerful built-in PHP function and will stop SQL Injection in most of the cases. I have used it for long time and found it performing great. You can try to inject SQL after you use the mysql_real_escape_string function and test if you can succeed any way. This powerful function rejects the possibility of many clever techniques used by the intruders.

5. Hide detailed error messages from the user

First of all avoid using the built-in MySQL mysql_error() function. The clever intruder can guess many things from the error message and sometimes the error message may show the connection parameters. Using mysql_error() function is good at development stage. But avoid or clean it when you run it in the real server for users or visitors.

The second thing is stop error reporting in PHP. This is simple and one line code.

<?php
	// Turn off all error reporting
	error_reporting(0);
?>

The third thing is better you give a customized error message. See an example:

<?php
	if(!mysql_query($statement))
	{
		echo 'We are sorry BUT The server is not responding. Please try again later.';
	}
?>

As a result, the user will not know what the error is and how it is. He will also not get any accidentally disclosed crucial information such as database name, table name, username etc.

6. Create a database user with less privileged role

In most cases, you will notice that the visitors do not need to delete or update any information. Think of a music selling site. The user can request for data (which is SELECT query) and make their orders (which are INSERT query). Even sometimes the SELECT operation is just OK in many sites.

So, create different users with different privileges. For admin grant all the permissions. But for a general visitor, grant only limited permission. For example, I am creating, two connection string for two users.

<?php
	$visitorDbLink = mysql_connect('host', 'general_user', 'general_user_pass');
	$visitorDbLink = mysql_connect('host', 'admin_user', 'admin_pass');
?>

And now we can user the $visitorDbLink link for regulating information connecting with the visitors and will use the $visitorDbLink link only for the administrator.

7. Set the limitation for maximum value in your HTML form

We can set that the username cannot be more than 10 chars. Try to use the “maxlength” property for HTML form. An example is like this:

	<input name="username" type="text" id="username" maxlength="10" />

As a result, possibility of attack by intruder somewhat goes down since he cannot input enough harmful SQL statement input. But it is not very helpful though and the user can bypass this creating his own form and submitting the custom form.

8. Using a PHP Framework

If you are using a PHP framework like CodeIgniter or CakePHP, the framework will protect you at maximum level from SQL injection. Complain of SQL Injection after using a PHP framework is rare and I did not hear even one still now.

This kind of frameworks are Open Source, very matured and strong nowadays and they do each and everything possible such as from input filtering to URL rewrite. Learning them is easy although it take some time. You can get books and other online materials about this PHP frameworks.

If you are an advanced PHP coder or developer, my suggestion is you better code using a PHP framework you like.

These are all I know about SQL Injection.

Thanks for reading.

Hey Guys,

I am a Freelance Web Developer and my main tools are PHP & MySQL. Few days ago, I got a Chinese project where I had to develop a Real Estate site in Chinese language. You know we often build websites in English and Databases are in English too. So, the default configuration in MySQL works fine everytime.

But when it comes a language other than English, many people do not know what to do. Well. When I started the project, I did not even know that the default MySQL settings will not work for the Chinese language. So, I started searching for a stable solution where my program will support any language for adding, updating and searching data from the MySQL database.

And Yeah.
I found it!

OK.

Let us see the solution now.
It is very very simple.

Step One: SET THE CHARSET TO UTF-8 IN THE HEAD SECTION

First of all, the browser needs to know that you are going to display or use Unicode in this page. So, go to your <HEAD></HEAD> section and set the charset to utf-8. So, the browser will be able to show the Unicode text without any error and smoothly. You can also copy and paste the line below:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Step Two: CREATING THE DATABASE

When you create your (a) Database and (b) any Table in the database, set the Collation of both of them to utf8_unicode_ci and you know it is very easy if you are using phpMyAdmin.

Step Three: DATABASE INITIALIZATION

When you initialize the database connection, please add the “extra lines”

<?php
	define('HOSTNAME', 'localhost');
	define('USERNAME', 'database_user_name');
	define('PASSWORD', 'database_password');
	define('DATABASE', 'database_name');
	$dbLink = mysql_connect(HOSTNAME, USERNAME, PASSWORD);
	mysql_query("SET character_set_results=utf8", $dbLink);
	mb_language('uni');
	mb_internal_encoding('UTF-8');
	mysql_select_db(DATABASE, $dbLink);
	mysql_query("set names 'utf8'",$dbLink);
?>

But why are you adding the extra lines? Because you are letting the database know what kind of input you are going to work with soon.

Step Four: INSERTING INPUTS/DATA IN THE DATABASE

<?php
	mysql_query("SET character_set_client=utf8", $dbLink);
	mysql_query("SET character_set_connection=utf8", $dbLink);
	$sql_query = "INSERT INTO
	TABLE_NAME(field_name_one, field_name_two)
	VALUES('field_value_one', 'field_value_two')";
	mysql_query($sql_query, $dbLink);
?>

Why are you adding the first two lines for? Because the database should know what kind of data is going to be stored.

Step Five: UPDATING INPUTS/DATA IN THE DATABASE

<?php
	mysql_query("SET character_set_client=utf8", $dbLink);
	mysql_query("SET character_set_connection=utf8", $dbLink);
	$sql_query = "UPDATE TABLE_NAME
	SET field_name_one='field_value_one', field_name_two='field_value_two'
	WHERE id='$id'; ";
	mysql_query($sql_query, $dbLink);
?>

So, you are adding the extra two lines before you run your query string as you are playing with Unicode.

Step Six: SEARCHING DATA FROM THE DATABASE

<?php
	mysql_query("SET character_set_results=utf8", $dbLink);
	$sql_query = "SELECT * FROM TABLE_NAME WHERE id='$id'; ";
	$dbResult = mysql_query( $sql_query, $dbLink);
?>

Adding the one extra line every time you search your Unicode data is enough.

OKKK.
You are done. This should work smoothly for handling your data in any language does not matter it is Bangla (my mother tongue), Hindi, Chinese, French, German, Spanish, Russian, Arabian (Arabic), Urdu, or any other language.

And do not forget to leave a comment if you have any. Because I need to update the post in case required.

Thanks for reading and please check if it works for you.