SQL Injection Prevention - A Practical Approach

SQL injection is a technique, used to attack data-driven applications. Using this method, hackers will try to execute their SQL statements within your application and access your database data.

Here is an example SQL injection. Let’s consider you have a login form with two fields – username (text field) and password (password field). Upon login, you will build and execute a similar query:

<?php
“SELECT * FROM `admin` WHERE `username` = ‘”.$_POST[“username”].”‘ AND `password` = ‘”.$_POST[“password”].”‘”;
?>

You can see that the query is searching for a user in `admin` table, which matches the username and password posted via the login form. However, since both username and password are not properly handled, an attacker can easily modify the query.

Assume that they enter:

Username: yeahhub
Password: yeahhub123

The constructed query will be:

SELECT * FROM `admin` WHERE `username` = ‘yeahhub’ AND `password` = ‘yeahhub123’;

Which seems to be correct. However, if the attacker uses:

Username: yeahhub
Password: yeahhub123′; DROP TABLE ‘admin

the query will become:

SELECT * FROM `admin` WHERE `username` = ‘yeahhub’ AND `password` = ‘yeahhub123’; DROP TABLE ‘admin’;

And of course, you do not want people to execute such queries over your database because it will actually delete your admin table from the current database and you’ll not be able to login then.

To protect your PHP application from being abused via such SQL injections, you should correctly set all SQL queries that are being run. With older versions of PHP (>= 4.3.0, 5) you would do that with mysql_real_escape_string(). So above query would look like this:

<?php
“SELECT * FROM `admin` WHERE `username` = ‘”.mysql_real_escape_string($_POST[“username”]).”‘ AND `password` = ‘”.mysql_real_escape_string($_POST[“password”]).”‘”;
?>

This is how the SQL injection protected query looks like now:

SELECT * FROM `admin` WHERE `username` = ‘yeahhub’ AND `password` = ‘yeahhub123\’; DROP TABLE \’admin’

You can see that the data being passed via $_POST is now escaped and DROP TABLE query will not be executed separately, but will be considered as a part of the password string.

With the latest versions of PHP you can now use PDO and prepared queries. Here is an example:

$stmt = $conn->prepare(“SELECT * FROM `admin` WHERE `username`=:username AND `password` = :password”);
$stmt->bindValue(‘:username’, $_POST[“username”]);
$stmt->bindValue(‘:password’, $_POST[“password”]);
$stmt->execute();

The key function here is prepare(). It secures the SQL query and protects it from SQL injections.

There are other ways to verify that data passed via SQL queries is valid and not abused. For example, if you expect an integer to be passed, you may use intval() to convert the inputted data into an integer.

“SELECT * FROM `admin` WHERE `age` = ‘”.intval($_POST[“age”]).”‘”;

Or if you expect an email address, you can use email validation to guarantee that $_POST[“email”] is a valid email address. SQL injection is one of the top website vulnerabilities, so you should be very careful when using user inputted data to construct SQL queries.

You can even use SQLiv and viSQL tools which are open source scripts through which you can easily find out all the vulnerable websites with the help of just a single Google Dork.

Other Useful Links –

You may also like:

SQL Injection Prevention – A Practical Approach

Sarcastic Writer

Related Posts