How to prevent sql injection in PHP

How to prevent sql injection in PHP

In this article let’s understand how to prevent sql injection in PHP. First let us understand what is sql injection.

SQL injection is a type of security vulnerability that occurs when an attacker is able to manipulate the input of a SQL query in a web application. It happens when the application does not properly validate or sanitize user input before including it in an SQL statement.

Here’s an example to illustrate how SQL injection works. Consider a simple login form where a user enters their username and password:

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

$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";

In this case, an attacker can manipulate the input to execute arbitrary SQL statements. For instance, if the attacker enters ' OR 1=1 -- as the username and leaves the password field empty, the resulting query will be:

SELECT * FROM users WHERE username='' OR 1=1 --' AND password=''

The injected SQL code ' OR 1=1 -- causes the condition to always evaluate to true, effectively bypassing the password check. The double hyphen (--) signifies a comment in SQL, causing the rest of the query to be ignored. As a result, the attacker can log in without a valid password.

To prevent SQL injection, it is crucial to use secure coding practices, such as parameterized queries or prepared statements, to separate the SQL code from the user input. By doing so, the input is treated as data rather than executable code, effectively preventing the injection of malicious SQL statements.

To prevent SQL injection in PHP, you can follow these best practices:

  • Prepared Statements: Use prepared statements (or parameterized queries) with bound parameters instead of dynamically building SQL queries. Prepared statements separate the SQL code from the data, preventing the injected code from being executed. PDO (PHP Data Objects) and MySQLi are two PHP extensions that support prepared statements.

Here’s an example using PDO:

$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute([
    'username' => $username,
    'password' => $password
]);

Here’s an example using MYSQLi:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("server", "username", "password", "database_name");

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// "s" means the database expects a string
$stmt->bind_param("s", $variable);
$stmt->execute();
  • Input Validation: Validate and sanitize user input to ensure it meets the expected format and type. Use appropriate PHP functions like filter_var() or regular expressions to validate user input. This step helps to reject any malicious input before it reaches the database.
$username = $_POST['username'];
if (!preg_match('/^[a-zA-Z0-9]+$/', $username)) { 
   // Handle invalid input 
}
  • Escape Special Characters: If you need to include user input directly in SQL statements (although it’s not recommended), make sure to escape special characters using the appropriate escaping functions for the database connection you are using. For example, for MySQL, you can use mysqli_real_escape_string() or prepared statements.
$username = mysqli_real_escape_string($connection, $_POST['username']);
$password = mysqli_real_escape_string($connection, $_POST['password']);
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

By implementing these measures, you can significantly reduce the risk of SQL injection attacks in your PHP application.