Prevent web 'User A' viewing 'User B' records in MySQL via query?

Question

Still new to MySQL.

I have the following MySQL table named secrets:

+----+--------+----------------+--------------------+
| id | userid |      name      | secret_ingredients |
+----+--------+----------------+--------------------+
| 72 |      1 | Nigella Lawson | Pixie dust         |
| 99 |      2 | Delia Smith    | Ground beatles     |
| 32 |      3 | Anjum Anand    | Minced fairies     |
+----+--------+----------------+--------------------+

I also have an HTML table that lists all the secret ingredients for each logged in user based on their SESSIONID which is their DB userid.

The table has a form button that posts the displayed row id to a php file to show all the row data on a new (details) page.

I've used the following query in the php file to pull the data:

$id = $_POST["secret_id"];

$userid = $_SESSION['uid'];

{

$result = mysql_query("SELECT * FROM secrets WHERE id=" .$id );
$data = mysql_fetch_array($result);

        $secretstuff = $data['secret_ingredients'];
        $name = $data['name'];
}

The problem with the above is that a user can alter the 'secret_id' value posted to the php file, and hence view another users secret ingredients.

How do I prevent this from happening.? How do I construct a query that fetches the ingredient id only if the userid matches the current SESSION ID?

Should I fetch all records WHERE userid=" .$userid and then fetch the web selected $id variable from the fetched array?

Or am I going about this all wrong?

I have a little bit of advice for you, if I may, please refrain from using mysql* extensions in php, they have been deprecated, instead switch to mysqli* or pdo*. — Satya, Nov 17 '13 at 06:26
Why are you showing the ingredients for all logged in users if you don't want to show them? — Shomz, Nov 17 '13 at 06:34

score 1 · Accepted Answer · edited May 23 '17 at 12:28

Should I fetch all records WHERE userid=" .$userid and then fetch the web selected $id variable from the fetched array?

Yes, you should also check the userid along with the id

Modify your query..

$result = mysql_query("SELECT * FROM secrets WHERE id=" .$id ." AND userid = ". $userid);
if (!$result) {
    // redirect to the previous page (list page) with the message
    header('location: list.php?msg=Invalid request');
    die();
}

Note that if user tries to hack the form and change the id to check other's data you need to redirect them back because now query will return null/empty record set..

WARNING: mysql* functions deprecated

check Why shouldn't I use mysql_* functions in PHP?

Thank you so much. I was looking at much more complicated ways to achieve this! Sooo simple. :) — Elijah Paul, Nov 17 '13 at 06:46

score -1 · Answer 2 · answered Nov 17 '13 at 06:30

you can either store the session_id in a table somewhere that is related to that user_id (which gets into session management ) or you can obfuscate the id so its not a number. Maybe do an encrypt on it with a hash (sha-1, md5, or something crazy like sha-256) or something.

Prevent web 'User A' viewing 'User B' records in MySQL via query?

2 Answers2