chris579 chris579 - 3 months ago 18
MySQL Question

Bitwise operating issue

I'm trying to rebuild a function for generating GUIDs used by the game Arma 3 in a MySQL function. There are some examples in various languages over here: https://gist.github.com/Fank/11127158

Basicially this is what I tried so far:

CREATE DEFINER=`root`@`localhost` FUNCTION `generateGUID`(playerid varchar(17)) RETURNS varchar(32) CHARSET latin1
BEGIN
DECLARE temp bigint;
DECLARE i int;

SET i = 0;
SET temp = 0;

WHILE i < 8 DO
SET temp = temp + CHAR(playerid & 0xFF);
SET playerid = playerid >> 8;
SET i = i + 1;
END WHILE;

RETURN MD5("BE" + temp);
END


What I understood so far orientating at the php example:

First of all we have the steamid given as a varchar.

Afterwards we are iterating 8 times and adding the char of the bitwise addition of the playerid and the value of 0xFF. Then we are performing a 8 bitwise right shift on the playerid.

Afterwards the string "BE" is added the temp result and a md5 is generated of this and returned.

However I'm facing the issue that this function always returns 0. I tried many things so far, like using a blob instead of an int for the temp var.

Edit: After the hint to use
CONCAT
instead of the
+
operator the result is still not matching with the guid calculators out there.

The code looks now like this:

CREATE DEFINER=`root`@`localhost` FUNCTION `generateGUID`(playerid varchar(17)) RETURNS varchar(32) CHARSET latin1
BEGIN
DECLARE temp bigint;
DECLARE i int;

SET i = 0;
SET temp = "";

WHILE i < 8 DO
SET temp = CONCAT(temp, CHAR(playerid & 0xFF));
SET playerid = playerid >> 8;
SET i = i + 1;
END WHILE;

RETURN MD5(CONCAT("BE", temp));
END


However, using the playerid
76561197996545192
the function returns
2a0f7ebed67e04afaf7ea032e1ed22e3
instead of
cd97cc68c1038b485b081ba2aa3ea6fa
which should be the expected output.

Answer

This works for me:

DROP FUNCTION IF EXISTS `generateGUID`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `generateGUID`(`playerid` BIGINT UNSIGNED) RETURNS varchar(32) CHARSET latin1
    DETERMINISTIC
BEGIN
 DECLARE temp text CHARSET ascii;
 DECLARE i int;

 SET i = 0;
 SET temp = "";

 WHILE i < 8 DO
    SET temp = CONCAT(temp, CHAR(playerid & 0xFF));
    SET playerid = playerid >> 8;
    SET i = i + 1;
 END WHILE;

RETURN MD5(CONCAT("BE", temp));
END//
DELIMITER ;

http://sqlfiddle.com/#!9/6cc709/1

Comments