How to prevent SQL injection in PHP

SQL injection (or a SQL injection attack) occurs when a user provides SQL code as user input for a Web page, and the SQL code is then executed in the database. SQL Injection is a technique used by the hackers to change SQL statements running at the backend from forged executed SQL commands. Such kind of injections is usually done through input fields of the form causing a bad effect on database.

Step 1: Understanding SQL Injection

Example #1:

  1. SQL Injection is a type of vulnerability in applications that use a SQL database.The vulnerability arises when a user input is used in a SQL Statement:
$name = $_GET['username'];
$query = "SELECT password FROM tbl_user WHERE name = '$name' ";

2. The value a user enters into the URL variable username will be assigned to the variable $name. It’s then placed directly into the SQL statement, making it possible for the user to edit the SQL statement.

$name = "admin' OR 1=1 -- ";
$query = "SELECT password FROM tbl_user WHERE name = '$name' ";

3. The SQL database will then receive the SQL statement as follows:

SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 -- '

This is valid SQL, but instead of returning one password for the user, the statement will return all the passwords in the table tbl_user.

Example #2:

Our simple application will have a database with the following table called ‘users’.

id username password first_name last_name
1 johnsmith $2a$10$SakFH.Eatq3QnknC1j1uo John Smith
2 maryjohnson $2a$10$hA/hwCzhr6F23BsbRZBjd Mary Johnson
3 jameswilliams $2a$10$OkV5tCMMsy91pkkMXHa James Williams
4 lindabrown $2a$10$2NgAjstT9NcN58zMcF/Rq Linda Brown

The following is an example of a legitimate HTTP request that could be made to the  vulnerable application.

http://localhost/?id=1
> johnsmith

The following is an example of a malicious HTTP request that could be made to the vulnerable application above.

http://localhost/?id=-1 UNION SELECT password FROM users where id=1
> $2a$10$SakFH.Eatq3QnknC1j1uo

Step 2: Using “Prepared Statements” to prevent SQL injection in PHP

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

Prepared Statements in PDO (for any supported database driver):

The following example uses prepared statements and bound parameters in PDO:

 <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
    VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    // insert a row
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();

    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
$conn = null;
?>

Prepared Statements in MySQLi (for MySQL):

The following example uses prepared statements and bound parameters in MySQLi:

 <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Step 3: Using real_escape_string for Escapes special characters

The real_escape_string() function escapes special characters in a string for use in an SQL statement. This function has three different modes:

//Example of PHP default real_escape_string 
$firstname = mysql_real_escape_string($_POST['firstname']);


//Example of MySQLi
$firstname = mysqli_real_escape_string($connect,$_POST['firstname']);


//Example of PDO
$firstname = $connect->quote($_POST['firstname']);

Finally, by checking and study the links in this article, you can access more resources.