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
Process the form, then redirect to the results page. Reload then only redisplays the results page.
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.
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.
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.
Add a hidden field with a random string (produced by md5(uniqid()) for example), make a field in the database for that string and make it UNIQUE.
You can use a token to prevent the page from being processed again! Such a procedure is used in a lot web frameworks !
The pattern you should use is the "Synchronizer Token Pattern"! If you have a serviceoriented application, you can save your status in the Database.
The data can be send via JavaScript or by a hidden form field.
You should also have a look at libaries with out of the box support for things like this! Grails is such one!
See: http://www.grails.org/1.1-Beta3+Release+Notes ...
<g:form useToken="true">
...
withForm {
// good request
}.invalidToken {
// bad request
}
..
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 ...
You might want to check out the POST/Redirect/GET pattern most modern web apps implement, see http://en.wikipedia.org/wiki/Post/Redirect/Get
My two cents:
if(isset($_POST['submit'])Other useful information for similar cases:
Give a look to LOCK TABLES in MySQL
Disable buttons via JavaScript after the first click
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.
I usually rely on the sql UNIQUE index Constraint. http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html
Try including something in your forms to prevent double-submission, preferably at the same time protecting against cross-site request forgery. I recommend using what I call a formkey, which is a one-use field that uniquely identifies a form submission, tying it to an individual user at an individual address. The concept goes under other names too, but the short note I've linked to explains it well enough.
Ilya's answer is correct, I just wanted to add a little more than would fit in a comment:
If resubmission is dangerous (going back and submitting again, reloading the result page [if you haven't taken Ilya's advice], etc.) I use a "nonce" to make sure the form can only go through once.
On the form page:
<?php
@session_start(); // make sure there is a session
// store some random string/number
$_SESSION['nonce'] = $nonce = md5('salt'.microtime());
?>
// ... snip ...
<form ... >
<input type="hidden" name="nonce" value="<?php echo $nonce; ?>" />
</form>
In the processing page:
<?php
if (!empty($_POST)) {
@session_start();
// check the nonce
if ($_SESSION['nonce'] != $_POST['nonce']) {
// some error condition
} else {
// clear the session nonce
$_SESSION['nonce'] = null;
}
// continue processing
After the form has been submitted once, it cannot be submitted again, unless the user intentionally fills it out a second time.
POE (Post Once Exactly) is an HTTP pattern aimed at warning the client to block double submits using a proprietary header ...
GET /posts/new HTTP/1.1
POE: 1
...
... but is still in specification.
http://www.mnot.net/drafts/draft-nottingham-http-poe-00.txt
I think the above nonce is a good solution. Though storing the nonce as a discrete session variable will introduce some errors if the client is attempting to perform simultaneous posts from multiple tabs. Maybe better to ...
$_SESSION['nonces'][] = $nonce;
... and ...
if (in_array($_POST['nonce'], $_SESSION['nonces'])) {
... to allow for multiple nonces (nonci? noncei?).