Best way to store encrypted API identifiers (MariaDB)

Posted on

Question :

So for max security I have to encrypt some string keys which I need to call specific actions of an API, at a very regular basis of the use of an application, so performance should also be considered.

The data to be encrypted are strings of like 35 – 40 characters.

The RDBMS I’m using is MariaDB.

To do so properly, I’ve been checking in the docs, and I’m using AES_ENCRYPT() to encrypt and store the data. This comes with, as I understood:

  • the need of a key of a fix length, like 128 bits. An example of how I would store data is thus:

INSERT INTO sample VALUES( AES_ENCRYPT( "secret_identifier", SHA2( "my_encryption_key", 512))

  • the datatype of the column holding the data is VARBINARY, To avoid any problems concerning padding / stripping of any characters, etc. At least my conclusion was that to be the best type, as I need to re-use the decrypted raw version of the data for regular API calls.

  • the size of the column field is calculated according to this, so:

16 x (trunc(string_length / 16) + 1)

  • knowing that we have a length of max 40 characters, count with like 80 characters to be on the safe side, which gives a resulting length of:

16 x (5 + 1) = 96

So I’d use a VARBINARY(100) column to store the data.

  • to retrieve the raw unencrypted data, I’d then use:

SELECT AES_DECRYPT( column_name, SHA2( "my_encryption_key", 512))

Are these considerations correct? I have to assure that, as thousands / millions of API identifiers will be stored. I don’t even want to think of the nightmare of needing to regenerate all of the API identifiers (for this generation, a human needs to do it!) because the applied encryption somehow corrupted the data..

Answer :

SHA2( "my_encryption_key", 512) is no more secure than my_encryption_key but takes longer.

Encrypting something that is already encrypted (even if by hand) obfuscates, but does not add to the security. In some rare situations, it undoes the obfuscation.

The main security question left on the table: Where is the string “my_encryption_key” stored? It seems to be sitting in plaintext in your PHP code. But how secure is that?

One trick I have seen is to have a mod in Apache to load the key from a “root 400” file, then make that available to your PHP program. That means the key is in two places — a file that is as secure as “root” on the system and in memory (for Apache to hand to PHP). Both are much more secure than plaintext in the PHP code.

And the system needs to make it relatively impossible for a hacker to inject their own PHP code and run it.

I’ve created a fiddle which creates two tables, one which stored the encrypted data using a VARBINARY(100) column, another one wich stores it in a BLOB column. In the latter case, you then retrieve the decrypted data by casting the result to CHAR (for testing of what this does).

I’ve also used an encryption using a random generated 32 bytes long key, and another one using its 128 bits long hash, for both tables, to check upon the influence of the key length + using AES.

You can find the fiddle here.

Very interestingly, we can see that there’s totally no difference in terms of the key size, at least that seems to be the case. The comparisons in between the raw data and the encryption + decryption using a random length key + the raw data and the encryption + decryption using a 128 bits long key both return a match.

However, when I don’t specifiy the character limit of the CHARcolumns in the sample_blob table, I get an error when running the schema in the fiddle, saying that the decrypted data gets truncated. When I then specify CHAR(50) instead of CHAR as the datatype for the concerned columns of the sample_blob table, it all goes well.

From this, I conclude (this is a guess!) that the main issue why I had trouble with my API identifiers was that I’ve actually used the approach in table sample_blob; hence used the BLOB datatype to store the encrypted data, which requires you to do some kind of conversion upon the retrieval of the decrypted data, to be able to read it as string. Then, I most likely truncated the decrypted data when I retrieved the data using:

SELECT CAST ( AES_DECRYPT( encrypted_string, "key" ) AS CHAR )

resulting in non-equal identifiers when decrypted. My conclusion from this is that the best way is to follow the approach described in my question, using a VARBINARY(100) column to store the encrypted data, where the length of 100 bytes is calculated as described above. When you then insert the data, I’ve found in the docs that your encrypted data may actually get stored without throwing an error, although the data you’ve inserted is actually truncated! In this case, MySQL only throws a warning, and only interrupts with an error if you run the query in strict mode, as documented here.

In theory, your API provider could change the structure of the API keys, making them longer or whatever, which could lead to your column length limit (in this case, VARBINARY(100)) to not be long enough anymore, hence truncating the data. You wouldn’t even note this without checking for warnings, while your decryption would also work without errors, but it would return the decrypted truncated data, hence corrupted API identifiers, although the system never really reported an error!
To store the encrypted data, I would thus verify that no SQL warning has been thrown upon the insert.

To retrieve the data, you then simply use:

SELECT AES_DECRYPT( encrypted_string, SHA2("key",512) )

Hence no conversion / casting is used, so you shouldn’t run into data truncation issues. I’ve also used the hash of the key, to assure that the used key is 128 bits long, even if that doesn’t seem to influence my example above; again to minimize the chance of any padding / truncation issues, this time related to the used encryption key. Both the MYSQL and MariaDB Docs say that a fix key length is required in the use of AES, but don’t say why, so I prefer to stick to it..

Leave a Reply

Your email address will not be published. Required fields are marked *