php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #49643 Improper quoting on datetime bound parameters for postgresql
Submitted: 2009-09-23 12:21 UTC Modified: 2009-09-25 17:19 UTC
From: alandsidel at dnsstuff dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.3.0 OS: FreeBSD
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: alandsidel at dnsstuff dot com
New email:
PHP Version: OS:

 

 [2009-09-23 12:21 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-09-23 15:05 UTC] 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 15:09 UTC] 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-25 17:19 UTC] sjoerd@php.net
It is poorly documented. See also:

Bug #49614 PDOStatement::execute assumes string values in array
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved.
Last updated: Sat Mar 15 09:01:28 2025 UTC