CODEWITHSUNDEEP
phpmysqlsqlcode-injection
wiredmark
wiredmark

Reputation: 1108

Mysqli prepared statement (SQL injection prevention)

after stopping the use of deprecated mysql_* functions, I switched to mysqli. But then, I noticed that non-prepared statements are non-secure against SQL injection. Then, I changed again my code.

What I had was the following function that checks if the variable $ID exists in the database and prints the value of title for that row:

function showPostTitle($ID, $mysqli) {
    $result = $mysqli -> query("SELECT ID, title FROM blog where ID = $ID");
    $row = $result -> fetch_array(MYSQLI_BOTH);
    echo $row['title'];
}

I changed it to this:

function showPostTitle($ID, $mysqli) {
    $stmt = $mysqli -> prepare("SELECT ID, title FROM blog WHERE ID = ?");
    $stmt -> bind_param("i", $ID);
    $stmt -> execute();
    $stmt -> bind_result($ID, $title);
    $stmt -> fetch();
    print_r($title);
    $stmt -> free_result();
}

My question is: is this the correct way to implement prepared statements? Plus, am I safe now from SQL Injections? Big thanks to whoever will answer this question :)

Upvotes: 4

Views: 8961

Answers (2)

Ingmar Boddington
Ingmar Boddington

Reputation: 3500

Your mysqli logic seems fine, there are some examples in the PHP manual here in case you have not seen them.

Why are you selecting the ID when not consuming it though? Also you don't really need to bind a result when it's only going to have one row returned in the full result set as I assume will happen in this case (ID is unique index in the table), use get_result instead.

Using mysqli prepare will protect against all the common injection attacks but not 0-day style stuff which hasn't made it to the driver yet.

Upvotes: 4

Rob
Rob

Reputation: 1860

Take a look at this post:

Are PDO prepared statements sufficient to prevent SQL injection?

It's using PDO instead of MySQLi, which is solving the same problem by creating prepared statements.

Sorry for not answering your question, but just wanted to provide a resource for you to consider.

Upvotes: 1

Related Questions