Hack 68. Cope with Unexpected Redo

When a user initiates an operation you can ensure that your code copes well with repeated attempts.

Suppose you are writing user account creation routines for a web application. A user can create an account on your system, where the username is his preferred email address. As part of the process, the user will be given a random password if he was successful, or will be shown an error message if appropriate. The form uses USER for username and PASS for password. The username column is the primary key of usertable:

CREATE TABLE usertable (
 username varchar(20) primary key,
 password varchar(20)
);

Here is the PHP to provide a user creation form:

username','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));
if (! array_key_exists('_submit_check', $_POST)) {
 print '

'; print ''; print ''; print ''; print '

'; } else { $sql = sprintf("SELECT username FROM usertable WHERE username = '%s%%'", mysql_real_escape_string($_POST['username'])); $cursor = mysql_query($sql) or die(mysql_error( )); $found = 0; while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ $found = 1; } if ($found == 1) { print '

That username is already in use

'; } else { $pass = randomPass( ); $sql = sprintf("INSERT INTO usertable VALUES('%s%%','%s')", mysql_real_escape_string($_POST['username']),$pass); $cursor = mysql_query($sql) or die(mysql_error( )); print '

Your account has been created, password '.$pass.'

'; } } ?>

This user creation script is nonrepeatable. This means that running the code with the same input twice results in two different messages. Often this type of approach is accompanied by a warning that appears next to the Submit button: "Click this button only once!" Such warnings are common in "shopping-basket" type sites.

If the user does click the button twice, he receives an error message stating his email address is already being used as an account name. He can't log in using that email address, as the random password was not shown to him. By using a repeatable approach you can make the system much more user-friendly.

One way to make the query repeatable is to put a hidden field, random, in your form. When the form is generated set the hidden field to a random number. Now change the code so that multiple submissions with that value for random involving the same username always appear to succeed. You need to store the random value in usertable, in this case in a column called ccode:

ALTER TABLE usertable ADD ccode INT;

The new PHP code looks like this:

user','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));
if (! array_key_exists('_submit_check', $_POST)) {
 print '

'; print ''; print ''; print ''; print ''; print '

'; } else { $sql = sprintf("SELECT username,password,ccode FROM usertable WHERE username = '%s'", mysql_real_escape_string($_POST['username'])); $cursor = mysql_query($sql) or die(mysql_error( )); $found = 0; $pass = randomPass( ); while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ $found = 1; if ($line{ccode} == $_POST['random']) { $found = 2; $pass=$line{password}; } } if ($found == 1) { print '

That username is already in use

'; } elseif ($found == 2) { print '

Your pressed the submit button more than once. But thats ok!

'; print '

Your account has been created, password '.$pass.'

'; } elseif ($found == 0) { if (!preg_match("/^d+$/", $_POST['random'])){ print "SQL Injection Attack Alert."; exit(1); } $sql = sprintf("INSERT INTO usertable VALUES('%s','%s',%d)", mysql_real_escape_string($_POST['username']),$pass ,$_POST['random']); $cursor = mysql_query($sql) or die(mysql_error( )); print '

Your account has been created, password '.$pass.'

'; } } ?>

The new code allows you to click the Submit Query button as many times as you want, and the system will always seem to work right. Figure 9-2, Figure 9-3, and Figure 9-4 show examples of it in operation.

Figure 9-2. Registering using your email address

Figure 9-3. The first time Submit Query is clicked

Figure 9-4. Second and subsequent submit attempts

The check using the random code is an essential part of this algorithm. If you don't check this, anyone can come along at any point in the future and register with an email address that is already in use, and have the current password of that user displayed.

9.5.1. Shopping Baskets

You can extend this algorithm to shopping-basket processing with only a slight increase in complexity. The important thing to do is to ensure that you have an atomic test-and-set before dealing with the basket, and to store in the database the current state of the purchase process. With this, the first transaction to pass the test-and-set can handle the purchases, and all other repeated transactions display just the current state. For isolation levels such as REPEATABLE READ, READ COMMITTED, and SERIALIZABLE, testing an INSERT transaction should provide you with an atomic test-and-set, providing there is a primary key on the table involved.

The first transaction to start (i.e., the one triggered by your first Submit Query button click) is as follows:

mysql> INSERT INTO purchase (basketid,state) VALUES ('00001','Processing'); 
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT; 
Query OK, 0 rows affected (0.00 sec)

 Your program processes order, checks availability, works out costs 

mysql> UPDATE purchase SET state = 'Checking your credit card' 
 -> WHERE basketid='00001'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> COMMIT; 
Query OK, 0 rows affected (0.00 sec)

 Your program asks VISA if the card transaction was successfully completed. 

mysql> UPDATE purchase SET state = 'Order complete. Items dispatched.' 
 -> WHERE basketid='00001'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> COMMIT; 
Query OK, 0 rows affected (0.00 sec)

The next transaction to get this far starts the same way, but it finds that the INSERT fails with an error. This means that the script changes to displaying the current order state. The user, or perhaps JavaScript, can refresh the web page to keep the user informed of the current basket-processing state:

mysql> INSERT INTO purchase (basketid,state) VALUES ('00001','Processing'); 
ERROR 1062: Duplicate entry '00001' for key 1
mysql> COMMIT; 
Query OK, 0 rows affected (0.00 sec)
 With the error in the INSERT you know that another transaction is dealing 
 with this. 
mysql> SELECT state FROM purchase WHERE basketid='00001'; 
+----------------------+
| state |
+----------------------+
| Checking your credit |
+----------------------+
1 row in set (0.00 sec)