October 05, 2023

Prevent SQL Injection in PHP (MySQLi & PDO Prepared Statement Examples)

SQL injection is one of the most common and dangerous vulnerabilities that an attacker can use to change original queries for an attack. This post explains what SQL injection is and how to prevent SQL injection in a PHP application.

Prevent SQL Injection in PHP

SQL injection refers to an approach where an attacker injects malicious SQL code into the original query, causing it to expose or manipulate sensitive data. This SQL injection happens when user input is directly inserted into an SQL query without proper validation or escaping special characters. In this post, we will explore ways to avoid SQL injection in PHP and keep a web application secure.

 

SQL Injection Example

In order to secure a PHP application against SQL injection attacks, it is important to first understand it. As mentioned, SQL injection happens when unsafe user input is inserted into an SQL query. Let's understand with a simple example of a login form submitted to server-side processing.

// Unsafe example
$username = $_GET['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $query);

If a user enters admin' -- , it will change the whole logic of the query by ignoring the rest of the query after --, which in SQL starts a comment. This can allow an attacker to completely bypass the authentication and gain access to the restricted area of a web application.

SELECT * FROM users WHERE username = 'admin' --'
 

How to Protect Against SQL Injection in PHP

When building applications with PHP, it is crucial to understand how to prevent SQL injection. The most effective way to prevent SQL injection is by using prepared statements, also known as parameterized statements. Prepared statements ensure user input is treated as data and not as an executable SQL code. The following code snippets show prepared statements in MySQL and PDO (PHP Data Objects).

SQL Injection Prevention using MySQLi

$conn = new mysqli($host, $user, $pass, $dbname);

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ?");

$stmt->bind_param("s", $username); // "s" means string

$stmt->execute();

$result = $stmt->get_result();

SQL Injection Prevention using PDO

$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");

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

$stmt->execute();

$result = $stmt->fetchAll();
 

Additional Security Best Practices

In addition to SQL injection prevention, as a programmer, you should follow recommended programming practices to keep the application secure. The following are some important things to follow:

  • Always sanitize and validate user input before passing it to SQL queries.
  • Avoid connecting to the database with a user having more privileges than necessary.
  • Disable error reporting in production. Just in case something breaks, the front-end user should not see the details of the bug. Instead, log errors to investigate and trace bugs.
  • Keep PHP and database software updated.

We demonstrated how to prevent SQL injection in PHP for MySQL and PDO. Preventing SQL injection is not difficult once you have a proper understanding of it. By following secure coding practices like always using prepared statements, a web application can protect itself from one of the most critical vulnerabilities.

Home