1

I have a very simple table made of few fields, some of which I want to be non-readable by human eyes. By "readable" I mean I'd like to be able to open the database with any admin tool (such as PhpMyadmin) and see these fields as made of gibberish/protected text.

I'll try to reformulate: is there a way to avoid a client's comment such as "but you are in charge of the website and the databse, so you can read all its contents!". Think of private data, messages, etc. Stuff that people don't like to be "read" by anyone, not even the website admin of course.

What's the best approach to manage the database without accessing every shingle text info?

Note: the database must be accessible via PHP, which needs to run the usual SQL INSERT, SQL SELECT, ... queries.

Lub
  • 11
  • 3
  • 4
    Encrypt the data when it's being saved? Then decrypt it when you're accessing it? – Steven V Apr 03 '14 at 13:20
  • Yes, I could do that but I'd need to be able to decrypt it too, so I would still fall in the "but you can decrypt it, so you can still read it!" category. – Lub Apr 03 '14 at 13:22
  • Your client has to trust the system somehow. With physical access to server and network, there's always a chance you could read the data. – Steven V Apr 03 '14 at 13:23
  • 1
    ... if you ever need to display the data anywhere, there's no way to avoid this really - salted hashes for passwords don't have this issue since they're never read back, they're only ever compared to user input on login. – CD001 Apr 03 '14 at 13:24
  • Use [str_rot13](http://www.php.net/manual/en/function.str-rot13.php) to obfuscate the data. It's not fool proof but it will hide the data at a glance. You can use it to decode it also. Benefit being it would not take up any additional space like a base64_encode would. – phpisuber01 Apr 03 '14 at 13:26
  • @ CD001 - thanks, I was suspecting I had no options... or so @ phpisuber01 - Rot13 really doesn't add much value, apart from scrambling stuff a little bit... sorry. – Lub Apr 03 '14 at 13:31
  • 1
    If a client doesn't trust you, then you have a major problem... because even if you could secure the database in a way that you were unable to retrieve the contents in readable form (and there are some expensive commercial plug-ins that can do this) there's nothing to prevent you reading and logging the data within your app, before it goes into the database - so they have to trust you as the app developer or go elsewhere – Mark Baker Apr 03 '14 at 13:34
  • @ Mark Baker that's true, yes. I am just trying to find a possible solution to present to the client, so that I can argument any possible objection. – Lub Apr 03 '14 at 13:36
  • Note also that encrypted data makes things extremely difficult for database queries, because (even for an exact search) you need to encrypt the search terms before executing the query against the encrypted data, and "fuzzy" searching with LIKE, >, <, etc are basically impossible – Mark Baker Apr 03 '14 at 13:45
  • 1
    @Lub the argument to present to the client is that you have stringent controls over who has server and database access at your company, that you have a password change/rotation policy, and internal security audits. – Digital Chris Apr 03 '14 at 13:46
  • @ MarkBaker Thanks, I was worried about that... @ DigitalChris true, that's a valid point – Lub Apr 03 '14 at 13:50
  • "fuzzy" searching isn't impossible `SELECT CONVERT(AES_DECRYPT( table.column, $key ) USING utf8) AS data ... HAVING data LIKE '%...%'` but it **is** very, very slow because it needs to decrypt the entire table before performing the match. – CD001 Apr 03 '14 at 14:10
  • @CD001 - yeah, true enough.... you need to use HAVING rather than WHERE, and the query can't use indexes in any way so performance will go downhill as more and more data is added to the tables... not impossible, but a pretty major stumbling block for many systems – Mark Baker Apr 03 '14 at 14:16

3 Answers3

3

If your clients are not trusting you to not read their data, you've got a problem there. This is not a technical problem, but a social one. Of course, you can encrypt their data (or even just a base64 encode) to make it not casually readable, but at some point the data has to be decryptable in order to be useful to them.

A one-way encryption (hash) is not useful here, as you need to be able to recover the original content. A fixed encryption key will not do the job, as you would be able to read content at will. Perhaps the client's password could be used as a key to encrypt the data, so that only they know the key that was used? Whenever they change their password, the data will have to be de-encrypted with the old password and re-encrypted with the new. And if they ever forget their password and need a new one generated, the encrypted data will be useless.

Phil Perry
  • 2,126
  • 14
  • 18
  • I've toyed with the tinfoil hat idea of *using some function of the password as an AES_ENCRYPT key* ... but then I remembered how many "forgotten password" requests I have to deal with *dowp* :) – CD001 Apr 03 '14 at 13:33
  • Well yes, the trust problem is true. But still... we all know passwords -for exmple- MUST be hashed before being stored, right? No one doubts you will do that. Why should I hash a password if I can read everything else? I suppose because "passowrds" are special words that people tend to use for multiple environments (bank, email, ...), right? – Lub Apr 03 '14 at 13:34
  • @Lub that's basically it yes - people tend to re-use passwords over several sites .. what you're really hiding when you salt and hash passwords is not the user data, but their login "key" to those other sites should *your* site ever be compromised - most other user data you have (unless you're a medical/insurance site perhaps) is quite likely to be in the public domain anyway. – CD001 Apr 03 '14 at 13:36
  • 2
    Hashing/salting passwords has nothing to do with keeping the server owners from seeing them.... any server owner can dump the form vars you submit and see your password before it ever gets compared to the hashed value. – Digital Chris Apr 03 '14 at 13:38
  • @CD001 "unless you're a medical/insurance site perhaps" and that is the case. This is a medical-related project indeed :-( – Lub Apr 03 '14 at 13:47
  • @Digital well yes, I could just log every user input even before it gets hashed/salted and matched on the db, that's true. In that sense, the client is just forced to trust me. – Lub Apr 03 '14 at 13:49
  • @Lub well, for the data to be of any use whatsoever it will still need to be decrypted at some point, somewhere - otherwise there's no point in asking the user for it in the first place. Your best bet is to `AES_ENCRYPT` any sensitive data in the database and have the key stored in a config file (or similar) outside the web tree... it prevents casual access and an SQL injection attack would need to be paired with an application/SFTP attack to retrieve the key before the data is of any use. – CD001 Apr 03 '14 at 13:51
  • A _hashed_ input cannot be turned back into plaintext (not easily, anyway). It's useful only for comparing the two hashes for equality, such as with a password. Be careful of the distinction between hashing and encrypting. – Phil Perry Apr 03 '14 at 13:51
  • There is one other properly tin-foil hat solution perhaps where data can be entered on the web system but never read there - and another system that can read it - and both share a database. `GPG` encrypt any sensitive data before it goes into the database. Put the pubkey on the web server and the seckey on the other system. The data encrypted with the pubkey can only be decrypted with the corresponding seckey on the other system... keep the 2 servers physically separate but reading from the same DB. It would be slow as hell and probably OTT ... but the theory is sound I think. – CD001 Apr 03 '14 at 13:57
  • If the data on the database is itself encrypted, can you still do SQL queries, etc.? I suspect not, and the DB server (with unencrypted contents) would have to be physically isolated with all communications in and out encrypted. Possible, but probably not worth the effort. – Phil Perry Apr 03 '14 at 14:01
  • If medical-related data, read up on HIPAA data standards (U.S.) and any similar E.U. standards, and see if your client will accept those. Also PCI-DSS (credit card data) standards. – Phil Perry Apr 03 '14 at 14:02
  • @PhilPerry yeah, you can run queries on encrypted database tables with things like `AES_ENCRYPT` or `AES_DECRYPT` but they'll be slow as hell because it'll often require `HAVING` conditions and that means decryption of the entire table whilst reading from it - unless you're only ever doing reads based on say a foreign key index (`WHERE user.id = ...` or whatever) - an unencrypted index anyway. – CD001 Apr 03 '14 at 14:07
  • I trust you're also using SSL throughout, including between web/appserver and database server as well as between client browser and web server, etc – Mark Baker Apr 03 '14 at 14:35
1

If the issue is that the user does not trust the server environment with data, the ONLY solution that will work is to encrypt the data before it gets to the server environment. You don't specify what this data is or what the use cases are or who needs to access it, but there are ways to accomplish client-side encryption with something like:

crypto-js

Other viable options are discussed in this SO question

Community
  • 1
  • 1
Digital Chris
  • 6,177
  • 1
  • 20
  • 29
  • The scenario is very simple: a user hasa web form with some fields to fill (name, town, address...). Some of those fields should be user-only readable (in some way) – Lub Apr 03 '14 at 13:37
  • I feel like we're trying to answer a very advanced question, when you're asking a very basic question. Things submitted to a site can always be read by trusted admins of that site. Security is implemented to keep OTHERS from getting the data. I mean, obviously if name/town/address is given, it's not ONLY so the user can read it... the ordering system needs to read it to print labels (or some other use case). – Digital Chris Apr 03 '14 at 13:42
  • 1
    Well, at _multiple_ points in the process, the content will available in plain text on the server, _if_ it is sent to the server in plaintext (even if it is immediately encrypted). As @DigitalChris suggests, perhaps it could be encrypted on the browser (client side) (and decrypted there for display) with a password that never gets sent to the server. Of course, it's then useless for any purpose on the server side. Maybe you should look at PCI-DSS standards for handling credit card information -- perhaps your client will accept those? – Phil Perry Apr 03 '14 at 13:43
  • @Digital - "Security is implemented to keep OTHERS from getting the data" was a great point. – Lub Apr 03 '14 at 13:45
  • @Phil that would work pretty good BUT would completely kill server-side (php) SQL queries (I would unable to perform basic SELECT queries for example). – Lub Apr 03 '14 at 13:46
  • That's what I meant by "it's then useless for any purpose on the server side". If the server is not merely storing it as a big blob of bits, it has to be in plaintext at some point(s) on the server. If you're going to do SQL queries against it, it has to be stored in plaintext. – Phil Perry Apr 03 '14 at 13:48
  • @Phil that is suddenly making me aware og hoe EASY and SIMPLE is to read others' personal info, when you work in the IT area. – Lub Apr 03 '14 at 13:52
  • Yeah, like your doctor or your lawyer or your clergyman, you have to have a certain level of trust in your IT guy. There _are_ some technical assists here to make it harder for him to peek at your goodies, but nothing (on a normal server setup) to stop him if he's determined to see them. – Phil Perry Apr 03 '14 at 13:54
  • @Phil you know what? Everyone just trusts any random hotel owner who kindly asks your card via phone (to pay in advance) or keeps your card/document "for a while" to let you enter your room. – Lub Apr 03 '14 at 14:34
  • And your point is? We all have to trust some people to do the right thing in certain cases. Example: a special key or mode for your car, so when the parking attendant takes it, the speed is limited to 10 mph and the trunk won't open. This is for when you don't trust the attendant not to go for a spin or rifle through your shopping in the trunk. But you would give the regular key to your mechanic when they're doing service on the car. – Phil Perry Apr 03 '14 at 14:41
  • @PhilPerry I think Lub was agreeing with you ;). – Digital Chris Apr 03 '14 at 14:44
0

Take a look at the reference manual of MySQL, there's many functions you can use for encrypt/decrypt.

MySQL reference manual: Encryption and compression functions

JordiVilaplana
  • 485
  • 3
  • 9