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 (255.255.248.0) and compare it with our
-- subnet (10.10.0.0)
-- 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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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