CODEWITHSUNDEEP
phpuser-input
Ben McRae
Ben McRae

Reputation: 3531

Stop data inserting into a database twice

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.

Upvotes: 26

Views: 27806

Answers (16)

kumar
kumar

Reputation:

The best way to avoid duplicate record insertion on page refresh is, after inserting records in the database on button click, just add this line:

Response.Write("<script>location.href='yourpage.aspx'</script>");

Upvotes: 0

VeRJiL
VeRJiL

Reputation: 564

You have to pass an uniqid variable into your html inside the showAddProductForm() method and the same uniqid into your $_SESSION forexample:

public function showAddProductForm()
{
    $uniId = uniqid();
    $_SESSION['token'][$uniId] = '1';
    $fields['token'] = 'token['.$uniId.']';

    $this->fileName = 'product.add.form.php';
    $this->template($fields);
    die();
}

Then you have to put a hidden input in HTML code inside your form with the value of the uniqid that has been passed into the HTML from showAddProductForm method.

<input type="hidden" name="<?=$list['token']?>" value="1">

right after the submit event you will analyze it in the beginning of the addProduct() method. If the token exists in the $_SESSION and it has an equal value inside that array, then this is the new reques. Redirect him to the proper page and unset the token and continue insert. Else it is from the reloading page or a repetitive request, redirect him to addProdeuct page

public function addProducts($fields)
{
    $token_list = array_keys($fields['token']);
    $token = $token_list['0'];
    if (isset($_SESSION['token'][$token]) and $_SESSION['token'][$token] == '1') {
        unset($_SESSION['token'][$token]);
    } else {
        $this->addAnnounceForm($fields, '');
    }
}

Maybe you ask why an array of tokens why not a single variable. Because in admin Panel users open multi tabs and inserting in multi tabs so this algorithm will fail if they use multi tabs.

Special thanks to who figure out this method malekloo

Upvotes: 1

Keith Gaughan
Keith Gaughan

Reputation: 22675

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.

Upvotes: 0

KevBurnsJr
KevBurnsJr

Reputation: 4878

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?).

Upvotes: 0

James Socol
James Socol

Reputation: 1795

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.

Upvotes: 6

Carlo
Carlo

Reputation: 2112

I usually rely on the sql UNIQUE index Constraint. http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

Upvotes: 1

vyger
vyger

Reputation:

My two cents:

  • Redirect the user to the same page after sending data and verify if(isset($_POST['submit'])

Other useful information for similar cases:

Upvotes: 0

TravisO
TravisO

Reputation: 9540

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.

Upvotes: 2

jmoz
jmoz

Reputation: 8006

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

Upvotes: 2

jeroen
jeroen

Reputation: 91734

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 ...

Upvotes: 4

Martin K.
Martin K.

Reputation: 4703

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
}

..

Upvotes: 0

Ilya Birman
Ilya Birman

Reputation: 10072

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.

Upvotes: 41

Patrick Glandien
Patrick Glandien

Reputation: 7851

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.

Upvotes: 0

Lazarus
Lazarus

Reputation: 43074

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.

Upvotes: 2

Kibbee
Kibbee

Reputation: 66122

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.

Upvotes: 0

chaos
chaos

Reputation: 124297

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

Upvotes: 7

Related Questions