[web] MySQL - Problem with multiple POSTs

Check the number of current units the user has before updating the database.

basically:
Start the transaction
do a SELECT query and lock the rows covered by it, check the result serverside using whatever language you're using, and then if the users post data would be valid you run the update query.
and finally commit the transaction.

To lock the rows you just add "FOR UPDATE at the end of your initial SELECT statement (The lock is released once the transaction is commited)

Oh, and you probably need to switch away from the default MyISAM engine and use something like InnoDB.
You probably want the uid as a key to make it easy for InnoDB to only lock the row you're interested in. (IIRC InnoDB will lock all rows scanned by the select query, even if the WHERE statement fails on a row, so without good keys you'll end up locking large portions of the table)

Edit: Another option could be to use shared memory in the server side scripts to ensure that no user has more than one instance of the script running.

[Edited by - SimonForsman on March 27, 2009 9:48:20 PM]

I should have made clear that I'm on a shared host, and MyISAM tables are the only option I have at the moment, so transactions are NOT supported. That is why I need to come up with something else.

You could generate a unique id for every form. Then store all ids of submitted forms in a server-side session array allowing a particular form to be processed only once by checking whether its id already exists in the array.

You haven't specified your server side language, but I would guess that something in the server's session would be sufficient, providing you can be sure that 1 session is accessed atomically, or that you can't have 2 sessions for the same player. If that's ok, storing the eligibility in the session is the way to go.

A poor-man's database lock by setting some sort of flag in a table might also work, but that depends on the complexity of the transaction, which again you haven't specified.

The server-side language is PHP, which has good session support. But from comments on sessions, it looks like the session data is written when the page exits, which would not prevent multiple simultaneous requests. It looks like the best method would be to use a database semaphore.

Any sort of ad-hoc semaphore is likely to be prone to the same problems, although the chance of a tester or player replicating them is much smaller. And that may be good enough for you. Or perhaps there's some sort of scheme where you implement your own integrity checking and rollbacks which would suffice. I don't have any firm suggestions in this area however.

I'm testing out a simple database semaphore, where I set it to a unique value for each post, and reset it after the post is done. Two "very" simultaneous requests could happen, but the chance is much less now. We'll see what testing results I get. (The problem browser is Opera. Firefox and IE don't allow double-clicks on buttons.)

the system knows (or at least it should know) how many troops the user is allowed to create, so when he submits a post you should check that action against the condition. The same condition that disables the button should be doubled in the background. Also, disable the button. Hacks can get through it, but not everyone can hack so is nice to have a disabled button.

Quote:

Original post by dinojazz the system knows (or at least it should know) how many troops the user is allowed to create, so when he submits a post you should check that action against the condition. The same condition that disables the button should be doubled in the background. Also, disable the button. Hacks can get through it, but not everyone can hack so is nice to have a disabled button.

I couldn't test, because my browsers (Firefox and IE) disable the post when clicked, but I know what the problem was. A double-post would cause the routine to check preconditions twice, each finding no problem. By the time the unit was created, which would cause the precondition to fail, the second post had already begun, and the second check was passed.

A simple database semaphore, putting a "unit-create" in a semaphore field specific to the user resolved the problem. The semaphore update was quick enough that the second post was refused. I'll be implementing that system throughout the game, anywhere a second post could cause a problem.

It's not as good as transactions, or checking the condition again before the commit, but it will prevent almost all problems. (I think!)

My resident hacker tried everything he could to defeat the database semaphore approach, and was unable to get the game to fail. So, the simple approach worked, this time.

Bah, why worry about hackers using this exploit, unless your game gets like thousands of visitors per day. But then again that is just my opinion.

Quote:

Original post by ID Merlin My resident hacker tried everything he could to defeat the database semaphore approach, and was unable to get the game to fail. So, the simple approach worked, this time.

Would it be possible to detect any duplicate unit costruction after it has occured ? (This could solve most problems by you simply deleting the newest unit and providing the user with an error message in case of an unfortunaty double post)

Quote:

Original post by SimonForsman Quote: Original post by ID Merlin My resident hacker tried everything he could to defeat the database semaphore approach, and was unable to get the game to fail. So, the simple approach worked, this time. Would it be possible to detect any duplicate unit costruction after it has occured ? (This could solve most problems by you simply deleting the newest unit and providing the user with an error message in case of an unfortunaty double post)

That would work, but it would hit the database a bunch of times to create the unit, then a bunch more to delete it.

Quote:

Original post by polyfrag Bah, why worry about hackers using this exploit, unless your game gets like thousands of visitors per day. But then again that is just my opinion.

Isn't it better to fix it now, than to wait until we have thousands of users.

Yeah true, but if I was doing I would just leave some things because I am not a pro and couldn't fix every possible exploit easily.

Quote:

Original post by polyfrag Yeah true, but if I was doing I would just leave some things because I am not a pro and couldn't fix every possible exploit easily.

Become a pro, grasshopper.

The playtester discovered a flaw, still. I think it was with two almost simultaneous posts, as he tried numerous times unsuccessfully. I changed my "semaphore" code a bit to try to counter that.

/* *** SEMAPHORE ******************************************************* *//** *  Acquire a semaphore.  Uses update and returns affected_rows (1 if success, 0 if fail) *  This should not allow any chance of multiple successes, as the operation is atomic */function acquire_semaphore($user_id, $semaphore) {global $db;    $sql = "update ao_user set semaphore = '$semaphore' " .        "where user_id = '$user_id' and semaphore = ''";    $db->query($sql);    return $db->affected_rows();}/** *  Release any semaphores set for the $user_id */function clear_semaphore($user_id) {global $db;    $sql = "update ao_user set semaphore = '' where user_id = '$user_id'";    $db->query($sql);}

The acquire_semaphore method used to be a query followed by a set_semaphore, which intruduced a slight delay between checking and getting the DB lock. I'm hoping this will eliminate that possibility. Anyone have any comments on this?