asked on

Needing help to understand how to correctly implement blind indexing using php, mySQL and libSodium - especially with respect to creation and use of index keys.

I'm trying to implement searchable encryption as proposed by Paragonie in a blog post on their website titled Building Searchable Encrypted Databases with PHP and SQL. I am very new to data encryption, so there are a several points I'm not understanding and hope I explain it clearly.

Using their examples, I've created a test table per the example in the blog and changed a few datatypes to suite mySQL better:

CREATE TABLE humans (
    humanid int PRIMARY KEY,
    first_name varchar(500),
    last_name varchar(500),
    ssn varchar(500), /* encrypted */
    ssn_bidx  varchar(500) /* blind index */
);
CREATE INDEX ON humans (ssn_bidx);

Open in new window

The example then goes on to use a series of functions to encrypt/decrypt the data and pull data from the table

function encryptSSN(string $ssn, string $key): string
{
    $nonce = random_bytes(24);
    $ciphertext = sodium_crypto_secretbox($ssn, $nonce, $key);
    return bin2hex($nonce . $ciphertext);
}

function decryptSSN(string $ciphertext, string $key): string
{
    $decoded = hex2bin($ciphertext);
    $nonce = mb_substr($decoded, 0, 24, '8bit');
    $cipher = mb_substr($decoded, 24, null, '8bit');
    return sodium_crypto_secretbox_open($cipher, $nonce, $key);
}

function getSSNBlindIndex(string $ssn, string $indexKey): string
{
    return bin2hex(
        sodium_crypto_pwhash(
            32,
            $ssn,
            $indexKey,
            SODIUM_CRYPTO_PWHASH_OPSLIMIT_MODERATE,
            SODIUM_CRYPTO_PWHASH_MEMLIMIT_MODERATE
        )
    );
}

function findHumanBySSN(PDO $db, string $ssn, string $indexKey): array
{
    $index = getSSNBlindIndex($ssn, $indexKey);
    $stmt = $db->prepare('SELECT * FROM humans WHERE ssn_bidx = ?');
    $stmt->execute([$index]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Open in new window

I get the encrypt and decrypt piece but am lost with the function that retrieves the data based on the blind index.

If I understand correctly, the $indexKey has to be created for each field that needs to be searched and that value is inserted into the table whenever a record is submitted. But, once someone tries to search the data, where does the $indexKey value being passed to the function used to search the database?

I've searched on this over and over and most results are a reprint of the original blog post, so no help there. I've even gone to Paragonie and asked for help. They did respond but after I told them what I needed (and that we'd be willing to pay for the knowledge transfer) it's been nothing but crickets with them. I know I'm missing something and am hoping someone can help me sort it out.

Thanks in advance.

David Favor

Just glancing at the article, there seems to be a good bit missing.

Specifically the blind index approach doesn't work as article suggests, as there is no ACLing of the blind index.

Said another way, anyone who can read can read the humans.bidx_ssn column can also read the humans.ssn column.

Also the article glosses over how to manage nonces or encrypt/decrypt keys. The missing part is somehow you have to store + associate the nonces to be able to decrypt the encrypted column, then encryption security breaks because the stored nonce can be retrieved + decrypt the data.

I think you'll find using MariaDB data at rest encryption much easier to implement.

https://mariadb.com/kb/en/library/data-at-rest-encryption-overview/ provides a good starting point.

Considerations for this type of design.

1) Anyone who gains root has access to your database can decrypt your data, so the normal way this is done is to keep your encrypt/decrypt key on an air gapped machine (meaning your code can call another machine's API + that machine has no public net access). This is a mandatory requirement for storing personal data.

2) If you store data like SSNs then you fall under legal code in many jurisdictions/countries, so if you make a mistake + you're hacked you face criminal proceedings resulting in jail time or high fees.

3) If this is your first time storing data which can... land you in jail or drain your bank account... if you're hacked... hire an expert to help you do your initial design.

4) To avoid this entire problem, assign a unique random userid for people, rather than using their SSN, as any sane person will simple leave a site which requests their SSN or they will give you a bogus SSN.

hielo

>> where does the $indexKey value being passed to the function used to search the database?
From a file. The article you reference contains a link to "supplemental material for (the) B-Sides Orlando 2017 talk"

class EncryptedSearch
{
      ...
      public function __construct(\PDO $db, string $keyDir = '', bool $highEntropy = false){
             ...
             if (\file_exists($keyDir . '/enc.key')) {
                   //read from file enc.key
             }
             else{
                  //creates the file enc.key
             }
             $this->encKey = new HiddenString($encKey);
             
             if (\file_exists($keyDir . '/index.key')) {
                   //read from file index.key and initalizes $indexKey
             }
             else{
                  //creates the file index.key and initalizes $indexKey
             }
             $this->indexKey = new HiddenString($indexKey);
      }
}

Open in new window

I suggest you go to the tests folder and read through EncryptedSearchTest.php

FYI: I did not run the test, but if you are interested in running it, you will need to find the dependencies. As an example, on the EncryptedSearch.php file you will see:

use ParagonIE\ConstantTime\{
    Base64UrlSafe,
    Binary
};

Open in new window

Go to https://github.com/paragonie and search for "constant_time_encoding". Within that project you will find needed Base64UrlSafe and Binary classes.

Regards,
Hielo

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.