SecurePHPWebAppCoding - SQL Injection - What is it and How to Stop It?

Introduction

In this article, I have tried to cover some basic information about SQL injection, how we write code while developing a web application which results in SQL injection vulnerability, how attacker uses this flaw to gain unauthorized access and how we can change code a little bit to overcome our mistakes and prevent attackers from using SQL injection in web application which makes our application more secure. This article uses PHP and MySQL to show an example but other languages also have similar functions to prevent SQL injection. So let's see what SQL injection is.

What is SQL Injection?

SQL injection is a type of web application vulnerability using which an attacker can manipulate and submit a SQL command to retrieve unauthorized information from database. This type of attack mostly occurs when a web application executes data provided by user without validating or escaping it. SQL injection can give access to sensitive information such as financial data, credit card information or users personal information to the attacker and allows the attacker to manipulate data stored in database. It is not a database or web application server issue but it is a web application programming issue and most of the developers are not aware of this.

What Can An Attacker Achieve Using SQL Injection?

Based on the application and how user data is handled by the application, SQL injection attack is used for the following. There are other scenario also.

• Unauthorized login: Attacker can use SQL injection to get unauthorized access to users account and perform any action they want on that account.
• Privileges escalation: A user with less privileges can use SQL injection to login to an account with more privileges than his account and add more privileges to his account so that attacker can access more data/features of that application.
• Tamper with database data: Attacker can update database data to change other profile details, change password which will result in problem for the other user.
• Dumping database: Attacker can use SQL injection to dump all data from database and expose it with sensitive information like logins, credit card information, etc. of users.
• Deletion/destruction of data: SQL injection can be used to delete data from database making website lose all records of user and all their details.
• Read files of web server: Attacker can use SQL injection to load file present in web server and read the application code, configuration files, etc.
• Damage company's reputation: SQL injection can be used to dump all data and it can be made available publicly. No user likes their personal/sensitive data leaked.

How Can We Prevent SQL Injection?

• Never believe in user input and client side validation. Always validate user input on server end for specific data type or convert data to specific data type before using it in query.
• For string data, escape single quotes and double quotes or convert string to HTML entities (this will increase length of string, so depending upon the field type/length, use it).
• Try to avoid creating query using string concatenation. It is one of the main reason which makes a web application vulnerable to SQL injection but most of the developers use this approach to generate query because they find it easy without thinking or knowing about the mistake they are making.
• Use prepared statement and parameter binding.
• Whenever possible, replace potentially dangerous characters for database from user input data.

  Special Database Characters	Function in database
  ;	Query Delimiter
  '	Character data string delimiter
  --, #	Single line comment
  /* */	Multiline comment

  NOTE: Special database characters may vary from database to database.

• Use account with less permissions for web application to execute query.

Now let's do some real work. Let's see how we write code which allows hacker to use SQL injection in website and with that we will see how can we write few more lines of code with that code to prevent SQL injection in website. We will see it using PHP but the same thing can be done to/using application written in other programming language. So let's begin. Let's see the classic example first which everyone gives when you ask about SQL injection.

SQL Injection in Login Page

When we implement login functionality, most of the developers write like this:

$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '" . $username . "' AND password = '" . $password . "'";

So when user provides his/her username (username@example.com) and password (password) in login form, this script generates a query like below:

$query = "SELECT * FROM users WHERE username = 'username@example.com' AND password = 'password'";

Web application executes the query, validates user's credentials and allows user to login. But when attacker supplies password as "' or '1'='1" without double quotes, query will be converted to:

$query = "SELECT * FROM users WHERE username = 'username@example.com' AND password = '' or '1'='1'";

which allows attacker to login without knowing password of the user. But you may say, we mostly use hashing to store password. So the query generated will be something like this:

$username = $_POST['username'];
$password = sha1($_POST['password']);
$query = "SELECT * FROM users WHERE username = 'username@example.com' AND password = '154eec809fb37f6944eccf1fa9c6c981eb15d063'";

