3 Smart Ways to Prevent SQL Injection in PHP

Introduction

Ignoring SQL injection mitigation skills can be disastrous, especially for a junior developer who has recently been hired by a tech business. Trust me, you don't want to be in this nasty situation on your first job.

This is why it is critical to have the skills of preventing SQL injection attacks. This guide will walk you through three different strategies for protecting your PHP project from SQL assaults.

If you're ready, let's get to the heart of this instructional...

What is SQL Injection

SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database.

Also, it can be an attack that poisons dynamic SQL statements to comment out certain parts of the statement or appends a condition that will always be true. It takes advantage of the design flaws in poorly designed web applications to exploit SQL statements to execute malicious SQL code.

How SQL Injection Works

A SQL injection attack targets vulnerabilities in dynamic SQL statements. Think of a dynamic SQL statement like a multivariate function in mathematics, in which the parameters are fixed, while the values substituted in the independent variables determine the result.

SQL injection is performed by using a structured query that instigates the desired response. The response is essential for the attacker to understand the database architecture and to access the secured information of the application.

An Example of SQL Injection

An attacker wishing to execute SQL injection manipulates a standard SQL query to exploit non-validated input vulnerabilities in a database. There are many ways that this attack vector can be executed, several of which will be shown here to provide you with a general idea about how SQLI works.

For example, the above-mentioned input, which pulls information for a specific product, can be altered to read http://www.estore.com/items/items.asp?itemid=999 or 1=1.

As a result, the corresponding SQL query looks like this:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemNumber = 999 OR 1=1

And since the statement 1 = 1 is always true, the query returns all of the product names and descriptions in the database, even those that you may not be eligible to access.

Attackers are also able to take advantage of incorrectly filtered characters to alter SQL commands, including using a semicolon to separate two fields.

For example, this input http://www.estore.com/items/iteams.asp?itemid=999; DROP TABLE Users would generate the following SQL query:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemNumber = 999; DROP TABLE USERS

As a result, the entire user database could be deleted.

Another way SQL queries can be manipulated is with a UNION SELECT statement. This combines two unrelated SELECT queries to retrieve data from different database tables. For example, the input http://www.estore.com/items/items.asp?itemid=999 UNION SELECT username, password FROM USERS produces the following SQL query:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemID = '999' UNION SELECT Username, Password FROM Users;

Using the UNION SELECT statement, this query combines the request for item 999’s name and description with another that pulls names and passwords for every user in the database.

How to Curb SQL Injection with Examples

Photo by Tima Miroshnichenko from Pexels: https://www.pexels.com/photo/man-and-woman-hacking-a-computer-system-5380655/

The following examples illustrate how to prevent SQL injection in your next PHP project.

Example #1: Using PDO (for any supported database driver)

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute([ 'name' => $name ]);

foreach ($stmt as $row) {
    // Do something with $row
}

Example #2: Using MySQLi (for MySQL)

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

Example #3: Using a Customized method

$safe_variable = mysqli_real_escape_string($_POST["user-input"], $dbConnection);
mysqli_query($dbConnection, "INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

And there you have it, three fantastic examples to help you prevent SQL injections in PHP.

Conclusion

We have come to the conclusion of this tutorial, hopefully, you’ve gained a ton of value.

Understanding how to prevent SQL injection attacks is a vital skill you should have in your arsenal as a developer. The tech space is fierce, and attackers will not think twice to jeopardize your system, so you must be on guard all the way.

If you need help with a PHP project, feel free to consult me through my website.

Till next time, enjoy!

About Author

Emmanuel Okolie kick-started his journey as a software engineer in 2020. Over the years, he has grown full-blown skills in JavaScript, PHP, HTML & CSS, and more.

He is currently freelancing, building websites for clients, and writing technical tutorials teaching others how to do what he does.

Emmanuel Okolie is open and available to hear from you. You can reach him on LinkedIn, Facebook, Github, or on his website.

Also published here.