Should we always use prepared statements in MySQL and php or when to use these?

Question

I read so many articles saying that using prepared statements is very secure and good to prevent SQL injections. I built a few websites without using prepared statements. But after read all those articles, I am thinking to change whole codes using prepared statements.

My Question

Is it necessary to use prepared statements always? or is there any scenario whereby normal statement will be sufficient over prepared statements?

Possible duplicate of prepared statements - are they necessary — Obsidian Age

Bill Karwin Bill Karwin · Accepted Answer · 2018-06-25T03:01:28

Non-prepared statements are sufficient if you have an SQL query that is entirely hard-coded, and needs no PHP variables in the SQL.

Here's an example:

$result = $mysqli->query("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL 7 DAY");

The query is self-contained. It's just a fixed string, entirely under control of your application. There's no way any untrusted content can affect the query.

If your query needs some variable part, then use query parameters, like this:

$stmt = $mysqli->prepare("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL ? DAY");
$stmt->bind_param("i", $number_of_days);
$stmt->execute();

The point of query parameters is to separate potentially untrusted content from the SQL parsing step. By using parameters, the value of the bound variable is not combined with the query until after the SQL has been parsed. Therefore there is no way the bound parameter can affect the logic of the query — the parameter will be limited to act as a single scalar value in the query.

Should we always use prepared statements in MySQL and php or when to use these?

2 Answers