如何防止 order by 子句上的冷融合 SQL 注入

发布于 2024-11-26 23:40:05 字数 135 浏览 0 评论 0原文

由于 cfqueryparam 不能按 order by 工作,使用 xmlformat 会停止 sql 注入吗?

ORDER BY #xmlformat(myVariable)#

谢谢,

Since cfqueryparam doesn't work in an order by, would using xmlformat stop sql injections?

ORDER BY #xmlformat(myVariable)#

Thanks,

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

漫雪独思 2024-12-03 23:40:05

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

一个好方法解决此限制的方法是使用 ListFindNoCase 函数来限制可排序的列名称,例如:

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

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

A good way to get around this limitation is to use the ListFindNoCase function, to limit the sortable column names, 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>
江城子 2024-12-03 23:40:05

这是来自存储过程,但将 @ORDER_BY 值转换为实际的数据库列,并将 @SORT_ORDER 值转换为 SQL 命令。


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,

This is from a stored procedure, but translate an @ORDER_BY value to an actual database column and a @SORT_ORDER value to a 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,
善良天后 2024-12-03 23:40:05

XML 格式无法处理所有情况。

列检查很好,但我猜测让用户定义顺序的优点是因为您可以使其比单个列更复杂。例如,您可以添加几列以及升序、降序等...

我建议您创建一个全局可用的函数,该函数可以去除任何不是数字、字母或逗号的字符。如果有人尝试进行 SQL 注入,它就会失败。

XML Format won't handle all cases.

The column check is good, but I'm guessing that the advantage of letting the user define what the order by is, is because you can make it more complex than just a single column. For instance, you could add several columns and an Ascending, Descending etc...

I'd suggest you make a globally available function that strips out any character that isn't a number, letter or comma. If someone did attempt to do a SQL Injection it would just fail.

只是一片海 2024-12-03 23:40:05
<cfif refindnocas('^\w+ ?(desc|asc)?

<cfset columnList = 'col1,col2,etc' /> <!--- might want to use in select as well --->
<cfset regexColList = replace(columnList, ',', '|', 'all') />

<cfif not refindnocas('^(#regexColList#) ?(desc|asc)?

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)?

等等

, myVariable)> ORDER BY #myVariable# </cfif>



等等

, myVariable)> <cfset myVariable = "DefaultSort" /> </cfif> ORDER BY #myVariable#


等等

, myVariable)> ORDER BY #myVariable# </cfif>

等等

, sort)> <cfreturn sort /> <cfelse> <cfreturn default /> </cfif> </cfargument>

等等

, myVariable)> ORDER BY #myVariable# </cfif>

等等

, myVariable)> <cfset myVariable = "DefaultSort" /> </cfif> ORDER BY #myVariable#

等等

, myVariable)> ORDER BY #myVariable# </cfif>

等等

<cfif refindnocas('^\w+ ?(desc|asc)?

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)?

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)?

etc

, myVariable)> ORDER BY #myVariable# </cfif>

or


or


etc

, myVariable)> <cfset myVariable = "DefaultSort" /> </cfif> ORDER BY #myVariable#

or


etc

, myVariable)> ORDER BY #myVariable# </cfif>

or

or

etc

, sort)> <cfreturn sort /> <cfelse> <cfreturn default /> </cfif> </cfargument>

etc

, myVariable)> ORDER BY #myVariable# </cfif>

or

or

etc

, myVariable)> <cfset myVariable = "DefaultSort" /> </cfif> ORDER BY #myVariable#

or

etc

, myVariable)> ORDER BY #myVariable# </cfif>

or

or

etc

梦罢 2024-12-03 23:40:05

另一种选择是对 ListFindNoCase 方法稍加修改。列信息可以存储在结构中。 key 将是公开可见的列名称,value 是真实的列名称。它有点复杂。但我喜欢这样一个事实:它不需要您公开您的架构。正如戴夫提到的,它还支持更多复合语句。

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

Another option is a slight twist on the ListFindNoCase approach. Column information can be stored in a structure. The key would be the publicly visible column name and the value is the real column name. It is a little more complex. But I like the fact that it does not require you to expose your schema. It also supports more compound 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>
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文