Easy Guide to Preventing SQL Injections PHP

SQL injection is a critical security vulnerability commonly found in web applications, particularly those developed using PHP and interacting with a backend database.

This attack allows unauthorized users to manipulate data or execute commands on the server, making prevention in PHP essential for web application security.

By preventing SQL Injections, PHP developers can effectively safeguard sensitive data

In this article, we’ll explore the causes of SQL injection and discuss security measures to prevent it in web apps deployed on various PHP web hosting servers.

Optimize Your Database Performance With Cloudways

Sign up now and master the art of connecting MySQL databases with PHP hosting today!

TRY NOW

What Is SQL Injection?

SQL Injection is a technique used by hackers to change SQL statements running at the backend from forged executed SQL commands. Such injections are usually done through the form’s input fields, causing a bad effect on the database. This results in the loss of sensitive information from the database.

Through such tactics, attackers input vulnerable data to an SQL interpreter that executes unintended commands. Using such PHP MySQL injections, attackers may insert, update, or even delete data from the database.

Just recently, an attack occurred where a hacking group, ResumeLooters, stole over two million email addresses and personal data from 65 websites using SQL injection. They targeted retail and recruitment sites, highlighting the need for robust security measures to prevent such breaches.

Following a Test-Driven Development approach (TDD) in software development can improve code quality and reduce the likelihood of certain types of bugs, including vulnerabilities.

We’ll look at the solutions to prevent SQL injection in PHP in a bit. Just so you know, if you’re a Cloudways customer, you don’t need to worry about vulnerabilities, as we offer a Dedicated Server-Level Firewall, Automated Protection with Fail2ban, Regular Security Patches and updates, Bot Protection, Vulnerability Scanner (WP), and more.

Read More About: Ultimate Guide to PHP Security Best Practices

What Is TDD?

Test-Driven Development (TDD) is a practice where you write a test before writing the corresponding code. This approach ensures that the code is designed to meet the requirements outlined in the test.

By writing test cases first, developers can identify potential bugs early in the development process. The cycle typically involves writing a failing test, writing the minimum amount of code necessary to pass the test, and then refactoring the code to improve its design while ensuring that all tests continue to pass.

This iterative process helps improve code quality and reduce the number of bugs encountered during development.

Check out the visual representation of the TDD process below:

What Causes SQL Injection?

While coding, we should follow best practices to avoid SQL injection in PHP. Some of the causes which can affect these attacks are:

1. Incorrectly filtered space characters
2. Incorrect Type Handling
3. Passing unsanitized data to DB
4. Not using full Unicode encoding
5. Mixing of the code and data.
6. Use of quotation marks to delimit strings

These are some causes you might want to watch out for while coding to avoid SQL injections.

Let’s See the Examples

The following PHP SQL injection example will help you better understand the concept of SQL injections:

Example # 1

Suppose there is a form containing two text fields: one for the username and one for the password, along with a login button. The backend PHP code will be as follows:

<?php
$userName=$_POST['userName'];
$password=$_POST['password'];
$sqlQuery="SELECT * FROM users WHERE user_name='".$username."' AND user_password='".$password"';";
?>

The above code contains a loophole. If a user enters ‘ or ‘a’=’a’ ‘or’, then the variable $password will have the value ‘ or ‘a’=’a’ ‘or’.

In this way, the above query will be updated as:

<?php
$sqlQuery="SELECT * FROM users WHERE user_name='".$username."' AND user_password='' or 'a'='a';";
?>

In the above example, the statement ‘a=a’ is always true. Therefore, the condition is satisfied without needing the actual password to match, leading to potential security vulnerabilities.

Solution for Example #1: Use Parameterized Queries

Parameterized queries are pre-defined SQL statements that include placeholders for user input. When executed, these queries accept actual user input separately.

Parameterized queries are widely supported across various database interfaces and PHP frameworks (like PDO and mysqli), enhancing application security against SQL injection attempts.

Incorporating parameterized queries is crucial for bolstering PHP applications against SQL injection. By segregating SQL statements and user input, these queries effectively mitigate potential injection attacks.

Like so:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

// Define the SQL query with placeholders
$sql = "SELECT * FROM users WHERE username = :username AND password = :password";

// Prepare the statement
$stmt = $pdo->prepare($sql);

After preparing the statement, we bind the actual user input values to the placeholders:

$username = $_POST["username"];
$password = $_POST["password"];

// Bind values to placeholders
$stmt->bindParam(":username", $username, PDO::PARAM_STR);
$stmt->bindParam(":password", $password, PDO::PARAM_STR);

Here, bindParam binds the variables $username and $password to their corresponding placeholders. To enhance security, we also specify the data type (PDO::PARAM_STR for string).

Finally, we execute the prepared statement with the bound values:

$stmt->execute();

// Fetch results (if applicable)
$user = $stmt->fetch(PDO::FETCH_ASSOC);

After implementing parameterized queries, the PHP application safeguards user data effectively, strengthening its defenses against SQL injection risks.

Example # 2

In SQL programming, we often build queries dynamically by adding data on the go. This data can mess up our query if not handled properly, as demonstrated in SQL injection examples, especially in PHP/MySQL setups.

For instance, consider this regular query:

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

This gives us a regular query:

SELECT * FROM users where id=1

Now, let’s introduce some problematic data:

$spoiled_data = "1; DROP TABLE users;";
$query   = "SELECT * FROM users where id=$spoiled_data";

This results in a malicious sequence:

SELECT * FROM users where id=1; DROP TABLE users;

The issue arises because we’re directly adding data into the query, effectively making it a part of the program. Depending on the data added, we might get a normal output or unintended consequences like deleting a table.

Solution for Example #2: Use PHP Data Objects (PDO)

