How to prevent the injection of sql injections in a cold state in order according to the article

Question

Since cfqueryparam is not working in order, will xmlformat stop SQL injections?

ORDER BY #xmlformat(myVariable)# 

Thanks,

Answer 1

http://www.petefreitag.com/item/677.cfm

A good way to get around this limitation is to use the ListFindNoCase function to restrict the names of the sorted columns , for example:

 <cfset sortable_column_list = "age,height,weight,first_name"> <cfquery ...> SELECT first_name, age, height, weight FROM people ORDER BY <cfif ListFindNoCase(sortable_column_list, url.sort_column)>#url.sort_column#<cfelse>first_name</cfif> </cfquery> 

Answer 2

This is from a stored procedure, but translate the @ORDER_BY value into the actual database column and the @SORT_ORDER value for the SQL command.

 ORDER BY CASE WHEN @ORDER_BY = 'LENDER' AND @SORT_ORDER = 'D' THEN l.tms_name END DESC, CASE WHEN @ORDER_BY = 'LENDER' AND @SORT_ORDER != 'D' THEN l.tms_name END, CASE WHEN @ORDER_BY = 'LOAN_NUMBER' AND @SORT_ORDER = 'D' THEN p.Loan_Number END DESC, CASE WHEN @ORDER_BY = 'LOAN_NUMBER' AND @SORT_ORDER != 'D' THEN p.Loan_Number END, 

Answer 3

The XML format will not handle all cases.

Checking a column is good, but I assume that the advantage of allowing the user to determine what an order is is because you can make it more complex than just a single column. For example, you can add multiple columns and ascending, descending, etc.

I suggest you create a globally accessible function that removes any character that is not a number, letter or comma. If someone tries to execute SQL Injection, he simply fails.

Answer 4

 <cfif refindnocas('^\w+ ?(desc|asc)?$', myVariable)> ORDER BY #myVariable# </cfif> 

or

 <cfset columnList = 'col1,col2,etc' /> <!--- might want to use in select as well ---> <cfset regexColList = replace(columnList, ',', '|', 'all') /> <cfif not refindnocas('^(#regexColList#) ?(desc|asc)?$', myVariable)> <cfset myVariable = "DefaultSort" /> </cfif> ORDER BY #myVariable# 

or

 ORDER BY #query_sort(myVariable, columnList, defaultSort)# ... <cffunction name="query_sort"> <cfargument name="sort" /> <cfargument name="columns" /> <cfargument name"default" /> <cfset var regexcolumns = replace(columns, ',', '|', 'all') /> <cfif refindnocas('^(#regexcolumns#) ?(desc|asc)?$', sort)> <cfreturn sort /> <cfelse> <cfreturn default /> </cfif> </cfargument> 

etc.

Answer 5

Another option is a small twist on the ListFindNoCase approach. Information about the columns can be stored in the structure. key will be the publicly visible name of the column, and value be the true name of the column. This is a little trickier. But I like the fact that it does not require you to disclose your scheme. He also supports more complex statements, as Dave mentioned.

 <cfset sortCols = { defaultCol="DepartmentName" , date="ReportDate" , type="DepartmentName" , num="EmployeeID" } /> .... SELECT Columns FROM TableName ORDER BY <cfif structKeyExists(sortCols, url.sort_column)> #sortCols[url.sort_column]# <cfelse> #sortCols["defaultCol"]# </cfif>