MySQL - INSERT validation in query

Question

Take the following scenario:

Item cost = 30 User money = 25

Form won't submit if user doesn't have enough money.

$error = false;
if($user_money < $item_cost){
 //don't submit form
 $error = true;
}

But is that enough? Can a user get around it and purchase the item even if there isn't enough money?

Would it be better to do something like this:

Keep the above:

$error = false;
if($user_money < $item_cost){
 //don t submit form
 $error = true;
}else{
 $myclass->purchaseItem($item_id, $user_id);
}

public function purchaseItem($item_id, $user_id) {
 //do the validation here again something like. I don t know how to do the query exactly.
 $q = $this->db->mysqli->prepare("INSERT INTO buys (bl bla blah) VALUES (?,?,?) IF ... user has enough points in user_points table");
}

Hope that makes sense and I don't get down voted.

sure. $99 item, $100 in the bank - should go through. so they place a gazillion orders for 1 each $99 item simultaneously. at least SOME of those orders will get the same "$100 balance" data, allow the transaction through, and now you've got a kajillion * $99 hole in your bank account. — Marc B, Jul 07 '15 at 17:13
wow ... I knew it. So how would I protect against something like that? — Ciprian, Jul 07 '15 at 17:14
all validations should be in the back, never trust user input — Eric, Jul 07 '15 at 17:15
transactions, record locking, blah blah blah. as soon you pull 'critical' data from the db into your client, you have to LOCK the db record so no one else can mess with the records while you're doing your critical work. — Marc B, Jul 07 '15 at 17:15
I'm not trusting user input. The user_money isn't provided by the user :) — Ciprian, Jul 07 '15 at 17:15
A modern [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) will give you a database Object Relationship Mapper (ORM) that makes implementing this sort of thing considerably easier. Worth checking out if you're committed to developing a full application, **especially** if it involves money. — tadman, Jul 07 '15 at 17:26
A little late for that. But I ll try to implement this to my app. — Ciprian, Jul 07 '15 at 17:37

score 0 · Answer 1 · answered Jul 07 '15 at 17:24

0

Validating data on server shouldn't be made twice. Validating the data on the php side would be easier and as reliable as on your database server. For more information on validating input data you can check this.

answered Jul 07 '15 at 17:24

Yann Thibodeau

1,111
1
12
20

score 0 · Accepted Answer · edited May 23 '17 at 11:51

In your database you can use a trigger to check the constraint. Depending on you model you might need a transaction to prevent a record from being inserted incorrectly.

Assuming the following:

Two tables:

buys
wallet

If a user buys something (definite BUY, so not a shopping cart placement action), the wallet is updated in the same action.

To do this you can either write a transaction: See How to start and end transaction in mysqli? on how to.

and use 2 statements:

 UPDATE wallet SET amount=amount-{buyAmount} WHERE user=?;
 INSERT INTO buys (amount,user,orderId) VALUES (?,?,?);

(Of course buyAmount is also a ? in the prepared statement)

Or you can use a trigger. The trigger has to lock the user record when inserting in the buys table:

CREATE TRIGGER updateWallet() BEFORE INSERT ON buys
BEGIN
  SET @updatedWalletAmount=0;
  SELECT amount-NEW.buyAmount FROM wallet WHERE user=NEW.user FOR UPDATE;
  IF(@updatedWalletAmount>0) THEN
    UPDATE wallet SET amount=@updatedWalletAmount;
  ELSE
     SIGNAL SQLSTATE 'ERR0R'
       SET
         MESSAGE_TEXT = 'Not enough money',
         MYSQL_ERRNO = 'USER-1';
  END;
END;

The error will have to be caught in php.

MySQL - INSERT validation in query

2 Answers2