动态查询的 SQL 注入保护
针对 SQL 注入缺陷的典型控制措施是使用绑定变量（cfqueryparam 标记）、验证字符串数据以及转向实际 SQL 层的存储过程。这一切都很好，我同意，但是如果该网站是旧网站并且具有大量动态查询，该怎么办？然后，重写所有查询是一项艰巨的任务，需要大量的回归和性能测试。我正在考虑使用动态 SQL 过滤器并在调用 cfquery 进行实际执行之前调用它。
我在 CFLib.org 中找到了一个过滤器（http://www.cflib.org/udf/sqlSafe)：
<cfscript>
/**
* Cleans string of potential sql injection.
*
* @param string String to modify. (Required)
* @return Returns a string.
* @author Bryan Murphy ([email protected])
* @version 1, May 26, 2005
*/
function metaguardSQLSafe(string) {
var sqlList = "-- ,'";
var replacementList = "#chr(38)##chr(35)##chr(52)##chr(53)##chr(59)##chr(38)##chr(35)##chr(52)##chr(53)##chr(59)# , #chr(38)##chr(35)##chr(51)##chr(57)##chr(59)#";
return trim(replaceList( string , sqlList , replacementList ));
}
</cfscript>
这似乎是一个非常简单的过滤器，我想知道是否有方法可以改进它或提出更好的解决方案？
The typical controls against SQL injection flaws are to use bind variables (cfqueryparam tag), validation of string data and to turn to stored procedures for the actual SQL layer. This is all fine and I agree, however what if the site is a legacy one and it features a lot of dynamic queries. Then, rewriting all the queries is a herculean task and it requires an extensive period of regression and performance testing. I was thinking of using a dynamic SQL filter and calling it prior to calling cfquery for the actual execution.
I found one filter in CFLib.org (http://www.cflib.org/udf/sqlSafe):
<cfscript>
/**
* Cleans string of potential sql injection.
*
* @param string String to modify. (Required)
* @return Returns a string.
* @author Bryan Murphy ([email protected])
* @version 1, May 26, 2005
*/
function metaguardSQLSafe(string) {
var sqlList = "-- ,'";
var replacementList = "#chr(38)##chr(35)##chr(52)##chr(53)##chr(59)##chr(38)##chr(35)##chr(52)##chr(53)##chr(59)# , #chr(38)##chr(35)##chr(51)##chr(57)##chr(59)#";
return trim(replaceList( string , sqlList , replacementList ));
}
</cfscript>
This seems to be quite a simple filter and I would like to know if there are ways to improve it or to come up with a better solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将此编码放入您的 application.cfm 文件中。
http://ppshein.wordpress.com /2008/08/23/sql-injection-attacks-by-store-procedure/
http://ppshein.wordpress.com/2008/08/28/block-ip-in-coldfusion/
Put this coding into your application.cfm file.
<cfif FindNoCase(“DECLARE”,cgi.query_string) and FindNoCase(“CAST”,cgi.query_string) and FindNoCase(“EXEC”,cgi.query_string)>
<cfabort showerror="Oops..!! It's SQL injection." >
</cfif>
http://ppshein.wordpress.com/2008/08/23/sql-injection-attacks-by-store-procedure/
http://ppshein.wordpress.com/2008/08/28/block-ip-in-coldfusion/
是的，但如果您执行任何重大更改，包括使用您提议的函数，就会出现这种情况。
因此，我仍然建议进行一些测试设置，重构以使用合理的框架，然后修复查询以使用 cfqueryparam。
这个特定的函数是一堆废话，它没有做它声称要做的事情，并且有可能破坏东西（通过错误地超过最大长度）。
它所做的只是将
--转换为--并将'转换为'- 这不是 SQL 注入保护！所以，是的，如果您仍然想走这条路，请找到不同的函数，但我建议进行适当的重构。
Yep, but that's the case if you perform any significant changes, including using a function like the one you are proposing.
So I'd still recommend getting some tests setup, refactoring to use a sensible framework, and then fixing the queries to use cfqueryparam.
That specific function is a bunch of nonsense, which does not do what it claims to do, and has the potential to break stuff (by incorrectly exceeding max lengths).
All it does is turns
--into--and'into'- this is not SQL injection protection!So yeah, if you still do want to go down that route, find a different function, but I'd recommend proper refactoring.
显然你还有很多工作要做。但是，当您卷起袖子时，为了减轻注入攻击的一些潜在损害，您可以做的一件小事是创建多个数据源，并通过仅限于以下数据源运行所有
select-only查询仅select语句。对于所有数据源，请确保诸如grant、revoke、create、alter和等内容>drop被禁用。Obviously you have a lot of work ahead of you. But as you roll up your sleeves, one small thing you might do to mitigate some of the potential damage from injection attacks is to create several datasources, and run all your
select-only queries through a datasource restricted to onlyselectstatements. And for all of the datasources, make sure things likegrant,revoke,create,alter, anddropare disabled.您可以尝试Portcullis。它是一个开源 CFC，可用于扫描 URL、FORM 和 COOKIE 范围以查找 SQL 注入和 XSS 攻击。它不能保证得到保护，但至少会在您重写查询时毫不费力地提供一些保护。好处是它可以包含在 Application.cfm/cfc 中，以扫描每个 CF 页面请求的范围，只需大约 4 行代码。
You might try Portcullis. It is an open source CFC that you can use to scan the URL, FORM and COOKIE scopes for SQL Injection and XSS attacks. It won't be guaranteed protection but would at least provide some protection today with little effort while you work on a rewrite of the queries. The nice thing is it can be included in the Application.cfm/cfc to scan the scopes on every CF page request at the cost of about 4 lines of code.