1
$\begingroup$

I want to store account numbers and routing numbers into SQL database, and i was looking for best way to encrypt/decrypt this sensitive information. After researching i found i can use symmetric (like AES) or asymmetric (like RSA, X509 Certificate) algorithms. I just want to encrypt/decrypt the information locally on the server and store it into SQL DB. Is it recommend to use AES with 128 bit HEX key for such purpose?
Use of asymmetric algorithms seems to be overkill since encryption/decryption will happen locally on the same server.

(Note that this information will ultimately transfer to vendor using SFTP, however during that transfer we will be using vendor provided PGP public key for encryption so vendor can decrypt it. What i am looking here is encryption/decryption for local storage)

$\endgroup$
  • 2
    $\begingroup$ Most,commercial modern databases offer column encryption (at least Oracle has a column encryption and Microsoft offers Always Encrypted for client side crypto) $\endgroup$ – eckes Apr 16 at 2:12
  • $\begingroup$ Absolutely! Oracle has Transparent Data Encryption via AES. And it handles all of the key management too, which is not at all trivial. The other vendors will have something as well. You might want to review what the database already provides before rolling your own. $\endgroup$ – Paul Uszak Apr 17 at 0:24
2
$\begingroup$

First, you should carry out a threat analysis and determine the attack scenarios that the encryption is supposed to protect you against. Without such an analysis, all you're doing is sprinkling magic crypto dust over your database and hoping that it will somehow protect you from any and all possible threats. (Spoiler: It probably won't.)

For example, some more or less plausible threats might include:

  • a remote attacker gaining access to the database via SQL injection;
  • a remote attacker gaining access to an entire server, including all data and keys stored on it, via a remote code execution (and/or privilege escalation) vulnerability;
  • a remote attacker guessing the password to a privileged account;
  • an employee with physical access to the servers carelessly disposing of a disk that used to contain sensitive data;
  • a privileged employee carelessly copying sensitive data to an insecure or publicly accessible location;
  • a privileged employee maliciously accessing or modifying sensitive data; and/or
  • a privileged employee's workstation being infected with malware.

Encryption can protect against some of these threats, but not all of them, and the extent to which it can protect against some of those threats depends on the degree of compartmentalization (e.g. separating publicly accessible services onto dedicated servers, implementing strict firewall rules, requiring secure multi-factor authentication for all accounts, storing active encryption keys in a hardware security module, splitting inactive master keys into multiple shares and storing them in separate locations, etc.) that you're willing to implement and endure.

Of course, it's also possible that your sole proximate reason for encrypting your data is to pass a certification that requires it. In that case, you may wish to review the certification standards and determine what they require of your encryption and what threats they expect it to counter.


Depending on your specific requirements and threat model, it's possible that your need for encryption of data at rest might be sufficiently served simply by activating built-in encryption features of your database software, or even just by storing the database on an encrypted disk. If so, that's certainly the simplest option, even though it protects only against a limited set of attacks.

Database front-end such as CryptDB may also be useful, although again, you should carefully review their ability to protect you against the threats you've determined to be relevant while still providing the access to the data that you need.


If you must implement your own database column encryption, and if symmetric-key encryption is sufficient for your needs, I would strongly recommend using AES-SIV (or possibly AES-GCM-SIV). If you don't need to do equality comparisons on the encrypted fields, use SIV with a unique random nonce for each field (and consider including the table and column names and the row's primary key as associated data). If you do need the ability to compare encrypted fields for equality without decrypting them, use SIV without a nonce (but preferably still with the table and column names as associated data).

(If you find yourself wanting to do any kind of searching or comparisons on encrypted data besides simple equality comparison, I would strongly suggest reviewing your design and trying to find an alternative solution. While there do exist various "order-preserving" and/or "searchable" encryption schemes that claim to provide this functionality, they do not — and fundamentally cannot — meet the level of security normally expected of a modern encryption scheme, as the ability to perform such comparisons unavoidably leaks information about the encrypted data. Even nonceless SIV should be used with caution, since it leaks the equality of encrypted values.)

