Solved

User control using mysql query in PHP

Posted on 2010-11-19
12
354 Views
Last Modified: 2012-05-10
I have 200 products, each user only has control of certain products that they can edit.  I have 3 tables in my database.

I have a table for "users" that has the user_id (auto incrementing number), username, password and email.

Next table is "users_products" that has their up_user_id number and then up_product_id number.

Then I have products which has product_id, name, price.

I store their user id in a session.

Through the URL is how I pass my variables to each page.  So for instance http://domain.com/edit_product.php?id=7

I want to make it so on edit_product.php that they can only edit product_ids that are listed with their user_id in "users_products".  Or else they can simply manipulate the URL and change the id to some product that they aren't allowed to edit.

This is the query I use on the "view_products.php" page so they only see their products

SELECT product_id, name, price FROM users_products, products WHERE product_id=up_product_id AND up_user_id='20' GROUP BY product_id

0
Comment
Question by:kevandju
  • 5
  • 3
  • 2
  • +2
12 Comments
 

Expert Comment

by:smantscheff
ID: 34176631
So what is exactly your question? Use a similar query in edit_product.php:
SELECT products.* FROM users_products, products
WHERE product_id='$productId'
AND product_id=up_product_id
AND up_user_id='$userId'
0
 

Author Comment

by:kevandju
ID: 34176636
My question is that I need to validate that the user is allowed to edit the product id or else take them back to view_products.php.  I don't want someone to edit the URL and start editing products that aren't authorized to edit.
0
 
LVL 7

Expert Comment

by:armchang
ID: 34176684
Hi,

Use this query to check if they can edit:

SELECT product_id, name, price FROM users_products, products WHERE product_id=up_product_id AND up_user_id='20' AND product_id IN (SELECT users_products WHERE up_user_id='20') GROUP BY product_id
0
 
LVL 7

Expert Comment

by:armchang
ID: 34176696
Sorry, this should be the correct one:

SELECT product_id, name, price FROM users_products, products WHERE product_id=up_product_id AND up_user_id='20' AND product_id IN (SELECT up_product_id FROM users_products WHERE up_user_id='20') GROUP BY product_id
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34177232
Do your users sign in with password authentication?  If so, you've got the user id.  And with the user id you can make a SELECT against the users_products table.  If the thing they want to edit matches, you're OK.  If there are no rows in the results set, they cannot make the edit.  I'm guessing that the id= string in the URL GET argument is the id of the products, right?
0
 

Author Comment

by:kevandju
ID: 34177714
Yes they sign in with password identification and the user_id is stored in their session so I have access to that.

I"m thinking I need to run a query to see if they user_id is allowed to see that product_id and if results of the query returns 0 rows then I will just redirect them back to view_products.php.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:DFPercush
ID: 34177737
I suggest generating a fairly long random string and setting this as a session cookie whenever they log in. Compare the string from the cookie with the string in temporary session storage on the server, and bam your user is authenticated. Then it will be impractical for users to guess this long string. If you can't use cookies, compare ip address with $ip = getenv("REMOTE_ADDR") ;

http://www.w3schools.com/php/func_http_setcookie.asp

0
 

Accepted Solution

by:
kevandju earned 0 total points
ID: 34177857
Here is code that I put on my pages that works.  Store product id as $p and store user id as $id then check to see if user is an admin, if they are not then I check to if there is a row in users_products with that user id and product id, if not I redirect them back to the view_products.php page.  

if ($_SESSION['user_type'] != "admin") {
            $id = $_SESSION['user_id'];
            
            $query2="SELECT * FROM db.users_products WHERE up_user_id='$id' AND up_user_products='$p'";
            $result2=@mysql_query ($query2);
            $num2 = mysql_num_rows($result2);
            
            if ($num2 == 0) {
                  $url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
            
                  if ((substr($url, -1) =='/') OR (substr($url, -1) == '\\')) {
                        $url = substr ($url, 0, -1); //chop off the slash
                  }

                  $url .= '/view_products.php';
                  header("Location: $url");
                  exit();
            }
      }
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34178040
Looking at this comment from ID:34177714
"... if results of the query returns 0 rows then I will just redirect them back to view_products.php."

Here is how I might design it.  For every product that is displayed on the view_products page check the users_products table.  If they are allowed to update the product, you can add a link that says, "Update this" and you can omit that link from the other product displays.  You still have to perform server-side validation of any update requests, but your site will make sense to all the visitors because they will only see "Update this" links on the products they control.

A little bit of clever query design can make this very efficient.  Perhaps one query gets all the rows for all the products and a second query gets all the rows that the client controls.  Then as you produce the HTML, you can test with in_array() to see if the product should get the "Update this" link.
0
 

Author Comment

by:kevandju
ID: 34178075
I already do this. The view products page only shows their products. I was trying to avoid people manipulating the URL to edit products that weren't theirs.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34178228
Then just check the users_products table whenever an update request is received and only proceed if there is a match between user and product.  It should work great.
0
 

Author Closing Comment

by:kevandju
ID: 34203814
Was able to come up with the query and solution on my own for this problem.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now