PHP/MySQL Theoretical Questions

Need help with "can it be done..."

[Nick_W]

Hi all,

I'm contemplating an application in PHP/MySQL that would need to allow me, the site owner, the following:

• Find out the size of a users table (restrictions on size)
  
• Protect users tables from myself. 100% secure/private
  

The nature of the app would require the users to be 100% confident in the privacy of their information, even from me.

Q. Is this posssible, and if so, what areas should I be looking at to get the right tools for the job?

Many thanks...

Nick

[Grumpus]

If you can teach your app to have access the the mySQL table, then you already have enough knowledge to access it yourself.

No?

G.

[ukgimp]

Could you encrypt the actual data inserted so that only the person with the correct key can understand it despite the fact that you as the admin will still be able to see it?

[Nick_W]

That's exactly what I was thinking..

Nick

[Nick_W]

So, we think it possible to protect the data from the admin?

What about getting the table size, anyone?

Nick

[lorax]

So, we think it possible to protect the data from the admin?

I would think so. If you encrypt the data on insert it will be inserted so that it couldn't be read even by PHPMyAdmin. The only way to read that data would be to use MySQL/PHP to query the db and pull the records. Now the only way to personalize it would be to either use logins and hash the pwd or use personal certificates.

[Nick_W]

Yeah, I'd keep a seperate table of username/pass info with the pass md5()'d. Sounds secure enough to me..

No thoughts on getting the size of a users table guys?

Nick

[jatar_k]

I know you can do it I am just having trouble remembering how exactly I've done it.

this looks promising
[mysql.com...]

maybe here
[mysql.com...]

[Nick_W]

Many thanks Jatar, the first appears to do it. Now all I got to do is work out how to USE the data it spits out ;)

More reading, but another day, got to watch a movie now :)

Nick

[jatar_k]

>>how to USE the data it spits out

I was having the same thoughts there should be something on php.net under mysql functions.

[toadhall]

The db directory holds three files for each table:

A data (.ISD or .MYD) file, an index (.ISM or .MYI) file, and a data dictionary (.frm) file.

Kind of clunky but I guess you could just add up their sizes for resource use total.

T

[toadhall]

...then again

If you do go the encryption route (best bet) then why not just use phpadmin to read the table stats?

T

[andreasfriedrich]

Nick, could you sum up how you intend to Protect users tables from yourself. After having read this thread I am not entirely convinced that a solution to your problem was suggested.

I am not sure whether there is such a solution at all (at least when there are only two parties involved).

Your application would need to encrypt the data. Symmetric encryption is not an option. You would have the key to encrypt the data and could use it to decrypt the data. So the only way to go would be to use some sort of asymmetric encryption. Using the public key you could encrypt the data. But your app would not be able to decrypt the data. You would need the private key for that. If you supplied a way to upload that private key to the server and use it in your app you would need a way to ensure that you do not have access to the private key. Your application would need to encrypt the key. Symmetric encryption is not an option... ;)

I believe the way to go would be to decrypt the data on the client side using a Java thingy or some kind of proxy server or have a third party involved. But even then your clients would have to at least trust that third party.

If you really want to do something like this have a look at the FreenetProject. This might give you some ideas.

Andreas

[Nick_W]

Thanks andreas. Good points.

I may very well have to go with just a strong privacy policy. The data that is to be stored would be extremely sensitive, I just need to assure users that I (or anyone involved with the site) would not use the information in the users personal table...

Nick

[ggrot]

Basically, my understanding would be that you would need 1 table with a plaintext username and a 1 way has of the user's password. The original password would then be the key for the symetrical encrpytion/decryption of the rest of the data and would not be stored on the server. This setup would prevent you from being able to view the data by having access to the database alone, but you can still edit the scripts to store the incoming passwords at any point and thus have access. So basically, I think andreas is correct in that the encryption/decryption process needs to occur in some trusted environment (either third party or local to the client machine).

[andreasfriedrich]

I may very well have to go with just a strong privacy policy.

Yes, eventually doing business with somebody is about trusting the other party to do what they promised in the contract.

Personally I would not have anybody else have access to extremely sensitive data. I would store it on an encrypted partition on my own computer which is not connected to the net in any way.

If the data is not that extremely sensitive then I would have to rely on a strong privacy policy or on a business partner in a jurisdiction whith strong privacy laws where violation of data privacy is an offense. Of course those measures will only help you to minimize the risk of a breach of privacy and to ease of getting compensated for such a breach.

Only if you were to provide a way to store illegal data would I suggest you make 100% sure that you do not have any way of knowing what is actually stored because then the CPS/DA/StA would not be able to prove that you knew what was stored and the court would have to assume that you did not know what was stored or that what is stored and could not be decrypted is legal rather than illegal. This is the way how the FreeProject works. But even the you need to have some kind of "trusted environment", i.e. a jurisdiction that actually heeds basic procedural human rights.

The bottom line: Interaction is inherently insecure. Without interaction there really isnīt anything to life ;)

Andreas

[andreasfriedrich]

But even then your clients would have to at least trust that third party.

I can understand that people would be more willing to trust a third party than Nick_W, moderator of the CSS [webmasterworld.com] at WebmasterWorld ;)

And no, Iīm not a cheeky *** ;)

Andreas

[edited by: eelixduppy at 9:55 pm (utc) on Feb. 18, 2009]

[Nick_W]

hehe,

The other option would be to make the database dowmloadable with an installer. Then folks could run it locally or on their website.

If they ran it on their website, the admin could still view it but it would not be such a big detail. The information is what one webmaster would not want another webmaster seeing. It's not the blueprints to fort knox ;)

Lot's of thinking to do....

Nick