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

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?

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);