Is it possible to have cfqueryparams sent to MySQL

Question

When using <cfquery> and <cfqueryparam> (or queryExecute), ColdFusion (or perhaps the JDBC) will apply its own string replacement against the original SQL query before sending it to the MySQL server.

This is not as efficient as a prepared statement and has a greater potential for SQL injection if the ColdFusion servers escaping implementation is flawed.

Coldfusion debugging and tracing will make it appear as though the query was processed as a prepared statement but MySQL reveals otherwise.

This can be proven by logging sql queries: set global general_log=1;

And watching the logs, tail -f /var/lib/mysql/$(uname -n).log

The log should show PREPARE statements and question marks in the queries, but instead the log shows the full query with variables substituted into the question marks.

Is it possible to force ColdFusion to use proper PREPARE statements instead of string replacement when <cfquery> and <cfqueryparam> are used against a MySQL database? If it boils down to the JDBC connection string, what must be changed from the default settings in order to use prepared statements.

I think the answer may exist in here somewhere:

• https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html
• https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html#DataSourceManagementforColdFusion-ConnectingtoMySQL
• condition for creating a prepared statement using cfqueryparam?
• https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-useconfigs.html
• https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Answer 1

The default behavior of the JDBC driver is to set useServerPrepStmts=false according to https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

Add useServerPrepStmts=true to the JDBC Connection String parameters in the CFIDE (/cfide/administrator/enter.cfm) under Data & Services > Datasources.