"insert into" and security

[blackhorse]

for example there are 3 tables

1) customer

customerid, customername

2) list

listid, customerid, listname

3) product

productid, productname, listid

So here is the question:

after the customer login, they can manage its own lists and products.

when they add/update/delete the lists, i will always check if the customerid of the list belong to this customer. so no problem.

when they update/delete product, I will always to check if the listid belong to this customer, by use where and multiple table relationships.

the only problem is insert.

$sql_insert="INSERT INTO product (productsid, productname, listid) VALUES ($productid, '$productname', $listid)";

There are no where condition in insert statement that I can make sure the listid belongs to this customer. so the insert is valid.

What I did is in the beginning of the php page, I will check the listid or listid(s) which are passed to this page and will be used in this page, belong to this customer or not. This should take care of all the above problems.

But I still in the sqls about list, and sqls about update/delte product, check to see if listid belong to this customer. It is unnecessary duplication but no hurt. the database are not so big.

But I want to know is there a simple mysql built in query, that I can enforce in the sql insert statement, only allow the listid belongs to this customer to be insert into the database?

in simple words, i know customerid=3

when $sql_insert="INSERT INTO product (productsid, productname, listid) VALUES ($productid, '$productname', $listid)";

I want to make sure $listid belong to customerid 3.

Thanks!

[laserlight]

A foreign key constaint can check that the listid being inserted exists in the list table, and another foreign key constraint can check that every customerid in the list table exists in the customer table, but I do not see how the database can verify that customerid was not tampered with. If the user has such privileged access, he/she can specify a valid customerid other than his/her own. I think that the solution is to simply use appropriate authentication and access control such that the user cannot change his/her own customerid.

[blackhorse]

yes. user cannot change his/her own customerid. That has been establised.

but when they insert into products

$sql_insert="INSERT INTO product (productsid, productname, listid) VALUES ($productid, '$productname', $listid)";

they can chang the value of $listid and that listid actually could belong to some other customers.

I can SIMPLY use where condition to check to see if the listsid belong to this customer to see if I allow the activities (select/edit/delete list. select/edit/delete product), and when i allow the cutomer add the list, the cutomerid they cannot change. so add the list is fine too.

Only in the activity of add product, i cannot use where condition to check to see if the listsid value input actually belong to this customer, and the listid value customer can attempt to change it.

i control this cutomer's privilege through php, such as they cannot change their customerid.

As about valid the listid values. in the beginning of the php scripts, when i accept the value from customer, i check in the database to see if the listid submit by this customer belongs to him/her. and then continue if valid.

I was looking for an additional database approach (1 sql query etc. like I use where condition to control all the other activities about table list and table product.) to control that the cutomer cannot insert into product table with other customers' listid(s). just like there are also database control approach for edit/select/delete list table and product table.

Or I should just stick with my php solution. valid the listid passed by the customer first before continue?

[laserlight]

I am not sure if this is the correct design, but I might suggest something along these lines:

CREATE TABLE customer (
    customerid   INT NOT NULL AUTO_INCREMENT,
    customername VARCHAR(50),
    PRIMARY KEY(customerid)
);

CREATE TABLE list (
    listid     INT NOT NULL AUTO_INCREMENT,
    customerid INT NOT NULL REFERENCES customer(customerid),
    listname   VARCHAR(50),
    PRIMARY KEY(listid, customerid)
);

CREATE TABLE product (
    productid   INT NOT NULL AUTO_INCREMENT,
    listid      INT NOT NULL,
    customerid  INT NOT NULL,
    productname VARCHAR(50),
    PRIMARY KEY(productid),
    FOREIGN KEY(listid, customerid) REFERENCES list(listid, customerid)
);

This way, it is impossible to insert a product into a list belonging to a different customer. Technically, it also makes it possible for each customer to have a list id #1, list #2, etc, since the primary key is a compound key of both the list is and customer id, but the auto increment would mean that does not actually happen.

[blackhorse]

Thanks, I will give a try.