3 ms·

Could you give some actual examples of prepared statements that are injectable? I do not understand how it would work. For example the following example from h

by aw3c2 14y ago

Could you give some actual examples of prepared statements that are injectable? I do not understand how it would work. For example the following example from http://de.php.net/manual/en/sqlite3.prepare.php http://de.php.net/manual/en/sqlite3.prepare.php : $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id'); $stmt->bindValue(':id', 1, SQLITE3_INTEGER);

FuzzyDunlop 14y ago
You're missing his point somewhat. I don't know if this applies universally to SQL, but consider MySQL. $stmt = $db->prepare('SELECT bar FROM foo LIMIT :offset, :count'); ... You can't do that. You can only bind data to fields. $stmt = $db->prepare('SELECT bar FROM foo ORDER BY :column :direction'); $stmt->bindValue(':column', 'foo'); $stmt->bindValue(':direction', 'DESC'); You can't do that either, because again, it's not data. So, you end up having to do something similar to this (for the love of god, don't actually do this): $stmt->prepare("SELECT bar FROM foo WHERE id = :id ORDER BY {$column} {$direction} LIMIT {$offset}, {$count}"); Bound parameters won't save you, and the potential attack vector is there if you're not really careful. That example shows the most stupid thing you could ever do, so don't do it.
- shabble 14y ago
  Other than probably historical reasons and interface definitions, is there any reason why this is the case? I can't immediately think of any sensible argument against allowing the binding of a value into a limit/order field. In fact, it should be type-checking/converting it as well, to prevent exactly mistakes like this. Even if the DB interface doesn't directly support this, it seems like it's something all client wrappers should handle, preferably at the low level in addition to having to pull in a whole ORM + assorted bacon for the purpose.
  - tptacek 14y ago
    Easy: bound parameters are a performance feature, not a security feature.
- aw3c2 14y ago
  Thanks! That would not have been a "real" prepared statement in my mindset so I get why I was confused. I don't feel super smart, but wouldn't I simply make sure that $column is a valid column (ok, that one might need extra attention) and $direction would be either ASC or DESC and that $offset and $count are integers?

by aw3c2 14y ago

FuzzyDunlop 14y ago

You're missing his point somewhat. I don't know if this applies universally to SQL, but consider MySQL. $stmt = $db->prepare('SELECT bar FROM foo LIMIT :offset, :count'); ... You can't do that. You can only bind data to fields. $stmt = $db->prepare('SELECT bar FROM foo ORDER BY :column :direction'); $stmt->bindValue(':column', 'foo'); $stmt->bindValue(':direction', 'DESC'); You can't do that either, because again, it's not data. So, you end up having to do something similar to this (for the love of god, don't actually do this): $stmt->prepare("SELECT bar FROM foo WHERE id = :id ORDER BY {$column} {$direction} LIMIT {$offset}, {$count}"); Bound parameters won't save you, and the potential attack vector is there if you're not really careful. That example shows the most stupid thing you could ever do, so don't do it.

shabble 14y ago
Other than probably historical reasons and interface definitions, is there any reason why this is the case? I can't immediately think of any sensible argument against allowing the binding of a value into a limit/order field. In fact, it should be type-checking/converting it as well, to prevent exactly mistakes like this. Even if the DB interface doesn't directly support this, it seems like it's something all client wrappers should handle, preferably at the low level in addition to having to pull in a whole ORM + assorted bacon for the purpose.
- tptacek 14y ago
  Easy: bound parameters are a performance feature, not a security feature.
aw3c2 14y ago
Thanks! That would not have been a "real" prepared statement in my mindset so I get why I was confused. I don't feel super smart, but wouldn't I simply make sure that $column is a valid column (ok, that one might need extra attention) and $direction would be either ASC or DESC and that $offset and $count are integers?