5

imagine this scenario , i have a website that users get credit with activity like selling their items or clicking on ads or whatever my users table would be something like this 

users : id , username , credit 
         15 , alex     , 1000 
         16 , jack     , 1500

so now users can request to withdraw their credit in some sort of payment my withdraws table would be something like this 

withdraws : 
id , user_id , amount 
1  ,  15      , 500 
2  ,  16      , 100

i have to subtract withdraw amount from their credit ... i like to do this with trigger 

CREATE TRIGGER  withdraw
  BEFORE INSERT
  ON withdraws
  FOR EACH ROW
BEGIN

     UPDATE  users SET credit = credit-NEW.amount WHERE id  = NEW.user_id;
END $$

and to make sure users cant end up with negative credit in the withdraw process i have this trigger ( of curse i'll check for this in the code logic as well ) 

CREATE TRIGGER  update_user
  BEFORE UPDATE
  ON users
  FOR EACH ROW
BEGIN

    IF NEW.credit < 0 THEN 
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid credit error'; 
    END IF;


END $$

now my question is , is it possible someone intentionally or trough some sort of server error send multiple withdraw request at the same time , and withdraw more than his credit .... and if so how can i prevent this ?

do i need to lock users table before each withdraw insert or anything like this?

update : if im going to lock the user row and use transaction wrapper for the whole operation ... seems reasonable but how does the trigger fits in ? i mean obviously i have to start the transaction and finish/commit it in the code (since i hate stored procedures and never use them ) does it mean i have to give up trigger and do the subtraction in the code as well or somehow trigger happens inside transaction despite starting/executing in the different platform/place

hretic
  • 999
  • 9
  • 36
  • 78

2 Answers2

1

I would solve this through Intention Locks. Look at an example here that I wrote. There is a narrative there of what happens between the Line 1 to Line 5 of a Transaction chunk as well as other pertinent info.

You naturally are not getting an incrementor like in that example. Rather, your code would as fast as possible perform your update and do a Commit.

That code can belong anywhere, not just in a stored proc. It can be and Event or a Trigger. The DB Engine (such as INNODB) is your mechanism to ensure two people can't get inside that LOCK at the same time. How you write your code related to this is up to you.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • so basically lock the user row and use transaction wrapper for the whole operation ... seems reasonable but how does the trigger fits in ? i mean obviously i have to start the transaction and finish/commit it in the code (you see i hate stored procedures as much as i love triggers) does it mean i have to give up trigger and do the subtraction in the code as well or somehow trigger happens inside transaction despite starting/executing in the different ... platform/place ( my english is failing me hopefully you know what i mean ) – hretic Oct 17 '16 at 01:31
  • another unrelated question i have , like i asked from @lengyelg : the way i see it the subtracting happens in database trigger after each insert not in the code , so since 2 insertions in the same table cant happen at the same time ( actually im just guessing this !! you know becuz of auto increment id thing there should be some sort of que for insertion also im guessing they would be writing in the single phisical file ) 2 triggers cant happen at the same time – hretic Oct 17 '16 at 01:35
  • I am trying to finish up another answer and my mom comes over with food and is babbling nonsense so I can't concentrate too well. Will get back to you in a little bit. – Drew Oct 17 '16 at 01:39
  • thanx , its 5 am here and im about to sleep anyway so ... about the second question i just realized the trigger happens before insertion so its irrelevant but im still curious if what i said is true – hretic Oct 17 '16 at 01:45
  • Two triggers could theoretically *begin* near or at the same time but the point of showing you the link to the Oracle Sequences link thing that I wrote is to show you the Intention Lock. It would forbid any two threads on the server to get *INSIDE* (at the same time) to the protected Line3/Line4 *Area*. In the stored proc that *Area* is a single update statement. But for you it could be a series of calls but make them as snappy as possible. Snappy out of courtesy to the UX experience of your users. – Drew Oct 17 '16 at 01:55
  • Even if it (your code) is not snappy fast, you are still protected. But focus on doing only what is necessary for high concurrency and don't screw around in that area with calls that are not germane to getting the Lock work done. So your fears are put to rest if done right knowing that if MySQL could not handle what you are talking about, then it has never had any business being in the high concurrency transactional oriented RDBMS pool of contenders in which it competes. – Drew Oct 17 '16 at 01:59
1

This is called a race condition, and the code in the question is indeed subject to that. Interestingly though, I think if nothing else happens in the triggers, it is not an exploitable vulnerability, you may be erring on the safe side only (it is possible that a valid withdraw results in an error, but I think it is not possible that an invalid withdraw does not result in an error).

You should still not have it this way, the race condition should be removed by for example adding locks (at the cost of some performance).

Gabor Lengyel
  • 14,129
  • 4
  • 32
  • 59
  • thanx , i didnt know its name ... the way i see it the subtracting happens in database trigger after each insert not in the code , so since 2 insertions in the same table cant happen at the same time ( actually im just guessing this !! you know becuz of auto increment id there should be some sort of que for insertion ) 2 triggers cant happen at the same time – hretic Oct 17 '16 at 01:16