Different ways to prevent SQL Injection in PHP?

This post will explain about sql injection and different ways to prevent it.

SQL Injection

SQL injection is a technique where malicious SQL code is passed from the input field of the frontend to the backend of database-driven application.

All the application now days are database-driven which means that user has to log-in in an application to properly use it?s functionality.

SQL injection usually occurs when you ask a user for input, like their username, password and instead of username and password the user gives you an SQL statement that you will unknowingly run on your database

Different examples are,

Example 1

Figure 1

The hacker attack shown figure 1 will have the following execution at the server side.

SELECT * FROM USER_TABLE WHERE Username= ?Brian? and Password=? ? or 1 = 1 ? ?

1= 1 is always true so this query will return all the users information from the table.

Example 2

figure 2

The hacker attack shown figure 2 will have the following execution at the server side.

SELECT * FROM customers WHERE username = ?Brian? and Password= ? ? ; DELETE FROM customers WHERE 1 or username = ? ?

If the hacker is able to run this query at the server the customer table will become empty and all the records will be gone.

Prevention of the SQL injection

There are different methods proposed to prevent the sql injection which are as follows.

Method 1

Using mysql_real_escape_string()

What mysql_real_escape_string does is take a string that is going to be used in sql query and return the same string with all sql Injection attempts safely escaped. Basically, it will replace those troublesome quotes(?) a user might enter with the backslash ().

//The injection shown in figure 1,$sql_injected_password = “‘ OR 1′”;//The injection shown in figure 2, $sql_injected_password = “‘; DELETE FROM customers WHERE 1 or username = ‘”;//Using the mysql_real_escape String$safe_password = mysql_real_escape_string($password);

Output,

By using the mysql_real_escape_string the troublesome quotes (?) shown in figure 1,2 will be replaced by the backslash () shown below,

SELECT * FROM customers WHERE username = ?Brian? and Password= ?? OR 1??

SELECT * FROM customers WHERE username = ?Brian? and Password= ??; DELETE FROM customers WHERE 1 or username = ??

Conclusion

This method provide the protection against the sql injection but it is not very reliable. It is because hacker can make a combination that will by-pass the mysq_real_escape_string method. No, matter how much escape you made at the server side but still there are many possibilities and combinations hacker can think of.

Note: mysql_real_escape_string was depreciated in php 5.5.0 and is removed from 7.0.0. mysql has been replaced by mysqli. so the method becomes mysqli_real_escape_string()

Method 2

Using Prepared Statements (with Parameterised Queries)

The right way to prevent SQL injection is by using parameterised queries. This means defining the SQL code that is to be executed with placeholders for parametric values, programmatically adding the parameter values, then executing the query.

It includes three steps,

1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled ???). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
2. The database parses, compiles, and performs query optimisation on the SQL statement template, and stores the result without executing it
3. Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

$conn = new mysqli($servername, $username, $password, $dbname);$stmt = $conn->prepare(“INSERT INTO MyGuests (username, password) VALUES (?, ?)”);$stmt->bind_param(“ss”, $_POST[‘username’];, $_POST[‘password’]);$stmt->execute();$stmt->close();

The code above is described as,

$conn->prepare(“INSERT INTO MyGuests (username, password) VALUES (?, ?)”);

The (?) present in the sql statement shows where the parametric values will be substituted. It can be integer, string, double or bob value.

$stmt->bind_param(“ss”, $username, $password);

The bind method will tell the sql what the parameters are.

The ?ss? argument tell the sql about the parameter type.

The parameter may be one of four types:

• i ? integer
• d ? double
• s ? string
• b ? BLOB

Then the values are assigned to the parameters and query is executed.

Conclusion

Using prepared statement and parametrised query the sql injection can be prevented because the parameter values (even if they are injected SQL) won?t be executed because they are not part of the query statement and it will be treated as user input in the form of string, not SQL code. In other words, the query will look for a user with this password instead of executing unexpected sql code.

Reference

What’s the Right Way to Prevent SQL Injection in PHP Scripts?

How to prevent SQL injection in PHP scripts is probably a topic that doesn’t need anything more written about it. It is?

blogs.msdn.microsoft.com

PHP: SQL Injection – Manual

Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted?

www.php.net

PHP Prepared Statements

Prepared statements are very useful against SQL injections. A prepared statement is a feature used to execute the same?

www.w3schools.com

mysqli::real_escape_string

(PHP 5, PHP 7) mysqli::real_escape_string — mysqli::escape_string — mysqli_real_escape_string – Escapes special?

www.php.net