Encrypt mysql database
Asked Answered
R

3

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.

Rattigan answered 7/10, 2015 at 0:13 Comment(21)
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.Ssr
@Ssr has a point. Perhaps this article on why Apple can't decrypt an iPhone might help.Orji
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.Liquidation
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 thoughtSelfcontent
@EvanCarslake I think the OP is concerned about encryption of all data, not just username/password. Or perhaps I misunderstood.Orji
@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 !)Rattigan
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 systemDragrope
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.Penang
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.Annabel
MariaDB starting with 10.1.4 supports encryption - Read more @ mariadb.com/kb/en/mariadb/data-at-rest-encryptionOnesided
@TanHongTat this will not help eitherKenelm
@TanHongTat see my answer belowKenelm
@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?Monolithic
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.Monolithic
@Kenelm - Yes. MariaDB 10.1.X supports what you mentioned (Key management can/should be handled by a 3rd party).Onesided
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 ....Monolithic
@TanHongTat you are missing the pointKenelm
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.Onesided
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 and b) in homomorphic encryption schemes. For a) be somewhat prepared before bothering them. And b) is something for the future ;-)Monolithic
@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.Kenelm
@Rattigan do you ever plan to revisit your own question?Kenelm
K
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.
Kenelm answered 7/10, 2015 at 1:43 Comment(3)
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.Himalayas
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.Kenelm
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.Himalayas
D
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

Discriminator answered 7/10, 2015 at 6:35 Comment(0)
S
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.

Savannasavannah answered 8/6, 2016 at 12:37 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.