encrypted column - with ZENDB

2 posts by 2 authors in: Forums > CMS Builder
Last Post: August 26 (RSS)

By kitsguru - August 25 - edited: August 25

How do I decrypt a column when using ZENDB? I have a column that stores a token for access to a service. I want to encrypt it - not a problem. I am using ZENDB, DB::get() to retrieve the record, but how do I decrypt it when making the connection to the service?

Jeff Shields
yaadev.com

By Dave - August 26

Hi Jeff,

We don't have a built-in function for that yet, but you could try something like this:

function getDecryptColumnSql($col) {
  static $key = null;
  $key ??= mysql_escape(settings('mysql.columnEncryptionKey'));
  $sql = "AES_DECRYPT(`$col`, UNHEX(SHA2('$key',512))) as '$col'";
  return DB::rawSql($sql);
}

$result = DB::query("SELECT num, :decryptedToken FROM ::myTable", [
  ':decryptedToken' => getDecryptColumnSql('token'),
]);

Let me know how that goes or any questions.

Thanks!

Dave Edis - Senior Developer
interactivetools.com