How to prevent fraud in credit system?

Question

Supposing our accounts table has a column called balance, each transaction is logged in the transactions table. Of course, we should verify there is sufficient funds prior to doing any transactions to sell products. Therefore, for performance purposes we should check the balance column of the user, deduct the amount on a successful sale, and update his balance.

However, if the user asynchronously bought 2 products, could not that potentially cause fraud? I've wrote a script that will deduct funds from an account and cloned it to another file. I executed both scripts at the same time and the results were surprising.

Deduct.php

<?php
//database connection...
$amount = 11;
$deducted = 0;
$blocked = 0;

for($i = 0; $i < 5000; $i++){
    $sql = $dbh->prepare('SELECT balance FROM accounts WHERE id = ?');
    $sql->execute(array(1));
    while($u = $sql->fetch()){
        $balance = $u['balance'];
        $deduct = $balance - $amount;
        if($deduct >= 0){
            $sql2 = $dbh->prepare('UPDATE accounts SET balance = ? WHERE id = ?');
            $sql2->execute(array($deduct,1));
            echo $balance . ' -> ' . $deduct . "\n";
            $deducted += $amount;
        } else {
            $blocked++;
        }
    }
}

echo 'Deducted: '.$deducted. "\n";
echo 'Blocked: '.$blocked;

Before running the scripts my balance was 1000000. I've executed two processes of this script with different $amount values.

Here are the results:

As you can see both scripts deducted a total of 125000 and my balance is 879778.00 which is a proof of fraud

• Any alternative solutions to overcome this? I understand that logging each transaction and calculating the final balance is precise but is also intensive.

Answer 1

If this is a school homework problem and you just want your script to work, do this:

$sql2 = $dbh->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
$sql2->execute(array($amount,1));

If you are doing anything real, you should be recording all the individual transactions. To speed things up, you can sum the transactions each night and update the account balance, then create a view which includes a query like this to get the current balance:

create or replace view current_accounts as
select a.id as account_id
, max(a.balance) + ifnull(sum(t.amount), 0) as current_balance
from accounts a
left join transactions t on t.account_id = a.id and t.transaction_at > a.updated_at
group by a.id

When inserting each transaction, if you require that the balance never goes negative:

insert into transactions (transaction_at, amount, account_id)
select now(), ?, v.account_id
from current_accounts v
where v.account_id = ?
and v.current_balance + ? >= 0

When binding this, make sure that the amount being deducted is negative if you are withdrawing money and positive if you are depositing money into the account. You will need an index on transactions.transaction_date and accounts.updated_at for this to have any speed advantage.

The nightly update should look like:

drop table accounts_old if exists;
create table accounts_new as
select t.account_id as id
, sum(t.amount) as balance
, max(t.transaction_at) as updated_at
from transactions t
group by t.account_id;
rename table accounts to accounts_old;
rename table accounts_new to accounts;

Also, the primary key in the accounts table should be called account_id and you should have a primary key in the transactions table called transaction_id. Resist the convention to name anything "id" because it will confuse you eventually.

Answer 2

Why would you fetch the balance in one query and then set it in another? Just use:

UPDATE accounts
    SET balance = balance - ?
    WHERE id = ?;

Do the arithmetic in the database.

Answer 3

What you're looking for is a database that allows transactions: in other words, either everything inside a transaction works, or it's rolled back, as if it never happened. This is possible with MySQL InnoDB tables (but NOT with MyISAM tables, you may need to convert them).

Depending on the operations you are trying to do, you have various locking options available - e.g. "while I'm working on this row, everyone else trying to read or write has to wait until I'm done" or "everyone has to wait for writing, but is able to read", etc.

See e.g. this for an example: http://coders-view.blogspot.com/2012/03/how-to-use-mysql-transactions-with-php.html

You may also want to check constraints (e.g. avoid debiting more than the account contains); in MySQL, you can use triggers for that purpose: Can a MySQL trigger simulate a CHECK constraint?