Stop data inserting into a database twice

Question

Im quite new to PHP, i was wondering what methods/preventions other programmers use to stop data being entered twice into a MySQL database when a user refreshes on the same page as a form? Obviouly it happens and i need a good way to stop this.

Thanks, Ben

Answer 1

To state the obvious (I have not seen it here yet...): Never use GET to post data, always use POST, that way the user at least gets a warning if he or she tries to refresh /re-post the page (at least in Firefox, but I suppose in other browsers as well).

By the way, if you cannot afford to have the same data twice, you should also consider a MySQL solution with a unique key (can be a combination of fields) and:

    INSERT INTO ... ON DUPLICATE KEY UPDATE ...

Answer 2

I'd agree with Ilya there and add that you should use some client javascript to disable the 'submit' button once it's been clicked or present a modal dialog (css can help you here) to avoid multiple clicks on the submit button.

Lastly, if you don't want the data in your database twice then also check your database for the data before you try to insert it. If you do allow duplicate records but don't want rapid repeat insertions from a single source then I'd use a time/date stamp and IP address fields to allow for a time-based 'lockout' in my submission code, i.e. if the IP is the same and the last submission time was less than 5 minutes ago then don't insert the new record.

Hope that gives you some ideas.

Answer 3

In addition to the good suggestions already mentioned about taking the user away from the posting page so a refresh and back button are harmless, another layer in improving your data storage is to use UUIDs as keys in your table and let your applications generate them.

These are also known as GUIDs in the Microsoft world and in PHP you can generate one via uniqid() in PHP. This is a 32 character hex value which you should store in a hex/binary column format but if the table isn't going to be heavily used than CHAR(32) will work.

Generate this ID when you display your form as a hidden input, and make sure to mark the database column is marked as the primary key. Now if the user does manage to go all the way back to a posting page, the INSERT will fail because you can't have duplicate keys.

An extra bonus to this is, if you generate the UUID in code, then after you perform an insert you'll never need to use wasteful queries retrieving the key that was generated because you'll already know it. This is a nice benefit when you need to INSERT child items into other tables.

Good programming is based upon layering your work, not relying on 1 thing to work. Despite how common it is for coders to rely on incremental IDs, they are one of the laziest ways to build a table.

Answer 4

Well, first of all, to minimize, you should make it so they have to do form post to insert the data. That way, at least they will get that nice little confirmation dialog asking if they really want to resubmit it.

To get more complicated than that, you could put a hidden one time use key in each form, and once the form with that key has been submitted, display an error when they try to submit a form with the same key. To create this key, you probably want to use something like a GUID.

Answer 5

Process the form, then redirect to the results page. Reload then only redisplays the results page.

Answer 6

I call this a golden rule of web programming:

Never ever respond with a body to a POST-request. Always do the work, and then respond with a Location: header to redirect to the updated page so that browser requests it with GET.

This way, refreshing will not do you any harm.

Also, regarding a discussion here in comments. To protect from double posting from, say, accidental doubleclicking the Submit button, store an md5() of your form in a text file, and compare the new form’s md5 to the stored one. If they are equal, you are having a double post.