How to avoid SQL injection in PHP?

  • How-Tos FAQs
  • December 15, 2018
SQL FAQ Feat

SQL injection is one of the most common vulnerabilities in a web application. We can avoid it in PHP by using parameterized SQL queries . The parameterized SQL query provides a placeholder for the user inputs. All the inputs provided by the user are treated as data. So, when a user passes data, including SQL injection, the whole input will be treated as data, including the SQL injection part. This way, the SQL injection can not be run as an SQL command.

The two popular ways of connecting PHP with databases are PDO and MySQL.

Let us know how to use parameterized SQL queries in both of the connection methods.

PDO:

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

MySQL:

$stmt = $dbConnection->prepare('SELECT * FROM user WHERE user_name = ?'); 
$stmt->bind_param('s', $user_name); // 's' specifies that the variable is string 
$stmt->execute();

In the above example, if a malicious user pass value as

$user_name = John'; 
DROP table user;

The SQL query will be constructed as

SELECT * FROM user WHERE user_name = 'John''; DROP table user;'

Here DROP table user; is also treated as part of the user name variable instead of SQL command. Hence, injected SQL commands can never be executed.


Up Next:

Read In SQL, how to limit the number of rows after ordering it in Oracle DB?