18

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?

Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
Jacob Cannon
  • 195
  • 1
  • 1
  • 7
  • If you want it secure, it should be encrypted in the MySQL database, not only during the transit. – Jon Feb 25 '13 at 03:49
  • If it can be decrypted by you, it can generally be decrypted by someone who has access to your database. Maybe you need to hire a professional? – sjdaws Feb 25 '13 at 03:50
  • This is an awfully broad question, Jacob. Can you focus it some more? If you're more interested in end-to-end encryption (why? to ensure confidentiality? So that your users trust that they're submitting the data to the right entity?), then take out the other bits. What's your background in this area? If it's little to none, then let me humbly suggest that you do some offline reading - any help that can fit in an SO answer won't help much. The [OWASP](https://www.owasp.org/index.php/Category:OWASP_Guide_Project) is a good place to start. – Michael Petrotta Feb 25 '13 at 03:51
  • SQL injection and XSS hs to be taken care from application . – Arun Killu Feb 25 '13 at 03:56
  • Also, just to mention. Encrypted information is decryptable information. – anditpainsme Feb 25 '13 at 04:00
  • well i really want to ensure confidentiality and make it safer for our employees, I've done html a lot in the past and have been studying it since 15, and just recently got back into it aggressively because my friend needed this done. I just started learning PHP and MySQL a lot more than a hobbyist standpoint, and am going to school for this as well – Jacob Cannon Feb 25 '13 at 04:03
  • 1
    For more information, come and look at http://security.stackexchange.com - we cover a lot of this kind of thing :-) – Rory Alsop Feb 25 '13 at 10:21

2 Answers2

19

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.

Community
  • 1
  • 1
Martin Höger
  • 804
  • 7
  • 6
17

This is an overly simplified answer and should be taken with a grain of salt, as most answers about security:

  • Use SSL everywhere.

  • Use a secure encryption key

For storage of encrypted data, you could use a BLOB field, and use MySQL's built in encryption functions. Example:

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

If you prefer to do the encryption/decryption in the application code, take a look at PHP's Mcrypt functions.

  • Encrypt the user input
  • Store in the database
  • Decrypt it after fetching it

This is by no means a complete guide, but it's a start and better than doing nothing.

You may be able to learn more on https://security.stackexchange.com/

Community
  • 1
  • 1
Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
  • so what your saying is to encrypt it and put the encrypted info in the database, then when we need it to somehow decrypt it in a secure, not public, seperate webpage? – Jacob Cannon Feb 25 '13 at 04:14
  • You should have explained encryption better, it's really important and as developers it's our job to make sure we do security well. Check out [this](http://stackoverflow.com/questions/16600708/how-do-you-encrypt-and-decrypt-a-php-string/30159120#30159120) response to a question, he has a really good guide to encryption. – Daniel Aug 03 '15 at 00:30
  • It is best not to use mcrypt, it is abandonware, has not been updated in years and does not support standard PKCS#7 (née PKCS#5) padding, only non-standard null padding that can't even be used with binary data. mcrypt had many outstanding [bugs](https://sourceforge.net/p/mcrypt/bugs/) dating back to 2003. Instead consider using [defuse](https://github.com/defuse/php-encryption), it is being maintained and is correct. – zaph Jul 13 '16 at 17:07
  • 1
    You shouldn't _actually_ guard it with your life -- it's just computers. ;) – JakeParis Nov 09 '16 at 17:42