Debug PDO mySql inserts NULL into database instead of empty

Question

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

STRUCTURE TABLE:

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)) ); } 

eg. sometimes the value of $fix['FixPM'][$i] sometimes 'NULL'. How to insert this into a database? When I run the query and then look at the data in the database, this record shows 0.0000, not null.

How to insert null values ​​using PDO? offers several solutions.

• I don’t think I can use $stmt->execute(array( ':v1' => null, ':v2' => ... )) as an example, because sometimes an element is null and sometimes not . Thus, I need to access the variable that I created $fix['FixPM'][$i] , and make it null as needed

Thanks in advance.

Answer 1

It seems to me that this is an error (n unreported?) In emulating a pronounced PDO:

• the implementation of PDOStatement::execute() eventually calls pdo_parse_params() ;

• which, in turn, tries to specify / exclude values based on the corresponding data type of the parameters (as indicated by the $data_type arguments PDOStatement::bindValue() and PDOStatement::bindParam() - all parameters represented as $input_parameters - PDOStatement::execute() are considered as PDO::PARAM_STR , as indicated in the documentation for this function);

• the values ​​in the string sequence are escaped / quoted calls the corresponding quoter() database driver, regardless of whether they are null : in the case of PDO_MySQL, mysql_handle_quoter() , which (ultimately) passes the value either mysqlnd_cset_escape_quotes() or mysql_cset_escape_slashes() , depending on the server NO_BACKSLASH_ESCAPES SQL mode;

• with argument null , both of these functions return an empty string.

My opinion is that before turning on the parameter type (in step 2 above) pdo_parse_params() should set the type PDO::PARAM_NULL if null . However, some may argue that this will prevent the processing of null types specific to the type where necessary, in which case the case string (in step 3 above) should definitely handle null values ​​before proceeding to quoter() method drivers.

As a temporary workaround, disabling off-the-shelf operator emulation is usually at best:

 $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);