How to Prevent SQL Injection in PHP

1. Use the Prepared Statements and the PDO to Prevent SQL Injection in PHP
2. Use the Prepared Statements With the Parameterized Query to Prevent SQL Injection in PHP
3. Set the PDO::ATTR_EMULATE_PREPARES Attribute to false to Prevent the SQL Injection

We will introduce a method to prevent SQL injection in PHP using the prepared statements and PHP Data Objects(PDO). We use PDO to establish the database communication in this method. This method sends the data and query separately to the database server, which prevents the mixing of the data and server.

We will introduce a method to prevent SQL injection in PHP using the prepared statements and the parameterized query. We use mysqli to establish the database communication in this method. This method has a similar working mechanism as the first method. The contrasting point is only the mysqli functions that we use to prevent SQL injection.

We will show you an example of how to be safe from SQL injection while using the PDO in PHP by setting the emulation of prepared statements to false.

Use the Prepared Statements and the PDO to Prevent SQL Injection in PHP

We can use the prepared statement along with the PDO to prevent SQL injection in PHP. SQL injection occurs when there is the mixing of the query and the data while sending to the database. In this method, we do not specify the exact value of the data in the SQL statement. We use the placeholders instead. Due to this, the parameterized SQL statements are sent to the server as the first request. We use the prepare() function to achieve this. We bind the exact value of the parameter in the second request to the database server. We use bindValue() function for this purpose. In this way, we send the program at the first request and the data in the second request. If we request the data along with the SQL codes, the user can alter the program and write malicious codes. Thus, it prevents the malicious SQL codes from being injected into the server.

For example, a table users contains the following fields and data.

+----+-----------+----------+------------+
| id | firstname | lastname | dob        |
+----+-----------+----------+------------+
|  1 | bruce     |  rose    | 1998-02-13 |
|  2 | jeff      |  james   | 2000-03-30 |
+----+-----------+----------+------------+

It creates a variable $firstname and assigns it with the name bruce. It creates a variable $sql and writes a query SELECT * FROM users WHERE firstname =:fname; on it.

Do not write the exact value of the data for firstname. Instead, make use of the parameter :fname. Use the $pdo variable to call the prepare() function on the query variable . Replace the value of :fname with the variable $firstname. Execute the statement with execute() function. Check the result with the fetch() function if the credentials match the database. If it does, display the id, firstname, and the lastname of the selected row.

The example below demonstrates the use of a prepared statement. It stores the value of the firstname field in a variable to verify if the credential matches. If the variable had contained some malicious code instead, it would display the message Credentials do no match. This is because the prepared() function takes only the parameterized query and does not allow the exact data. The $pdo variable contains the object of the database connection.

Example Code:

# php 7.*
<?php
$firstname = "bruce";
$sql = "SELECT * FROM users WHERE firstname =:fname ;";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":fname", $firstname);
$stmt->execute();
if(!$result = $stmt->fetch(PDO::FETCH_OBJ)){
    echo "Credentials do no match";
} else {
    echo"Id: ".$result->id. " Name: ".$result->firstname." ".$result->lastname;
}
?>

Output:

Id: 1 Name: bruce rose

Use the Prepared Statements With the Parameterized Query to Prevent SQL Injection in PHP

We can use the prepared statement along with the parameterized query to prevent SQL injection in PHP. We use the object of the mysqli() function to create a database connection. In this method, we use a question mark symbol ? as the data’s placeholders. We use the prepare() function as the method above. We use the bind_param() function for binding the real data in the placeholder. This method follows a similar working mechanism as the method above.

For example, establish a database connection creating an object of mysqli() function, and assign it to a variable $conn. Assign with the name jeff to a variable $firstname. Create a variable $sql and write a query SELECT * FROM users WHERE first name =?;. Use the $conn variable to call the prepare() function on the query variable . Replace the placeholder ? with the variable $firstname. Execute the statement with execute() function. Call the get_result() function to store the result in $result variable. Check if the row exists with the num_rows property and return the message No Rows with the exit() function if the condition fails. Call the fetch_assoc() method and store it in $row variable in a while loop. Display the id, firstname, and the lastname of the selected row.

Example Code:

#php 7.x
<?php
$conn = new mysqli($host, $user, $pwd, $dbName);
$firstname = "jeff";
$sql = "SELECT * FROM users WHERE firstname = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $firstname);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
    while($row = $result->fetch_assoc()) {
    echo"Id: ".$row['id']. " Name: ".$row['firstname']." ".$row['lastname'];
}

Output:

Id: 2 Name: jeff james

Set the PDO::ATTR_EMULATE_PREPARES Attribute to false to Prevent the SQL Injection

Using prepared statements in PDO may not be enough to prevent the SQL injection if we don’t set the PDO attributes correctly. We should set the PDO::ATTR_EMULATE_PREPARES attribute to false to prevent the injection. If we set the attribute to true, the PDO will only emulate the prepared statements instead of using them. Thus, the system will be vulnerable to SQL injection.

For example, create a PDO connection to the database in a variable $pdo. Use the variable to call the setAttribute() function. Set the attribute PDO::ATTR_EMULATE_PREPARES to false.

Code Example:

#php 7.x
<?php
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
?>