Mysql: xor a string with a key

I want to Bitwise-XOR a string (actually its binary representation) with a KEY.

The result of the operation should be represented as HEX.

What I have: 'a' - the UTF-8 String to be changed. 'ACF123456' - the key in HEX.

Result seen as BIGINT:

select CONV(HEX('a'), 16, 10)  ^  CONV('ACF123456', 16, 10);

Result seen as HEX:

select CONV( CONV(HEX('a'), 16, 10)  ^  CONV('ACF123456', 16, 10), 10, 16);


  1. Is the conversion above done correctly?
  2. What happens if the string is too long (i.e instead of 'a' we have 'a veeeeeery long string')? It seems that the conv() function has a limitation (is it the 64-bit precision from the documentation)? And besides the XOR operator ^ has also a limitation, related to the nr. of bits of the returned result. Any solutions that work for any string (a stored procedure is allowed)?



Your conversions look fine to me. And as you point out, both CONV() and ^ have indeed a 64-bits precision.

2^64 = 16^16, therefore strings of more than 16 hexadecimal digits should convert to integers larger than 2^64. However, such strings will be brutally (silently) truncated from the left when attempting to convert them to integers.

The point of my solution here is to slice such strings. Obviously, the result may not be displayed as an integer, but only as a string representation.

Let @input be your "string to be changed" and @key, your "key".

  1. Assign HEX(@input) to @hex_input. No problem here since HEX() works with strings.
  2. Slice @hex_input into 16 hexadecimal digit long strings, starting from the right
  3. Likewise, slice @key into 16 digit long strings.
  4. Compute the X-OR of each 64-bit slice of @hex_input with each 64-bit slice of @key, starting from the right. Use CONV(@slice, 16, 10). If either @hex_input or @key has less slices than the other string, then X-OR the remaining slices of the other string with 0.
  5. Convert each 64-bit number resulting from the X-OR in point 4. back into an hexadecimal string with UNHEX().
  6. Reassemble the resulting slices. This is your result.

A three-columns TEMPORARY table could be used as an array to store slices of @hex_input, @mask and the resulting slices.

Put this all together into a stored procedure, and voilĂ !

You sound like you have some skills in MySQL, you should be able to translate the above into real code. But I'll be happy to help if you need further guidance.

Need Your Help

JazzHub and Bitbucket

bitbucket ibm-cloud devops ibm-jazz

I am trying to do continuous delivery with my code on Bitbucket using Bluemix DevOps tools.

Find the Method that will be executed in the controller using reflection

I'm maintaining an ASP.NET MVC site where they are doing their own security. So they have created a class derived from AuthorizeAttribute. In the OnAuthorization, they have some reflection code t...