How to prevent of giving vote to his own post?

Question

I have a table named Votes like this:

// Votes
+----+---------+------------+---------+-------+------------+
| id | post_id | table_code | user_id | value | timestamp  |
+----+---------+------------+---------+-------+------------+
| 1  |  1424   | 2          | 433     | 1     | 1445898101 |
| 2  |  53431  | 4          | 54      | -1    | 1443891149 |
  .     .        .         .       .
  .     .        .         .       .  
+----+---------+---------+-------+------------+

Now I want to know, how should I check Votes table that the new vote is belong to his-own-post or that post is from other guy? Should I check $_SESSION['user_id'] with what?

...yes and with a `WHERE` clause. Plus, make sure you start the session. — Funk Forty Niner, Dec 18 '15 at 20:32
Ofcourse just check the session id either equal to posted user id or not — devpro, Dec 19 '15 at 09:08
Why do you let people vote on their own posts in the first place? — Barmar, Dec 19 '15 at 16:10
@Barmar I don't let them *(vote button is deactivate when people open their own post)*, But I'm worried about hacker. They can active that vote-button simple. — stack, Dec 19 '15 at 17:21
All important checks should be done on the server, not in the client. — Barmar, Dec 19 '15 at 17:26
@Barmar Well that's exactly my question, *How to prevent of giving vote to his own post* server-side. So, what do you mean of *"first place"*? Actually I want to do that before inserting. — stack, Dec 19 '15 at 17:28
When the user is submitting a vote, perform a query to get the `author_id` of the post they're voting on. If it's the same as `$_SESSION['user_id']`, don't insert into the `Votes` table. — Barmar, Dec 19 '15 at 17:30

Barmar · Accepted Answer · 2015-12-19T17:50:26.437

2

You should check for this when they're trying to vote, and not allow the vote. Do a query like:

SELECT author_id
FROM Posts
WHERE id = $_POST[post_id]

Then check if the author is the same as the current user:

if ($row['author_id'] == $_SESSION['user_id']) {
    echo "You can't vote on your own posts!";
} else {
    // insert into Votes table
}

If you want to do the check in INSERT query, you can do it like this:

INSERT INTO Votes (post_id, user_id, value, timestamp)
SELECT $post_id, $user_id, $value, $timestamp
FROM DUAL
WHERE $user_id != (SELECT author_id FROM Posts WHERE id = $post_id)

Then to report the error, you can get the number of rows affected by the query; if this is 0, it means they tried to vote on their own post.

if (mysqli_affected_rows($conn) == 0) {
    echo "You can't vote on your own posts!";
} else {
    echo "Thank you, your vote has been recorded.";
}

edited Dec 19 '15 at 17:50

answered Dec 19 '15 at 16:15

Barmar

741,623
53
500
612

Ok thanks, +1 ..! Just one thing, how can I use this query? what happens if user gives a vote to his own post? – stack Dec 19 '15 at 17:22
Nothing happens except they get more votes than they should. – Barmar Dec 19 '15 at 17:28
I've added some sample code to show how to check when they try to vote. – Barmar Dec 19 '15 at 17:33
Ah, then you mean is I send a query for checking that before `INSERT`. Emm that's not bad. But I think two separated queries (`select`,`insert`) just for a vote is kinda unoptimized, anyway ok thanks for yor attention. – stack Dec 19 '15 at 17:44
BTW, is this the same with yours `INSERT INTO Votes (post_id,user_id,value,timestamp) VALUES (".$post_id.",".$user_id.",".$value.",".$timeStamp.") WHERE user_id != (SELECT user_id FROM Post WHERE post_id = ".$post_id.")` ? – stack Dec 19 '15 at 17:45
You can't use a `WHERE` clause in `INSERT`. – Barmar Dec 19 '15 at 17:45
And my last question, Is there any difference between checking that using a separated query and a trigger? I mean is I can do what you said with a trigger, now I want to know which one is better? trigger or a query in php file? – stack Dec 19 '15 at 17:53
Whichever you prefer. See http://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342 for how to use a trigger to make the attempted insertion fail. – Barmar Dec 19 '15 at 17:56
If you have many different scripts inserting votes, using a trigger allows you to put the logic in one place, instead of repeating it in each script. If there's just one voting script, I would just do it there, because you can then see the logic. – Barmar Dec 19 '15 at 17:57
`... FROM DUAL ...`, what is `DUAL` ? Is it a real table? – stack Dec 20 '15 at 13:41
DUAL is a fake table that you can use as a placeholder in queries like this. – Barmar Dec 20 '15 at 16:01
Oh, very well ...! Now I have three questions: 1. Just `DUAL`? I mean is `DUAL` is a constant keyword? or any word can be an alternative instead of `DUAL`? – stack Dec 20 '15 at 16:42
2. Your query is better or this: `... FROM Posts p WHERE id = $post_id and p.author_id != $user_id` ? – stack Dec 20 '15 at 16:43
1

