HOW TO PREVENT SQL INJECTION IN PHP?

jayprakash sharma

Total Post:117

Points:821
Posted by  jayprakash sharma
PHP 
PHP
 1583  View(s)
Ratings:
Rate this:
Hi Everyone!

If user input is inserted into an SQL query directly, the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')");
That's because the user can input something like value'); DROP TABLE table;--, making the query:

INSERT INTO table (column) VALUES('value'); DROP TABLE table;--')

What should one do to prevent this?

Thanks in advance!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: How to prevent SQL injection in PHP?

    Hi Jayprakash!

    Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This 

    way it is impossible for an attacker to inject malicious SQL.

    You basically have two options to achieve this:

        Using PDO:

        $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

        $stmt->execute(array(':name' => $name));

        foreach ($stmt as $row) {
            // do something with $row
        }

        Using mysqli:

        $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
        $stmt->bind_param('s', $name);

        $stmt->execute();

        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            // do something with $row
        }

    PDO

    Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared 

    statements. An example of creating a connection using PDO is:

    $dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

    $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    I hope it resolve your problem!

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!