How does pdo help prevent sql injections in PHP applications?

Spending a lot of effort to build a useful PHP application and launch it, only to see it come down or be taken over by someone can be a very disappointing. Although the frameworks like Laravel, codeignitor and others do a fantastic job in preventing SQL injection in applications built with safety in mind, if you are someone like me building PHP applications without any framework should be aware of what PDO is and why should you care to use PDO for your interactions with the database. In this article, we will show why PDO is such a good practice to adopt, especially if you are building your applications without using any of the frameworks.

1. Prepared Statements

A prepared statement is a feature that allows you to write the SQL query with placeholders for the values that will be provided later. Instead of directly inserting user input into the query, placeholders are used. PDO will then safely bind the user input to those placeholders, ensuring the input is treated as data (not executable SQL).

Example without Prepared Statements (Vulnerable to SQL Injection)

$username = $_POST['username'];

$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

$result = $pdo->query($query); // This is vulnerable to SQL injection

Why is this vulnerable to SQL injection? because a bad actor can easily inject via the form fields a simple SQL script that can destroy your db if not sanitized properly.

Example with Prepared Statements (Secure)

$username = $_POST['username'];

$password = $_POST['password'];

// Use placeholders in the SQL query

$query = "SELECT * FROM users WHERE username = :username AND password = :password";

$stmt = $pdo->prepare($query);

// Bind parameters to the placeholders

$stmt->bindParam(':username', $username);

$stmt->bindParam(':password', $password);

// Execute the statement

$stmt->execute();

Here:

• :username and :password are placeholders for the user-provided values.
• The values provided by the user are bound to the placeholders using bindParam (or bindValue).
• The query is then executed with the safely bound values.
• 
  

Why does this prevent SQL injection?

• Escaping Values: PDO ensures that the values are properly escaped and treated as data, not executable code. For example, if a user enters a value like ' OR 1=1 --, PDO will treat it as just a string value, not part of the SQL code.
• Separation of Code and Data: The SQL query is parsed and compiled separately from the data being input by the user. This means user input can't alter the structure of the query itself.

2. Parameterized Queries

In PDO, parameterized queries mean that the data is bound to the query only after the query structure is fixed. Since the query structure (the SQL code) is defined first, the database engine can safely determine what part of the query is code and what part is data, preventing malicious code injection.

3. Binding Data Types (Optional)

PDO also allows you to explicitly specify the data type of parameters. This ensures the correct handling of the values. For example, you can specify that a parameter should be treated as an integer, string, or date.

Example with Data Types:

$query = "SELECT * FROM users WHERE id = :id";

$stmt = $pdo->prepare($query);

$id = 123;

$stmt->bindParam(':id', $id, PDO::PARAM_INT); // explicitly bind as an integer

$stmt->execute();

Here, the PDO::PARAM_INT ensures that the id is treated as an integer, reducing the risk of accidental type-related SQL injection.

4. Automatic Handling of Special Characters

PDO automatically handles special characters such as quotes (e.g., " or '), semicolons, and others in input data, ensuring that they don't interfere with the SQL query or cause it to behave unexpectedly.

Summary of How PDO Prevents SQL Injection

Prepared statements separate SQL code from user input.

Bound parameters ensure that user input is treated strictly as data, not executable code.

Automatic escaping of user input helps prevent harmful SQL characters from affecting the query.

Explicit data type binding provides additional security by ensuring the input is interpreted correctly.

This combination makes PDO a powerful tool for preventing SQL injection vulnerabilities in your PHP applications.