Where (in code) to place elements of prepared statements in a website where many users interact with the database?
I'm converting my code from using $mysqli->escape_string to using prepared statements since people say it's better way for protection against sql injection.
Is it simply removing all $mysqli->escape_strings and also replacing other parts of my code like this:
$mysqli->query("update `users` set `email`='$newEmail' where `userid`={$_SESSION['userid']} limit 1");
replaced with this:
$stmt = $mysqli->prepare("update `users` set `email`=? where `userid`=? limit 1");
$stmt->bind_param('si',$newEmail,$_SESSION['userid']);
$stmt->execute();
$stmt->close();
?
People say prepared statements is more efficient as statement template is prepared only once and then it can be re-used with different parameters. But in the code replacement above wouldn't the template preparation be done every time any user wants to update their email address? Should i instead prepare all statements when server is started? And don't prepare them again? (Though i don't know how to do that.) Where should the prepare, bind_param and close be placed throughout my website's code?
Is it simply removing all $mysqli->escape_strings and also replacing variables with placeholders
Yes.
People say prepared statements is more efficient as statement template is prepared only once and then it can be re-used with different parameters.
Forget about it. There is no way to re-use a prepared statement between requests.
It works within a statement variable only and and therefore only applicable only within a single PHP instance. Means you only can use it for repeated inserts/updates. And even there the gain is not something significant.
That's more of a marketing trick than a really useful feature.