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
 [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

Add a Patch

Pull Requests

Add a Pull Request

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-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 12:01:27 2024 UTC