coldfusioncfmlcfquerycfqueryparam

how to print/display cfquery with cfqueryparam values replaced?

As you may be aware that when we dump cfquery, it dumps the cfqueryparam values into SQLPARAMETERS of the dump. It's been alright to replace a couple of parameters manually. However, lately I've started working on a project where there are tens(or way more sometimes) of cfqueryparams being used in building the complex db queries.

is there a way to actually print a query with the cfqueryparam values replaced and display a valid sql statement on the screen?

Thanks in advance!

Has there been any plugin or a piece of code that I'm unable to find or it is never written?

Solution

  • 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

    1. 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.

    2. 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.

    3. 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;
}