Prevent SQL Injection in PHP

Do you know that 42% of the attacks faced by public systems are SQL injection attacks? Prevent SQL injection to avoid the most occurring security vulnerability in a public facing system.

SQL injection is the higher most threat faced by any software system. All you need is just parameterised queries to prevent SQL injection and make your application safer.

// use prepared statement to prevent SQL injection
$preparedStatement = $dbConnection->prepare('SELECT * FROM animals WHERE name = ?');
$preparedStatement->bind_param('s', $name); 
$preparedStatement->execute();
$result = $preparedStatement->get_result();
while ($row = $result->fetch_assoc()) {
// Process $row
}

What is SQL injection attack?

SQL injection is a software security vulnerability that allows a user to intercept the SQL query execution and execute queries of their choice.

SQL injection prevention is essential, because this security vulnerability will allow the user to perform unintentional CRUD,

• modify the data
• delete data
• view unauthorised data

In the worst case scenario, the attacking user can take control of the complete software infrastructure using sql injection. Prevent SQL injection to avoid a serious business loss.

Report by Edgescan. 2020 Vulnerability Statistics Report.

When a software security audit is conducted, prevent SQL injection should be the first and foremost item in the checklist. Lethargic approach in SQL injection prevention may lead to data breaches, loss of sensitive data like bank and credit card information, etc.

SQL injection may lead to prolonged unauthorized access to software systems. It will lead to serious financial and personal information loss.

Preventing SQL injection in PHP is even straightforward. The PHP frameworks that we use for database interaction like PDO, MySQLi provide sophisticated mechanisms.

How to detect SQL injection vulnerabilities

SQL injection is the first and foremost serious security threat to a software. To prevent SQL injection we need not invest huge effort, as it can be easily identified and fixed.

There are security scanning tools available that can detect and report SQL injection vulnerability. SQL injection can also easily be detected using manual code reviews and functional tests.

Following are some of the sample test cases. Input the below and check for the application behaviour. If it returns any error and results in an unexpected condition, then you have a task at hand to prevent sql injection.

• Input single quote character ‘ and submit.
• Input a boolean expression that evaluates to true or false. Example are 1=1 or 1=2.

These above two are simple cases. That will detect a SQL injection vulnerability.

How to prevent SQL injection

To prevent SQL injection, it does not require any huge framework implementation or costly processes. All we need to do is avoid concatenation of user input in the SQL queries.

When you get user input via a form, as a security precaution, always filter and sanitize user input using PHP functions before usage.

To prevent SQL injection, use parameterised queries, that should be sufficient in majority of the cases.

Example for SQL injection

The below SQL query takes a parameter. User’s input is directly appended with the query.

// pseudo code describing SQL injection vulnerability
String sqlQuery = "SELECT * FROM animals WHERE type = '"+ $userInput + "'";
Statement sqlStatement = dbConnection.createStatement();
ResultSet result = sqlStatement.executeQuery(sqlQuery);

In the above scenario, ‘userInput’ is directly appended to the SQL query. For example, the user can give an input that can terminate the SQL query and continue with his own sub-query.

"'dummy'; DELETE FROM animals"

If the user inputs the above. The string concatenation in the SQL query makes this an opportunity for SQL injection.

First part of the query is abruptly finished with just a search for value ‘dummy’. Then the second SQL query gets executed which deletes all the records from the ‘animals’ database table.

This second SQL query performs malicious activity via SQL injection. Sot the golden rule is, to prevent SQL injection, use Prepared Statement.

Prepared statement to prevent SQL injection

Prepared statement or parameterized statement allows to declare a SQL query in a template form. Then substitute the parameters using values and execute the query.

In addition to preventing SQL injection, prepared statement also gives better performance. The substitution and execution can be repeated multiple times which improves the efficiency of the SQL query execution. This will result in gain of speed where the same SQL query is executed multiple times.

// prevent SQL injection using prepared statement
PreparedStatement preparedStatement = dbConnection.prepareStatement("SELECT * FROM animals WHERE type = ?");
preparedStatement.setString(1, $userInput);
ResultSet sqlResultSet = preparedStatement.executeQuery();

The above pseudo code explains how the prepared statement can be used to prevent sql injection. PHP’s MySQLi and PDO supports Prepared Statements and it is easy to use.

So always make it a practice to not to append dynamic values to a query, just via string concatenation. To prevent SQL injection, any part of the SQL query should not be constructed using dynamic string concatenation.

Prevent SQL injection in PHP using PDO

PDO is the ‘PHP data objects’, an extension for PHP to accessing databases. PDO is available with PHP by default. Refer CRUD using PHP PDO for a full fledged PDO based PHP example.

// prevent SQL injection in PHP using PDO prepared statement
$sqlStatement = $pdoConnection->prepare('SELECT * FROM animals WHERE type = :type');
$sqlStatement->execute([ 'type' => $type ]);
foreach ($sqlStatement as $resultRow) {
// process $resultRow
}

When you create a PHP PDO database connection, remember to set the attribute ATTR_EMULATE_PREPARES to value false. Because, by default it is set to true.

This instructs PHP PDO to disable emulated prepared statements. This is to ensure that the values are not parsed before sending to the MySQL database server.

