How to Prevent SQL Injection in PHP

SQL Injection is a major security vulnerability in websites that allows attackers to exploit and manipulate your website’s performance. In this article, we will look at how to prevent SQL injection in PHP.

What is SQL Injection Attack?

SQL Injection attack is a common web hacking technique whereby attackers place malicious code on your website via SQL statements run on your website. They inject these code into your database via website inputs on your website such as forms and textboxes. If your website’s back end does not scan and clean up user input data before saving it into the database, or if user input is executed without any modification, then it can cause serious damage to your website. Here is an example.

Let us say you have the following code on your website which uses a user input (e.g text box) received via user_input variable.

$unsafe_variable = $_POST['user_input'];  
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

If a user enters malicious input text such as value’); DROP TABLE table;– then the SQL query in your PHP code becomes

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

The above query will automatically delete your database table which is dangerous.

How to Prevent SQL Injection in PHP

Here are a few ways to prevent SQL injection in PHP. The most common way to do this is to use prepared SQL statements and parameterized queries.

There are two ways to do this.

1. Using PHP Data Objects (PDO)

PHP Data Objects (PDO) provides a uniform abstraction layer that allows you to connect to and query upto 12 different types of databases. It uses prepared SQL statements, thereby preventing SQL injection.

Here is an example,

$stmt = $pdo->prepare('SELECT * FROM employees WHERE age = :age');
$stmt->execute([ 'age' => $age ]);

foreach ($stmt as $row) {
     // Do something with $row
 }

In the above case, PDO will substitute :age variable only using $age PHP variable after validation, thereby preventing SQL injection.

Also read : How to Disable TLS 1.0/1.1 in Apache

2. Using MySQLi

If your website runs on MySQL database, you can also use MySQLi to prevent SQL injection.

 $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE age = ?');
 $stmt->bind_param('s', $age); // 's' specifies the variable type => 'string'

 $stmt->execute();

 $result = $stmt->get_result();
 while ($row = $result->fetch_assoc()) {
     // Do something with $row
 }

In the above code, MySQLi binds parameter $age with ? in the prepared SQL statement.

The key point to note is that in both the above cases, the prepare function parses and compiles the SQL statement passed to it. When execute command is called, the parameter values (passed as :age or ? )are combined with compiled PHP statements and not SQL queries, thereby preventing SQL injection.

Also read : What File Permissions for Apache Server