Prevent for viewing all data in table

[arot]

Hello,

I am not sure how to get around this:

I have a MySQL database with 2 tables (table1 and table2). Queries to this database is restricted only to autenticated users (by using password protected directories).

Now I would like one user can make a query to table1 but I would like to restrict the results to match only specific rows, and preventing this user can query the rest of the table1 data. Doing this with a form is just easy, but if the user types in the location bar on browser whatever he wants, the query is executed and the user can access all data in this table, and this is what I want to prevent.

Is this possible? What would be the steps involved?

Thanks in advance for any idea.

[Ron]

There are many ways of doing what you want, none of the one that I can think of are particularly attractive using MySQL (to my limited knowledge of MySQL).

Based on your message, if simply protecting the data through the use of forms is sufficient, you could use POST instead of GET in your forms... that would hide the data in the query.

I guess it depends how secure you need your data to be.

[jason]

You just need to do some input checking before executing the query. For example, if the user only has access to a row of info about himself then store the login info in a place that isn't passed from page to page (such as in a PHP session variable). If there are different criteria for determinig what a user can see you'll probably need to do some kind of "senity check" on the form data before executing the query. This would be done in the programming language you are using to call MySQL (such as PHP).

Without more details about your application it is hard to give you more specific details.

--Jason

[arot]

Hello

I am using a PHP script in order to get the data.

The script uses GET instead POST (with POST does not work and no results are displayed). How could I fix this?

P.S. I am using the following script:
http://kentung.f2o.org/scripts/paging/

[arot]

Jason,

Database is working as follows:

Users are feeding database (table1) every day by filling a web form. Data is stored on it and only 2 users can access to another web form in order to make queries. Queries are made by another web form, and these 2 users can view all data with no restrictions. O.K.

Now I want one more user can query this database (table1) but only to specific rows that contain data about itself, so I want the user can access his/her data. For example:

Table1

ID: 1
User: Surname, name
E-mail: user@domain.com
Data: data input for that user

ID: 2
User: Surname2, name2
E-mail: user2@domain.com
Data: data input for that user2

(...)

I want to filter the query results in order to prevent this user for viewing data stored in this table1 that is not owned by him and I am not sure how to get it.

I was thinking that one solution can be to make another database that contains only a table with the user's data, but I don't know how to do it, because data is changing every day so the new database must be feeded by dynamic data of table1.

I am using PHP in both web forms (user's input and queries results).

Any idea is highly appreciated.

[jason]

Hello

I am using a PHP script in order to get the data.

The script uses GET instead POST (with POST does not work and no results are displayed). How could I fix this?

P.S. I am using the following script:
http://kentung.f2o.org/scripts/paging/

The script is probably using the script values from either the $HTTP_GET_VARS or the $_GET array. Try changing all $HTTP_GET_VARS[...] and/or $_GET[...] statements to $_POST[...] (or, to make the script work with either get or post, $_REQUEST[...]). Post is the better option when submitting more than a minor amount of data because there is no length restriction and the information does not become part of the URL. Some browsers limit how much data can be sent via GET.

I just looked at the script quickly and I see now that it doesn't use either of the aforementioned arrays. Instead it takes the value of the query string (the stuff in the URL after the ?) and parses it into its own array. It'll probably take a fair amount of work to get this working with POST data. Further, since this is a paging script, which allows you to view a query's results x records at a time, you probably won't be able to avoid the query string since it isn't possible to pass POST data through a normal link (which is probably used for the forward/back "buttons").

--Jason

[jason]

Jason,
I want to filter the query results in order to prevent this user for viewing data stored in this table1 that is not owned by him and I am not sure how to get it.

Are your users logging in in order to access this data? If so then you probably have (or should have) some kind of a unique identifier (like a username or other unique id) that you can match to specific rows in the table. You could store this value in a session variable and add it into the query before it is executed without passing it in the querystring. That way the user wouldn't be able to change it. Try adding something like "WHERE UserName = '{$_SESSION['user']}'" to your query.

--Jason

[arot]

Further, since this is a paging script, which allows you to view a query's results x records at a time, you probably won't be able to avoid the query string since it isn't possible to pass POST data through a normal link (which is probably used for the forward/back "buttons").

--Jason

Yes, you are right. I had been testing this and it is not possible to use POST instead GET, because the variable is missed and paging does not work. I think I will find another script that can use POST.

Thanks for your review!

[arot]

Are your users logging in in order to access this data? If so then you probably have (or should have) some kind of a unique identifier (like a username or other unique id) that you can match to specific rows in the table. You could store this value in a session variable and add it into the query before it is executed without passing it in the querystring. That way the user wouldn't be able to change it. Try adding something like "WHERE UserName = '{$_SESSION['user']}'" to your query.

--Jason

Users are logged only by using the password protect option for folders, so they have to autenticate in order to get access into the form used to make queries into the database.

It's a quite difficult to explain. The target is allowing one user to query only into some (specific) rows, but no the rest of the database. This user could view infomation regarding himself and his team (user1, user2, user3), but preventing accessing data of user4, user5 and user6.

I'm going to investigate a bit more that option of WHERE UserName = '{$_SESSION['user']}.

Thanks!

[jason]

What you could do is implement a "GROUPS" table that defines which users are part of each group. For example, something like this:

+----------+----------+
|Groups    |Members   |
+----------+----------+
|Grp1      |Mbr1      |
|Grp1      |Mbr2      |
|Grp2      |Mbr3      |
|Grp2      |Mbr4      |
+----------+----------+

You'd then have to do some table joining to match the groups to the to the records.

You can use $_SERVER['PHP_AUTH_USER'] and $_SERVER['PHP_AUTH_PW'] to get the username and password that the user entered, just in case you didn't know that already.

--Jason