Prepared Insert doesn't do anything if ATTR_EMULATE_PREPARES is false

Question

So after a whole lot of struggle with PDO I've narrowed down the root of my problem here. When I set the attribute ATTR_EMULATE_PREPARES to false my insert query runs without error but does not add an entry into the database table.

However when I run the query through either PhpMyAdmin or with ATTR_EMULATE_PREPARES set to true it executes successfully.

This is causing me some frustration as there should be no good reason why it doesn't work.

Here's the query I directly executed through PhpMyAdmin.

INSERT INTO account (guid, displayname, password_hash, password_salt, email, superuser) VALUES (UNHEX('fcfd7f2355f211e5acfd2174e316c493'), 'bob', 'test', 'test', 'test', 1);

Here is the relevant secions of code.

$db = null;
try
{
    $db = new PDO($pdo_connectionstring, $pdo_username, $pdo_password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (PDOException $ex)
{
    die('[FATAL ERROR] Could not connect to the Database: '.$ex->getMessage());
}

try
{
    $stmt = $db->prepare("INSERT INTO `account` (`guid`, `displayname`, `password_hash`, `password_salt`, `email`, `superuser`) VALUES (UNHEX(:guid), :displayname, :passwordhash, :passwordsalt, :email, :superuser);");

    $stmt->bindValue(':guid', $guid, PDO::PARAM_STR);
    $stmt->bindValue(':displayname', $displayname, PDO::PARAM_STR);
    $stmt->bindValue(':passwordhash', $hash, PDO::PARAM_STR);
    $stmt->bindValue(':passwordsalt', $salt, PDO::PARAM_STR);
    $stmt->bindValue(':email', $email, PDO::PARAM_STR);
    $stmt->bindValue(':superuser', $superuser, PDO::PARAM_BOOL);

    $stmt->execute();
}
catch (Exception $ex)
{
    $jsonresult['generalerror'] = 'Failed to create user. Please contact your GM: ' . $ex->getMessage();
}

EDIT: Here is a SQLFiddle of my database schema, and the version information of the system and components in question

• Debian 6.0.10 running on 1and1's Shared Hosting
• PHP 5.4.44
• MySQL server 5.5.44-0+deb7u1-log

EDIT: Credit to @RyanVincent for figuring this out. The first part of this is that my database column superuser was defined as a tinyint(1) which despite being a common Boolean storage datatype, requires PDO::PARAM_INT when binding the value. The second part of this is that when the PDO driver is running with PDO::ATTR_EMULATE_PREPARES set to false it doesn't throw errors or exceptions if the database returns an error message, only when the local PDO driver encounters a problem. PDOStatement::execute() returns a Boolean value as to whether the query was successful or not. It is up to the developer to manually check PDOStatement::errorCode() and PDOStatement::errorInfo() if execute() returns false. This is of particular note during transactions as it is important to rollback the transaction if one of the statements fails. An annoying quirk of PDO is that if you set the wrong datatype I.E. PDO::PARAM_BOOL instead of PDO::PARAM_INT like I did, the returned errorInfo() will be pretty much empty, leaving you scratching your head as to what went wrong anyway.

TL:DR when setting ATTR_EMULATE_PREPARES to false, use additional error catching and if something isn't working, check and double check your datatypes.

Answer 1

The issue is that when using emulate_prepares as false then the query fails and no exception is thrown from PDO.

TL;DR : goto: Test setup and explanation

TL;DR : goto: The tests and the results

TL;DR : goto: Conclusions

Overview of PDO prepared query processing

The mysql driver:

• It does not understand named placeholders, it only uses ? to mark the position of bind variables.
• Each command returns, if it succeeds - useful information, false if it couldn't complete the request.

The result of this is that, when you have PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, it is the responsibility of PDO to convert that to an Exception.

Overview of what is expected to happen when a query is executed via PDO:

• prepare: Query is sent to PDO with named placeholders.
• PDO formats it with '?' and sends it to the mysql driver.
• The mysql driver sends it the the actual mysql server, which processes it, and returns information explaining how it went on back to the mysql driver
• The mysql driver sends the result back to PDO.
• PDO examines it and converts it in useful results or error codes or exceptions, as required.

As can be seen, there are rather more places than we would like, for confusion to happen.

To debug what is happening I wanted to know exactly what the mysql server received and what was returned.

To do that, the mysql server provides general logging for queries see: 5.2.3 The General Query Log.

I have provided utilities to control switching on and off the general log for both writing to log files and writing to the mysql.general_log table.

Tested code: IMysqlGeneralLogging: Control the MySQL General Logging Facility and class.

Debugging and Explaining the actual O/P code:

I have supplied PHPUnit testing scripts to run the O/P code and check things. I am using it as a 'execute and report' tool rather than for testing anything.

This is really explaining what I found out and demonstrating it. It took quite a bit of time and quite a lot of 'trial and error'. With the emphasis being on the 'error'. ;-)

Test setup and explanation

/**
 * Test Data for all tests...
 */
protected $guid             = '420D4B65565311E5958000FFD7CBE75F';
protected $displayname     = 'Example User 42';
protected $hash             = '$2y$17$12345678901234567890121234567890123456789012345678942';
protected $salt             = '$2y$17$1234567890123456789042$';
protected $email            = 'example42@example.com';
protected $superuser       =  true;

/**
 * Expected Results
 * Must be static so they are not reset with each test
 */
protected static $rcExpected = array(
    'prepare'       => true,
    'execute'       => true,
    'exception'     => false,
    'exceptionMsg'  => '',
);

/**
 * Actual results stored in here. 
 */
protected static $rcActual = array(
    'prepare'       => false,
    'execute'       => false,
    'exception'     => false,
    'exceptionMsg'  => '',
);

The code used was changed to store results in the above arrays.

    // these change for each test...
    $this->userPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $this->userPDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

    try
    {
        self::$rcActual['exception'] = false;

        $stmt = $this->userPDO->prepare(
                   "INSERT INTO `account`
                            (`guid`,       `displayname`, `password_hash`, `password_salt`, `email`, `superuser`)
                    VALUES
                            (UNHEX(:guid), :displayname,  :passwordhash,   :passwordsalt,   :email,  :superuser);");


        self::$rcActual['prepare'] = $stmt !== false; // record result

        $stmt->bindValue(':guid',         $this->guid,         PDO::PARAM_STR);
        $stmt->bindValue(':displayname',  $this->displayname, PDO::PARAM_STR);
        $stmt->bindValue(':passwordhash', $this->hash,         PDO::PARAM_STR);
        $stmt->bindValue(':passwordsalt', $this->salt,         PDO::PARAM_STR);
        $stmt->bindValue(':email',        $this->email,        PDO::PARAM_STR);
        $stmt->bindValue(':superuser',    $this->superuser,   PDO::PARAM_BOOL);

        self::$rcActual['execute'] = $stmt->execute(); // record result

        $this->assertTrue(self::$rcActual['execute']);
        $this->assertFalse(self::$rcActual['exception']);
    }
    catch (\Exception $e) {
        self::$rcActual['exception'] = true;
        self::$rcActual['exeptionMsg'] = $e->getCode() .' : '. $e->getMessage();
        $this->assertTrue(self::$rcActual['exception']);
    }

Code to start the mysql general logging to a trace file:

    $testFilename = $this->logging->newTraceFilename('Test_01_EmulatesOn_ExceptionOn'));
    $this->logging->setLogFile($testFilename);
    $this->logging->startLoggingToFile(); // start logging...

And stop logging to the trace file:

    $this->logging->stopLogging();

The tests and the results:

---

Test Series 1 - standard PDO (emulates ON):

    $this->userPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $this->userPDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Test 1 : All Ok...

Testing Code: Q32451215Test 01 EmulatesOn_ExpectsAllOk

Results:

Expected / Actual:

prepare   : Expected:  true, Actual: true
execute   : Expected:  true, Actual: true
exception : Expected:  false, Actual: false
exceptionMsg : Expected:  '', Actual: ''

Trace Log:

150914 14:15:30   569 Query INSERT INTO `account`
                            (`guid`,       `displayname`, `password_hash`, `password_salt`, `email`, `superuser`)
                    VALUES
                            (UNHEX('420D4B65565311E5958000FFD7CBE75F'), 'Example User 42',  '$2y$17$12345678901234567890121234567890123456789012345678942',   '$2y$17$1234567890123456789042$',   'example42@example.com',  1)

Note:

• There is not a separate prepare and execute statement sent to the server. Interesting?

---

Test Series 2 - PDO (emulates OFF):

Test 2A : Same settings as Test 1 except emulates OFF

Testing Code: Q32451215Test 02A EmulatesOff__WithSuperUserAsBooleanParamType

Unchanged bind statement:

 $stmt->bindValue(':superuser',    $this->superuser,   PDO::PARAM_BOOL);

Results:

Expected / Actual:

prepare   : Expected:  true, Actual: true
execute   : Expected:  true, Actual: false
exception : Expected:  false, Actual: false
exceptionMsg : Expected:  '', Actual: ''
errorCode : '00000', ErrorMsg: array (
  0 => '00000',
  1 => NULL,
  2 => NULL,
)

Trace Log:

150915 11:37:12   693 Prepare   INSERT INTO `account`
                            (`guid`,       `displayname`, `password_hash`, `password_salt`, `email`, `superuser`)
                    VALUES
                            (UNHEX(?), ?,  ?,   ?,   ?,  ?)

Notes:

These are unexpected! ;-/

• The trace log only contains the prepare statement!
• There was no execute command sent to the server.
• The PDO execute statement returned false but no exception was raised!
• There was no unusual errorCode or errorInfo message returned.

This indicates that either PDO or the mysql driver didn't like the 'PDO::PARAM_BOOL` or the actual value supplied to it.

• The only way you can check this SQL statement has failed is to check the results returned from the individual prepare and execute statements! Also, you have no clue why it failed!

This makes debugging interesting ;-/

Test 2B : Same settings as Test 2A except PDO::PARAM_INT

Testing Code: Q32451215Test 02B EmulatesOff__WithSuperUserAsIntegerParamType

This is the only change to the code:

  $stmt->bindValue(':superuser',    $this->superuser,   PDO::PARAM_INT);

Results:

Expected / Actual:

prepare   : Expected:  true, Actual: true
execute   : Expected:  true, Actual: true
exception : Expected:  false, Actual: false
exceptionMsg : Expected:  '', Actual: ''
errorCode : '00000', ErrorMsg: ''

Trace Log:

150915 12:06:07   709 Prepare   INSERT INTO `account`
                            (`guid`,       `displayname`, `password_hash`, `password_salt`, `email`, `superuser`)
                    VALUES
                            (UNHEX(?), ?,  ?,   ?,   ?,  ?)

      709 Execute   INSERT INTO `account`
                            (`guid`,       `displayname`, `password_hash`, `password_salt`, `email`, `superuser`)
                    VALUES
                            (UNHEX('420D4B65565311E5958000FFD7CBE75F'), 'Example User 42',  '$2y$17$12345678901234567890121234567890123456789012345678942',   '$2y$17$1234567890123456789042$',   'example42@example.com',  1)

Notes:

These are exactly as expected:

• The trace log contains the prepare and execute statements.
• The PDO execute statement returned true
• There was no unusual errorCode or errorInfo message returned.

What is odd is that only the binding paramater was changed.

Conclusions

standard PDO: Emulates_Prepares ON

PDO::ATTR_ERRMODE ==> PDO::ERRMODE_EXCEPTION

• Exceptions will always be raised on errors.

• Bound parameters will try to be interpreted in a reasonable manner and not cause failures.

• Use this mode normally.

standard PDO: Emulates_Prepares OFF

PDO::ATTR_ERRMODE ==> PDO::ERRMODE_EXCEPTION

• Exceptions will not always be raised

• Bound parameters will be interpreted exactly as requested.

• This can cause a return of false from the PDO execute statement.

• This is the only clue that the query has failed.

• Always check the value returned from PDO prepare and PDO execute from being false.