4

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?

PatPeter
  • 394
  • 2
  • 17
  • Have a look at https://stackoverflow.com/questions/15621862/insert-varbinary-data-on-mysql-table-via-php to see if this helps. – Nigel Ren Dec 16 '19 at 15:00
  • There is nothing special in varbinary fields. You just compare them like any other data. Where did you get that $hexip and why it hex? – Your Common Sense Dec 16 '19 at 16:07

1 Answers1

2

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);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It works!!! Thank you very much. This issue has been driving me crazy since the beginning of December. Jokes on me for thinking bindParam would automatically convert a PHP hexadecimal integer to binary. – PatPeter Dec 17 '19 at 22:28
  • If it did that, how would you ever search for a literal string that happens to be comprised of the digits 0-9 and letters A-F? :-) – Bill Karwin Dec 17 '19 at 22:43
  • Well, I was providing an integer after converting it with `dechex` to hexadecimal and the bind type `\PDO::PARAM_LOB`, so I thought it would convert it to binary, not take it as a string. I actually forgot that I could manipulate raw binary in PHP. – PatPeter Dec 20 '19 at 06:53