How to Prevent SQL Injection in PHP using PDO Prepared Statements

Hello Friends, In this tutorial we will learn how to prevent SQL injection in PHP using PDO prepared statements. Here's an example code snippet to demonstrate how to do so:

Step 1: Create a PDO connection

First, you need to establish a connection to your database using PDO. Here's an example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully"; 
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Step 2: Use prepared statements

Instead of concatenating user input directly into your SQL queries, you should use prepared statements. Prepared statements separate the SQL statement from the user input, which prevents SQL injection attacks. Here's an example:

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
// execute the query
$stmt->execute();
?>

In the above example, the user input is bound to the prepared statement using the bindParam() function. The :username and :password placeholders in the SQL statement are replaced with the actual user input at runtime.

Step 3: Sanitize user input

Even though prepared statements prevent SQL injection attacks, it's still a good practice to sanitize user input. Sanitization is the process of removing any unwanted characters or formatting from user input. Here's an example of how to sanitize user input:

<?php
// sanitize user input
$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
$password = filter_var($_POST['password'], FILTER_SANITIZE_STRING);
?>

In the above example, we use the filter_var() function with the FILTER_SANITIZE_STRING flag to remove any unwanted characters or formatting from the $_POST variables.

Step 4: Use parameterized queries

Another way to prevent SQL injection attacks is to use parameterized queries. Parameterized queries use placeholders in the SQL statement, which are then replaced with actual values at runtime. Here's an example of how to use parameterized queries:

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bindParam(1, $username);
$stmt->bindParam(2, $password);
// execute the query
$stmt->execute();
?>

In the above example, we use the ? placeholder in the SQL statement, and the bindParam() function to bind the user input to the prepared statement.

Step 5: Use input validation

In addition to sanitizing user input, you should also validate user input to ensure it meets your application's requirements. Input validation is the process of checking if the user input is valid, such as checking if it's the correct data type or if it meets a certain length requirement. Here's an example of how to use input validation:

<?php
// validate user input
if (!isset($_POST['username']) || !is_string($_POST['username']) || strlen($_POST['username']) > 50) {
    die('Invalid username');
}
if (!isset($_POST['password']) || !is_string($_POST['password']) || strlen($_POST['password']) > 50) {
    die('Invalid password');
}
// sanitize