separate user data in database

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
zensys
Forum Newbie
Posts: 9
Joined: Mon May 31, 2010 5:16 am

separate user data in database

Post by zensys »

I am not sure if this is security in the sense of PHP - Security but I'll give it a try anyway:

I am developing an accounting application where all accounting data are in one table distinguished by user by the field "user".

For entries, updates and reports in all queries I include "... WHERE user='foo' ...." using session data. But my fear is that one day I will forget to include this statement in a new page and one user will be able to see other users data.

What is best practice to avoid this? Or any other ideas on this, either in mysql or in php ?

thanks
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: separate user data in database

Post by Zyxist »

This is why programmers are supposed to do tests in order to find and remove such errors. The proper application design and programming practices should help, too. It's not an ordinary security issue.
zensys
Forum Newbie
Posts: 9
Joined: Mon May 31, 2010 5:16 am

Re: separate user data in database

Post by zensys »

Zyxist, thanks for your reply, you are absolutely right about that. The thing is that testing is not 100% fool proof.

I am looking for a structural approach e.g. setting a variable or view immediately after login such that the query is generated automatically (with the 'where user' statement. Somebody pointed me to stored procedures and that is something I want to investigate as well.

After all I cannot imagine that e.g. financial institutions solve this problem only with relying on proper testing and programming.
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: separate user data in database

Post by Zyxist »

If you worry about the missing SQL conditions, you can always use any ORM and a kind of query builder which will add it automatically. You say that testing is not 100% fool proof, and I agree (especially if the test author is a fool :)), but there is no tool and no technique that is. The more automated tool is, the bigger probability that you will have to disable the automatic actions in some places for certain kinds of tasks that do not need them. As an effect, you are "secured" in one place, but at the same time you get an opportunity to make a mistake in another one.

When it comes to algorithms, their correctness can be proved mathematically. However, their implementations must be checked manually, with unit tests or some other "unreliable" form. From the complexity theory we know that it is impossible to write a general algorithm that would take two implementations/representations and tell us if they give exactly the same results for the same input. By "general" I mean "that would work correctly for every two algorithms and every code that could be written". For some special cases, such algorithms do exist, but for the rest - they do not.
zensys
Forum Newbie
Posts: 9
Joined: Mon May 31, 2010 5:16 am

Re: separate user data in database

Post by zensys »

Hi Zyxist, thanks again for your profound reply to which I can only agree. Thanks to you and others (and Google of course) I am progressing in my thoughts on this and I am now thinking of using a database connect class which includes a query builder as you suggested.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: separate user data in database

Post by Mordred »

I think it is "smelly", from a code design point of view, to have queries in your code. Introduce a middle layer that hides the queries, let the "application" code call $aTransactions = GetUserTransactions($user_id), instead of querying the database in-place. Of course you still have to make sure the middle layer uses the queries properly, but it's easier to remember to put a WHERE user='$user_id' in a function called GetUserTransactions (or something).
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: separate user data in database

Post by kaisellgren »

Mordred wrote:I think it is "smelly", from a code design point of view, to have queries in your code. Introduce a middle layer that hides the queries, let the "application" code call $aTransactions = GetUserTransactions($user_id), instead of querying the database in-place. Of course you still have to make sure the middle layer uses the queries properly, but it's easier to remember to put a WHERE user='$user_id' in a function called GetUserTransactions (or something).
and getUserTransactions() could even notice/alert if there is no user parameter.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: separate user data in database

Post by William »

You should give this a read - http://msdn.microsoft.com/en-us/library/aa479086.aspx - although your system might not fall under the category of "multi-tenant" it still might give you a few ideas.
zensys
Forum Newbie
Posts: 9
Joined: Mon May 31, 2010 5:16 am

Re: separate user data in database

Post by zensys »

thanks William, this is exactly what I was looking for, very interesting. I will probably go for the shared approach as my service will start of for free.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: separate user data in database

Post by William »

No problem, good luck with your project.
Post Reply