October 05, 2023

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

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

Prevent SQL Injection in PHP

SQL injection refers to approach where an attacker injects malicious SQL code to 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 SQL query. Let's understand with a simple example with 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 user enters admin' -- , it will change the whole logic of query by ignoring the rest of query after -- which in SQL starts a comment. This can allow attacker to completely bypass the authentication and gain access to restricted area of 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 to prevent SQL injection is using prepared statements also known as parameterized statements. Prepared statements ensure user input is treated as data and not as an executable SQL code. 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 application secure. Following are some important things to follow:

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

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

Home