mysql DELETE with field check from another table

Question

Good morning all, happy thursday morning. I wish I could have done this by myself but since I'm not a master in MySQL statements (yet) and I got lost in this DELETE query, here it goes...

I have to do a simple DELETE query like this, (deleting a comment by its id)

DELETE FROM mya_news_comments WHERE comment_id='".$_GET['comment_id']."'";

but at the same time, to prevent people deleting comments throughout the website i need to ensure that the person deleting this comment is who it is supposed to be (in our case, an artist).

I have another table mya_news which has among the fields news_id, artist_id In mya_news_comments I also have a field called news_id

So I need to check that I delete the comment_id of the particular artist, not of other artist. Basically i need to cross-check if the news_id field from mya_news_comments checks out with a field with same news_id from mya_news, and artist_id from mya_news is equal to $_id (which holds my artist_id)

I'm really stuck here. I'd be glad to give more details if needed.

Thanks.

Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables. — eggyal
– eggyal, Commented May 31, 2012 at 8:05
Alternatively, you should at least use mysql_real_escape_string(), if you ABSOLUTELY cannot use prepared statements. Everything is better what you do here... — glglgl
– glglgl, Commented May 31, 2012 at 8:12

abon999 · Accepted Answer · 2012-05-31 08:10:16Z

2

DELETE mya_news_comments
FROM   mya_news_comments
WHERE  mya_news_comments.comment_id = (SELECT [another_table].comment_id WHERE [cond])
   AND mya_news.artist_id = (SELECT [another_table].artist_id WHERE [cond]);

edited May 31, 2012 at 8:10

answered May 31, 2012 at 8:07

abon999

3361 silver badge15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

eggyal Over a year ago

But this query won't event compile. It references mya_news in the WHERE condition, but no such table reference exists in the query.

abon999 Over a year ago

I don't make his homework, i just give him a way to search. Now, he can use two different ways to code. My solution is just a little more expressive, so more easier.

eggyal Over a year ago

Quite apart from there being no indication that this problem is homework, I fail to see how someone could arrive at a solution to the problem given only your erroneous answer.

abon999 Over a year ago

He must test and arrange code, he will gain experience! Now, i need to go make a web application. Bye!

Adrian Tanase Over a year ago

thanks for the contribution anyways....i've learned a new trick from your line of mysql :)

eggyal · Accepted Answer · 2012-05-31 08:04:43Z

1

You can join tables in DELETE statements just like you can in SELECT statements:

DELETE mya_news_comments
FROM   mya_news_comments JOIN mya_news USING (news_id)
WHERE  mya_news_comments.comment_id = ?
   AND mya_news.artist_id = ?

answered May 31, 2012 at 8:04

eggyal

127k18 gold badges220 silver badges244 bronze badges

8 Comments

Adrian Tanase Over a year ago

I managed to do something way simpler from stuff I've read on Stackoverflow

eggyal Over a year ago

@AdrianTanase: This isn't simple? Perhaps you could link to what you've found / post as another answer so that others can learn the simpler way too?

Adrian Tanase Over a year ago

DELETE FROM mya_news_comments WHERE comment_id='".$_GET['comment_id']."' AND EXISTS (SELECT news_id FROM mya_news WHERE artist_id = '".$_id."' AND news_id = '".$_GET['item_id']."')"; I added in the _GET data also the $_GET['item_id'] which is a news_id, or event_id depending on what i need (i got more stuff going on) Checked with deletion and see that it's working. So it deletes if it finds the artist_id identifier, if someone tries to manually write the URI and try to delete other comment_id that doesn't belong to the account they're using, it's not being deleted. Thanks for the help.

Adrian Tanase Over a year ago

yes, eggyal thanks a lot, I was just typing the answer (copying from my laptop screen on my desktop here)...i'm new to the community :)

eggyal Over a year ago

@AdrianTanase: Your proposed answer wouldn't stop a malicious user from merely changing the comment_id variable in the query string, but leaving the item_id variable pointing to a news item which they own. The EXISTS subquery will succeed, and the comment will be deleted even though it is on an unrelated news item. You must at very least ensure that the same news_id is used in the mya_news_comments table - this is where joins become very much easier, per my suggested answer above.

|

Collectives™ on Stack Overflow

mysql DELETE with field check from another table

2 Answers 2

5 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related