How to Prevent SQL Injection in PHP?

Problem:

In your dynamic website, you receive user input and use it as part of the query to interact with database (ex. site search, user login etc). In this stage, your application is not safe. Because, attackers may inject malicious SQL statements which may result modification of existing information or removing information or even can destroying the entire database.

Solution:

Use prepared statement to prevent SQL injection

When user supplied input (which contain malicious codes) is attached with SQL statement and the SQL statement is executed in the database server, then, disaster may happen. As because, SQL statements in this form are vulnerable.

In the prepared statement user supplied inputs are not directly attached with the SQL statements. So, malicious codes have no negative impact on the database. In this way, SQL statement is compiled at first then it is attached with the user’s input.

Two ways to use prepared statement

There are two ways you can use prepared statement to build SQL queries.

  1. Using PHP Data Objects (PDO) prepared statement, and
  2. Using MySQLi prepared statement

Now, using the above two methods separately, we’ll see how to build the following SQL queries –

  • SELECT query
  • INSERT query
  • UPDATE query
  • DELETE query

Here is a sample database table we’ll work with to execute different queries-

database table

1.a. PDO SELECT query

In the following example, we’ll fetch name and email of all students who class no is 7

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// User supplier input
$class = 7;

try {
    /// Connecting to the server and selecting database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to select records
    $stmt = $dbh-&gt;prepare("SELECT Name, Email FROM students WHERE Class = :Class");
    // binding user-supplied input($class) with the named parameter(:Class)
    $stmt-&gt;bindParam(':Class', $class, PDO::PARAM_STR);
    // executing the statement
    $stmt-&gt;execute();
    // fetching information.
    while ($row = $stmt-&gt;fetch(PDO::FETCH_ASSOC)) {
        echo $row['Name'] . "  -- " . $row['Email'] . '&lt;br /&gt;';
    }
}catch(PDOException $e){
    echo "Error occurs:". $e-&gt;getMessage();
}
?>

[wpdm_file id=16]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

1.b. PDO INSERT query

In the following example, we’ll insert a new student in the table.

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be inserted
$name = “Mariano Brais”;
$email = “marino@gmail.com”;
$class = 8;

try {
    // Connecting to the server and selecting database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to insert record
    $stmt = $dbh->prepare("INSERT INTO students(Name, Email, Class) VALUES(:Name, :Email, :Class)”);
    // executing the statement
    $stmt->execute(array(‘:Name’ => $name, ‘:Email’ => $email, ‘:Class’ => $class));
    echo “Student information inserted.”;
}catch(PDOException $e){
    echo "Error occurs:". $e->getMessage();
}
?>

[wpdm_file id=17]

Output:
Student Information inserted.

1.c. PDO UPDATE query

In the following example, we’ll update the student info whose id is 4.

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be updates
$name = “Olek Predrag”;
$email = “olek@gmail.com”;
$class = 3;
$sid = 4;

try {
    // Connecting to the server and selecting database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to update record
    $stmt = $dbh->prepare("UPDATE students SET Name = :Name , Email = :Email, Class = :Class WHERE SID = :SID”);
    // executing the statement
    $stmt->execute(array(‘:Name’ => $name, ‘:Email’ => $email, ‘:Class’ => $class, ‘:SID’ => $sid));
    echo “Student information updated.”;
}catch(PDOException $e){
    echo "Error occurs:". $e->getMessage();
}
?>

[wpdm_file id=18]

Output:
Student Information updated.

1.d. PDO DELETE query

The following code deletes students whose class no is 8.

<pre><?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be deletes
$class = 8;

try {
    // Connecting to the server and selecting database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to delete record
    $stmt = $dbh->prepare("DELETE FROM students WHERE Class = ‘:Class’ “);
    // binding user-supplied input($class) with the named parameter(:Class)
    $stmt->bindParam(':Class', $class, PDO::PARAM_STR);
    // executing the statement
    $stmt->execute();
    echo "Student information deleted.";
}catch(PDOException $e){
    echo "Error occurs:". $e->getMessage();
}
?>

[wpdm_file id=19]

Output:
Student Information deleted.

2.a. MySQLi SELECT query

<pre><?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

// Connecting to the server and selecting database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
    echo 'Unable to connect to database '. $dbh->connect_error;
}else{
    // run query to select records
    if ($stmt = $dbh->prepare("SELECT Name, Email FROM students WHERE Class = ?")) {
        // binding user-supplier input $class to the parameter as integer
        $stmt->bind_param("i", $class);
        // executing the statement
        $stmt->execute();
        // binding the table columns Name and Email to the $Name and Email parameters respectively.
        $stmt->bind_result($Name, $Email);
        /* fetch associative array */
        while ($stmt->fetch()) {
            echo $Name. ' -- '. $Email. '<br />';
        }
    }else{
        echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
?>

[wpdm_file id=20]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

2.b. MySQLi INSERT query

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be inserted
$name = “Evelyn W. Milam”;
$email = “evelyn@gmail.com”;
$class = 6;

// Connecting to the server and selecting database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
    echo 'Unable to connect to database '. $dbh->connect_error;
}else{
    // run query to insert records
    if ($stmt = $dbh->prepare("INSERT INTO students(Name, Email, Class) VALUES(?, ?, ?)”)) {
        // binding user-supplier inputs to the parameters
        $stmt->bind_param("ssi", $name, $email, $class);
        // executing the statement
        $stmt->execute();
    }else{
        echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
?>

[wpdm_file id=21]

Output:
Student Information inserted.

2.c. MySQLi UPDATE query

<pre><?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be updated
$name = “Olek Predrag”;
$email = “olek@gmail.com”;
$class = 3;
$sid = 4;

// Connecting to the server and selecting database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
    echo 'Unable to connect to database '. $dbh->connect_error;
}else{
    // run query to insert records
    if ($stmt = $dbh->prepare("UPDATE students SET Name = ? , Email = ?, Class = ? WHERE SID = ?”)) {
        // binding user-supplier inputs to the parameters
        $stmt->bind_param("ss", $name, $email, $class, $sid);
        // binding user-supplier inputs to the parameter as integer
        $stmt->bind_param("i", $Class);
        // executing the statement
        $stmt->execute();
        echo "Student information updated.";
    }else{
        echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
?>

[wpdm_file id=22]

Output:
Student Information updated.

2.d. MySQLi DELETE query

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// data to be deletes
$class = 8;

// Connecting to the server and selecting database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
    echo 'Unable to connect to database '. $dbh->connect_error;
}else{
    // run query to insert records
    if ($stmt = $dbh->prepare("DELETE FROM students WHERE Class = ? “)) {
        // binding user-supplier inputs to the parameter as string
        $stmt->bind_param("i", $Class);
        // executing the statement
        $stmt->execute();
        echo "Student information deleted.";
    }else{
        echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
?>

[wpdm_file id=23]

Output:
Student Information deleted.

If you like to know more explanation on how these codes work, check the following tutorials –
How to fetch data from database in php