Preventing SQL Injection in PHP Tutorial

Introduction

In this tutorial, I will teach you how to Prevent SQL Injection in writing your PHP Queries for your current and future PHP Projects. This tutorial aims to give the IT/CS Students or those new to PHP Language a reference to learn for securing their database data in PHP. Here, I will be providing some snippets with a sample source code that demonstrates our topic for this tutorial.

What is SQL Injection?

An injection attack known as SQL Injection (SQLi) enables the execution of malicious SQL commands. These commands manage a database server that sits in front of a web application. SQL Injection flaws allow attackers to get around application security safeguards. The full content of a SQL database can be retrieved by getting past authentication and authorization of a web page or online application. They can also add, alter, and delete records in the database using SQL Injection.

How to Prevent SQL Injection in PHP?

To prevent SQL Injection in your PHP Project that uses MySQL Database. You must practice the following step for writing your PHP Code and MYSQL Queries.

1. Validate and Sanitize Input Data
2. Use a Prepared Statement
3. Bind Query Parameters
4. Execute the Queries

The above step can be achieved by using PDO and MySQLi.

Example

Below are the sample snippets for both vulnerable and prevents SQL Injection. You will see the sample result for both.

Database Schema and Data

Let's pretend that we have the following database schema and data in our database. The table is the user list of your web application which contains the user access.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  `username` text NOT NULL,
  `password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
INSERT INTO `users` (`id`, `first_name`, `last_name`, `username`, `password`) VALUES
(1, 'Super', 'Administrator', 'super_admin', 'super_admin#123'),
(2, 'Admin 1', 'Administrator', 'admin1', 'admin1#123'),
(3, 'Admin 2', 'Administrator', 'admin2', 'admin2#123');

Sample Form

Here is the sample login form for fetching and checking the user credential. I will only be using text type for the password input field just for demonstration purposes only for this tutorial.

<fieldset class="border px-2 py-3 mb-3">
    <form action="" method="POST">
        <div class="mb-2">
            <label for="" class="control-label">Username</label>
            <input type="text" name="username" class="form-control" value="<?= (isset($_POST['username'])) ? $_POST['username'] :'' ?>">
        </div>
        <div class="mb-2">
            <label for="" class="control-label">Password</label>
            <input type="text" name="password" class="form-control" value="<?= (isset($_POST['password'])) ? $_POST['password'] :'' ?>">
        </div>
        <div class="mb-2">
            <button class="btn btn-primary btn-sm" name="login">Submit</button>
        </div>
    </form>
</fieldset>

Output

Sample Data

Use the "anything' or 'x' = 'x" as the username and password to the form and see the result for both PHP Queries execution of the snippets below.

Sample Vulnerable PHP Script

The following PHP Code is an example of vulnerable to SQL Injection.

    <?php
    if($_SERVER['REQUEST_METHOD'] == 'POST'){
        $username = $_POST['username'];
        $password = $_POST['password'];
 
        $sql =  "SELECT * FROM `users` where `username` = '{$username}' and `password` = '{$password}' ";
        $query = $conn->query($sql);
        $results = $query->fetch_all(MYSQLI_ASSOC);
    }
    ?>

Output

Sample PHP Script that Prevents SQL Injection

The following PHP script is an example of a PHP Query that prevents SQL Injections. The script below is using MySQLi Prepared statement and sanitizes input data.

<?php
if($_SERVER['REQUEST_METHOD'] == 'POST'){
    // Sanitizing Input Value
    $username = addslashes($conn->real_escape_string($_POST['username']));
    $password = addslashes($conn->real_escape_string($_POST['password']));
 
    $sql2 =  "SELECT * FROM `users` where `username` = ? and `password` = ? ";
    // Prepare Statement
    $stmt = $conn->prepare($sql2);
    // binding query paramters "s = string"
    $stmt->bind_param('ss', $username, $password);
    $stmt->execute();
    $results2 = $stmt->get_result();
    $data = $results->fetch_all(MYSQLI_ASSOC);
}
?>

Output

That's it! I also created and provided a simple web application that demonstrates How to Prevent SQL Injection in PHP. The download button is located below this article. Feel free to download and modify it.

That's the end of this tutorial. I hope this tutorial will help you learn to secure your PHP Projects. Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)