How to manually escape a boolean value for a mySQL Database insert in CakePHP?

Question

i'm having the following code:

  /** @var DboSource $db */
  $db = $this->getDataSource();

  var_dump($db->value($open, 'boolean'));
  die;

  $this->query(
    'INSERT INTO foo(`client_id`, `open`, `modified`, `created`) VALUES(:clientId, :open, NOW(), NOW()) ON DUPLICATE KEY UPDATE modified = now();',
    [
      ':clientId' => $db->value($clientId, 'integer'),
      ':open' => $db->value($open, 'boolean')
    ]
  );

$open is a boolean value, the 'open'-column is defined as tinyint(1). When wrapping $open with $db->value($open, 'boolean') the result is '1', '0' (see the single quotes).

Unfortunately this output leads to a new record with open = false (as '1' is not properly inserted as true)

If I use $db->boolean($open) as option, everything's working correctly.

But I think, $db->value() should do the same job as well?

ndm · Accepted Answer · 2018-03-16T19:52:22.283

Looking at when and how DboSource::value() is being used internally, this is the expected behavior. If it wouldn't do what it does, then values wouldn't get prepared properly for Model::save() operations.

DboSource::value() internally passes the "booleanized" value (DboSource::boolean($value, true) this already adds quotes) to PDO::quote(), where the value is going to be quoted anyways no matter what, ie 0, 1, '0', '1', true, or false, it will always return a quoted value, that is '0', '1' or even '' (for false, which is equal to 0).

The problem with your code is, that values passed to the second argument of Model::query() are finally being passed to PDOStatement::execute() (.../DboSource.php#L458), which treats all values as strings and escapes them accordingly, so finally in your query a '1' will end up as '\'1\'', hence the problems.

TL;DR

This seems to be the expected behavior, it's just poorly documented. So when using the second argument of Model::query(), sanitize the values if necessary (ie cast to integers, booleans, strings, etc), but do not escape them, escape them only when you manually insert them in the actual query string (which should however be avoided whenever possible)!

How to manually escape a boolean value for a mySQL Database insert in CakePHP?

1 Answers1