In some cases, e.g. if you want your server to be able to add new encrypted values to the database but not to be able to decrypt existing data once it has been added, you may want to use a public-key encryption scheme. In particular, you will probably want a hybrid cryptosystem such as ECIES, where the public-key part of the system is used to generate and encrypt a random symmetric cipher key, and this random symmetric key is then used to encrypt the data itself. The main disadvantages of such systems compared to AES-SIV are their extra complexity and their higher overhead costs (both in terms of processing time and storage requirements). Their main advantage, of course, is that the confidentiality of the data is not compromised even if an attacker gains access to the public encryption key.

$\endgroup$
  • 1
    $\begingroup$ One small addition, as attacks always get better and they become more complex the risk analysis must also consider the combined cases, too. for example; an attacker accessed the DB but the keys are given by a malicious employee. $\endgroup$ – kelalaka Apr 16 at 18:59
1
$\begingroup$

There is a work by MIT named CryptDB that can handle much of the stuff that you asked and that was presented in 2011. They released their code, but you may have a hard time to use it in your application. For example, the test codes for PhpBB and HotCRP are not totally released. Also, the computation resource increase for the proxy servers is not calculated.

There are at least two security analysis one and two. And, a short one here. The first one shows what happens when integrity is missing and a dual shot of the database can result in information leaks. The second one is showing that the OPE and DET (CMC with zero IV) onions of CryptDB can leak information.

If the problems mentioned in the articles do not bother you, it is for you.

store account numbers and routing numbers

  • If you don't want to search over encrypted data, AES with CBC mode (RND onion in CryptDB) or AES with CTR mode will be enough for you. For these modes, you have to store the IV or nonce in the database, too. Note that, these modes don't give you authenticated encryption if you want, and you should, you should prefer AES-GCM or chacha20-poly1305. The integrity and authentication of the rows and tables in another issue.

  • The CryptDB uses AES-CMC to check the equality of encrypted data. $$E_k(m_1) = E_k(m_2) \Leftrightarrow m_1 = m_2.$$ Since the account numbers and routing numbers most probably will be unique they will not reveal statistical information under the way the CryptDB uses AES-CMC, as I can see from the given limited information.

  • You can use the AES-SIV which prevents the danger of nonce reuse of GCM mode ( internally the CTR mode). In SIV mode, if none reused it will not leak information as in CTR mode.

  • It is also possible to use a Cryptographic hash function. While keeping the original value semantically secured to retrieve when needed, you can hash the values and store them on the database. The comparison is performed on the hash values. This can only leak the number of hits.

Use of asymmetric algorithms

Asymmetric Encryption, in general, used to key exchange. Asymmetric Encryption Algorithms will be very slow compared to Symmetrical Algorithms. Modern Intel and AMD CPU's have AES-NI instructions per core. This will reduce your server timing.

$\endgroup$
  • 1
    $\begingroup$ Do all the ones and twos make sense in that equality equation? $\endgroup$ – Paul Uszak Apr 15 at 14:32
  • $\begingroup$ @PaulUszak Ah, thanks that is typo.. $\endgroup$ – kelalaka Apr 15 at 14:37
  • 1
    $\begingroup$ Do not use ECB mode, at least not unless you're sure that all your values will fit in a single AES block (16 bytes). If you want a general-purpose encryption scheme that allows equality comparison on encrypted data, use AES-SIV. $\endgroup$ – Ilmari Karonen Apr 15 at 15:01
  • 1
    $\begingroup$ ‘ECB mode’ is a mistake of a concept that should be abolished, even moreso than the misbegotten ontology of ‘block cipher modes of operation’. Just call it AES if you want to use it on a single block; just go straight to shooting yourself in the foot if you have multiple blocks. $\endgroup$ – Squeamish Ossifrage Apr 16 at 2:59

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.