CODEWITHSUNDEEP
mysql
kojow7
kojow7

Reputation: 11414

Update table based on condition in parent table

I have the following tables defined in my database:

moderator (id, name)

parent_object (id, moderator_id, parent_name)

child_object (id, parent_id, child_name, quantity)

I want to update a child object based on the ID. I currently have something like this which works: 

update child_object set child_name = "Fred", quantity=5 where id = 3;

However, there is a security hole because this allows any moderator to update any child object that they do not own. If the moderator_id is 2, how can I do the update on this table so that it will only update if the moderator_id of the parent_object is 2?

Upvotes: 0

Views: 49

Answers (2)

Donnie
Donnie

Reputation: 46933

In mysql update you can do joins.

update
  child_object c
  join parent_object p on p.id = c.parent_id
set
  c.child_name = "Fred",
  c.quantity = 5   -- Seriously, this better not be a string.
where
  c.id = 3 and p.moderator_id = 2

Upvotes: 1

Gratus D.
Gratus D.

Reputation: 877

update child_object set child_name = "Fred", quantity="5" 
where id = 3 AND parent_id IN (Select id from parent_object where moderator_id = 2)

This should work, but using "IN" is expensive. Alternatively, you write a test query first to validate the the update is allowed.

Select parent_id, moderator_id FROM child_object c
Left Join parent_object p on p.id = c.parent_id

Upvotes: 0

Related Questions