Preventing SQL injection in PHP, especially when using PDO (PHP Data Objects), is crucial for ensuring the security of your applications. PDO provides prepared statements, which are powerful tools for defending against SQL injection attacks.

You can follow these steps when using PDO. First, establish a PDO connection to your database:

<?php
$host = 'your_host';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

// Establish PDO connection
try {
 $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 // Set PDO to throw exceptions on error
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
 die("Error connecting to database: " . $e->getMessage());
}
?>

Prepared statements are SQL statements that are precompiled by the database server. They allow you to separate SQL code from data, effectively preventing SQL injection attacks. Here’s how you can use prepared statements with PDO:

<?php
// Assume $userInput is coming from user input (e.g., form submission)
$userInput = $_POST['username'];

// Prepare a SQL statement with a placeholder
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");

// Bind the parameter to the placeholder
$stmt->bindParam(':username', $userInput);

// Execute the prepared statement
$stmt->execute();

// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Use the results as needed
foreach ($results as $row) {
 echo "Username: " . $row['username'] . "<br>";
}
?>

By using prepared statements and parameter binding, you can effectively prevent SQL injection attacks in your PHP applications.

Secure Your Data with Cloudways PHP Hosting

Protect your data with Cloudways’ automated backups, robust firewall protection, and real-time monitoring. Sign up now for top-tier PHP Hosting!

Start Today

How to Prevent SQL Injection in PHP – Step By Step

To prevent SQL injection in PHP, you can follow a comprehensive step-by-step approach that includes input validation, prepared statements, and proper handling of query results. Here’s a detailed guide:

Step 1: Validate Input

Before processing any user input, validate and sanitize it to ensure it meets your application’s requirements and is safe for database operations.

$username = isset($_POST['username']) ? $_POST['username'] : '';
// Validate input (e.g., check if it's not empty, contains valid characters, etc.)
if (empty($username)) {
    // Handle validation failure (e.g., show error message)
    die("Username is required.");
}
// You can apply further validation as needed...

Step 2: Prepare a Query

Construct your SQL query as a string, ensuring it’s clear of any user-supplied data. Use placeholders (? or named placeholders like username) instead of directly embedding user input.

$sql = "SELECT * FROM users WHERE username = ?";

Step 3: Create the Prepared Statement

Create a prepared statement using PDO to compile the SQL query on the database server. This step prepares the SQL statement for execution while separating the SQL logic from the data.

$stmt = $pdo->prepare($sql);

Step 4: Bind the Parameters to the Prepared Statement

Bind the validated user input to the placeholders in the prepared statement. This process automatically handles proper escaping and prevents SQL injection.

$stmt->bindParam(1, $username, PDO::PARAM_STR);
// You can also use named placeholders:
// $stmt->bindParam(':username', $username, PDO::PARAM_STR);

Step 5: Execute Your Query

After preparing the SQL statement and securely binding parameters to the placeholders, you execute the prepared statement using the execute() method. This step substitutes the securely bound parameters into the SQL query at execution time.

$stmt->execute();

Step 6: Fetch the Result

This step involves fetching the result set from the executed SQL statement using suitable fetch methods offered by PDO.

// Fetch the result set into an associative array
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Process the results, if any
foreach ($results as $row) {
    // Output each username from the result set
    echo "Username: " . $row['username'] . "<br>";
}

In this code snippet:

• fetchAll(PDO::FETCH_ASSOC) retrieves all rows from the result set into an associative array. Each element in the array corresponds to a row in the result set, with column names as keys.
• The foreach loop iterates over each row in the result set.
• Within the loop, $row[‘username’] accesses the value of the ‘username’ column for each row, which is then echoed to output it.

Step 7: Validate Your Application

Perform comprehensive validation and testing of your application to ensure that all input validation, SQL queries, and output handling are secure and functioning correctly. Regularly review and update your security practices to mitigate potential risks.

How Can Cloudways Help Mitigate SQL Injection Attacks?

Cloudways offers several features that can help mitigate security threats on your server, including:

Layer 3 & 4 DDoS Mitigation: This shields your server from malicious traffic floods that could take your site down—Cloudways partners with top cloud providers to deal with these attacks before they reach your server.

Dedicated Server-Level Firewall: The server-level firewall can be configured to block unauthorized access attempts, including those aimed at exploiting SQL injection vulnerabilities.

Automated Protection with Fail2ban: Fail2ban monitors login attempts and can automatically block suspicious activities, including those indicative of SQL injection attempts.

Patchstack Integration: Cloudways integrates with Patchstack, a vulnerability scanner that continuously checks your applications for weaknesses.

Regular Security Patches: Cloudways keeps the underlying server software up-to-date with the latest security patches, fixing vulnerabilities identified by OS providers.

Bot Protection: While primarily aimed at bot traffic, bot protection mechanisms can also help mitigate automated SQL injection attacks.

Vulnerability Scanner (WP): The Patchstack collaboration provides a vulnerability scanner that can detect and alert you to potential SQL injection vulnerabilities in WordPress core, plugins, or themes.

Conclusion

Staying secure from SQL injection attacks is crucial, as attackers can bypass security measures of applications and use SQL queries to modify, add, update, or delete records in a database.

In this guide, we discussed practical solutions for preventing SQL injection PHP, emphasizing parameterized queries and PHP Data Objects (PDO).

We’ve also outlined a step-by-step approach to prevent SQL injection in PHP, covering input validation, prepared statements, and secure query handling.

Additionally, we’ve highlighted how Cloudways offers security features like DDoS mitigation, firewall, Fail2ban, patches, and vulnerability scanning. By implementing these measures, developers can reduce SQL injection risks and ensure data integrity.

Questions? Feel free to ask in the comments section below.