How To Prevent SQL Injection With PHP

How To Prevent SQL Injection With PHP

SQL injection is said to be a technique which is used to attack data driven applications. Using hackers, method to execute their SQL statements within your application to access your database data.

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

“SELECT * FROM `users` WHERE `email` = ‘”.$_POST[“email”].”‘ AND `password` = ‘”.$_POST[“password”].”‘”;

?>

You can able to see query for searching a user in “users” table which matches the meail and passowrd posted through login form. Since both email and password are not properly said to be handled then an attacker can able to modify query.

Example:

Email: myemail@domain.com

Password: mypassword

The constructed query will be:

SELECT * FROM `users` WHERE `email` = ‘myemail@domain.com’ AND `password` = ‘mypassword’;

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

Email: myemail@domain.com

Password: mypassword’; DROP TABLE ‘users

the query will become:

SELECT * FROM `users` WHERE `email` = ‘myemail@domain.com’ AND `password` = ‘mypassword’; DROP TABLE ‘users’;

Ofourse, you dont want people to execute such queries over your database.

Inorder protect your PHP application from abused through SQL injections which should correctly set all SQL queries are being run. With older versions of PHP(>= 4.3.0, 5) with mysql_real_escape_string(). Above query will look like:

“SELECT * FROM `users` WHERE `email` = ‘”.mysql_real_escape_string($_POST[“email”]).”‘ AND `password` = ‘”.mysql_real_escape_string($_POST[“password”]).”‘”;

?>

This is how SQL injection protected query is said to look like now:

SELECT * FROM `users` WHERE `email` = ‘myemail@domain.com’ AND `password` = ‘mypassword\’; DROP TABLE \’users’

You can able to see that data is said to passed through $_POST is now escaped and DROP TABLE query will not to be excuted separately but will be considered to be a part of password string.

Its latest version of PHP can now be used as PDO and prepared queries. Here is an example:

$stmt = $conn->prepare(“SELECT * FROM `users` WHERE `email`=:email AND `password` = :password”);

$stmt->bindValue(‘:email’, $_POST[“email”]);

$stmt->bindValue(‘:password’, $_POST[“password”]);

$stmt->execute();

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

There are other ways to verify that data passed through SQL queries which is valid. For example, if you expect an integer to be passed through use intval() to convert the inputed data into an integer.

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

Or if you can expect an email address then you can email validation to guarantee that $_POST[“email”] is a valid email address. Take a look on PHP validation and verification tutorial for various string validations.

SQL injection is one of the top website vulnerabilities and should be very careful when user inputted data to construct SQL queries.