Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I store sensitive data securely in a MySQL database?

I am making an employment application for a company I am working for. I've got it to protect against SQL injection and some XSS techniques. My main issue is keeping sensitive information secured, like SSN and address, because the company needs that to make 1099 forms for the salesmen's taxes.

I don't know how to do this part, but should I encrypt everything and then decrypt it when it gets into the MySQL database?

like image 964
Jacob Cannon Avatar asked Feb 25 '13 03:02

Jacob Cannon


People also ask

How to store passwords securely in MySQL?

Storing passwords securely. Storing a sensitive data in plain text format could turn into a nightmare if the access to your database has been compromised. To minimize losses in such an cases MySQL provides functions for encrypt and hash of data. The hash functions are intended to map data of arbitrary size to data of fixed size.

How can I safely store my data in a database?

To safely store your data in a database, you’d start by generating a strong secret key value in a byte array. This is best generated programmatically. This single key can be used to encrypt all of the data you’d like to store.

How to identify sensitive data in a SQL database?

The easiest way to understand where sensitive information can be found is to utilize a tool like ApexSQL Mask for automatic identification and classification of all sensitive information in a database This solution will help in identification, classification, and masking sensitive data in the SQL database.

How to encrypt and decrypt data in MySQL?

The MySQL encryption functions allow us to encrypt and decrypt data values. If you plan to store a data values encrypted with these functions always use a BLOB column type. The MySQL functions used for an encryption can be divided into 3 sets according to the used algorithm. To encrypt a password use the ENCODE (str,pass_str) function:


1 Answers

SQL query with key in it (as Wesley Murch suggests) is not a good idea. If you do:

update mytable set myfield = AES_ENCRYPT('some value', 'your secure secret key');

... and the query gets logged (slowlog for inst.) your secure secret key is captured in plain text, which should never happen. Such a query with the secret key would be also visible when you run query like SHOW PROCESSLIST.

Next problem where to store the secure key? In PHP file? It is again plain text.

Encrypt data:

Use private/public key encryption (http://en.wikipedia.org/wiki/Public-key_cryptography). PHP has quite good support for it.

  • Public keys can be stored with the user in DB, it is public.
  • Private key can be encrypted with user's password. When user logs in, you decrypt the private key and store it in his cookies (if you use SSL, it is not that bad place) or session. Both are not perfect but better than plain text in php file.
  • Use the public key to encrypt, private key to decrypt.
  • Only user will have the access to his data.

If you want to learn more, you can google "user controlled encryption" or "zero knowledge privacy".

SQL inserts / XSS:

The best protection is secure app. No doubt. If you want to secure it, you can use for inst PHP IDS to detect attacks: https://github.com/PHPIDS/PHPIDS

I have quite good experience with it.

like image 162
Martin Höger Avatar answered Sep 19 '22 09:09

Martin Höger