0

I'm building a virtual Keyring in our DB to allow users to store PGP/GPG public keys with relation to email addresses so that they can optionally encrypt all communications our system sends them. I'm just building the table now and wasn't sure about the column type to store the fingerprint.

From what I understand fingerprints will be 128 or 160 bits long, generally in the form of:

43:51:43:a1:b5:fc:8b:b7:0a:3a:a9:b1:0f:66:73:a8

... which I believe is just a 32bit MD5 hash / UUID. I'm guessing I can store this as a 16bit Binary value, or a 32 char string...

Is there a best practice, and if so what is it, or lacking that, what's your preference?

As a related follow up, is there any reason to Encrypt the public key string itself, or is that redundant since it is public after all?

EDIT: BTW, this is MySQL currently 5.5, but will be 5.7 in the near future.**

EDIT 2: I can't find any examples out there of a table schema for storing this type of record and I admittedly have only minimal understanding beyond the basics of using public/private keys in my own role, so while I could discern that the fingerprint is a hash, I'm unaware of the different formats for public keys and what range their lengths can come in (i.e. what about the -----BEGIN CERTIFICATE----- components, what size field will these need?).

If possible can someone provide a basic CREATE TABLE statement with what you see as an optimal fieldset? Below is what I've created thus far, is it sufficient, would you change anything?

CREATE TABLE `public_keys` (
    `ai_col`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
    /* ... other foreign keys ... */
    `label`  varchar(100) NULL ,
    `fingerprint`  binary(16) NOT NULL ,
    `key_type`  varchar(10) NOT NULL ,
    `public_key`  varchar(1024) NULL ,
    PRIMARY KEY (`ai_col`)
);
Improve this question
  • Store it as a string. Over time you'll be dealing with different keys in different formats. May also want a key_type column or something. – Philᵀᴹ Jan 26 '16 at 15:17
  • @Phil Thanks for the suggestion, I've updated the question a bit with what I've got so far. Storing the fingerprint as a string means twice as much space requirement, and I'm not sure what the benefit of that would be, unless of course fingerprints are not all 32 char hashes?!?! I just don't know. – oucil Jan 26 '16 at 15:54
1

If I'm not mistaken, the fingerprint is a hexadecimal number. That means you could store it as a binary number, since two hexadecimal characters can be represented by a single byte, making a 32-character hexadecimal string = 16 bytes.

Here's the documentation on binary and varbinary data types for MySQL 5.7 (I don't have a test environment handy).

I do something similar for IP addresses based on this question and answer from StackExchange. Store the data as binary, convert it back to text when it needs to be displayed or compared against another string. Create a view that calls the functions as a part of it so you can easily get and store the full string when you want it.

Also, public keys are indeed public, and are often stored in searchable databases like this one.

Improve this answer
  • Thanks Norman, once the ':' are removed, you're correct, the fingerprint is just a base 16 hash, it's just broken into 2-char hex digits, so when stripped down it can actually be stored in a BINARY(16) field. Though, your math is off... a 32 char hash stored as a string would require 32 bytes, since it's only using hex characters which only require 1 byte each, not 2. The same 32 char hash converted to a base 2 binary string would only require 16 bytes since binary has no character set. – oucil Jan 28 '16 at 15:38
  • You're right, I'll edit the answer to reflect the correct math... I seem to have confused the storage of hexadecimal and ASCII. – NDC Jan 29 '16 at 3:15

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.