How can I get string from the blob (an id) stored in fusionauth DB?

Question

For analytical purposes I want to run query against Fusionauth DB. But the id fields are stored as binary blobs. Do you have a function (java or sql) I can use to convert this blob into a string?

Answer 1

I'm assuming you are using MySQL. FusionAuth uses UUIDs for unique Ids, and in MySQL we store these as BINARY(16).

If you want to select this value in a human readable form, you can perform a select such as SELECT HEX(id) FROM table_name.

If you want to select this column and deserialize it into a Java UUID type, you can use code similar to the following:

public UUID fromByteArray(byte[] ba) {
  long msb = 0;
  long lsb = 0;
  for (int i = 0; i < 8; i++) {
    msb = (msb << 8) | (ba[i] & 0xff);
  }
  for (int i = 8; i < 16; i++) {
    lsb = (lsb << 8) | (ba[i] & 0xff);
  }
  return new UUID(msb, lsb);
}