PDO fails a operation inside a transaction, but commit returns true. Why?

Question

I have the following code

public function actualizarLogin($idusuario, $usu_login=NULL, 
            $usu_passwd=NULL) {
        $result=false;
        try {
            $db=new PDO(MYSQL_CON, MYSQL_USER, MYSQL_PASSWD,
                    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
            try {
                $db->beginTransaction();
                $sql="UPDATE usuario SET usu_login=?, usu_passwd=? 
                        WHERE idusuario=?";
                $pstmt=$db->prepare($sql);
                $pstmt->bindParam(1, $usu_login, PDO::PARAM_STR);
                if($usu_passwd==null) {
                    $pstmt->bindParam(2, $usu_passwd, PDO::PARAM_NULL);
                } else {
                    $pstmt->bindParam(2, crypt($usu_passwd, CRYPT_MD5), PDO::PARAM_STR);
                }
                //Accidentaly using wrong variable here
                $pstmt->bindParam(3, $usu_login, PDO::PARAM_INT);
                $pstmt->execute();
                //But commit return true and no Exception is thrown
                $result=$db->commit();              
            } catch (PDOException $e) {
                $result=$e->getMessage();
                $db->rollBack();
                throw new RPC_INTERNAL_ERROR();
            }
        }catch (PDOException $e) {  
            $result=$e->getMessage();
            throw new RPC_INTERNAL_ERROR();
        }
        return $result;

the RPC_INTERNAL_ERROR is an exception from zoservices, the library that I'm using for the JSON-RPC Server, and this method is called from a client. As you can read the comments inside the code, I made a mistake, something very human, I'm using the wrong variable to bind the last parameter, and I'm telling to bind it as an integer while actually is string (it's the login instead of the id). But for some reason even when I'm telling to throw an exception on failure there is no such exception, but the worst of all is that commit return true... Why? I spent a precious time just to find that error, because PDO wasn't telling me nothing about that. There is any way to handle those mistakes?

score 0 · Accepted Answer · answered May 07 '14 at 12:10

PDO doesn't throw when you do that because input parameters such as these are coerced to the specified type transparently to you. No error occurs as far as the database is concerned, because it receives valid values for the column data type.

This kind of behavior is prevalent throughout PHP because the alternative would be that this code would not work:

$foo = "4"; // not techically an int
$pstmt->bindParam(3, $foo, PDO::PARAM_INT);
$pstmt->execute();

If you want to enforce matching types for bound parameters you would have to manually check (e.g. with a wrapper around PDOStatement::bindParam that does gettype($param) and compares the result with the specified type).

Oh, so it's a PHP issue, that's why I prefer strongly typed languages like Java. And there is anyway to get the SQL after the parameter bound operations? — lordscales91, May 07 '14 at 14:27

score 0 · Answer 2 · answered May 07 '14 at 12:12

There is no error because there is no error.

$pstmt->bindParam(3, $usu_login, PDO::PARAM_INT);

$usu_login is being cast to an int and bound as parameter 3, which creates a query along the lines of:

UPDATE ... WHERE idusuario = 0

When executing that, simply nothing happens, since the WHERE condition doesn't apply to anything (presumably). PDO::PARAM_INT doesn't cause an error, as it simply makes sure the argument is bound as an int, and the query itself is perfectly fine, even if it does nothing (that's a perfectly valid outcome).

You may want to check $pstmt->rowCount() to see if the number of affected rows matches your expectation.

PDO fails a operation inside a transaction, but commit returns true. Why?

2 Answers2