How to prevent SQL-injection when using MySQLi

Question

I'm building a simple form and want to secure this against the following SQL-injections: - blind-injection - boolean-based - blind injection - UNION query-based - Stacked queries - error-based injections

I thought that I had it all secured, but when I run SQL-map it still exploits my database.

<?php

$input = $_GET['input'];

if ($input) {
    $db = mysqli_connect("localhost", "sec", "dubbelgeheim", "bookshop");

// Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $escaper = real_escape_string($input);
    $statement = $db->prepare("SELECT * FROM productcomment WHERE ProductId = ? LIMIT 1");
    $statement->bind_param("s", $escaper);
    $statement->execute();
    $result = $statement->get_result();
    $statement->close();
    $count = $result->num_rows;
    if ($count > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "Product:" . $row['ProductId'] . "<br>";
            echo "Annotation:" . $row['Comment'] . "<br>";
            echo "TestOK!<br>";
        }
    } 
    else {
        echo 'No record!';
    }
    $result->free();
    $db->close();
}
?>

Did I forget something?

Can anyone help?

Thanks in advance!

`"it still exploits my database"` Can you give more detail about that? I don't see anything here that can be exploited. — m59, Mar 03 '15 at 20:05
Using prepared statements alone doesn't guarantee against injection. Make sure you've taken XSS exploits into account. — Funk Forty Niner, Mar 03 '15 at 20:07
By the way; `real_escape_string()` [isn't a core PHP function](http://php.net/manual-lookup.php?pattern=real_escape_string&scope=quickref). `$city = $mysqli->real_escape_string($city);` is Object oriented style http://php.net/manual/en/mysqli.real-escape-string.php - so, show us what that function does. — Funk Forty Niner, Mar 03 '15 at 20:10
@GjertGjersund PDOException: SQLSTATE[HY000] [1045] Access denied for user: '@localhost' (Using password: YES) was my kind of issue. I could'nt solve it anyway despite of trying the default of w3schools but that didn't help so I tried an alternative. — NielsDePils, Mar 03 '15 at 20:32
@theMAster: what does `real_escape_string()` do? If its analgous to `mysqli_real_escape_string()` then you shouldnt be using it on the bound parameters. If it does something else you should let us see the code and you might also want to rename it s it is less confusing for others who might work with your code int he future. — prodigitalson, Mar 03 '15 at 20:45
@theMaster: Did you try your PDO connection with `127.0.0.1` instead? Its possible you only have it set up to use TCP instead of socket, or that you need to change the location of the socket file in the config. — prodigitalson, Mar 03 '15 at 20:47

score 0 · Answer 1 · answered Mar 03 '15 at 21:56

0

Your problem is caused by you displaying mysqli_connect_error(). This is OK for testing but should NOT be used in production code. You also don't need $escaper = real_escape_string($input);.

Try this instead

/* check connection */
if (mysqli_connect_errno()) {
    file_put_contents('MySQLiErrors.txt',date('[Y-m-d H:i:s]'). mysqli_connect_error()."\r\n", FILE_APPEND); 
    exit();
}else{
     $statement = $db->prepare("SELECT * FROM productcomment WHERE ProductId = ? LIMIT 1");
     $statement->bind_param("s", $input);


}

answered Mar 03 '15 at 21:56

david strachan

7,174
2
23
33

Thanks for your reply, but it didn't help. I could still enter my db using sqlmap – NielsDePils Mar 04 '15 at 08:50
could you please take a look at this topic I recently posted? http://stackoverflow.com/questions/28850098/my-pdo-connection-doesnt-work?noredirect=1#comment45967698_28850098 – NielsDePils Mar 04 '15 at 09:15

How to prevent SQL-injection when using MySQLi

1 Answers1