Select Page

A Relational Database Table To Prevent Double Form-Submissions In ColdFusion

Published: June 10, 2022

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`)

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.

	// 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 {
						token = <cfqueryparam value="#form.submissionToken#" sqltype="varchar" />,
						expiresAt = ( UTC_TIMESTAMP() + INTERVAL 1 HOUR )
						message = <cfqueryparam value="#form.message#" sqltype="longvarchar" />
			} // 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." );
			echo( "Sorry, an unexpected error occurred." );
<!--- Reset the output buffer and render the page. --->
<cfcontent type="text/html; charset=utf-8" />
	<!doctype html>
	<html lang="en">
		<meta charset="utf-8" />
		<title>Send a message</title>
			Send a Message
		<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

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:

Double form-submission being prevented by the unique token index in the database.

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.