#49643 [Bgs]: Improper quoting on datetime bound parameters for postgresql

ID: 49643 Updated by: sjoerd@php.net Reported By: alandsidel at dnsstuff dot com Status: Bogus Bug Type: PDO related Operating System: FreeBSD PHP Version: 5.3.0 New Comment: It is poorly documented. See also: Bug #49614 PDOStatement::execute assumes string values in array Previous Comments: ------------------------------------------------------------------------ [2009-09-23 15:09:32] alandsidel at dnsstuff dot com Is that documented somewhere? I don't see it on the PDOStatement::execute page nor the PDO::prepare page. ------------------------------------------------------------------------ [2009-09-23 15:05:37] sjoerd@php.net Thank you for your report. The behavior you describe is not a bug. PDOStatement->execute() assumes the values in the array are string. Furthermore, you pass a string in the array. The result is that the value is escaped as it was a string. This means that it is quoted. ------------------------------------------------------------------------ [2009-09-23 12:21:51] alandsidel at dnsstuff dot com Description: ------------ Running PostGreSQL 8.4, given a simple table like: When using a bound parameter in prepare (named or unnamed) to a timestamp field, the parameter is improperly quoted resulting in errors on INSERT or UPDATE statements that are using expressions rather than simple datetime strings. Reproduce code: --------------- // In the postgresql database CREATE TABLE foo ( id SERIAL UNIQUE NOT NULL, dtSometime TIMESTAMP WITH TIME ZONE NOT NULL ); // Assuming $dbh is a connection to the above database if ($stmt = $dbh->prepare('INSERT INTO foo (dtSometime) VALUES (:dtsometime)') { if ($stmt->execute(array('now() + interval \'1 year\'')) { print("ok!\n"); } else { print_r($dbh->errorInfo()); } } Expected result: ---------------- Expect a row to be inserted and 'ok!' to print. Actual result: -------------- The following is printed on the console: //--cut ( [0] => 00000 [1] => 7 [2] => ERROR: invalid input syntax for type timestamp with time zone: "now() + interval '1 year'" ) //--cut A direct insert with the given SQL works fine as expected, so this must be a quoting issue that is forcing postgresql to interpret the expression as a literal datetime. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=49643&edit=1