Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
SQL FAQ Feat

How to avoid SQL injection in PHP?

  • How-Tos FAQs
  • December 15, 2018

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 ]);

From 1 Month to 1 Hour! Create Your Data Models Faster in Datameer Then With SQL.

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?

More Resources We Think You Might Like

SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

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

How to return all information of a row with MAX...

Suppose we have a table “score” as below, and we need all information on a row with the maximum s...

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

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018