I need to dynamically construct a set of JOIN statements where the table and column names are passed in from another ColdFusion query. When passing the string values to into the statement, CFQUERYPARAM adds single quotes around it - that's part of the point of CFQUERYPARAM. Given that this breaks the SQL statement, is it acceptable not to use CFQUERYPARAM in this case and instead ensure that the incoming query is cleansed, or is there a way round which allows CFQUERYPARAM to be used? (I can lock down these pieces of code using circuit/fuse permissions in Fusebox.)
我需要动态构造一组JOIN语句，其中表名和列名从另一个ColdFusion查询传入。将字符串值传递给语句时，CFQUERYPARAM会在其周围添加单引号 - 这是CFQUERYPARAM的一部分。鉴于这会破坏SQL语句，在这种情况下是否可以不使用CFQUERYPARAM，而是确保传入的查询被清除，或者是否有允许使用CFQUERYPARAM的方法？ （我可以使用Fusebox中的电路/保险丝权限锁定这些代码。）
Thanks.
谢谢。
2 个解决方案
#1
3
cfqueryparam does not add single quotes - it uses bind variables.
cfqueryparam不添加单引号 - 它使用绑定变量。
I am instantly suspicious of the statement "dynamically construct a set of JOIN statements" - it doesn't sound like you're necessarily doing things properly if you're dynamically joining.
我立即怀疑语句“动态构造一组JOIN语句” - 如果你动态加入，你听起来并不一定正确地做事。
However, for table/column names, once you are definitely sanitizing fully - if cfqueryparam doesn't work and you need cf variables - then yes, you can use CF variables directly.
但是，对于表/列名称，一旦您完全清理完全 - 如果cfqueryparam不起作用并且您需要cf变量 - 那么是的，您可以直接使用CF变量。
Note: To sanitize safely, you can use rereplacenocase(table_name,'[^a-z_]','','all') to remove everything other than a-z and underscore.
注意：要安全地清理，可以使用rereplacenocase（table_name，'[^ a-z _]'，''，'all'）删除除a-z和下划线之外的所有内容。
#2
1
You can escape the single quotes by using two of them. You can also use the preserveSingleQuotes function.
您可以使用其中两个来转义单引号。您还可以使用preserveSingleQuotes函数。
#1
3
cfqueryparam does not add single quotes - it uses bind variables.
cfqueryparam不添加单引号 - 它使用绑定变量。
I am instantly suspicious of the statement "dynamically construct a set of JOIN statements" - it doesn't sound like you're necessarily doing things properly if you're dynamically joining.
我立即怀疑语句“动态构造一组JOIN语句” - 如果你动态加入，你听起来并不一定正确地做事。
However, for table/column names, once you are definitely sanitizing fully - if cfqueryparam doesn't work and you need cf variables - then yes, you can use CF variables directly.
但是，对于表/列名称，一旦您完全清理完全 - 如果cfqueryparam不起作用并且您需要cf变量 - 那么是的，您可以直接使用CF变量。
Note: To sanitize safely, you can use rereplacenocase(table_name,'[^a-z_]','','all') to remove everything other than a-z and underscore.
注意：要安全地清理，可以使用rereplacenocase（table_name，'[^ a-z _]'，''，'all'）删除除a-z和下划线之外的所有内容。
#2
1
You can escape the single quotes by using two of them. You can also use the preserveSingleQuotes function.
您可以使用其中两个来转义单引号。您还可以使用preserveSingleQuotes函数。