CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
gom
gom

Reputation: 897

How to use prepared statements in multi user website?

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?

Upvotes: 2

Views: 90

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157863

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.

Upvotes: 3

Related Questions