Preventing duplicate form submissions 2

[cmayo]

I'm working on an existing project for a client and the client has asked me to prevent the system from allowing duplicate form submissions, usually produced by users backing through the system and okaying the re-post warnings, or by reloading post-submission landing pages.

The project is a custom CRM system, pretty involved, and the thought of somehow tokenizing and/or recoding several hundred MySQL queries doesn't really sound all that appealing (though the billable hours wouldn't hurt).

Anyone have any general ideas how I might approach the issue from a centralized standpoint? The system is PHP 5/MySQL 5 and uses the MDB2 database abstraction layer, and there are already some common includes where I can access the workflow from a single point.

Any ideas appreciated.

 

[jpadie]

absolutely. I use a nonce system to authorise each database update. I have posted the nonce code in this forum here and, if i say so myself, it's pretty foolproof for your use and for things like login protection.

usage would be something like this

$nonce = new nonce();
//insert form code
echo $nonce->insertNonceField(); //within the form tags

and to check that the nonce is valid and the db update can be processed

$nonce = new nonce();
if ($nonce->checkNonce()){
 //process the form
}

when i have more complex applications I add an identifier to the nonce to limit the authorised action to a specific action-type. But mostly this is not needed.

 

[cmayo]

jpadie, thanks for the response. Your code is definitely going into play if I have to touch all the forms and submission processes, but I'm really hoping to find a method where I don't have to do that.

Ideally, I'd like to just tell MySQL not to perform duplicate insert statements within a certain timespan, or have MDB2 require confirmation on duplicate inserts... something like that, where I can use a single piece of code to act as a sort of centralized traffic cop.

 

[Ruggie]

It depends on how your using the database, if you have a unique field or key field that you are populating (not auto-increment) you can run a SELECT before the INSERT to determine if the record is already there, and if it is, then maybe the user is trying to change something, so switch the INSERT to an UPDATE.

It really depends on how the database is designed and how its being used.

jpadie's number once class would work great if you don't have any unique identifiers that your inserting. But if you do, simply checking for a pre-existing record and if found running an update instead of an insert would be a little more user friendly.

With jpadie's method you would have to abondon the second request and maybe display an error to the user.
Typically what I've found is someone will type something, click submit, then decide they entered something wrong and want to fix it, hit the back button, make their changes then click submit again.

 

[jpadie]

With jpadie's method you would have to abondon the second request and maybe display an error to the user.
Typically what I've found is someone will type something, click submit, then decide they entered something wrong and want to fix it, hit the back button, make their changes then click submit again.

not really: i would typically make the incoming form sticky and redisplay with whatever message you wanted.

@cmayo
following Ruggle's comments, I agree that if you are creating your own primary keys (using a random number generator) then you can probably control things adequately through programmatic intervention.

if you're like the rest of us and do not do this, then you're a bit stuck. potential workarounds are these:

1. assuming you use an abstraction layer (you mention MDB2), edit the code and log each data manipulation query that is made to a separate table or text file, along with a timestamp. then edit again and interrupt the update and insert methods to check first whether the identical query was run in the past [x] minutes. if it is, then ignore it or do whatever. Given the abstraction layer, this seems best to me.
2. deliver a wee bit of javascript with every page (have you a common menu script for example). use this js to add the extra form field as I advised earlier. it's only 4 or 5 lines of code and then you don't have to retrofit. you can add the checknonce method into the abstraction layer.
3. write a php script to parse all your directories within the webroot for form tags and insert the php code therein. again, only a few lines of code. Dreamweaver/Aptana and others will do this for you semi-automatically.
4. store each data manipulation query in a session variable. then test each newly assembled data manipulation query against the session values. this can be done in the abstraction layer again so you have to do this only once.

there are probably other variations on a theme. however, a cms is totally reliant on data integrity. i would bite the bullet and manually fix each of my scripts with nonces. However I would also unfailingly add a query logging feature into the mdb2 abstraction layer. if you do this into a text file then you end up with a perfect database backup created on the fly for you, and a very easy way to revert the database to a particular point in time, which can be very valuable if you suffer a corruption event or are hacked/whatever. I typically mirror my mysql data manipulation queries to a simple sqlite database table with three cols: id, timestamp and the query.

 

[cmayo]

Jpadie, thanks again; you truly rock.

Most of these suggestions are on a level of thinking somewhat more advanced than I'm accustomed to so I'll have to take some time and really think these ideas through, but they do seem to be within reach.

MDB2 was my first thought, but I just don't have to chops to extend it without help, and MDB2 help seems rather hard to come by. Well... perhaps "help" isn't the right word to use there, I just need more hand-holding than I can find to implement the suggestions I've been given.

Maybe you're right about biting the bullet and modifying all the forms to use your nonce method. Again, I don't have the chops to extend MDB2 but modifying the forms won't be an issue... once I get a working model, it's pretty much just gruntwork from there.

I've looked at MDB2 query logging and it seems doable, but I'm also messing around with MySQL's binlog which is only logging queries which affect data, i.e. insert/update/delete, and from which a database can also be rebuilt or rolled back to specific points.

Let me take all these great suggestions and see what I can figure out, at which time I'll probably have to return to the thread and beg for a bit more help.

 

[jpadie]

we're happy to help with mdb2 questions here. no trouble at all.

i did toy with adding querying of the binlog to my list above. I decided against it for reasons of simplicity.

actually of all the above, my fave is the js fix coupled with some abstraction layer mods. path of least resistance.

anyway, post back with any mdb2 q's you have.

 

[cmayo]

I really appreciate that. I'll revive this thread when I start coding on the feature.

Thanks again,
Chuck

 

[Ruggie]

jpadie, I never thought about logging all sql commands to a file or another database, that is such a simple idea and a great one!

Just wanted to say thanks for mentioning it.

 

[jpadie]

you're welcome! of course, not a great idea if you're storing blobs in the database. but then doing so is not a great idea either!

i use PDO for all my database needs and it's easy to extend the PDO class to interrupt the prepare, exec etc methods.