php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #67213 Add PDO::PARAM_DEFAULT constant
Submitted: 2014-05-06 11:33 UTC Modified: 2014-12-30 10:42 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: public at gaxweb dot com Assigned:
Status: No Feedback Package: PDO related
PHP Version: 5.5.12 OS:
Private report: No CVE-ID: None
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
30 - 22 = ?
Subscribe to this entry?

 
 [2014-05-06 11:33 UTC] public at gaxweb dot com
Description:
------------
It's currently not possible to bind the SQL DEFAULT keyword to a placeholder in  prepared statements like you can for NULL for example. This would be very helpful for prepared INSERTs (see test script).

It'd allow for more flexible statements, since you wouldn't have to put DEFAULT into the INSERT statement (

So please add a PDO::PARAM_DEFAULT constant. I'm not sure if, the value param of the bindValue() method should actually matter.

Test script:
---------------
// The calories column has a default value set in the TABLE definition
$sth = $dbh->prepare('INSERT INTO fruit (name, calories) VALUES(?,?)');
// This is supported:
$sth->bindValue(1, null, PDO::PARAM_NULL);
// This isn't, but would be helpful:
$sth->bindValue(2, 'DEFAULT', PDO::PARAM_DEFAULT);
$sth->execute();
// actual query executed:
// INSERT INTO fruit (name, calories) VALUES(null, DEFAULT);
// notice the lack of quotes around DEFAULT

Expected result:
----------------
It should replace the 2nd placeholder with the literal keyword DEFAULT, and not put it in quotes.

Actual result:
--------------
It fails.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-05-06 17:18 UTC] requinix@php.net
-Status: Open +Status: Feedback
 [2014-05-06 17:18 UTC] requinix@php.net
What's wrong with

$sth = $dbh->prepare('INSERT INTO fruit (name, calories) VALUES(?,DEFAULT)');
$sth->bindValue(1, null, PDO::PARAM_NULL);
$sth->execute();

Or simply not including calories in the list of fields?
 [2014-05-07 09:54 UTC] public at gaxweb dot com
I thought I had addressed that. Like I said, this is not flexible. That way, you can only ever insert the default value, not an actually different one. So you'd have to prepare both statements (yours and mine) to make it work for both cases.

Imagine a loop, where for every execute() different values will be bound. I'd have to know the column's default value beforehand to make use of my prepared statement without access to a PDO::PARAM_DEFAULT constant. If the constant was available, I could just leave that up to the database (less duplication).
 [2014-12-30 10:42 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 [2020-03-25 12:45 UTC] contact at maz dot digital
Hi. I faced today the same problem. Since the previous comment's question was unanswered:
> What's wrong with
> $sth = $dbh->prepare('INSERT INTO fruit (name, calories) VALUES(?,DEFAULT)');
> $sth->bindValue(1, null, PDO::PARAM_NULL);
> $sth->execute();

> Or simply not including calories in the list of fields?

So I wanted to share my use case where I need PDO::PARAM_DEFAULT.

I have a table like this:
```SQL
CREATE TABLE IF NOT EXISTS ants (
    uuid UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
    city_uuid UUID NOT NULL,
    born_time TIMESTAMP NOT NULL DEFAULT NOW() + INTERVAL '6 HOURS'
)
```

I'm working on a game and I want to create many model in only one insert into request. So I'm looping over some data and create the SQL statement:

```php
$sql = "INSERT INTO ants (city_uuid, born_time)
            VALUES";
foreach ($ants as $index => $ant) {
  $data["caste_${index}"] = $ant["caste"];
  $data["born_time_${index}"] = $ant["born_time"] ?? 'DEFAULT'; // << Here is the problem
  $sql .= "(:city_uuid, :born_time_${index}),";
}

$sql = substr($sql, 0, -1) . ' RETURNING uuid';
```

The problem is: sometimes, the $ant["born_time"] will be generated by php, sometimes I just want to set the default value of the column so I can't just set "DEFAULT" inside my insert into, I need bindings, but "DEFAULT" in bindings is escaped and became a wrong timestamp parameter.

I can't fix this "properly" without PDO::PARAM_DEFAULT, the only workaround I've found for now is this:
```php
$born_time = $ant["born_time"] ?? "NOW() + INTERVAL '6 HOURS'"; // Set the timestamp from PHP or set a "fake default" value
$sql .= "(:city_uuid, :dead_time_${index}, ${born_time})"); // Escape the born_time bindings
```

But it's kind of ugly: why set a default value on table if I can't use it? And here I'm escaping the pdo bindings, it's fine because it's a timestamp calculated SQL statement which is injected, but what would happen if my column were a string?
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC