Edit report at https://bugs.php.net/bug.php?id=67213&edit=1
ID: 67213
Comment by: contact at maz dot digital
Reported by: public at gaxweb dot com
Summary: Add PDO::PARAM_DEFAULT constant
Status: No Feedback
Type: Feature/Change Request
Package: PDO related
PHP Version: 5.5.12
Block user comment: N
Private report: N
New Comment:
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?
Previous Comments:
------------------------------------------------------------------------
[2014-12-30 10:42:36] 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.
------------------------------------------------------------------------
[2014-05-07 09:54:05] 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-05-06 17:18:27] 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-06 11:33:31] 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.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=67213&edit=1