1. https://en.wikipedia.org/wiki/DUAL_table 2. Yes, you could write it like that instead. – Barmar Dec 20 '15 at 16:57
Well Just those two above questions `:-)` I understand the third o by myself. Also really thanks for your old answers and *(beforehand)* thanks for your new answers. – stack Dec 20 '15 at 17:04

SamyQc · Answer 2 · 2015-12-18T21:10:31.383

1

You could do the check within your MySQL query. Don't forget to protect against MySQL injection. Not 100% sure about the syntax, but this should give you a good idea.

$answer = $MySQL->query("UPDATE Votes SET ['value'] = (['value']+1) WHERE post_id = ".$post_id." AND user_id != ".$SESSION['user_id']);
if($answer) return "Upvoted";
else return "Can't upvote yourself !";

EDIT :

You should actually do a check in your Posts table first

$userWhoPosted= $MySQL->query("SELECT user_id FROM Post WHERE post_id = ".$post_id);
if($userWhoPosted != $_SESSION['user_id']){
    $answer = $MySQL->query("INSERT INTO Votes (post_id,user_id,value,timestamp) VALUES (".$post_id.",".$user_id.",".$value.",".$timeStamp.")  ")
}  
else return "Can't upvotes yourself";

EDIT 2 :

$answer = $MySQL->query("INSERT INTO Votes (post_id,user_id,value,timestamp) VALUES (".$post_id.",".$user_id.",".$value.",".$timeStamp.") WHERE user_id != (SELECT user_id FROM Post WHERE post_id = ".$post_id.")  ")

edited Dec 18 '15 at 21:10

answered Dec 18 '15 at 20:38

SamyQc

365
2
10

`user_id` stores what user? who is owner of post or who is giving a vote? – stack Dec 18 '15 at 20:52
edited my post, At first, I didn't get how your DB was structured. – SamyQc Dec 18 '15 at 21:05
1

Your solution after EDIT works ..! But 2 queries for inserting a vote does't seem good ..! – stack Dec 18 '15 at 21:06
You could also do it in a subquery, I'll do another edit. But imo, that's still the best way you could do it – SamyQc Dec 18 '15 at 21:08
1

DONT make another query. Presumably, the author of the post's information is already pulled into that page. Just check it before doing the query. – I wrestled a bear once. Dec 18 '15 at 21:11
Still I believe I can do that using one without another query or a sub-query. But thanks for your try. +1 – stack Dec 18 '15 at 21:13
@Pamblam The author of the post is not in the Votes tables, only the post_id and the user who upvoted/downvoted is. – SamyQc Dec 18 '15 at 21:14
1

When you're creating the page, make sure you include the author's user ID number somewhere, maybe in a hidden input field if security isn't a big issue. Then when the user click just do a simple conditional to see if it's the same id. This is super basic stuff and you have given no helpful information or code at all, so that's all i'm gonna say. in the future, please include code samples and be very specific about the question. – I wrestled a bear once. Dec 18 '15 at 21:18
There is no need for two queries here – Strawberry Dec 19 '15 at 00:08

score 0 · Answer 3 · answered Dec 18 '15 at 20:42

0

You didn't give us enough information, but there is a classical solution in MySQL

CREATE TABLE Votes
(
// your fields here
CONSTRAINT uc_post_user_vote UNIQUE (post_id,user_id,value)
)

It means that one user won't be able give vote up or down twice for the same post. Think abobut this, I hope it'll help you.

answered Dec 18 '15 at 20:42

D.Dimitrioglo

3,413
2
21
41

`user_id` stores what user? who is owner of post or who is giving a vote? – stack Dec 18 '15 at 20:52
@Pamblam How this is a great solution? It just prevent of giving two votes to one post ..! My question is, where should I store the user of author? in the Post table or Votes table? – stack Dec 18 '15 at 21:23
if that is your only question, the answer is store the OP's id in posts, store the voters in votes. or whatever. – I wrestled a bear once. Dec 18 '15 at 21:28

How to prevent of giving vote to his own post?

3 Answers3

Linked