Encrypt in mysql, decrypt in javascript

README.md

What is this?

A way to encrypt data on mysql and then decrypt in javascript.

Why is this useful?

Sometimes you have data in your mysql database and you want to pass this data to a JS app.
For example, say you want to run an email campaign and you want to send emails to users from your mysql database. In this campaign you want ppl to click a link and when clicked you want the target site (your site) to be able to identify the user. You could pass the user-id as plaintext. Or the email as plain text. But that would be insulting the user's intelligence plus you might leak information "in the face" of a user. If you want to pass information from mysql to javascript and be able to obfuscate this info such that it isn't in the face of a user, this method is simple and effective.

This is not "secure"

Please note that the encryption method used here aes in it's simple form of use isn't considered secure and is known to be susceptible to several attacks so if you need to encrypt something really secretive you'd have to do something better than what mysql has to offer. However, if you only care about obfuscating your data in a reasonable way, then aes would do. AES isn't trivially breabable, but with many data repititions etc it is breakable. Obfuscation wise, that's good enough though.

Example

Here's example result of the encryption function:

mysql> select email, hex(aes_encrypt(email, "my very secret key")) from (SELECT "me@example.com" AS email UNION SELECT "her@example.com" UNION SELECT "us@example.com") as emails;
+-----------------+-----------------------------------------------+
| email           | hex(aes_encrypt(email, "my very secret key")) |
+-----------------+-----------------------------------------------+
| me@example.com  | 36B8A66AA699DDB61BB1F88BB9ECB313              |
| her@example.com | FCF18D1DBA4751B8D83CC6F8685A5702              |
| us@example.com  | B2E8122DE0725AEB551536D06037114A              |
+-----------------+-----------------------------------------------+

javascript-decrypt.js

import crypto from 'crypto';


function convertCryptKey(strKey) {
  const newKey = new Buffer([0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]);
  const bufStrKey = new Buffer(strKey);
  for (let i = 0; i < bufStrKey.length; i++) {
    newKey[i % 16] ^= bufStrKey[i];
  }
  return newKey;
}

function decrypt(cyphertext) {
  // although this function could run on the client - you should not store 'My very secret key' on the client, nor pass it
  // via API call. You should decrypt on the server. 
  const dc = crypto.createDecipheriv('aes-128-ecb', convertCryptKey('My very secret key'), '');
  const decrypted = dc.update(cyphertext, 'hex', 'utf8') + dc.final('utf8');
  return decrypted;
}

mysql-encrypt.sql

-- Create a column for email_enc and populate it with the value of the encrypted email

alter table users add email_enc text;

update users set email_enc = hex(aes_encrypt(email, "my very secret key"));



-- And if you just want to test what the encryption looks like on several emails, try this: (no database table required)

select email, hex(aes_encrypt(email, "my very secret key")) from (SELECT "me@example.com" AS email UNION SELECT "her@example.com" UNION SELECT "us@example.com") as emails;