MySQL – basic hash jumbler function

[adsense id=”0514458240″ width=”468″ height=”60″]

There are times when we need to mask some information. This can be achieved easily using some kind of hashing function which will reverse back to original if we re-run the basic hash function with the hashed out value.

This is a small white paper which shows a basic function which can achieve this. The function simply transposes each number to another number. The number transposed is fully reversible. In other words, 1 will become 2 and 2 will become 1.

DROP FUNCTION IF EXISTS jumbler;
DELIMITER $$
CREATE FUNCTION jumbler(pNumber VARCHAR(20)) 
RETURNS VARCHAR(20)
NOT DETERMINISTIC
SQL SECURITY INVOKER
NO SQL
BEGIN
	DECLARE lRes VARCHAR(20) DEFAULT '';
	DECLARE lMod VARCHAR(1);
	DECLARE lNew INTEGER;
	DECLARE lNumber INTEGER;

	SET lNumber := pNumber;
	WHILE lNumber > 0 DO
		SET lMod := RIGHT(lNumber,1);
		CASE lMod
		WHEN '0' THEN SET lRes := CONCAT(lRes, '4');
		WHEN '4' THEN SET lRes := CONCAT(lRes, '0');
		WHEN '1' THEN SET lRes := CONCAT(lRes, '8');
		WHEN '8' THEN SET lRes := CONCAT(lRes, '1');
		WHEN '2' THEN SET lRes := CONCAT(lRes, '3');
		WHEN '3' THEN SET lRes := CONCAT(lRes, '2');
		WHEN '6' THEN SET lRes := CONCAT(lRes, '9');
		WHEN '9' THEN SET lRes := CONCAT(lRes, '6');
		WHEN '5' THEN SET lRes := CONCAT(lRes, '7');
		WHEN '7' THEN SET lRes := CONCAT(lRes, '5');
		END CASE;
		SET lNumber := LEFT(lNumber,LENGTH(lNumber)-1);
	END WHILE;
	RETURN lRes;
END;
$$
DELIMITER ;

Here it is in action. This is simply to demonstrate how something like this is possible. One should look more closely at a more suitable function.

mysql> select jumbler(12345);
+----------------+
| jumbler(12345) |
+----------------+
| 70238          | 
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(70238);
+----------------+
| jumbler(70238) |
+----------------+
| 12345          | 
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(270238);
+-----------------+
| jumbler(270238) |
+-----------------+
| 123453          | 
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(2702382);
+------------------+
| jumbler(2702382) |
+------------------+
| 3123453          | 
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select jumbler(3123453);
+------------------+
| jumbler(3123453) |
+------------------+
| 2702382          | 
+------------------+
1 row in set, 1 warning (0.00 sec)