How do I prepare a statement with VARBINARY in PHP PDO for MySQL?

Question

This is the code that I'm using to try comparing to a VARBINARY using PHP PDO:

$st = $pdo->prepare('SELECT * FROM `xf_ip` WHERE user_id = ? AND ip = ?;');
$st ->execute(array($new_row[':forums_id'], $hexip));

I tried prefacing it with 0x ('0x' . $hexip), as well as using bindParam:

$st = $pdo->prepare('SELECT * FROM `xf_ip` WHERE user_id = :user_id AND ip = :ip;');
$st->bindParam(':user_id', $new_row[':forums_id'], \PDO::PARAM_INT);
$st->bindParam(':ip', $hexip, \PDO::PARAM_LOB);
$st->execute();

This is the only query that works, but it's an unsafe query because it's not prepared and could be vulnerable to SQL injection:

$st = $pdo->query('SELECT * FROM `xf_ip` WHERE user_id = ' . (int) $new_row[':forums_id'] . ' AND ip = 0x' . $hexip);

The hexip has to be in the format 0xFFFFFFFF with no quotes, and not in integer format, otherwise MySQL will not accept it.

Is this not possible with PDO?

Answer 1

Parameters always act as if you had passed them as a string, at least in the MySQL PDO driver. The eight-character string 'FFFFFFFF' is not equal to the 4-byte binary string represented by 0xFFFFFFFF. The following two SQL statements are NOT the same:

SELECT * FROM `xf_ip` WHERE user_id = :user_id AND ip = 0xFFFFFFFF
SELECT * FROM `xf_ip` WHERE user_id = :user_id AND ip = 'FFFFFFFF'

But passing 'FFFFFFFF' as your parameter executes a statement like the latter one.

There are two solutions:

One is to pass a string of hex digits, but use UNHEX() in SQL to convert those hex digits to the equivalent binary string before you compare it to your column:

SELECT * FROM `xf_ip` WHERE user_id = :user_id AND ip = UNHEX(:ip)

The other solution is to pass a binary string, after first un-hexing it in PHP:

$binip = hex2bin($hexip);
$st->bindParam(':ip', $binip, \PDO::PARAM_LOB);