4

I am trying to dynamically insert 'NULL' into the database using PDO.

TABLE STRUCTURE:

CREATE TABLE IF NOT EXISTS `Fixes` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `CurrencyId` int(11) NOT NULL COMMENT 'FK',
  `MetalId` int(11) NOT NULL COMMENT 'FK',
  `FixAM` decimal(10,5) NOT NULL,
  `FixPM` decimal(10,5) DEFAULT NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `CurrencyId` (`CurrencyId`),
  KEY `MetalId` (`MetalId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

PHP / PDO QUERY:

$sql = 'UPDATE 
            Fixes
    SET 
            FixAM = :fixAM,
        FixPM = :fixPM
        WHERE
            MetalId IN (SELECT Id FROM Metals WHERE Name = :metal) AND
        CurrencyId IN (SELECT Id FROM Currencies Where Id = :currency)';

$stmt = $db->prepare($sql); 

for ($i = 0; $i<3; $i++) {  
    $stmt->execute(array(
    ':metal' => 'Silver', 
    ':fixAM' => $fix['FixAM'][$i], 
    ':fixPM' => $fix['FixPM'][$i],
    ':currency' => ($i+1))
    );      
}

e.g. sometimes, the value for $fix['FixPM'][$i] is sometimes 'NULL'. How do I insert this into the database? When I run the query and then view the data in the database, this record shows 0.0000, and not null.

How do I insert NULL values using PDO? provides a few solutions.

  • I dont think I can use $stmt->execute(array( ':v1' => null, ':v2' => ... )) as per example because sometimes the item is null, and sometimes not. As such, I need to refer to the variable I have created $fix['FixPM'][$i] and make that null as and when needed

Thanks in advance.

Community
  • 1
  • 1
Gravy
  • 12,264
  • 26
  • 124
  • 193
  • Is `FixPM` field nullable in `Fixed` Table schema? – luchosrock Dec 18 '12 at 21:14
  • Hi there, I have clarified the question. Table structure shows that FixPM is nullable. – Gravy Dec 18 '12 at 21:23
  • 1
    Is the variable's value actually `NULL`, or is it the _string_ `'NULL'`, which when cast to a numeric value will cast to `0`? PDO should handle a real `NULL` correctly, but the string would get cast. – Michael Berkowski Dec 18 '12 at 21:28
  • $fix["FixPM"][0] = null; which doesn't do anything (NO GOOD). and $fix["FixPM"][0] = 'NULL'; which casts to 0.00000 (NO GOOD) – Gravy Dec 18 '12 at 21:30
  • The actual NULL value doesn't work? I find that strange. To fix the string you would need something like `':fixPM' => strtoupper($fix['FixPM'][$i]) === 'NULL' ? NULL : $fix['FixPM'][$i],` – Michael Berkowski Dec 18 '12 at 21:33

1 Answers1

7

This appears to me to be a(n unreported?) bug in PDO's prepared statement emulation:

  1. the implementation of PDOStatement::execute() eventually invokes pdo_parse_params();

  2. that, in turn, attempts to quote/escape values based on the relevant parameter's data type (as indicated by the $data_type arguments to PDOStatement::bindValue() and PDOStatement::bindParam()—all parameters provided as $input_parameters to PDOStatement::execute() are treated as PDO::PARAM_STR, as stated in the documentation of that function);

  3. string-typed values are escaped/quoted by calling the relevant database driver's quoter() method irrespective of whether they are null: in the case of PDO_MySQL, that's mysql_handle_quoter(), which (eventually) passes the value to either mysqlnd_cset_escape_quotes() or mysql_cset_escape_slashes(), depending on the server's NO_BACKSLASH_ESCAPES SQL mode;

  4. given a null argument, both of those functions return an empty string.

My opinion is that, prior to switching on the parameter's type (in step 2 above), pdo_parse_params() should set the type to PDO::PARAM_NULL if the value is null. However, some might argue that this would prevent type-specific handling of null values where appropriate, in which case the string case (in step 3 above) should definitely handle null values before proceeding with a call to the driver's quoter() method.

As an interim workaround, disabling prepared statement emulation is usually for the best anyway:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
eggyal
  • 122,705
  • 18
  • 212
  • 237