Thomas Williams Thomas Williams - 25 days ago 6x
MySQL Question

What is the easiest way to check if table data has been manipulated in firebug

I have a table, that obviously has ids for each row. When a user clicks on a table row he is taken to a different page where it shows the full data(The different page is really the same page which is unhid after the load event).

I am picking up the click event with jquery and then sending the id to jquery load and it loads in the data.

The problem is this. If the person using the page has firebug installed he can manipulate the id on the table and maybe access something he shouldn't be accessing.

So I have had to write a function that checks the id coming in can be accessed by that user.

function checkID($searchColumn,$userColumn,$tablename,$table_id,$user_id)
Global $db;
$query = "SELECT `{$userColumn}` FROM $tablename WHERE `{$searchColumn}`=?";
//echo $query;
$stmt = $db->prepare($query);

return true;
return false;

Basically the above function gives me a true or false depending on whether that id can be accessed by the user.

Is there a better way to check if data has been manipulated in firebug on server side?

Basically I want to make sure the id being sent to the server is the same id that is in the table, and not been manipulated by firebug. Even though my function works I keep thinking perhaps there is a simpler solution. What do the professionals do?


Data coming from the client always needs to be validated and sanitized on the server side.

I.e. if $searchColumn, $userColumn, $tablename, $table_id and $user_id come in as request variables, you need to check each of them for validity. Especially $userColumn, $tablename and $searchColumn need to be checked carefully for SQL injections, because they are added before you call prepare().
If you don't require the flexibility, you should enter the table and column names into the string.

You may even keep the user ID on the server side (as session variable) and only set it once on login, so you can get rid of the check completely. The tables referencing the user data still need to have a column with the user ID, though.

To ensure that a user can only access the data (s)he's supposed to see, you need to include the user ID in the query.


Imagine you have orders and order items in an 1 to m relation. You show the user the list of orders (s)he has made. When the user now chooses an order to display its items you can include the user ID in the query to ensure the user cannot access orders of other users.

Then the SELECT statement may look like this:

FROM order_item oi
INNER JOIN order o ON = oi.order_id
  AND o.userID = ?

When the user then provides an ID of another user's order, there won't be any items returned.

This could also be done in two steps by first making a query against the order table to check whether the order is assigned to the user and if so, making a query against the order items.