which will block user from login. Yes, that is true. But when user supplies username as "' or 1; -- " or "' or 1;#" without double quotes, now login query looks like:

$query = "SELECT * FROM users WHERE username = '' or 1; -- ' AND password = '' or '1'='1'";

and in MySQL everything after " -- "(carefully note, space around --, it will give error if there is no space around --) or "#"(if not enclosed by quote) is ignored as it is considered as comment. So it will allow user to login to an arbitrary account and the account owner will have no idea about it.

So how can we avoid it?

• Escape all single quotes and double quotes.

$username = $_POST['username'];
$password = sha1($_POST['password']);
$query = "SELECT * FROM users WHERE username = 'username@example.com' AND password = '\' or \'1\'=\'1'";

This will check for "' or '1'='1" as password because single quotes are escaped and obviously will not match with the user password unless user has set this as its password.

• Do not allow characters which are potentially dangerous for database and remove those characters from user supplied data wherever possible. Instead of writing code shown below:

$username = $_POST['username'];
$password = sha1($_POST['password']);
$query = "SELECT * FROM users WHERE username = '' or 1; -- ' AND password = '' or '1'='1'";

we can write it like this:

$username = str_replace(array(' -- ', '#', ';'), array('', '', ''), $_POST['username']);
$username = addslashes($username);
$password = sha1($_POST['password']);
$query = "SELECT * FROM users WHERE username = '\' or 1' AND password = '154eec809fb37f6944eccf1fa9c6c981eb15d063'";

and this will block user as user name is invalid.

• Do not create query using string concatenation. User prepared statement and parameter binding.

$username = $_POST['username'];
$password = sha1($_POST['password']);
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param('ss', $username, $password);

It generates query with all single quotes and double quotes escaped (a slash(\) gets added before single quote and double quote if present in supplied parameter) and quote is treated as a character in the user supplied data, not as character data string delimiter which prevents SQL injection.

Let's take another example.

Show Product Info Using Product Id Which is in url as Query String

Assume you have implemented a functionality where you are listing all the products under a category. And you need to show details of a product when user clicks on it. When user clicks on product, you are redirecting user to a page whose URL is like below:

http://www.website.com/product_details.php?product_id=1

In product details page, code to fetch product is like this:

$product_id = $_GET['product_id'];
$sql = 'SELECT * FROM products WHERE id = ' . $product_id;

When product_id in query string is changed to "1 or 1 = 1" without double quote, query will be:

$sql = 'SELECT * FROM products WHERE id = 1 or 1 = 1';

And it will return all products. But it is not the most dangerous situation in this case. Attacker can inject UNION query here like below:

http://www.website.com/product_details.php?product_id=-1 union select * from users

and query will be:

$sql = 'SELECT * FROM products WHERE id = -1 union select * from users';

which will return details from users table.

The above query works only when:

• both tables have the same number of columns
• the data types of the columns match correspondingly

Note: To inject UNION query which returns exact required details takes few steps and we will not cover it in this article.

So now let's see what we can do here. We know product id will be integer only (at least most of the time). So to avoid SQL injection in such case, convert user data to exact data type. So in this case:

$product_id = intval($_GET['product_id']);
$sql = 'SELECT * FROM products WHERE id = ' . $product_id;

If we provide "1 or 1 = 1 " or "-1 union select * from users", it will be converted into integer value which will prevent the SQL injection.

There are lots of scenarios where we use SQL to access database which is not possible to cover here. Above, we see most possible usual cases which we can compare to others and use above explained way to prevent SQL injection. Only one of the above methods may not be sufficient to prevent SQL injection always. We may need to use combination to avoid SQL injection. But the best solution is to replace all unnecessary characters from user provided data, validate/convert data to its best suitable data type and use parameter binding.

Conclusion

SQL injection in a web application occurs mostly because of developers' negligence while writing code. We write code to make the application work. But simultaneously, we should write code to make the application secure from unauthorized access which can be done just by putting a little extra effort while developing a web application.