Is it OK to allow sometimes dynamic SQL without sanitization?

Question

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?

Answer 1

Frankly, your partner is off his rocker: sanitizing is cheap, there's no good reason not to do it. Even if you are sanitizing what is in the HTML forms, if those checks somehow break on production, you'll be happy that you have a backup in other places. Also, it promotes a good practice.

You should sanitize—always