Mysql prepared statements - what do they do, security wise?

I have been trying to move from non-prepared statements to prepared statements. I have heard the mysqli_stmt_bind_param does some of the work that mysqli_real_escape_string does. I tested it though and I am receiving some confusing results. I tried to insert

$variable = a\a\

into a database by passing it through mysqli_stmt_bind_param gives me an sql error. (“Problem: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘a\a\’’ at line 1”)

However, if I do that in just a normal query using $result = mysql_query(insert into, values), I do not get this error, and a\a\ shows up in the database.

However, using mysqli_stmt_bind_param to insert a\a AFTER using mysqli_real_escape_string on a\a\ puts it into the database, escaped as expected as a\\a\\.

I have several questions here:

  1. why do I get an error when trying to insert $variable into my database using mysqli_prepare/mysqli_stmt_bind_param?

  2. what are the supposed security features of mysqli_stmt_bind_param?

and
3) should I still escape everything before putting it into a prepared mysqli statement?

I should note that I am of course using mysqli_stmt_execute and the related statements to get the database insertion to work, but I heard that the security features of prepared statements were located in mysqli_stmt_bind_param, so that is why I focused on it.

In regards to your first question, would you mind posting what the query looks like?

In terms of security the advantage of prepared statements is that they separate the inputs from the query itself. Basically when you use a prepared statement the query is sent with place holders which will then be filled in after the query is executed. This basically protects you from SQL injection as the variables that fill the place holders are not executed by MYSQL. With this in mind you don’t need to escape variables if you’re using prepared statements.

Thanks. Here’s the query (it’s actually quite a bit longer with about 15 different variables to enter, so I shortened it here)


$name = a\\a\\

$stmt = mysqli_prepare($link, "INSERT INTO members (name,password) VALUES ( ?,? )");

mysqli_stmt_bind_param($stmt, "ss", $name,$password );

mysqli_stmt_execute($stmt);