php - Advantages of using prepared statements over normal mysqli statements?

Question

I have done my research and have decided to use prepared statements in my queries, all I ask if there is anything I should know, good or bad about switching to normal mysqli queries to prepared statements.

Also I don't understand the logic how the need for escaping bad characters is not needed?

Answer 1

Escaping bad characters is still needed, but the library does it automatically for all parameters you bind. It's just slightly more convenient, and prevents the programmer from forgetting to sanitize a value.

However, note that this automatism is limited to parameters!

The following query is safe, because bind_param() takes care of escaping:

$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

$stmt = $mysqli->prepare("INSERT INTO items VALUES (?, ?, ?)");
$stmt->bind_param('iss', code, $name, $percentage);
$stmt->execute();

the following query is unsafe, because anything you put directly into the query will not be escaped automatically:

$tablename = $_GET["prefix"]."_items";  
$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

                                    ---- UNSAFE! ----
$stmt = $mysqli->prepare("INSERT INTO `$tablename` VALUES (?, ?, ?)");
$stmt->bind_param('iss', $code, $name, $percentage);
$stmt->execute();

that said, one shouldn't be using dynamic table names like shown in this example anyway. But the point stands: Be careful, even with parametrized queries!

The only downside I can think of is that you can't see the final query any more for debugging (because it gets assembled only on server side).