$pdoConnection = new PDO('mysql:dbname=animaldb;host=127.0.0.1;charset=utf8', 'dbusername', 'dbpassword');
$pdoConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

In the above code “:type” is the placeholder in the SQL template. This is key to prevent SQL injection.

By using this template substitution method, we are not parsing the value in the PHP side. We just pass the value “$type” as substitution value to the database server.

It is substituted by the database engine and the query is evaluated and executed. This way we prevent SQL injection.

PHP PDO insert example

Following code snippet explains how to prevent SQL injection using PHP PDO during database insert operation.

// example to prevent SQL injection using PHP PDO insert
$preparedStatement = $pdoConnection->prepare('INSERT INTO table (column) VALUES (:columnname)');
$preparedStatement->execute([ 'columnname' => $userInput ]);

Prevent SQL injection in PHP using MySQLi

MySQLi is the ‘MySQL Improved’ extension to access the MySQL database in PHP. It is the improved version of the now obsolete MySQL extension. Refer CRUD with MySQLi using prepared statement for a full fledged example.

// prevent SQL injection in PHP using MySQLi prepared statement
$preparedStatement = $dbConnection->prepare('SELECT * FROM animals WHERE type = ?');
// 's' specifies the variable data type as a 'string'
$preparedStatement->bind_param('s', $type); 
$preparedStatement->execute();
$result = $preparedStatement->get_result();
while ($row = $result->fetch_assoc()) {
// Process $row
}

PHP database util

Following is a database utility that can be used as a generic class for accessing database. It is written for MySQLi extension and can be easily adapted for PHP PDO. The way it is designed is using Prepared Statement and so implicitly it used to prevent SQL injection in PHP.

In the article PHP login script with session, I have demonstrated how to use this DataSource PHP class. Login is a critical component in any website. So while building a website, you can use this PHP database utility class and prevent SQL injection.

<?php
namespace Phppot;

/**
 * Generic datasource class for handling DB operations.
 * Uses MySqli and PreparedStatements.
 *
 * @version 2.3
 */
class DataSource
{

    // PHP 7.1.0 visibility modifiers are allowed for class constants.
    // when using above 7.1.0, declare the below constants as private
    const HOST = 'localhost';

    const USERNAME = 'root';

    const PASSWORD = '';

    const DATABASENAME = 'phpsamples';

    private $conn;

    /**
     * PHP implicitly takes care of cleanup for default connection types.
     * So no need to worry about closing the connection.
     *
     * Singletons not required in PHP as there is no
     * concept of shared memory.
     * Every object lives only for a request.
     *
     * Keeping things simple and that works!
     */
    function __construct()
    {
        $this->conn = $this->getConnection();
    }

    /**
     * If connection object is needed use this method and get access to it.
     * Otherwise, use the below methods for insert / update / etc.
     *
     * @return \mysqli
     */
    public function getConnection()
    {
        $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);
        
        if (mysqli_connect_errno()) {
            trigger_error("Problem with connecting to database.");
        }
        
        $conn->set_charset("utf8");
        return $conn;
    }

    /**
     * To get database results
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return array
     */
    public function select($query, $paramType="", $paramArray=array())
    {
        $stmt = $this->conn->prepare($query);
        
        if(!empty($paramType) && !empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $resultset[] = $row;
            }
        }
        
        if (! empty($resultset)) {
            return $resultset;
        }
    }
    
    /**
     * To insert
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return int
     */
    public function insert($query, $paramType, $paramArray)
    {
        print $query;
        $stmt = $this->conn->prepare($query);
        $this->bindQueryParams($stmt, $paramType, $paramArray);
        $stmt->execute();
        $insertId = $stmt->insert_id;
        return $insertId;
    }
    
    /**
     * To execute query
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     */
    public function execute($query, $paramType="", $paramArray=array())
    {
        $stmt = $this->conn->prepare($query);
        
        if(!empty($paramType) && !empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType="", $paramArray=array());
        }
        $stmt->execute();
    }
    
    /**
     * 1. Prepares parameter binding
     * 2. Bind prameters to the sql statement
     * @param string $stmt
     * @param string $paramType
     * @param array $paramArray
     */
    public function bindQueryParams($stmt, $paramType, $paramArray=array())
    {
        $paramValueReference[] = & $paramType;
        for ($i = 0; $i < count($paramArray); $i ++) {
            $paramValueReference[] = & $paramArray[$i];
        }
        call_user_func_array(array(
            $stmt,
            'bind_param'
        ), $paramValueReference);
    }
    
    /**
     * To get database results
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return array
     */
    public function numRows($query, $paramType="", $paramArray=array())
    {
        $stmt = $this->conn->prepare($query);
        
        if(!empty($paramType) && !empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        
        $stmt->execute();
        $stmt->store_result();
        $recordCount = $stmt->num_rows;
        return $recordCount;
    }
}

Related attacks to SQL injection

• SQL Injection Bypassing WAF
• Blind SQL Injection
• Code Injection
• Double Encoding
• ORM Injection

We will see about each of these in the upcoming articles one by one.

Further reading

• OWASP on SQL injection
• SQL injection prevention cheat sheet
• Websec on SQL injection

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js