1

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..

Improve this question
2
  • How does this Question differ from dba.stackexchange.com/questions/303200/… ? Commented Nov 28, 2021 at 16:48
  • @Rick James it covers the entire approach of the datatype determination and ideal length calculation, plus the issue with the fix key length. Just in case the API identifier size or encryption key change, I wanted to additionally assure that I understant why I do as you said, and not only that I should do it. But thx a lot for ur help again, really appreciated! Commented Nov 28, 2021 at 22:19

2 Answers 2

Reset to default
2

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.

Improve this answer
6
  • yep absolutely agree. The thing is that the mariadb docs tell you to do the encryption using SHA2() mariadb.com/kb/en/aes_encrypt. But it doesn't say WHY anywhere, nor do the mysql docs. The only reason I can think of is to get a fix key size (128 bits) and not a random length string to be used for the encryption algorithm, as the docs insist on the key size, but again don't tell WHY (same link) . So my hope with this followup question was to list what u said, what I researched and what the docs say, to verify that I get the reasons, to not just copy paste, but understand why.. Commented Nov 28, 2021 at 22:24
  • Often the MariaDB documentation is a slightly improved version of the MySQL doc. In this case, see dev.mysql.com/doc/refman/8.0/en/encryption-functions.html . I still see no valid reason for feeding a sha string by calling the function instead of feeding the resulting constant 128-bit hex string. (Note: it seems that AES will use only 128 bits.) Commented Nov 29, 2021 at 0:55
  • Yup, which is why I checked on both docs, and neither rly explain why.. Yeah well no clue, maybe based on the idea that you don't store the actual hash used as encryption key anywhere in ur application, but yeh with the drawback of running the algorithm with each statement, which is weird.. And yeah huh, seems like AES encryption key length of 128 bits is a total must! Maybe that's the reason why the API sometimes returned "invalid identifier" when decrypted, as I used a randomly generated 32 bytes string as encryption key first. They should rly improve the docs on this... Thx a lot!! Commented Nov 29, 2021 at 1:05
  • It's also astonishing that the AES_ENCRYPT call does not throw a warning if the encryption key is not 128 bits long... Commented Nov 29, 2021 at 1:11
  • @DevelJoe - A guess: If less than 128 bits, it pads with zeros. If more than 128 bits, it takes the first 128 bits. (Seems like the doc should include this.) You could test it with a hex key string that is shorter than 128, then pad it to 128 to see if it still works. Then pad it further with random bits and see if it again works. Then repeat with non-zero padding. (Encrypt a string with a short key string, then try to decrypt with various other strings.) Commented Nov 29, 2021 at 1:15
0

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..

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.