How to prevent mysql injection 1=1 using msqli?

Question

Possible Duplicate:
Best way to prevent SQL Injection in PHP

In my website users can submit posts and delete their posts.

To delete a post, they follow the link /posts.php?deletid=X where X is the id of the post in database (for example: 1).

When clicked, it will run the following:

if(isset($_GET['deleteid'])) { 
    $deleteid = $_GET['deleteid'];
        $sql = "DELETE from `posts` WHERE `id`=".mysql_real_escape_string($deleteid).";";
        $query = mysql_query($sql);
        header('Location: posts.php');
        exit(); 
}

The problem is that it's vulnerable to the 1=1 SQL injection. If they type into the address bar /posts.php?deletid=1 OR 1=1; it will delete all posts on database.

In this question: How can I prevent SQL injection in PHP?, I realized I need to use mysqli statements, and I tried to make it work but with no success..

Can someone please tell me exactly how I can prevent this with mysqli?

Answer 1

You need to have the value in quotes for mysql_real_escape_string to have any useful effect.

$sql = "DELETE from `posts` WHERE `id`='".mysql_real_escape_string($deleteid)."'";

Alternatively, instead of mysql_real_escape_string, which is intended for strings, try intval.

Answer 2

With MySQLi and prepared statements you do not need to worry about this, as a parameter cannot be replaced by 1 OR 1=1 (or if it is provided as the parameter value, then it’s interpreted as a string).

Answer 3

By using prepared statements, the mysql_* functions are on there way out and soon tobe deprecated, one should not be writing new code with these functions, refactor your code.

PDO

<?php 
$db = new PDO("mysql:host=localhost;dbname=yourDB", $username, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** prepare the SQL statement ***/
$query = $db->prepare("DELETE from `posts` WHERE `id`=:id;");

/*** bind the paramaters ***/
$query->bindParam(':id', $deleteid, PDO::PARAM_INT);

/*** execute ***/
$query->execute();

header('Location: posts.php');
exit(); 
?>

mysqli

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
/* create a prepared statement */
if ($stmt = $mysqli->prepare("DELETE from `posts` WHERE `id`=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("i", $deleteid);

    /* execute query */
    $stmt->execute();

    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();
header('Location: posts.php');
exit(); 
?>

Answer 4

One thing first: if you can, it would be wise not to use mysql_* but e.g. mysqli_* functions or PDO, since the first are outdated. There you can use placeholders (?) instead of string concats. You don't have to care for quoting yourself there.

The easiest option in your example code would be to run all numbers through integer parsing (use intval).

if(isset($_GET['deleteid'])) { 
    $deleteid = $_GET['deleteid'];
            $sql = "DELETE from `posts` WHERE `id`=".intval($deleteid).";";
            $query = mysql_query($sql);
            header('Location: posts.php');
            exit(); 
}