Stack Stack - 5 months ago 9
MySQL Question

Do I need to use prepared statement for the data I'm getting from database?

I have a query like this:

$result = $db
-> prepare("SELECT value FROM mytable WHERE id = ?")
-> execute(array($id))
-> fetch(PDO::FETCH_ASSOC);


And I want to use
$result['value']
as a parameter for another query (an
UPDATE
statement)
. Should I use prepared statement for that
UPDATE
statement? Or because I've taken it from database, then no need to pass it as prepared statement?

Answer

Yes. Use a prepared statement with a bind placeholder.

Just because a value is being returned from a database doesn't mean that the value is safe for inclusion in SQL text.

You may have domain knowledge that the value column in mytable is INTEGER type, so that it would be safe. But in the more general case, and for the reader who doesn't know the definition of mytable, and what value might contain. A reader of your code is going to assume that value isn't "safe". For all we know, we could be getting something like this:

Robert'); DROP TABLE students; --

Whenever we see a variable concatenated into the SQL text, we are going to assume that the variable could contain something other than a value, and it could contain actual SQL. (Or, if we do see a variable concatenated into the text of a SQL statement, we would be expecting that it's going to be properly escaped right at the point it's being concatenated.)

So, the preferred pattern would be use a prepared statement with a bind placeholder. That makes it unambiguous to the reader that value is indeed a value, and that it's not intended to be interpreted as SQL text.

Comments