SQL injection from form insert

Question

Possible Duplicate:
Best way to prevent SQL injection?

I'm having trouble understanding how to sanitise php against SQL injection and hope someone would be kind enough to explain to me what I need to change here in order to make my code safe?

<?php
$dbConnection = mysqli_connect('****', '****', '****', 'db');

$query = "INSERT INTO `table` (`1`, `2`, `3`) VALUES ('$_POST[1]', '$_POST[2]', '$_POST[3]')";

if (mysqli_query($dbConnection, $query)) {
echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";
} else {
echo "Error occurred: " . mysqli_error($dbConnection);
}
?>

score 2 · Accepted Answer · answered Nov 29 '12 at 11:24

MySQLi supports prepared statements, which is better than manually escaping:

Since you are using procedural MySQLi, here is an example:

/* create a prepared statement */
if ($stmt = mysqli_prepare($dbConnection, "INSERT INTO `table` (`1`, `2`, `3`) VALUES (?, ?, ?)"))
{

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "sss", $_POST[1], $_POST[2], $_POST[3]);

    /* execute query */
    if(mysqli_stmt_execute($stmt))
    {
        echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";
    }
    else
    {
        echo "Error occurred: " . mysqli_error($dbConnection);
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

score 1 · Answer 2 · answered Nov 29 '12 at 11:24

To prevent SQL injections, you could use prepared statements. You'll need some more mysqli_ functions for that:

With these you can write something like the following:

$dbConnection = mysqli_connect('****', '****', '****', 'db');

// prepare the query
$query = mysqli_prepare( $dbConnection, "INSERT INTO `table` (`1`, `2`, `3`) VALUES (?, ?, ?)";

// bind parameters; 2nd parameter is for data-types
mysqli_stmt_bind_param( $query, "sss", $_POST[1], $_POST[2], $_POST[3] );

// execute query
if ( mysqli_stmt_execute($query) ) {
  echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";
} else {
  echo "Error occurred: " . mysqli_error($dbConnection);
}

score 0 · Answer 3 · answered Nov 29 '12 at 11:22

If you want to keep using the old mysql_* functions look at http://php.net/manual/en/function.mysql-real-escape-string.php

$datapoint1 = mysql_real_escape_string($_POST[1]);
...
$query = "INSERT INTO `table` (`1`, `2`, `3`) VALUES ('$datapoint1', '$datapoint2', '$datapoint3')";

score 0 · Answer 4 · answered Nov 29 '12 at 11:22

0

You can use prepared statements or mysqli_real_escape_string

answered Nov 29 '12 at 11:22

merkushin

481
9
17

SQL injection from form insert

4 Answers4