Query with hex number using PDO in PHP - PDO always add quotes around the hex number?

2
votes

I am trying to execute this query using PDO:

select * from users where uuid = 0x1e8ef774581c102cbcfef1ab81872213;

I pass this SQL query to the prepare method of PDO:

select * from users where uuid = :uuid

Then I pass this hashmap to execute:

Array ( [:uuid] => 0x1e8ef774581c102cbcfef1ab81872213 )

It looks like this query is being executed on the mysql server, when I call fetchAll: 

select * from users where uuid = '0x1e8ef774581c102cbcfef1ab81872213';

How can I execute the query without having PDO add the quotes around my hex?

Thanks, Steve

1
phppdohex
Perhaps you should be storing your UUIDs as strings. The number specified seems far too large for any reasonable numerical data type, anyways. – Waleed Khan
arxanas: the uuid's are currently stored as binary(16). I would have a performance hit if I change it to char(32), right? – Steve Rodrigue
Perhaps, but is performance actually an issue in your app? – Waleed Khan
arxanas: yes, performance is very important. The application will receive million of unique visitors per day. – Steve Rodrigue

1 Answers

0
votes

Your value HAS to be inserted as a string, as it's far beyond (128bit) what can be represented as a normal number in PHP in both 64bit and 32bit editions.

e.g. skip the placeholders and embed it into the query string directly:

$uuid = '0x....';
$sql = "SELECT ... WHERE uuid = $uuid";

which means you lose the benefits of placeholders, and will have to deal with SQL injection mitigation directly.

You don't mention which DBMS you're using, but you might be able to get around it by exploiting your DBMS's casting functions, eg.

SELECT... WHERE uuid = CAST(:uuid AS uuid_type)

with this, even though it goes into the DB as a string, it'll be treated as a native uuid when push comes to shove.