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.