What SQL injections are, and how to prevent them as a PHP Developer

SQL injection attacks are very common in web applications. When they are not properly guarded against, they can hurt your application in bad ways as it allows unauthorized users gain access to sensitive data stored in your database, and do harmful operations like deleting or manipulating important information.

In this article, i would explain what SQL injection is, why you need to prevent it, and how you can prevent it in your PHP application.

What is SQL Injection?

SQL Injection is an attack that uses malicious SQL queries to manipulate data stored in the database. It is one of the most common techniques used to hack a web application that about 42% of hackers attempt to access a web application through SQL Injection as indicated on this website.

These malicious SQL queries are sent whenever a user submits a form or goes to a specific url which gives them the opportunity to pass any harmful data to our backend script.

Why do you need to prevent it?

Umm because it is very dangerous 🤷‍♀️. If SQL Injections are not prevented, and your application falls victim to its damage, the result can be very bad. No one would like to hear their personal information has been compromised, or even worse it doesn't exist anymore! 😱, you don't want to be in that kind of uncomfortable situation. That's why it is important to prevent this attack from affecting your web application.

So how can you prevent SQL Injection attacks?

1. Separate data from your SQL Queries

One of the mistakes developers make a lot is that they don't usually seperate SQL queries from the data the user sends (I still make this mistake sometimes 😅). This needs to be avoided at all costs because a user might send data that gives them access to information stored in your database. Take this code for example

<?php
 // Assume $pdo is already declared

 $username = $_POST["username"];
 $stmt = $pdo->prepare("SELECT * FROM users WHERE 
 username = $username");
 $stmt->execute();
 return $stmt->fetchAll();

There is a problem here (If you noticed), we are injecting the $username variable directly into the SQL query. This opens us up to SQL injection attacks very easily as someone can send something that executes with your SQL query. Let me give an example, assuming the user submits the form, and sends this to us '' OR '1 == 1', this is what the SQL query will now look like

<?php
 $username = $_POST["username"];

 /* $stmt = SELECT * FROM users WHERE username = '' OR '1 == 1' */
 $stmt = $pdo->prepare("SELECT * FROM users WHERE 
 username = $username");
 $stmt->execute();
 return $stmt->fetchAll();

This essentially means that unknowingly to us, we are going to fetch the entire users table for the user!, because 1 is always equal to 1. Seperating what the user sends from the SQL query will help to prevent this kind of thing from happening, and with PDO, we can do this in 2 ways by using

• Positional parameters
• Named parameters

Positional Parameters

<?php
 $username = $_POST["username"];

 $stmt = $pdo->prepare("SELECT * FROM users WHERE 
 username = ?");
 $stmt->execute([$username]);
 return $stmt->fetchAll();

With positional parameters, you use a question mark as a placeholder to represent the data the user sends. To give the placeholder a value, you pass an array to the execute() method containing the value that would replace the question mark when the SQL query runs.

Named Parameters

<?php
 $username = $_POST["username"];

 $stmt = $pdo->prepare("SELECT * FROM users WHERE 
 username = :username");
 $stmt->bindValue(":username", $username);
 $stmt->execute();
 return $stmt->fetchAll();

With named parameters, you specify a name as the placeholder (e.g :username), ensure you use the colon before specifying the name. To give the placeholder a value, we use the bindValue() method to bind (i.e connect) the placeholder to the appriopriate value.

Using this method to seperate our SQL query from what the user sends makes it more cleaner and secure. Even if the user tries to pass any harmful code to our script, it would be properly handled.

2. Validate user input

Another thing to do, which may sound like a no brainer is to properly sanitize what the user sends. This is way too important to do, even before passing it to your SQL query. It helps to remove any form of special characters the user might enter into the form. One of the several functions in PHP that helps in escaping special characters is htmlentities(), it converts any html tag into a string, so it doesn't get parsed by the browser.

<?php
  $username = "<script>alert('Hacked!')</script>";
  $newUsername = htmlentities($username, ENT_QUOTES);
  echo $newUsername; // returns &lt;script&gt;alert('Hacked!')&lt;/script&gt;

Conclusion

One rule of thumb every developer has learnt is to never trust what users send from a web form, because some of them just want to destroy your application and business. It's important to know you can still get hacked even though you prevent SQL injection attacks, but preventing SQL injection attacks is still one of the most important steps to take in protecting your application and database.

If you liked this article, you can follow me on twitter and linkedin for more content.