A Relational Database Table To Prevent Double Form-Submissions In ColdFusion
The other day on Episode 78 of the Working Code podcast, Adam Tuttle and I were talking about handling accidental, double form-submissions when there is no "natural key" in the database on which to attach a unique index. Adam suggested submitting a GUID (Globally Unique ID) along with the form POST and then storing that GUID in the target table. I think this is definitely the right direction; but, I didn't like the idea of storing a value in the table simply for the sake of uniqueness. And then it occurred to me: can I have a separate database table that does nothing but prevent double form-submissions in ColdFusion?
Normally, when considering database index design, I try to find ways to create natural, unique indexes that allow for idempotent workflows in my ColdFusion applications. Of course, I always want my business logic to explicitly handle uniqueness checks for the application. But, whenever possible, I also want to lean on the power of my database to enforce those uniqueness constraints as a fail-safe.
Based on the submission data, however, this isn't always possible. Sometimes, there's nothing inherent to the data itself that makes it unique. And, in such cases, an accidental, double form-submission could lead to double entry within the various database tables.
In some scenarios, a double form-submissions isn't so bad; and, it might be something that can be fixed by a data-reconciliation background process. In other scenarios, however, it's a critical failure and must be avoided.
For these latter scenarios, imagine a relational database table that does nothing but track temporary unique form submission tokens:
CREATE TABLE `double_submission_token` (
`token` varchar(50) NOT NULL,
`expiresAt` datetime NOT NULL,
PRIMARY KEY (`token`),
KEY `IX_byExpiration` (`expiresAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
As you can see, there is a PRIMARY KEY unique index on the token column. This means that any attempt to insert the same token twice - such as with a double form-submission - will lead to a Duplicate entry error. In theory, if we were to INSERT INTO this table as the first step in a mission critical workflow, it would prevent accidental, double form-submissions. Yes, it would lead to an error on the second request; but, an error is preferable to dirty data.
Since these tokens don't actually have any long-lasting value for the domain model, I'm also including an expiresAt column. This column can be used to clean-up tokens that are no longer serving a purpose.
Ok, let's see how I might use this in a ColdFusion form submission. In the following ColdFusion page, I'm attempting to submit a "message" to the server. When the form renders for the first time, I'm generating a UUID to be submitted along with the POST back to the server. This UUID will be the token that we store in the aforementioned database table.
<cfscript>
// Set up default form values.
// --
// NOTE: We're generating a unique token for this form instance. This value will only
// be generated once per form since the post-back will also include the submission
// token thereby avoiding the subsequent CFParam default call to createUuid().
param name="form.submitted" type="boolean" default=false;
param name="form.submissionToken" type="string" default=createUuid();
param name="form.message" type="string" default="";
if ( form.submitted ) {
try {
// By wrapping the processing in a CFTransaction tag, it creates an atomic
// boundary around both "INSERT INTO" queries. This means that if something
// goes wrong with the message insert (the second query), the token insert
// (the first query) will naturally rollback allowing for the form to be
// re-submitted without issue. And, of course, since the submission token has
// a unique index on it (primary key), any accidental double-submission will
// cause a "duplicate entry" error on the first insert, thereby preventing the
// second query from ever executing.
transaction {
```
<cfquery>
INSERT INTO
double_submission_token
SET
token = <cfqueryparam value="#form.submissionToken#" sqltype="varchar" />,
expiresAt = ( UTC_TIMESTAMP() + INTERVAL 1 HOUR )
</cfquery>
<cfquery>
INSERT INTO
ben_message
SET
message = <cfqueryparam value="#form.message#" sqltype="longvarchar" />
</cfquery>
```
} // END: Transaction.
location( url = "./success.cfm", addToken = false );
} catch ( any error ) {
if ( error.message contains "Duplicate entry" ) {
echo( "Oops, it looks like your message is already being processed." );
abort;
}
echo( "Sorry, an unexpected error occurred." );
abort;
}
}
</cfscript>
<!--- Reset the output buffer and render the page. --->
<cfcontent type="text/html; charset=utf-8" />
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Send a message</title>
</head>
<body>
<h1>
Send a Message
</h1>
<form method="post" action="#cgi.script_name#">
<!--- Posting our double-submission form token back to server. --->
<input type="hidden" name="submitted" value="true" />
<input type="hidden" name="submissionToken" value="#encodeForHtmlAttribute( form.submissionToken )#" />
<textarea name="message">#encodeForHtml( form.message )#</textarea><br />
<button type="submit">
Send message
</button>
</form>
</body>
</html>
</cfoutput>
As you can see, in my form processing, I am executing two queries: one for the form-submission token and one for the message. These two CFQuery tags are being wrapped in a CFTransaction tag so that they either commit or rollback atomically (that is, they either all work or all fail together). It's the atomic nature of the relational database that makes this so elegant: if something were to go wrong with the second INSERT, the token INSERT would also rollback thereby allowing the form to be re-submitted by the user.
Now, if I load this ColdFusion page and enable network throttling in my Chrome dev-tools, we can see how the control-flow handles the double form-submission:
As you can see, in the second attempt to submit the form, I'm double-clicking the submit button. We can see in the network activity that the browser automatically cancels the first network request and then initiates a second network request. Of course, just because the browser cancels the request, it doesn't mean that the request itself was aborted - once it hits the server, it's out of our hands - ColdFusion will continue to process it.
As such, when the second network request hits the ColdFusion server, it attempts to INSERT the same submissionToken value. However, since the same token was inserted by the first network request, the second transaction fails with a "Duplicate key" error. Our accidental, double form-submission was stopped in its tracks! And, no unnecessary data was stored in the "message" table.
I really like this approach because it seems like it has a nice separation of concerns. The "message table" doesn't have to have an artificial unique token, so we're not muddying-up the domain model. But, we still have an artificial unique token - it's just isolated within its own, single-responsibility table. And, furthermore, we can use this same approach with any other table that needs help preventing accidental, double form-submissions in our ColdFusion application.
Want to use code from this post? Check out the license.
Reader Comments
This is called a "nonce":
https://www.techtarget.com/searchsecurity/definition/nonce
@Dan,
The only caveat that I would add to that is that, in this case, the form token can be submitted more than once as long as the form doesn't get processed successfully. Meaning, the "single use" characteristic is only fully applied if the form is submitted without error.
I add this caveat only because there "nonce" implementation in which checking to see if the value is valid implicitly invalidates it for subsequent use. For example, we have a Redis powered one-time token service at work where the act of getting the token does this:
So, you can never even check to see if the token is valid without also consuming it. Same concept, but just slightly different constraints.
This is also not far from CSRF protection which, if I'm not mistaken, usually uses the user's session for storage, which would negate the need for the table. It doesn't play nice with multiple-tabs scenarios where the user might be bouncing between 3 different tabs, but as long as that's not a requirement, using a CSRF token approach (store it in the session when created at page render, and validate it against session and delete it at submit) might work, too.
@Adam,
I think if I were to use the CSRF token (Cross-Site Request Forgery token for other readers) I would have to cycle the token after I used it. Normally, I allows the CSRF token to remain static, and then cycle it after login and other critical actions like changing the password. But, in order to prevent double-submission while not preventing subsequent submission, I'd have to cycle it after it gets used. But, then I might accidentally do that and allow for double-submission 🤪 It's such a tricky little use-case.
I've always deactivated the submit button once it's clicked to prevent the second click.
@Chris,
That's more-or-less the approach I am taking today. Inside my submit handler, it looks like this(ish):
So, I guess I'm not technically disabling the submit button; but, I am making it inactionable. Would be better for me to (additionally) change the UI of the button as well.
@Chris, that's a fantastic idea! 🤓
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →