Is it OK to allow sometimes dynamic SQL without sanitization?
php, security, sql, zend-framework
Solution
Which do you consider more trouble:
- Having to track down a bug that didn't cause a failed SQL query.
- Having to restore data after you make a mistake in sanitizing the form input and someone exploits that.
Whichever you pick, there's your answer. Personally, I tend to lean towards the paranoid side of things as well.
If anything, you could do both: create your own function that first checks for null and then calls `intval()`, and use that instead. Then you get the best of both worlds.
Problem
My partner on a PHP project objects my practice of always sanitizing integer values in dynamic SQL. We do use parameterized queries when possible. But for UPDATE and DELETE conditions `Zend_Db_Adapter` requires a non-parameterized SQL string. That's why I, even without thinking, always write something like: ``` $db->delete('table_foo', 'id = ' . intval($obj->get_id())); ``` Which is equivalent, but is a shorter version of (I've checked the ZF source code): ``` $db->delete('table_foo', $db->qouteInto('id = ?', $obj->get_id(), 'INTEGER')); ``` My partner strongly objects this `intval()`, saying that if `$obj` ID is null (the object is not yet saved to DB), I will not notice an error, and the DB operation will just silently execute. That's what has actually happened to him. He says that if we sanitize all the HTML forms input, there's no way an integer ID can possibly get into `'; DROP TABLE ...'`, or `' OR 1 = 1`', or another nasty value, and get inserted into our SQL queries. Thus, I'm just paranoid, and am making our lives unnecessarily more complicated. "Stop trusting the `$_SESSION` values then" he says. However, for string value conditions he does agree with: ``` $db->update->( 'table_foo', $columns, 'string_column_bar = ' . $db->qoute($string_value)) ); ``` I failed to prove him wrong, and he failed to prove me wrong. Can you do either?