At-Rest Encryption for Complete Data Protection Eric Mann 

Let’s build a web application. Let’s make it secure. 

Data Classification Tiers Tier 1 - Unrestricted Data Tier 2 - Confidential Data Tier 3 - Highly Secured Data Tier 4 - Restricted Data 

(Full image slide with caption) STRIDE Threat Modeling - https://resources.infosecinstitute.com/threat-modeling-finding-defects-early-in-the-cycle/ 

(Full image slide with caption) Image credit: https://movieweb.com/ 

DREAD Risk Ratings Score each of the following from 1-10 ● Damage Potential ● Reproducibility ● Exploitability ● Affected Users ● Discoverability 

DREAD Risk Ratings D R E A D Rate Credentials sniffed from the network 10 10 5 5 5 7 SQL commands injected in the front-end 10 10 10 10 5 9 Arbitrary file navigation and download on the 10 10 10 5 0 7 

In Transit Encrypting communication between two parties over a (potentially) insecure medium. At Rest Encrypting data used by a single party between to periods of time. Data Encryption 

(Full image slide. No text) 

At-Rest Encryption 1. Protects data written out to disk 2. Transparent to the application layer 3. (Usually) Provided by hosted DB solutions 4. (Often) Required for regulatory compliance 

(Full image slide with caption) Tom Cruise in "Mission: Impossible — Rogue Nation." YouTube/Paramount 

Application-level Encryption By allowing the application to manage its own encryption keys, the crypto operations lift up into the application stack itself. The database engine can’t read or manage the data, neither can any other process in memory. Your data’s security then relies on the security of your application’s authentication and access control systems. 

Application-level Encryption By allowing the application to manage its own encryption keys, the crypto operations lift up into the application stack itself. The database engine can’t read or manage the data, neither can any other process in memory. Your data’s security then relies on the security of your application’s authentication and access control systems. 

To store encrypted data and still search the plaintext, we need a blind index. 

Blind Index Store a keyed hash of the data in one column Store the encrypted data in a separate column Be sure to use separate hashing and encryption keys! 

Blind Index CREATE TABLE reviews ( id INT NOT NULL, title TEXT NOT NULL, /* encrypted */ review TEXT NOT NULL, /* encrypted */ reviewer TEXT NOT NULL, /* encrypted */ reviewer_idx TEXT NOT NULL, /* blind index */ PRIMARY KEY (id) ); CREATE INDEX ON reviews (reviewer_idx); 

class Review { public int $id; public string $title; public string $review; public string $reviewer; } interface ReviewDatabase { function create(Review $review); function get(string $reviewId); function update(Review $review); function delete(string $reviewId); } 

