CODEWITHSUNDEEP
phppdoprepared-statement
ThomasK
ThomasK

Reputation: 2220

Is prepared statements on trusted data from another query really neccessary?

Is prepared statement necessary when dealing with trusted data fetched from another query?

For example.
When a user is navigating throughout the site, they click named links like this: /?category=health where health is the value that is sent to the database.
In this scenario I of course use prepared statement like this: 

$qry = $dbh->prepare('SELECT category_id, and, other, columns FROM categories WHERE query_value = ?');
$qry->execute([$_GET['category']]);
$get = $qry->fetch();
$qry = null;

But further down the script, I would display content associated with the selected category based on the categories.category_id fetched from the last query. 

$Banners = $dbh->query('SELECT image FROM Banners WHERE category_id = '.$get['category_id'])->fetchAll();

I would like to think that this is a secure query.
That the value could be no other than a trusted value since it has to be a result from the previous query?
And this query won't be executed if the previous query doesn't return true.

Here's how I've done it so far:
It's a 3-liner. But it would speed up the coding part a bit if I was certain that the 1-liner above is fine too.

$qry = $dbh->prepare('SELECT image FROM Banners WHERE category_id = ?');
$qry->execute([$get['category_id']]);
$Banners = $qry->fetchAll();

Upvotes: 0

Views: 70

Answers (1)

deceze
deceze

Reputation: 522382

What this all is about largely is about ensuring your SQL syntax is sane and what you think it is. Preventing malicious attacks is just a corollary of that. What if your category ids in the future spawn apostrophes or whatnot as part of the regular value? You need escaping for correct syntax then anyway, or, better, prepared statements.

Secondarily, there is second order injection, in which a value which was previously treated as unsafe is now suddenly treated as safe, even though it still has the potential for attacks. E.g. if your values legitimately contain apostrophes.

More broadly speaking: how do you ensure a value is "safe"?

// $get['category_id'] is safe and doesn't need escaping
'SELECT image FROM Banners WHERE category_id = '.$get['category_id']

Well, how do you know this? What code ensures this value is safe? Where does this value come from? Are you sure that's where it comes from? You're outsourcing the integrity of this query to some other part of the code here. How can you be sure that other part does its job correctly? Now, or in the future, after a lot of refactoring? Simply ensure yourself that you are producing correct queries by writing queries in a secure way, don't outsource your security.

You need to ask yourself: is this query prone to injection the way it is written? And the answer to the above example is a plain yes, it is prone to injection, and the safety merely depends on your trust that $get['category_id'] is what you think it is.

Upvotes: 2

Related Questions