Why is PDO converting my bool(false) param to string('')?

Question

I have a problem with a new MariaDB 10.5.8 install. STRICT_TRANS_TABLES is set, and when I try to use $sql of:

'INSERT INTO test (flag) VALUES (?)'

(where flag is defined as tinyint(1)) with var_dump($params) showing as:

array(1) {
  [0]=>
  bool(false)
}

I get this message:

Incorrect integer value: '' for column `mydb`.`test`.`flag` at row 1

If, instead, I do:

'INSERT INTO test (flag) VALUES (false)'

with no parameters, it works as expected.

This is how I connect to the database:

$this->PDO = new PDO('mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME . ';charset=utf8mb4', DB_USER, DB_PASSWORD, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_STRINGIFY_FETCHES  => false,
]);

$this->PDO->query("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");

and this is how I send the query/params to MariaDB:

$stmt = self::$_instance->PDO->prepare($sql);
$stmt->execute($params);

If I try to insert true instead of false, everything works fine. true is being converted to 1 somewhere along the lines, and false to ''. What am I doing wrong?

Answer 1

All values are treated as PDO::PARAM_STR.

https://www.php.net/manual/en/pdostatement.execute.php

The execute() method does not enable you to specify the variable's type. It always casts to a string, which is why you're getting strange results:

php > var_dump((string) true);
string(1) "1"

php > var_dump((string) false);
string(0) ""

You can use PDOStatement::bindValue() to specify the value's type:

$statement->bindValue('some_bool', true, PDO::PARAM_INT);

Note that MySQL lacks a 'true' boolean type, which is why I'd recommend PDO::PARAM_INT over PDO::PARAM_BOOL.

Answer 2

A much simpler solution would be just to cast a boolean value to int beforehand

$stmt = $PDO->prepare('INSERT INTO test (flag) VALUES (?)');
$stmt->execute([(int)$flag]);

Answer 3

Here is a quick snippet that would solve booleans casting to empty string in pdo params

$params = array_map(fn($param) => is_bool($param) ? intval($param) : $param, $params);