2

I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes.

hash1: string(64) "439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2" 
hash2: string(64) "c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43" >
script combo: string(64) "838559e2bd662dc116882c04d326a967189b4da9545e985114019879b6f457f1" 
mysql combo:  string(3) "439"

PHP:

$combo = bin2hex($hash1 xor $hash2);

MYSQL:

SELECT `hash1` ^ `hash2` as combo

As far as I can tell, the issue is that bit-wise XOR is limited to 64bit values, but I can't find a straightforward way to bi-wise XOR larger values.

Improve this question
1

Perhaps you could the following:

  • split each value into little two character strings
  • convert each 2-character string into a decimal numebr
  • XOR each decimal number side by side
  • CONCAT all results
  • Lower Case the Result

Sounds simple, eh??

Wanna see the code in MySQL ??

Here we go ...

SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Wanna see it executed ??? Here we go ...

mysql> SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----------------------------------------------------------------+
| HashResult                                                     |
+----------------------------------------------------------------+
| 838559e2bd662dc116882c4d326a967189b4da9545e98511419879b6f457f1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Sorry, it's late and I was not in the mood to do a Stored Procedure !!!

If you are interested why this works, this query let's look at the details:

SELECT *,LCASE(HEX(v1^v2)) v3 FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Here are the details:

mysql> SELECT *,LCASE(HEX(v1^v2)) v3 FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----+------+------+------+
| x  | v1   | v2   | v3   |
+----+------+------+------+
|  1 | 67   | 192  | 83   |
|  3 | 156  | 25   | 85   |
|  5 | 54   | 111  | 59   |
|  7 | 103  | 133  | e2   |
|  9 | 73   | 244  | bd   |
| 11 | 177  | 215  | 66   |
| 13 | 73   | 100  | 2d   |
| 15 | 224  | 33   | c1   |
| 17 | 35   | 53   | 16   |
| 19 | 140  | 4    | 88   |
| 21 | 36   | 8    | 2c   |
| 23 | 150  | 146  | 4    |
| 25 | 130  | 81   | d3   |
| 27 | 224  | 198  | 26   |
| 29 | 21   | 188  | a9   |
| 31 | 96   | 7    | 67   |
| 33 | 62   | 38   | 18   |
| 35 | 250  | 97   | 9b   |
| 37 | 70   | 11   | 4d   |
| 39 | 102  | 207  | a9   |
| 41 | 10   | 94   | 54   |
| 43 | 60   | 98   | 5e   |
| 45 | 100  | 252  | 98   |
| 47 | 125  | 44   | 51   |
| 49 | 8    | 28   | 14   |
| 51 | 188  | 189  | 1    |
| 53 | 230  | 126  | 98   |
| 55 | 154  | 227  | 79   |
| 57 | 24   | 174  | b6   |
| 59 | 74   | 190  | f4   |
| 61 | 122  | 45   | 57   |
| 63 | 178  | 67   | f1   |
+----+------+------+------+
32 rows in set (0.00 sec)

mysql>
Improve this answer
1
  • 1
    +1 but only if you typed out that UNION by hand. :) A slightly shorter operation might use CONV(SUBSTR(?,?,16),16,10) since the bitwise operators are good to 64 bits internally but I'll leave that to the imagination. – Michael - sqlbot Nov 15 '13 at 1:28
1

I would opt for a much more readable deterministic stored function:

DELIMITER //
DROP FUNCTION IF EXISTS bitwise256_xor //
CREATE FUNCTION bitwise256_xor(argL CHAR(64), argR CHAR(64)) RETURNS   CHAR(64) 
DETERMINISTIC
BEGIN
  DECLARE i INT;
  DECLARE answer CHAR(64);
  DECLARE L CHAR(2);
  DECLARE R CHAR(2);
  DECLARE N INT;
  DECLARE V CHAR(2);
  SET i = 0;
  SET answer = '';
  REPEAT
    SET L = SUBSTR(argL, 1+i*2, 2);
    SET R = SUBSTR(argR, 1+i*2, 2);
    SET N = CONV(L,16,10) ^ CONV(R,16,10);
    IF N < 16
    THEN
      SET V = CONCAT('0',HEX(N));
    ELSE
      SET V = HEX(N);
    END IF;
    SET answer = LCASE(CONCAT(answer, V));
    SET i = i + 1;
    UNTIL i = 32
  END REPEAT;
  RETURN answer;
END //

delimiter ;

Run like:

SET @masterPass = 'asdjfkl23rjklasdfjklqe';
SET @dn = 'cn=rob fielding,ou=decipher,c=us';
SET @efilekey = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
SET @usermasterkey = sha2(concat(@masterPass,@dn),256);
set @a = bitwise256_xor(@efilekey, @usermasterkey);
set @b = bitwise256_xor(@a, @usermasterkey);
select @usermasterkey, @efilekey, @a, @b;

Notice that it repeats after two applications of the xor, such that @b == @efilekey:

@usermasterkey  @efilekey   @a  @b

73c0ed2228ff34910cb8de348d8fdc0665e53150fb6aa31eb249fdce8553998f
439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2
305cdb45614e7d712f34faa20f6fc9665b1f7736f156c763baf51b549d19e33d
439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2
Improve this answer
1

Starting from RolandoMySQLDBA answer:

XOR result must be two digits in hex format.

In order to avoid loosing zeros in one digit result of xor, as happens in result x = 23

+----+------+------+------+
| x  | v1   | v2   | v3   |
+----+------+------+------+
| 21 | 36   | 8    | 2c   |
+----+------+------+------+
| 23 | 150  | 146  |   4  |

You can add the next enhancement in GROUP_CONCAT call (lpad(hex(v1^v2), 2, '0')):

mysql> SELECT LCASE(GROUP_CONCAT(lpad(hex(v1^v2), 2, '0')  SEPARATOR '')) HashResult FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.