How to handle data types in conversion from PHP Mysql API to PDO

Question

I am in the process of converting our site from the PHP Mysql API to PDO, and have run into an issue with data types.

Previously, we escaped all of our variables as if they were strings. for example,

SET varname = '$varvalue'

Now, with PDO, of course, I do

SET varname = :varvalue

then we have a class that handles binding of the value of $varvalue, setting the data type based on the type of the variable.

The problem for us comes when varname is supposed to be a string, and $varvalue is, for some reason, null. previously, '$varvalue' would have just become '' when $varvalue is null. Now, we are "properly" binding $varvalue as null, but the database field does not allow null.

I know that the most correct way to fix this would be to ensure that $varvalue comes into the function with the correct value, but we have a large legacy code base, and that would be really a lot of work to implement. Another solution would be to explicitly cast every variable when we bind it to the correct type. We'd prefer a solution that avoids us having to explicitly cast every variable in our models, if possible. Is there one?

Answer 1

This might not be an answer you were waiting for, but it should be mentioned: use exceptions.

You can configure PDO to throw exceptions of type PDOException instead of relying on return values:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

You can catch these exceptions and log them, email them, etc. so that you can identify exactly what piece of code was passing in the wrong values and fix it.

This is a somewhat painful operation; we've had to endure this ourselves when we started to report all uncaught exceptions on our website and our inboxes were cluttered with errors. It lasted a few days, but we managed to weed out all the really bad code :)