SQL injection - how to prevent security issue in PHP

No matter your practical experience and understanding of programming and web development, there is one thing no developer can deny; security is one of the major issues that constantly bug the mind throughout the development phase. Not to mention, the clients are also getting more concerned about security issues and their website's ability to avoid them. Speaking of PHP, SQL injection is a threat that is common for websites where the stored data originates from remote sources. Unfortunately, not much help is available in this regard.

Today, we will present a basic overview of this security threat and a few tips to help developers prevent this issue by making the application less vulnerable.

Understanding SQL injection

SQL injection occurs when an attacker inserts an unwanted SQL query statement in your database. The statement is usually inserted when the application asks for user input such as name and personal information required by authentication or login forms. The statement inserted by the attacker will run in the database without your knowledge. As a result, the attacker would be able to access sensitive information and data.

Let us show you an example of a simple SQL injection.

PHP code:

// user input without SQL injection
$userName = "joan"; //user input
$SQLQuery = "SELECT * FROM customers WHERE username = '$userName';";
echo "Normal: " . "<br /> " . $SQLQuery . "<br />";

// user input with SQL injection
$userName = "' OR '1'='1"; //user input
$SQLQuery = "SELECT * FROM customers WHERE username = '$userName';";
echo "Injection: " . "<br /> " . $SQLQuery . "<br />";

Display:

Normal:
SELECT * FROM customers WHERE username = 'joan';
Injection:
SELECT * FROM customers WHERE username = '' OR '1'='1';

In this example, the intended purpose of the SQL statement is to select everything from the customer whose username is "joan". The injected statement, however, will select every single entry in the table "customers". This is, of course, a simple example and the attacker can do much more damage to the data.

SQL injection is an application layer attack and indicates improper coding. You definitely don't want this kind of flaw reflected in your work. The good news is it can be prevented. So, without further ado, let's take a look at how you can avoid it.

Prevention

There are numerous ways and best practices to ensure that your code is optimized against SQL injection. One of the most common and easiest one is to use the mysql_real_escape_string function. A number of newbies are unaware of this PHP function made specifically to tackle injection issues. This function simply takes a string that is to be used in a query and returns it while escaping all injection attempts by the attacker.

Let's take a look at an example of injection escape for the above mentioned example.

PHP code:

// user input with SQL injection
$userName = "' OR '1'='1"; //user input
$userName = mysql_real_escape_string( $userName ); //escaping user input
$SQLQuery = "SELECT * FROM customers WHERE username = '$userName';";
echo "SQL: " . "<br /> " . $SQLQuery;

Display:

SQL:
SELECT * FROM customers WHERE username = '\' OR \'1\'=\'1';

As you can see, the backslash replaces the injected quotes and prevents the attack by making the query search for a non-existent username instead of serving the attacker's intended purpose. So, feel free to use as many mysql_real_escape_string functions as you deem necessary to make your application secure.