[Bug 13750] New: AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

Summary: AppDB votes system is being cheated
           Product: WineHQ Apps Database
           Version: unspecified
          Platform: Other
        OS/Version: other
            Status: UNCONFIRMED
          Severity: normal
          Priority: P2
         Component: appdb-unknown
        AssignedTo: [e-mail]
        ReportedBy: [e-mail]

Currently, there are 2 apps with obviously fake votes. These are
High Velocity Paintball
(http://appdb.winehq.org/objectManager.php?sClass=version&iId=11442)
Silencer (http://appdb.winehq.org/objectManager.php?sClass=version&iId=9823)

HVP has 2 test reports by teh same guy and no comments, yet it's listed that
> 100 have voted for this app. This smells awfully like someone is trying to

cheat to get his app supported.
Silencer also doesn't seem to be legit.

These apps currently reside on the frontpage of AppDB without any
justification. It would be interesting to find out who voted for these apps and
if they were added by a bot. For the future, I suggest a captcha for
registration on AppDB.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

Jeff Zaroyko  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[e-mail]

--- Comment #1 from Jeff Zaroyko   2008-06-07 19:22:07 ---
Yeah, the voting user ids look pretty much sequential for both the apps, the
email addresses look bogus and judging by the timestamps it looks human so I'm
not sure how much a captcha would help, maybe we just need to add email
verification to discourage this behaviour.

I've made a log of and removed 90% of the bogus users for high velocity
paintball.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #2 from Jeff Zaroyko   2008-06-07 19:53:02 ---
I've removed most of the bogus accounts for the other app too.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #3 from haarp   2008-06-08 05:05:29 ---
I thought email verification was already in progress? Oh well. in that case,
enabling it should be able to stop this behavior.

Thanks for fixing the votes btw!

I'm leaving this bug open until email verification is implemented :)

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #4 from Jeff Zaroyko   2008-06-15 20:29:35 ---
patches welcome

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #5 from Jeff Zaroyko   2008-06-15 22:00:32 ---
(In reply to comment #4)
> patches welcome

ok - I came up with an idea... patch sent.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #6 from Jeff Zaroyko   2008-06-15 22:17:51 ---
ok, it's been applied - not perfect but should act as a deterrent.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #7 from haarp   2008-11-30 10:54:20 ---
Another suspicious app:
http://appdb.winehq.org/objectManager.php?sClass=version&iId=8735

I don't know if those votes were made before or after Jeff's patch was in
place...

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #8 from Jeff Zaroyko   2008-11-30 15:11:59 ---
no idea.  you can download the appdb mysql backup from ftp.winehq.org and take
a look at the user accounts that added the votes if you want to investigate.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #9 from haarp   2008-12-06 12:27:49 ---
Heh, don't really have enough knowledge about databases to interpret that file
correctly :P

Not to complain, but the app from comment 7 still doesn't have the bogus votes
removed.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #10 from Jeff Zaroyko   2008-12-06 20:26:16 ---
(In reply to comment #9)
> Heh, don't really have enough knowledge about databases to interpret that file
> correctly :P
>
> Not to complain, but the app from comment 7 still doesn't have the bogus votes
> removed.

it looks like they were there from before, using the same copy of the db I took
last time I looked at this, the votes appear to be there.

and there's nothing to know really:

install mysql.
download the backup, uncompress it, 
mysql -uroot
create database apidb;
use apidb;
source backup071208.sql;
# the url in comment shows 8735 is the verisonId
select userId, count(userId) from appVotes where versionId = 8735 group by
userId;

+--------+---------------+
| userId | count(userId) |
+--------+---------------+
<snip>
| 113799 |             3 | 
| 113800 |             3 | 
| 113801 |             3 | 
| 113802 |             3 | 
| 114064 |             3 | 
| 114065 |             3 | 
| 114073 |             3 | 
| 114074 |             3 | 
| 114075 |             3 | 
| 114076 |             3 | 
<snip>

This pretty much shows that the userIds who voted for this app were created
sequentially, probably by the same person.

So, take a copy of the suspect UserIds:

select userId into outfile '/tmp/eh.txt' from appVotes where versionId = 8735
group by userId;
quit

Turn them into html links so that an AppDB admin can look at the user prefs
page for each of them and delete them if necessary:

ghci # glasgow haskell compiler
lamers <- readFile "/tmp/eh.txt"
writeFile "/tmp/lamers.html" $ (concatMap (\id -> "<a
href="http://appdb.winehq.org/contact.php?iRecipientId=" ++ id ++ "">" ++ id
++ "</a><br/>")) (lines lamers)
^D

firefox /tmp/lamers.html

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

Alexander Nicolaysen Sørnes  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|UNCONFIRMED                 |NEW
                 CC|                            |[e-mail]
     Ever Confirmed|0                           |1

--- Comment #11 from Alexander Nicolaysen Sørnes   2009-07-22 12:26:01 ---
I'm planning to make a small vote inspection page that can be used by admins
online.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

Alexander Nicolaysen Sørnes  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|                            |FIXED

--- Comment #12 from Alexander Nicolaysen Sørnes   2009-07-22 14:54:34 ---
I have deleted most users that voted for the NBA Live using the new vote
inspector.  I think this bug can be resolved now; please re-open if there are
more cases.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

--- Comment #13 from haarp   2009-07-22 14:59:13 ---
Hopefully that page will not be needed again, but thanks anyway :)
Resolving.

[Bug 13750] AppDB votes system is being cheated

http://bugs.winehq.org/show_bug.cgi?id=13750

Dmitry Timoshkov  changed:

What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |CLOSED

--- Comment #14 from Dmitry Timoshkov   2009-07-22 20:34:12 ---
Closing.