PHP SQL Injection Example and How to Avoid It

Lucas
August 08, 2015

SQL Injection is a technique for injecting malicious SQL code through loopholes in the application. This is a serious flaw that completely compromises the security of the site.

Example of SQL Injection

Let’s suppose that a hacker accesses a page in any store that has this security hole.

A survey of this kind would basically result in a consultation:

SELECT nome, preco
FROM produtos
WHERE nome = 'XBOX One'

Considering that in this research there is a flaw that allows us to inject SQL code, the hacker could then simply inject any SQL code:

Which would result in the consultation:

SELECT nome, preco
FROM produtos
WHERE nome = ''; DROP TABLE produtos; --'

i.e. the product table would be destroyed!

To get around the above problem we could use regular expressions to invalidate characters that could cause us problems, but below I’ll show you a simple PHP feature that will be very useful for avoiding this type of attack.

How to avoid SQL Injection using PHP PDO (PHP Data Object)

PDO is an interface for accessing databases in PHP.

We’ll use a simple feature that will help us get around the problem mentioned above.

<?php

class Database {

    private $host = 'localhost';
    private $db = 'loja';
    private $user = 'root';
    private $pass = '123';
    private $pdo;

    public function __construct() {
        $this->pdo = new PDO("mysql:host=$this->host;dbname=$this->db", $this->user, $this->pass);
        if (!$this->pdo)
            throw new Exception("Erro ao conectar com BD");
    }

    public function buscarProduto($nome) {
        $stmt = $this->pdo->prepare("SELECT nome, preco FROM produtos WHERE nome = ?");
        $stmt->bindParam(1, $nome, PDO::PARAM_STR);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row)
            return $row;
        else
            throw new Exception("Erro - Arquivo não encontrado", 12);
    }
}

?>

The PDO trick to avoid SQL Injection is the bindParam() function, which “escapes” characters that may be offensive to your Query.

PDO’s uses go beyond protecting us from SQL Injection, as it offers us other features such as Transactions and so on. For more information, go to http://php.net/manual/pt_BR/book.pdo.php.

I would like to emphasize that SQL Injection is an advanced subject, the example cited in this post is a simple case of the situation.

Tags:
pdo
PHP
sql injection