在cfquery中使用SQL的变量的最佳实践 [英] Best Practice for variable with SQL in cfquery
问题描述
如果我有一个变量与SQL代码如 #PreserveSingleQuotes（arguments.sql）＃
If I had a variable with with SQL code such as #PreserveSingleQuotes(arguments.sql)#
-
运行此代码的最佳方法是什么？
What is the best way to run this code?
如果SQL注入攻击可能？
Is there a way to run this if SQL injection attacks are possible?
此问题是变量作为cfquery中的SQL
推荐答案
＃1是一个有趣的问题，因为你没有大量的选择手。从ColdFusion的角度来看，您可以通过< cfquery> 或通过 Query.cfc 运行它。或者，DB系统将能够接受一个包含SQL的字符串并执行它，但实际上只是将问题从CF抽象到DB。
#1 is a bit of funny question, because you don't have a great number of options to hand. From a ColdFusion perspective you can either run it via <cfquery> or via Query.cfc. Alternatively DB systems will be able to take a string containing SQL and execute it, but that really just abstracts the issue slightly from CF to the DB. I'm not sure there are other options.
对于＃2，这是一个很大的问题，当使用< cfquery> ，因为没有良好的（或至少明智的）缓解SQL注入的方法（当然没有办法优化SQL的编译过程），因为在语句中参数化值的唯一方法是通过< cfqueryparam> ，并且无法将它们嵌入到您的SQL字符串中。这里的解决方案是使用 Query.cfc （我不相信我建议使用 Query.cfc ，因为它关于CFML语言（IMO）中执行最差的部分，IMO），因为可以在SQL字符串中指定位置参数或命名参数，并单独传递参数值。
As far as #2 goes, this is a big problem when using <cfquery> as there's no good (or at least sensible) way of mitigating SQL injection (and certainly no way of optimising the SQL's compilation process), because the only way to parametrise the values in the statement is via <cfqueryparam>, and one cannot embed those in your string full of SQL. The solution here is to use Query.cfc (I don't believe I am recommending using Query.cfc because it's about the most poorly implemented part of the CFML language, IMO), because one can specify either positional or named parameters within the SQL string, and pass the parameter values separately.
在结束时，你应该总是肯定传递值作为参数，而不是在SQL语句中手动编写它们，如果这意味着不使用预先创建的字符串为SQL语句，那么是。
In closing, you should always definitely pass values as parameters rather than hand-coding them in the SQL statement, and if that means not using a pre-created string for the SQL statement, then so be it.
这篇关于在cfquery中使用SQL的变量的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
