Am I using MySQL transactions correctly?

Question

I would like to confirm that I am using MySQL transactions correctly to handle something critical correctly (no race errors, etc)

$mysqli->autocommit(FALSE);
$mysqli->query("UPDATE users SET balance=balance-$amount, transactions=transactions+1, sent=sent+$amount WHERE email='$email'");
$mysqli->query("UPDATE users SET balance=balance+$amount, transactions=transactions+1, recv=recv+$amount WHERE email='$address'");
$newBalanceQ = $mysqli->query("SELECT balance FROM users WHERE email='$email'");
$newBalance = $newBalanceQ->fetch_row()[0];
if($newBalance < 0){
    $mysqli->rollback();
} else {
    $mysqli->commit();
}

score 2 · Accepted Answer · answered Jun 03 '13 at 01:16

Alternatively, you can do it without a transaction because you can combine both queries into one UPDATE statement,

UPDATE  users 
SET     balance = balance - (CASE WHEN email = '$email' THEN $amount ELSE $amount * -1 END), 
        transactions = transactions + 1,
        sent = (CASE WHEN email = '$email' THEN sent + $amount ELSE sent END),
        recv = (CASE WHEN email = '$address' THEN recv + $amount ELSE recv END)
WHERE   email IN ('$email','$address')

You are using MySQLi but you are not parameterizing the value and in this case you are still vulnerable with SQL Injection.

Am I using MySQL transactions correctly?

1 Answers1