Within this section, we can add and manage data sources (DSN’s) in ColdFusion. Connecting to databases was one of the first widespread uses of ColdFusion. In fact, the first iterations of what we now know as CFML was called DBML or database markup language. The administration of data sources remains one of the most important features available in the ColdFusion Administrator. Incorrect or inefficient settings here can have a dramatic impact on the usability of your applications. Here we will provide some time-tested guidance on how to configure a highly-available data source for your ColdFusion application.
One important point for MySQL and MariaDB: the JDBC driver for these systems does not ship with ColdFusion. It must be downloaded separately and manually added to your ColdFusion installation. This is accomplished by adding the file to {CF-HOME}/lib.
From the main page of the data source administrator, you can add, edit, validate, and remove data sources.
Here we are looking at the advanced data source settings for an existing local database.
Make sure your data source connection is using an account (username) with just enough permissions for your application to function. Do not use root level accounts for these connections. By running with only minimum required permissions, you may mitigate certain security risks by limiting access to your database.
The Connection String setting allows us to pass values into the JDBC URL without having to create a JDBC driver as “other”. This can be useful for passing custom attributes that change the behavior of the connection. See your data source’s documentation for details on what options are available.
Limit Connections and Restrict Connections are related. These settings will define a ceiling on connections to your database. To enable this feature, check Limit Connections and then enter a number in Restrict connections to. This is unchecked and blank by default. Typically this setting is only applicable to cases where a data source is used for infrequent but expensive queries that could impact the performance of the database.
It is advisable to enable Maintain connections across client requests. This setting will persist the ColdFusion connection to your database, eliminating the authentication and initialization overhead for every request. This may result in better performing queries. You can also specify a duration in minutes that a connection should persist, and how often (interval) the connection should be checked.
We also recommend reviewing the Allowed SQL settings for the data source. By default, they are all enabled. This means your ColdFusion queries have the ability to create new tables, drop existing ones, and delete large swaths of your database. This is probably not what you want. The best practice is to only enable the features your application absolutely needs to function correctly. By limiting SQL functionality in this way, you may reduce the likelihood of a successful SQL Injection attack against your site. You also reduce the possibility of an accidental data operation by a developer.
Blob / Clob
Whether you need Blobs and Clobs in your application depends on your requirements. Only enable these features if you know they will be needed. By default they are disabled.
Validate Connection
We recommend this feature be enabled on your data sources, with a simple query to the database added. The reasoning behind this is best explained by Steven Erat:
When a database connection is first created AND every subsequent time that connection is checked back out from the pool, the validation query will run BEFORE any queries for the page request. If the validation query fails, your page request will never see the error because ColdFusion will throw away that db connection and get another connection from the db connection pool. It will then run the validation query for that connection too. If that one errors, ColdFusion will continue closing the bad connections and checking out other connections until there are no connections left in the connection pool. If it actually got that far (meaning every connection in the pool turned out to be bad) then ColdFusion will then create a NEW db connection and use that one, and it will run the validation query on that too. All of this happens before your request runs to guarantee that your request gets a *good* db connection from the start.
It is important to note that this setting may impart a small performance penalty on your application. We advise testing your system under simulated load conditions prior to enabling this in a production environment.