How We Encrypt Data in MySQL With Go

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important for all sensitive data to be secured. Encryption plays an important role in information security. We encrypt data in-flight and at-rest, so your sensitive data is never exposed. I've used Go and MySQL extensively and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs).

Encryption Techniques

At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

1. Store the data in MySQL as normal, but encrypt the container holding MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
2. Encrypt the data before sending it to MySQL. In this case, the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

• Will data be exposed if backups are unencrypted?
• Are sensitive values possibly in cleartext in query logs?
• Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

It's best to err on the side of safety and security, rather than favoring convenience. A fairly simple question does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means someone has to get access to the keys for the particular data they’re trying to decrypt, to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.

Making It Convenient

Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer. We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet. To implement transparent encryption and decryption, we created a custom data type to implement the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except we used encryption libraries instead. Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of

var password string err = rows.Scan(&password) 

We simply use

var password EncryptedValue err = rows.Scan(&password) 

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

"Go feels under-engineered because it only solves real problems." Exactly. http://t.co/18LhLT0ALB #golang

— VividCortex (@VividCortex) September 18, 2014

Nuts And Bolts

The code is small. The exact details of all the code aren't important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is we store values as byte arrays:

• The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
• The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
• The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol. The result is that we have a simple and future-proof way to encrypt values.

Alternative Approaches

In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.

Conclusions

By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data. We highly recommend you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers hard questions about security and how it’s implemented. Every service needs to be secure to make the internet a safer place.

Post Updated 7/31/2017