How to protect your PHP website against SQL injection

Mysql injection is a method to attack the database by inserting extra sql statements, that when executed could help an attacker do what he wants with the database.

An example to mysql injection in PHP

<?php
$mysqli = new mysqli("localhost", "username", "password", "db");
 
$id = $_GET['id'];
$pass = $_GET['pass'];
$mysqli->query("SELECT title,email FROM users 
  WHERE userid='".$id."' and pass='".$pass."'");
?>

You are expecting the user to enter use to enter a text value, which would result in an example like this:

SELECT title,email FROM users 
  WHERE userid='john' AND password='123'

but an attacker could pass to ‘id’ something like this:

5' --

and the result query would be like this:

SELECT title,email FROM users 
  WHERE userid='5' --' and pass='123'

the — characters are considered comment starters in mysql,
in this example, the user will be able to access the user record without entering a valid password.

The solution to this is using mysql prepared statements:

<?php
$mysqli = new mysqli("localhost", "username", "password", "db");
 
$id = $_GET['id'];
$pass = $_GET['pass'];
if ($stmt = $mysqli->prepare("SELECT title,email FROM users 
  WHERE userid=? and password=?")) {
 
    $stmt->bind_param("s", $id);
    $stmt->bind_param("s", $pass);
 
    $stmt->execute();
 
    $stmt->bind_result($title, $email);
 
    /* fetch value */
    $stmt->fetch();
 
    printf("%s - %s\n", $title, $email);
}
?>

You can read more about using mysqli prepared statements:

http://php.net/manual/en/mysqli.prepare.php

You can also use PDO instead of mysqli:

http://php.net/manual/en/pdo.prepare.php

Leave a Reply

Your email address will not be published. Required fields are marked *

(Your message will only be visible after moderation)