1

I am building a dynamic SQL Query using CFLoop. I am using CFSaveContentto iterate through the variables and build the query and then I am executing it in CFQuery using PreserveSingleQuotes. That works fine but the issue with this method is that I can't use CFQueryParam so my query is vulnerable for injections. Is was wondering if there is any workaround for this issue?

Update:

    <cfsavecontent variable="sqlstring">
            SELECT   id
                    ,(CASE
                        <cfloop query="qGetRules">
                            WHEN val1 >=#qGetRules.equ# AND val2 >#arguments.leve# THEN 1
                        </cfloop>
                        ELSE 0
                         END) AS criteria
            FROM mt4_users
    </cfsavecontent>


    <cfquery name="qGetEquity" datasource="mydatasource">
        #PreserveSingleQuotes(variables.sqlstring)#
    </cfquery>

So on this example as you can see I can't use cfqueryparam for the arguments.leve because it will crash on PreserveSingleQuotes

BlackM
  • 3,927
  • 8
  • 39
  • 69
  • Have you had a look at the docs on the [cfscript equivalents of cfquery and cfqueryparam](https://helpx.adobe.com/coldfusion/cfml-reference/script-functions-implemented-as-cfcs/query.html)? – Leigh Oct 14 '15 at 12:54
  • 1
    In theory, all of us validate all user supplied fields all the time anyway. Relying on query parameter alone for security is not as secure as you could be. Also, PreserveSingleQuotes has it's place, but I've never had it deal successfully with apostrophes. – Dan Bracuk Oct 14 '15 at 13:46
  • Since this is a webservice for mobile I am not able to validate the user's input (beside the mobile app). If someone sniff the URL and tries to inject the SQL, I will need to stop it. – BlackM Oct 14 '15 at 13:54
  • 6
    You ALWAYS need to validate/sanitize user input. Always on the server and preferably on the front-end as well (for better user experience). I'm not following your question here. Why can't you use `cfqueryparam`? Of course a code example would be helpful. – Miguel-F Oct 14 '15 at 14:03
  • Parameters are not intended as a mechanism for SQLi protection, this is merely a side-effect of appropriately parameterising one's SQL statements. The answer is to create your dynamic SQL statement inside CFQUERY param tags. That's what they're *for*. That said what version of CF are you on? – Adam Cameron Oct 14 '15 at 16:30

1 Answers1

0

The trick is to use the list parameter of cfqueryparam. It will correctly format the data for the query, escaped quotes (if needed) and all.

If the list is of numeric values (e.g. in a WHERE X IN (1,2,3,4) statement):

<cfqueryparam value="#arguments.foo#" cfsqltype="cf_sql_integer" list="true">

If the list is of string values:

<cfqueryparam value="#arguments.foo#" cfsqltype="cf_sql_varchar" list="true">

This also works for cfprocparam.

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • I see nothing in the question that suggests that the OP is simply generating a list of values. Also, passing a list of numeric values using `cfprocparam` will not always accomplish the goal. When I do this with sql-server, I use table value parameters instead. – Dan Bracuk Oct 14 '15 at 17:08