Categories

How to prevent SQL injections in PHP

Posted on: February 14, 2016 by Dimitar Ivanov

What is SQL Injection?

SQL Injection is an attack that uses code injection through an unfiltered user input data. Its target is data-driven applications. A successful SQL injection can damage your data, put on a risk the sensitive information from your database, or even gets control over your SQL server.

A practical example of an SQL injection is a form submit with malicious data.

$email = "info@domain.com' OR 1=1";
mysql_query("SELECT * FROM users WHERE email = '$email'");

// will execute:
// SELECT * FROM users WHERE email = 'info@domain.com' OR 1=1

Avoidance Techniques

To prevent SQL injections always escape user input data using prepared statements, type casting or database-specific string escape function (e.g. mysql_real_escape_string().

  • PDO prepared statements
    # using named placeholders
    $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)");
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':pswd', $pswd);
    $stmt->execute();
    
    # or
    $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (:email, :pswd)");
    $stmt->execute(array(
        ':email' => $email, 
        ':pswd' => $pswd
    ));
    
    # using positional ? placeholders
    $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)");
    $stmt->bindParam(1, $email);
    $stmt->bindParam(2, $pswd);
    $stmt->execute();
    
    # or
    $stmt = $dbh->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)");
    $stmt->execute(array($email, $pswd));
    
  • MySQLi prepared statements
    $stmt = $mysqli->stmt_init();
    if ($stmt->prepare("INSERT INTO users (email, pswd) VALUES (?, ?)")) {
        $stmt->bind_param("ss", $email, $pswd);
        $email = $_POST['email'];
        $pswd = $_POST['pswd'];
        $stmt->execute();
        $stmt->close();
    }
    
  • Change data type
    settype($id, 'integer');
    $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
    
  • Type casting
    $id = (int) $id;
    $result = $mysqli->query("SELECT * FROM invoices WHERE id = $id");
    
  • Data format
    $result = $mysqli->query(sprintf("SELECT * FROM invoices WHERE id = %u", $id));
    
  • Escape special characters
    $id = $mysqli->real_escape_string($id);
    $result = $mysqli->query("SELECT * FROM invoices WHERE id = '$id'");
    
See also
References
Share this post

If you have questions about preventing SQL injections in PHP, leave a comment below. And do not forget to share this article. Thanks so much for reading!


0 Comments

Leave a comment

Captcha