separate user data in database
Moderator: General Moderators
separate user data in database
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
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
Re: separate user data in database
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.
Re: separate user data in database
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.
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.
Re: separate user data in database
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.
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.
Re: separate user data in database
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.
Re: separate user data in database
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).
- kaisellgren
- DevNet Resident
- Posts: 1675
- Joined: Sat Jan 07, 2006 5:52 am
- Location: Lahti, Finland.
Re: separate user data in database
and getUserTransactions() could even notice/alert if there is no user parameter.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).
Re: separate user data in database
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.
Re: separate user data in database
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.
Re: separate user data in database
No problem, good luck with your project.