class PlaintextDatabase implements ReviewDatabase { private $db; function __construct(PDO $db) { $this->db = $db; } function create(Review $review) { $statement = $this->db->prepare("INSERT INTO reviews (title, review, reviewer) VALUES (:title, :review, :reviewer)"); $statement->bindParam(':title', $review->title); $statement->bindParam(':review', $review->review); $statement->bindParam(':reviewer', $review->reviewer); $statement->execute(); } // ... 

class PlaintextDatabase implements ReviewDatabase { // ... function get(string $reviewId) { $statement = $this->db->prepare("SELECT * FROM reviews WHERE id = :id LIMIT 0, 1") $statement->bindParam(':id', $reviewId); while ($review = $statement->fetch()) { return new Review($review); } return null; } // ... and so on } 

Let’s add crypto... 

class EncryptedDatabase implements ReviewDatabase { private $db; private $key function __construct(PDO $db, string $key) { $this->db = $db; $this->key = $key; } // ... 

class EncryptedDatabase implements ReviewDatabase { // ... private function encryptString(string $plaintext) { $block_size = 16; $nonce = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCE_BYTES); $message = sodium_pad($plaintext, $block_size); // Return the nonce and the encrypted messsage return bin2hex($nonce . sodium_crypto_secretbox($message, $nonce, $this->key)); } private function decryptString(string $ciphertext) { $block_size = 16; $nonce = substr(hex2bin($ciphertext), 0, SODIUM_CRYPTO_SECRETBOX_NONCE_BYTES); $message = substr(hex2bin($ciphertext), SODIUM_CRYPTO_SECRETBOX_NONCE_BYTES); $message = sodium_crypto_secretbox_open($encrypted, $nonce, $this->key); return sodium_unpad($message, $block_size); } // ... 

class EncryptedDatabase implements ReviewDatabase { // ... function create(Review $review) { $statement = $this->db->prepare("INSERT INTO reviews (title, review, reviewer) VALUES (:title, :review, :reviewer)"); $statement->bindParam(':title', encryptString($review->title)); $statement->bindParam(':review', encryptString($review->review)); $statement->bindParam(':reviewer', encryptString($review->reviewer)); $statement->execute(); } // ... 

class EncryptedDatabase implements ReviewDatabase { // ... function get(string $reviewId) { $statement = $this->db->prepare("SELECT * FROM reviews WHERE id = :id LIMIT 0, 1") $statement->bindParam(':id', $reviewId); while ($review = $statement->fetch()) { return new Review([ "id" => $review["id"], "title" => decryptString($review["title"]), "review" => decryptString($review["review"]), "reviewer" => decryptString($review["reviewer"]) ]); } return null; } // ... and so on } 

Let’s add an index... 

class EncryptedDatabase implements ReviewDatabase { private $db; private $key private $hmac_keys; function __construct(PDO $db, string $key, array $hmac_keys) { $this->db = $db; $this->key = $key; $this->hmac_keys = $hmac_keys; } // ... 

class EncryptedDatabase implements ReviewDatabase { // ... function hashIndex($value, $index) { $hmac_key = $this->hmac_keys[$index]; return hash_hmac('sha256', $value, $hmac_key); } function create(Review $review) { $statement = $this->db->prepare("INSERT INTO reviews (title, review, reviewer) VALUES (:title, :review, :reviewer, :reviewer_idx)"); $statement->bindParam(':title', encryptString($review->title)); $statement->bindParam(':review', encryptString($review->review)); $statement->bindParam(':reviewer', encryptString($review->reviewer)); $statement->bindParam(':reviewer_idx', hashIndex($review->reviewer, 'reviewer')); $statement->execute(); } // ... and so on } 

class EncryptedDatabase implements ReviewDatabase { // ... function getAllByReviwer(string $reviewer) { $reviewerIdx = hashIndex($reviewer, 'reviewer'); $statement = $this->db->prepare("SELECT * FROM reviews WHERE reviewer_idx = :id") $statement->bindParam(':idx', $reviewerIdx); $reviews = []; while ($review = $statement->fetch()) { $records[] = new Review([ "id" => $review["id"], "title" => decryptString($review["title"]), "review" => decryptString($review["review"]), "reviewer" => decryptString($review["reviewer"]) ]); } return $reviews; } // ... } 

Take a deep breath 

(Full image slide with caption) CipherSweet by Paragon Initiative Enterprsies: Fast, searchable field-level encryption for PHP projects 

Application-level Encryption By allowing the application to manage its own encryption keys, the crypto operations lift up into the application stack itself. The database engine can’t read or manage the data, neither can any other process in memory. Your data’s security then relies on the security of your application’s authentication and access control systems. 

(Full image slide with caption) Tom Cruise in "Mission: Impossible." Paramount 

Defense-in-Depth 1. Evaluate threat models 2. Encrypt in transit 3. Encrypt at rest 4. Limit access to keys 5. GOTO 1 

Frequent Questions How do I query the database directly? How do I debug the system? What data really needs to be encrypted? Does CipherSweet work for more than just PHP? 

Questions? 

Thank you [email protected] | 503.925.6266