I'm not aware of any built in functions or code snippets that do this, but you could write something that works with most simple parameter types. The reason for saying "most* is that there are a few complications
The SQLParameters array doesn't include the original cfsqltypes. So it's not possible to definitively differentiate strings (which must be quoted) from numeric values (which are not).
One way to work around that deficiency is to use the internal debugging service, which does include the cfsqltypes in its output. An obvious disadvantage is that it requires debugging be enabled, and utilizes an undocumented internal class. However, since this kind of task isn't something you'd normally be running in production anyway, it's an acceptable limitation IMO.
Unfortunately, none of the available options include cfqueryparam's null attribute. Since Adobe chose to treat nulls as an empty string in the parameter list, there's no way to determine when a parameter value is null and when it's actually an empty string "". Sadly there's not much you can do it about that. Probably the best you can do is choose whatever default works for your application: null or empty string.
Syntax and supported data types vary by vendor, so any code will likely need to be adjusted for each dbms. Especially for less common or complex types like refcursor or blob.
There's also the issue of handling single quotes embedded within string parameter values. Using replace() to escape single quotes should do the trick, but there may be edge cases.
You didn't mention your dbms, but here's a VERY rough starter example for SQL Server. You'll have to decide how/if to handle less common types like refcursor and blob` (currently returns "{{unhandled_type_(typeName)}}".
Demo:
queries = unParameterizeQueries();
for (qry in unParameterizeQueries()) {
writeOutput("<pre>"& encodeForHTML(qry) &"</pre>");
}
Original Query
<!--- deliberately embed single quotes in string values --->
<cfquery ...>
SELECT
<cfqueryparam value="1234567890" cfsqltype="idstamp"> AS idstampCol
, <cfqueryparam value="100.50" cfsqltype="money"> AS moneyCol
, <cfqueryparam value="6789.876423" cfsqltype="float"> AS floatCol
, <cfqueryparam value="abc '1,2,3'" cfsqltype="char"> AS charCol
, <cfqueryparam value="10.52" cfsqltype="decimal" scale="2"> AS decimalCol
, <cfqueryparam value="abc '1,2,3'" cfsqltype="nchar"> AS ncharCol
, <cfqueryparam value="abc '1,2,3'" cfsqltype="nvarchar"> AS nvarcharCol
, <cfqueryparam value="#now()#" cfsqltype="timestamp"> AS timestampCol
, <cfqueryparam value="123.964" cfsqltype="double"> AS doubleCol
, <cfqueryparam value="123" cfsqltype="cf_sql_tinyint"> AS tinyintCol
, <cfqueryparam value="123" cfsqltype="integer"> AS integerCol
, <cfqueryparam value="12345.75" cfsqltype="numeric" scale="2"> AS numericCol
, <cfqueryparam value="abc '1,2,3'" cfsqltype="longvarchar"> AS longvarcharCol
, <cfqueryparam value="123" cfsqltype="bigint"> AS bigintCol
, <cfqueryparam value="#now()#" cfsqltype="time"> AS timeCol
, <cfqueryparam value="123" cfsqltype="bit"> AS bitCol
, <cfqueryparam value="#now()#" cfsqltype="date"> AS dateCol
, <cfqueryparam value='<AdventureWorks2012.Person.Person LastName="Achong" />' cfsqltype="sqlxml"> AS sqlxmlCol
, <cfqueryparam value="123" cfsqltype="smallint"> AS smallintCol
, <cfqueryparam value="123" cfsqltype="real"> AS realCol
, <cfqueryparam value="abc '1,2,3'" cfsqltype="varchar"> AS varcharCol
, <cfqueryparam cfsqltype="varchar" null="true"> AS NullVarcharCol
</cfquery>
Output
SELECT
'1234567890' AS idstampCol
, 100.5 AS moneyCol
, 6789.876423 AS floatCol
, 'abc ''1,2,3''' AS charCol
, 10.52 AS decimalCol
, N'abc ''1,2,3''' AS ncharCol
, N'abc ''1,2,3''' AS nvarcharCol
, '2022-04-06 02:33:16.59' AS timestampCol
, 123.964 AS doubleCol
, 123 AS tinyintCol
, 123 AS integerCol
, 12345.75 AS numericCol
, 'abc ''1,2,3''' AS longvarcharCol
, 123 AS bigintCol
, '02:33:16' AS timeCol
, 1 AS bitCol
, '2022-04-06' AS dateCol
, '<AdventureWorks2012.Person.Person LastName="Achong" />' AS sqlxmlCol
, 123 AS smallintCol
, 123.0 AS realCol
, 'abc ''1,2,3''' AS varcharCol
, NULL AS NullVarcharCol
UDF's
public array function unParameterizeQueries() {
// store results
local.results = [];
// get debugging service
local.svc = createObject("java", "coldfusion.server.ServiceFactory").getDebuggingService().getDebugger();
local.qry = local.svc.getData();
// get all queries for request
local.allQueries = queryExecute(
" SELECT Attributes AS SQLParameters, Body AS SQLString
FROM qry
WHERE Type = :type
"
, { type : "SqlQuery" }
, { dbtype : "query" }
);
// for each query, replace parameters and output sql
local.allQueries.each( function(row, currentRow, qry) {
local.sql = row.SQLString;
row.SQLParameters.each(function(param, index) {
sql = sql.replace( "?"
, formatSQLParam( param.value, param.sqlType, true )
, "one"
);
});
results.append( sql );
});
return local.results;
}
public string function formatSQLParam(
required string paramValue
, required string sqlType
, boolean emptyStringAsNull = true
){
local.handleAsString = "char,varchar,longvarchar,date,idstamp,time,timestamp,sqlxml,clob";
local.handleAsNumeric = "bigint,decimal,double,float,integer,money,money4,numeric,smallint,real,tinyint";
local.handleAsUnicodeString = "longnvarchar,nchar,nvarchar,nclob";
local.handleAsBoolean = "bit";
// remove used in old versions "cf_sql_" prefix used in old versions
local.typeName = arguments.sqlType.replaceNoCase("cf_sql_", "");
if ( emptyStringAsNull && isSimpleValue(arguments.paramValue) && arguments.paramValue == "" ) {
local.result = "NULL";
}
else if ( local.handleAsString.listFindNoCase(local.typeName) ) {
local.result = "'"& replace( arguments.paramValue, "'", "''", "all" ) &"'";
}
//For sql server, prefix unicode columns with "N"
else if ( local.handleAsUnicodeString.listFindNoCase(local.typeName) ) {
local.result = "N'"& replace( arguments.paramValue, "'", "''", "all" ) &"'";
}
else if ( local.handleAsNumeric.listFindNoCase(local.typeName) ) {
local.result = arguments.paramValue ;
}
else if ( local.handleAsBoolean.listFindNoCase(local.typeName) ) {
local.result = arguments.paramValue ? 1 : 0;
}
// Otherwise, indicate the type isn't currently handled
else {
local.result = " {{unhandled_type_"& arguments.sqlType &"}}";
}
return local.result;
}
