PDO prepared statements with bit fields

2018-06-23 19:10:52

I experience an issue when working with PDO prepared statements and bit data types

When retrieving the result from the DB with PDO prepared statement an empty result set is returnd, but the correct results are returned if the query is done by a normal sql query

If the field is_demo in the where clause is omitted it works as it should!?

DB table structure

CREATE TABLE IF NOT EXISTS `user` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` smallint(5) unsigned NOT NULL,
  `is_admin` bit(1) NOT NULL,
  `is_demo` bit(1) NOT NULL,
  `name` varchar(30) NOT NULL,
  `pass` varbinary(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  KEY `is_admin` (`is_admin`),
  KEY `is_demo` (`is_demo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

PDO prepared statement

$sql = "SELECT id,client_id,is_admin,is_demon"
    ."FROM usern"
    ."WHERE is_demo=? && client_id=? && name=? && pass=?n"
    ."ORDER BY name";
$fields = [
    0,
    500,
    'test',
    123
];
$sth = $dbh->prepare($sql);
$sth->execute($fields);

Return

PDOStatement Object
(
    [queryString] => SELECT id,client_id,is_admin,is_demo
FROM user
WHERE is_demo=? && client_id=? && name=? && pass=?
ORDER BY name
)

No rows returned

Ordinary query

SELECT id, client_id, is_admin, is_demo
FROM user
WHERE is_demo=0 && client_id=500 && name='test' && pass=123
ORDER BY name

Return

Returns the correct number of rows

这里是问题n不能在查询中

$sql = "SELECT id,client_id,is_admin,is_demon"
."FROM usern"
."WHERE is_demo=? && client_id=? && name=? && pass=?n"
."ORDER BY name";

MySQL可以将字符串值“0”转换为在字符串之前用“B”编写这样的代码：

$sql = "SELECT id,client_id,is_admin,is_demon"
    ."FROM usern"
    ."WHERE is_demo=B? && client_id=? && name=? && pass=?n"
    ."ORDER BY name";
$fields = [
    '0', 
    500,
    'test',
    123
];
$sth = $dbh->prepare($sql);
$sth->execute($fields);

链接地址: http://www.djcxy.com/p/66704.html