5

I'm trying to build a platform for users who will store confidential data about their clients. The context is quite simple: french laws prohibit me from having access to the data that my users will store (for example medical records of patients).

So when a user submits data which will be stored in the database, he should be the only one having access to that information. For example by encrypting it with his password. That way, if I log into mysql, I would only see encrypted nonsense and no readable data.

My philosophy, which might be wrong, is to learn by doing it. Hope you guys are ok with my approach.

Problem is: I have no idea where to start, how to do that... and actually not even what to search for on google. I even tried to find something suitable on codecanyon.net for example and couldn't fond any relevant scripts.

Thanks in advance :) !

PS: I will actually have the same problem with files (jpg, word, pdf, xls... that should be enough for the users). But that's another story.

YannickHelmut
  • 525
  • 1
  • 7
  • 17
  • 3
    This is a very tricky problem. It is relatively easy to encrypt the data such that it is very hard to decrypt (i.e. hash a password and store the hash, very difficult to work back to the plaintext password) but encrypting data such that even the developer can't read it but one user can is complicated. Perhaps see what algorithms Apple use to encrypt data - since they claim that even if they gave the government your data they couldn't decrypt it for them. – Kvothe Oct 07 '15 at 00:21
  • 1
    @Kvothe has a point. Perhaps [this article](http://blog.cryptographyengineering.com/2014/10/why-cant-apple-decrypt-your-iphone.html) on why Apple can't decrypt an iPhone might help. – camelCase Oct 07 '15 at 00:27
  • Your philosophy would not improve security at all. What is normal is that the user registers a username and password. The username can be anything, but is really needed to tie password to user. Use an algorithm, on top of a random hash. You should save the users password encryptively. To sum it up, encrypt and salt the users password, store both the encrypt and salt. When user logs in, it will check the database, match a username. Now the password the user entered (to login) you encrypt it and salt it from the saved value. It will match up if encrypt + salt is correct. – Evan Carslake Oct 07 '15 at 00:29
  • 1
    assume you achieve it, so you will be the one who knows how to encrypt the data (what ever method you use or what ever the approach will be) and then you will be the one who will also knows how to decrypt it, so still French Laws will have the problem, it's just a thought – Shehary Oct 07 '15 at 00:32
  • 1
    @EvanCarslake I think the OP is concerned about encryption of all data, not just username/password. Or perhaps I misunderstood. – camelCase Oct 07 '15 at 00:34
  • 1
    @camelCase, exactly. The thing is, if I have access to that DB and its content, every user would have to ask his patients to sign some documents allowing him to pass over this data to me. That doesn't make sense, I know that. Doesn't change the facts and therefore the problem... (you're still allowed to shoot someone with an arrow if you're on a horse, he's on the top of a hill and british. Figure that out !) – YannickHelmut Oct 07 '15 at 00:38
  • 1
    be interested to see what the law *actually* says. as its simply impossible to develop a system in which you don't have access to the data stored in said system –  Oct 07 '15 at 00:38
  • 1
    Many php libraries, like cake and codeigniter, have built in functionality for encryption. Since you won't be using a global salt string, and instead providing your own for each instance (i.e. the password) it might be a bit more complex. Still though, there are ways to encrypt it from the database end with sql or from the server with php. Basically though I think you should encrypt the passwords when you store them with some global encryption, but then encrypt their data with the unencrypted password. – Wold Oct 07 '15 at 00:39
  • 3
    This is not a topic that you can safely "learn by doing". Failure will likely cause your users' private data to be compromised, and may expose you to legal action. If you want to proceed, you need to formally study security. – duskwuff -inactive- Oct 07 '15 at 01:50
  • MariaDB starting with 10.1.4 supports encryption - Read more @ https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/ – Tan Hong Tat Oct 07 '15 at 02:07
  • @TanHongTat this will not help either – Shadow Oct 07 '15 at 02:09
  • @TanHongTat see my answer below – Shadow Oct 07 '15 at 02:19
  • @Tan Because Yannick is supposed to be unable even in principle, even if he changes the server-side scripts, to access the data. Once the secret to establish the en/decoding connection has been sent to the server what hinders the scripts to query arbitrary records and store or send them somewhere in an unencrypted form? – VolkerK Oct 07 '15 at 02:20
  • 1
    The interesting part will be separating the privileged data from the unpriviledged data that is needed for processing/for the service and how to structure this data, so that you do not have to transmit one giant encrypted data blob back and forth per session because the client has to do _all_ the computation and the server is only a (very) dumb remote storage. – VolkerK Oct 07 '15 at 02:30
  • @Shadow - Yes. MariaDB 10.1.X supports what you mentioned (Key management can/should be handled by a 3rd party). – Tan Hong Tat Oct 07 '15 at 02:30
  • 1
    That separation (or classification) of unpriviledged data may become rather tricky. One example: When the german Bundestag was discussing publication of ancillary income of MPs laywers wanted to be exempt from publishing the number of "sources", i.e. clients, because that information alone (+ the amount of money they earned) was -in their eyes- enough to make educated guesses about their clients (and cases?) and therefore priviledged. Not sure if I agree but the argument was there .... – VolkerK Oct 07 '15 at 02:32
  • 1
    @TanHongTat you are missing the point – Shadow Oct 07 '15 at 02:32
  • My comment is solely about MySQL (MariaDB) data encryption, not about the various laws, which I have no idea for your country. You have to work with the various departments to establish the requirements. – Tan Hong Tat Oct 07 '15 at 02:41
  • Since this question is on the brink of being closed as too broad (and I'm inclined to agree on that one) one more comment: There's no _easy fix_ for this kind of problem. Our "Gesundheitskarte" (electronic health care card) still struggles (amongst other problems) on that topic. If you're really interested and view this as a long project with real prospects you might be interested a) in contacting [EuroPriSe](https://www.european-privacy-seal.eu/EPS-en/Home) and b) in homomorphic encryption schemes. For a) be somewhat prepared before bothering them. And b) is something for the future ;-) – VolkerK Oct 07 '15 at 02:57
  • @TanHongTat you are completely missing the point. Applying encryption at a database level is 1) too late 2) will not prevent DBAs to read the data unless you propose that noone has access to the database at all. – Shadow Oct 07 '15 at 06:05
  • @YannickHelmut do you ever plan to revisit your own question? – Shadow Jan 05 '17 at 06:21

3 Answers3

6

Although I'm not familiar with the French data protection laws, I do have some experience with the general EU legislation. Probably you have to encrypt personal data in your system such way, that the person cannot be identified. This means, that technical data, such as a system specific id in a table can stay unencrypted. This is the only reason I believe you can actually make this work. Sort of. You have to work with lawyers to determine what data can stay unencrypted to comply with the laws and still to be able to provide a valuable service to your clients.

However, I believe that you are missing an important point: if you cannot have access to certain data, then that data MUST NOT arrive to your organisation in a plain format. Period. If it does, then you already have access to it. So, php (or any kind of server based encryption) or mysql based encryption solutions are out of the picture.

The only solution I can think of is it to team up with a 3rd party PKI provider, who will provide your clients with certificates ( perhaps on chip cards), and the client side of your application encrypts the sensitive personal data items on the client BEFORE they are sent to your server and also decrypt these data items on the client. This also means that you will have to use some plugins on the client side if you want this system to be web based. Probably you will need some signed java app to manage the card reader and the certificate.

The setup has 2 drawbacks for your clients:

  1. It's not a single provider they deal with, since the PKI provider must be an independent third party. You must not manage the certificates.
  2. In case the stored data gets corrupted, the encrypted data will be lost. So, you will have to implement some crazy backup and restore solution.
Shadow
  • 30,859
  • 10
  • 44
  • 56
  • I am skeptical of OP's assertion about the extent of burden imposed by French Law. If the assertion is correct, your point is spot-on: such restriction make it effectively impossible for data to be accepted for storage other than in an already-encrypted form upon its arrival and processing such data in a relational database would be completely pointless. With no indexing or aggregate functions available, OP may as well store the data in flat files, because it can't be effectively searched, grouped, or used in any meaningful way. – Michael - sqlbot Oct 07 '15 at 02:52
  • This is not entirely correct. As VolerK in comments and me in the reply pointed out, the personal data (privileged data) needs to be encrypted only. For example the patient's name, address, detailed medical history. But data, that are in itself do not enable the identification of a person, such as sex, treatments received, list of illnesses (not their details) may be stored in an unencrypted way enabling statistical analyis of the underlying data. The huge question is: what exactly can be stored in an unencrypted way. – Shadow Oct 07 '15 at 05:31
  • Sorry, @Shadow. +1, btw. I didn't communicate my thoughts as clearly as I should have. My point was not to disagree with your answer. I only intended to illustrate that *unless* your answer is correct, OP is contemplating an essentially impossible task. – Michael - sqlbot Oct 07 '15 at 12:07
2

So assuming the problem is as follows:

  • You need to encrypt the data before you store it.
  • You shouldn't have the keys to decrypt it, only encrypt it.

There's actually a tool for this: It's called a sealing API, and it can be accomplished through OpenSSL or Libsodium.

Sealing/Unsealing Data in PHP with Libsodium

$store_me = \Sodium\crypto_box_seal(
    $plaintext,
    $recipient_public_key
);

$visible = \Sodium\crypto_box_seal_open(
    $store_me,
    $recipient_keypair
);

Sealing/Unsealing Data in PHP with OpenSSL

/**
 * A human-usable variant of openssl_seal()
 * 
 * @param string $plaintext Your message
 * @param string $publickey_string PEM-encoded RSA public key
 * @param boolean $encode Hex-encode the output?
 * 
 * @return string
 */
function easy_seal($plaintext, $publickey_string, $encode = false)
{
    $pubkey = openssl_get_publickey($publickey_string);
    if ($pubkey === false) {
        throw new Exception('Could not load public key');
    }
    $sealed = '';
    $ekeys = [];
    $result = openssl_seal($plaintext, $sealed, $ekeys, [$pubkey]);
    if ($result === false) {
        throw new Exception('openssl_seal failed!');
    }
    if ($encode) {
        return json_encode([
            bin2hex($sealed), 
            bin2hex($ekeys[0])
        ]);
    }
    return json_encode([$sealed, $ekeys[0]]);
}

/**
 * Inverse operation of easy_seal()
 * 
 * @param string $ciphertext (the output of easy_seal())
 * @param string $privatekey_string PEM-encoded RSA private key
 * @param boolean $encoded Do we need to decode from hex?
 * 
 * @return string
 */
function easy_unseal($ciphertext, $privatekey_string, $encoded = false)
{
    list($sealed, $ekey) = json_decode($ciphertext, true);
    if ($encoded) {
        $sealed = hex2bin($sealed);
        $ekey = hex2bin($ekey);
    }
    $open_data = '';
    $privkey = openssl_get_privatekey($privatekey_string);
    if ($privkey === false) {
        throw new Exception('Could not load public key');
    }

    $result = openssl_open($sealed, $open_data, $ekey, $privkey);
    if ($result === false) {
        throw new Exception('openssl_open failed!');
    }
    return $open_data;
}

Usage Example

$public_key = file_get_contents('/path/to/publickey.pem');
$plaintext = 'Something something dark side';
$store_me = easy_seal($plaintext, $public_key);

// Elsewhere: 
$secret_key = file_get_contents('/path/to/secretkey.pem');
$visible = easy_unseal($store_me, $secret_key);

Demo: https://3v4l.org/BNavp

Scott Arciszewski
  • 30,409
  • 16
  • 85
  • 198
0

Actually, I'm also on a similar kind of project where I'm trying to build a secure database in MySQL Server that is also useful to run all the valid SQL Queries. It's still in the progress and too many difficulties are there; I accept.

But, for your problem , it seems you only need to encrypt and decrypt the values.and you don't want to store the key also there in the database. For me, there are two ways that comes in my mind:

  1. First way is, that you decide a fixed secret key to encrypt and decrypt the values and use it for every data, that is being stored on the database.
    But, that's not practical I guess, since the security gets weak with this approach and a person can identify your key with brute force approach.

  2. Second way is, that you generate a random key for every different user, like, at the time of registration. And data of different user can be viewed by only the user who has the key to decrypt it, which here only the user has. And then you apply the first approach after this. i.e., you decide a key that will be used to encrypt these keys of different users. and then store this encrypted key in the database in a separate table. So that, next time user will try to access his/ her data, his entered key (could be his password), will be encrypted with your decided static key, if this encrypted key is found in the table of your database your will fetch the data of that user, decrypt it with his/ her key and display to him/ her.
    All you need is a,
    (i) programming platform to select, JAVA is best.
    (ii) learn how to use database with this programming language, MySQL Server is a nice choice to work with. (iii) And a good encryption algorithm to implement.

Hope, I didn't make you angry with this answer :) Cheers.

Abhishek
  • 25
  • 9