Preventing Silent Database Value Truncation In ColdFusion

Databases are truly magical. Not only do they allow us to persist data across requests and server restarts, the sheer volume of operations that they execute is mind-boggling. They are the true work-horses that drive my ColdFusion applications. Which is why I felt so betrayed, some years ago, when data was getting silently truncated in one of my blog authoring workflows. I've long since fixed the issue; but I've only just now taken it upon myself to add logic in my Application.cfc that performs boot-time assertions about the state of my datasource and the underlying MySQL database in order to prevent this from happening again.

After so many years, I don't remember all of the details. I believe what happened was that my original INSERT worked fine. But then, when I went to edit a post, the SELECT was quietly truncating the content, which I didn't notice. Then, the subsequent UPDATE received the SELECT-truncated value in the form post and saved it back into the database in its truncated state.

Truncation in a ColdFusion application can happen in several places, each of which is controlled by a different mechanism:

• When writing to the database. This is controlled by the MySQL server (and connection) settings.

• When reading from the database. This is controlled by the Adobe ColdFusion datasource settings.

• When submitted a form to the server. This is controlled by the maxlength form input attributes.

Prior to MySQL 5.6, the default behavior when writing too-large-a-value to a field was to silently truncate the value and log a warning. My issue almost certainly occurred prior to 5.6. But, since I was storing the blog post in a TEXT field, I don't think that was the issue.

Instead, what I think was happening is that my ColdFusion datasource had "CLOB (Character Large Object) Support" capped at a low limit. So when I went to read a long post from the database, the post contents were being silently truncated by the MySQL driver mechanics (on the ColdFusion side).

To be safe, I'm adding a check in my Application.cfc for both the read and the write contexts. For the read context, I'm asserting that my disable_clob and disable_blob datasource settings are set to false. And for the write context, I'm asserting that the MySQL database has "strict mode" enabled (which turns field truncation from a warning into an blocking error).

Aside: If you're using a database other than MySQL, you almost certainly need different considerations. Possibly for both contexts.

I'm performing this check in my onApplicationStart() event handler because it's the first moment that the default datasource can be invoked (assuming that you're using a per-application datasource and not one defined in the ColdFusion administrator).

Here's my heavily truncated Application.cfc ColdFusion component:

component {

	// Setup the default datasource for MySQL.
	this.datasource = "bennadel";
	this.datasources = {
		"#this.datasource#": buildMySqlDatasource(),
	};

	// ---
	// LIFE-CYCLE METHODS.
	// ---

	/**
	* I get called once when the application is being bootstrapped.
	*/
	public void function onApplicationStart() {

		// Make sure that critical datasource settings are in place, otherwise we run the
		// risk of silently losing data in certain workflows.
		assertDatasourceSettings();

	}

	// ---
	// PRIVATE METHODS.
	// ---

	/**
	* I assert that both the database and default datasource are running with the core
	* required setting so as to ensure that data is never truncated on READ or WRITE.
	*/
	private void function assertDatasourceSettings() {

		// Phase 1: validate WRITE mode.
		var results = queryExecute( "SELECT @@SESSION.sql_mode AS sqlMode" );
		var sqlMode = results.sqlMode;

		if (
			! sqlMode.findNoCase( "STRICT_TRANS_TABLES" ) &&
			! sqlMode.findNoCase( "STRICT_ALL_TABLES" )
			) {

			throw(
				type = "Datasource.DangerousWriteMode",
				message = "MySQL isn't running in strict mode.",
				detail = "MySQL will silently truncate written values without strict mode.",
				extendedInfo = "Session Sql Mode: #sqlMode#"
			);

		}

		// Phase 2: validate READ mode.
		var defaultDatasource = this.datasources[ this.datasource ];

		if (
			defaultDatasource.disable_clob ||
			defaultDatasource.disable_blob
			) {

			throw(
				type = "Datasource.DangerousReadMode",
				message = "Datasource has disabled large object support.",
				detail = "The datasource has disabled large object support (disable_clob:true | disable_blob:true) which will silently truncate read values.",
				extendedInfo = "Disable CLOB: #yesNoFormat( defaultDatasource.disable_clob )#, Disable BLOB: #yesNoFormat( defaultDatasource.disable_blob )#"
			);

		}

	}

}

Now, every time my ColdFusion application boots up, I run a quick check to make sure that MySQL's strict mode is enabled and that my ColdFusion datasource has CLOB enabled. It might seem silly to have this in the application code. But it means that when I upgrade the MySQL database, or I move to a different server, or I change my JDBC driver, I have at least some mechanics in place to help prevent slipping back into a dangerous state.

If nothing else, it gives me some peace of mind that if I have to do large-volume data transformations — such as when back-filling my new code syntax highlighter to 20-years worth of posts — I won't be silently truncated value during the transformation.