Converting a MySQL Binary into an Integer

While writing a new accounting system for my dormitory (to be released here soon) I found the need to convert a MySQL Binary (string) into an Integer in order to use binary operators (like AND, NOT, XOR…). Hard to find, but this one works:

cast(conv(hex(_orig_ip_saddr),16,10) as unsigned integer)

It first changes the binary(16) into a hex representation which then is converted into a decimal representation and finally casted into an integer.

I use this to look whether an IP is in our subnet:

-- convert binary ip string to upper case hex, convert it to decimal base and cast it to int
-- now we can AND it with the subnet mask ( and compare it with our
-- subnet (
-- phew!
IF (cast(conv(hex(_orig_ip_saddr),16,10) as unsigned integer) & 0xfffff800)=0x0a0a0000 THEN ...

Stay tuned for the complete accounting solution based on ulogd2.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s