1

I am using CFWheels for my project.

In my controller I have sql query string;

r_query = "SELECT MAX(column)
            FROM user_checklist 
            INNER JOIN rooms 
            ON user_checklist.RID = rooms.ID 
            INNER JOIN buildings 
            ON rooms.BID = buildings.ID 
            INNER JOIN tb inspectors 
            ON user_checklist.INSPID = inspectors.ID 
            GROUP BY user_checklist.cdate, user_checklist.rid";

            if(StructKeyExists(date_key, "expression") AND StructKeyExists(date_key, "value"))
            {
                r_query &= " HAVING #date_key.expression# '#date_key.value#'";
            }

In My View I have the CFQuery tag;

<cfoutput>
    <cfquery datasource="local" name="total_records">
        #PreserveSingleQuotes(sql_query)#
    </cfquery>  
</cfoutput>

As you can see I am not using CFQueryParam for binding and preventing SQL Injection. How can I better this query in my controller, so it would have some sort of place holder like '?' or ":param", which can then be binded in the view? I basically want to prevent SQL inject in the query.

Thank you

Saad A
  • 1,135
  • 2
  • 21
  • 46
  • 4
    You want to avoid the string building approach. I am particularly concerned with the `#date_key.expression#`. Consider http://stackoverflow.com/questions/25872741/case-statement-in-where-clause-in-tsql-query. Furthermore, `` should never, never, ever, ever be in a view. – James A Mohler Jan 05 '16 at 16:23
  • Thank you for the advice, I will use http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ffb.html to build a query in the controller or model. Also I want to avoid using this method and just use what is available in CFWheels however, I have to use subqueries and I know that is not support in CFWheels. Any suggestions on how I may use subqueries or replicate that functionality in CFWheels? – Saad A Jan 05 '16 at 16:46
  • Moreover, #date_key.expression# hold a SQL Expression, which is generated in my controller, I assume it is safe since there is no contact from the client side. Is there still a problem with that? Please let me know – Saad A Jan 05 '16 at 16:46
  • I suspect that the ORM layer in cfwheels can't do aggregate functions. I ran into a similar issue when I tried to use CF's built in Entity functions. See http://stackoverflow.com/questions/21270202/getting-distinct-values-in-a-column-in-orm. I suspect you have a similar issue. – James A Mohler Jan 05 '16 at 17:13
  • 2
    why don't you use stored procedure and pass the parameters and based on parameters you can construct your query. – Keshav jha Jan 06 '16 at 06:03

0 Answers0