PDO选择查询的安全性？

Question

Could anyone help me with my query?

I am currently converting all my old PHP code to use PDO. I was wondering when is it necessary to use the prepare function for my querys?

I currently have this query.

    $sql = "SELECT deckName FROM decks WHERE deleted = '0' ORDER by deckName";

None of it is dynamic apart from maybe the deleted column. I am using the below to reiterate my data on screen.

   foreach($DBH->query($sql) as $row){ echo $row['deckName']?> }

Should I still be using this as good practice or is the above good enough?

   $sth = $DBH->prepare("SELECT deckName FROM decks WHERE deleted = '0' ORDER by deckName");

I am not quite sure how to use the fetch statement correctly when reiterating data from the row?

Thanks in advance :) Hayley

Answer 1

If you send a constant string to the database and do never mix in "untrusted input" the security concern is in my experience irrelevant, and only a potential performance aspect remains, which depends on the underlying database. IF the preparation step of an expensive to parse query may be spared for the execute phase (prepare, bind=NOOP here, execute) of all invocations after the first.

So guessing from the tags, that you use mysql and do not use memcached or the like as an intermediate layer, you might just want to measure the performance, otherwise you could migrate to direct queries.

When the "Project" later needs variable parametrization of the query (based on untrusted input e.g. from the network), you should use again prepare, to shield against injection attacks. Maybe then it will be good to find out "how to use the fetch statement correctly when reiterating data from the row" ...

Amending to the answer to accomodate the "uppercased nevers" for using variables in queries from the comments:

There is a practical reason for me writing "receiving untrusted input" instead of "never this and never that". In my experience there is a slippery slope, when it comes to variables: Without auditing the wider context of the $sql variable in above question - even the whole query is potentially vulernable to injection attacks. Also internal refactorings of building query strings from constants may lead to mixing further variables to compose a (more complex) string that acts as a query.

For a more balanced / gap minimizing hardening I would suggest:

1. Documenting a specific map of say "trusted boundaries" in a code realizing an app, and based on that, enforcing strict validation of any input crossing these boundaries - i.e. variables transporting values accross - is in most cases effective and efficient.

2. Adding the common sense concept of layered security / separation of concerns will then naturally lead to using prepared statements when "such input" (that crossed a boundary) is to be "injected" as parameter.

I am reluctant to hand out simple recipes giving an optimistic sense of security, when one deals with such a scyscraper like application stack as db based web apps usually do.