PDO-MySQL: Boolean values get converted to 1 or empty string on prepared statement binding

Question

I'm trying to insert some boolean values into JSON-type columns.

$taskSql = "INSERT INTO Tasks (data, taskListId) VALUES (JSON_OBJECT('title', :title, 'done', :done), :taskListId)";
$taskStatement = $connection->prepare($taskSql);
$taskStatement->execute([":title" => $task->title, ":done" => $task->done, ":taskListId" => $id]);

Which results in following SQL being executed.

-- $task->done is false
INSERT INTO Tasks (data, taskListId) VALUES (JSON_OBJECT('title', 'New Task', 'done', ''), '12')
-- $task->done is true
INSERT INTO Tasks (data, taskListId) VALUES (JSON_OBJECT('title', 'New Task', 'done', '1'), '12')

Is there a way to make PDO turn those into TRUE or FALSE in the SQL-statement, which would then convert them into proper JSON-boolean-values.

EDIT:

I've now tried using bindParam and bindValue instead of the argument to execute. Those don't fix the problem as PDO still converts the boolean values to 0 or 1.

EDIT: Looks like there is an 11 year old bug report, that still hasn't been addressed.

@HamzaAbdaoui They are genuine booleans, I've made sure of that. — Philippe, Jul 31 '17 at 10:56
I have this idea, try to change the `$task->done` into a string like this : `$newDone = ($task->done) ? 'true' : 'false';` — Hamza Abdaoui, Jul 31 '17 at 11:00
@HamzaAbdaoui That would save as JSON strings rather than booleans. I don't want to do that. — Philippe, Jul 31 '17 at 11:01
the problem here is that in `mysql` Boolean types are defined as, and i quote, "These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:" — Hamza Abdaoui, Jul 31 '17 at 11:04
Yes, but they are not in JSON. And we are talking about JSON booleans not SQL booleans. — Philippe, Jul 31 '17 at 11:05
All PDO would need to do is insert TRUE or FALSE (Standard SQL values) for boolean bindings and MySQL would take care of the rest. — Philippe, Jul 31 '17 at 11:12

score 0 · Accepted Answer · answered Nov 14 '17 at 12:21

0

I endet up changing the insert line to

INSERT INTO Tasks (data, taskListId) VALUES (JSON_OBJECT('title', :title, 'done', :done = TRUE || :done = '1'), :taskListId);

Not a solution but a workaround.

answered Nov 14 '17 at 12:21

Philippe

1,715
4
25
49

PDO-MySQL: Boolean values get converted to 1 or empty string on prepared statement binding

1 Answers1

Linked

Related