使用 LIKE 语句构建 Coldfusion 查询问题

发布于 2024-09-24 05:07:02 字数 317 浏览 3 评论 0原文

我正在尝试用这一行构建动态 sql 语句

<cfset SQL = "SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title from events where title LIKE '%#form.event_name#%' ">

<cfquery name="results" >
#SQL#
</cfquery>

似乎 like 子句有问题。有什么想法吗？我需要转义%吗？

谢谢

原文

I am trying to build a dynamic sql statement with this line

<cfset SQL = "SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title from events where title LIKE '%#form.event_name#%' ">

<cfquery name="results" >
#SQL#
</cfquery>

Seems there is a problem with the like clause. Any ideas? Do I need to escape the %?

Thanks

分享到QQ

分享到微博

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

发布评论

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

岁月如刀 2024-10-01 05:07:02

在 CFQUERY 中，ColdFusion 将自动用双引号替换 #SQL# 中的单引号。

因此，从理论上讲，您必须像这样编写查询：

<cfquery name="results" >
#PreserveSingleQuotes(SQL)#
</cfquery>

但是...接受表单变量并直接在查询中使用它而不进行进一步验证是非常危险的。对我来说这似乎是 SQL 注入攻击的邀请。

我宁愿使用像这样：

<cfquery name="results" >
SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title 
from events 
where title LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.event_name#%"> 
</cfquery>

Within a CFQUERY, ColdFusion will replace single quotes in #SQL# with double quotes automagically.

So in theory you would have to write your query like this:

<cfquery name="results" >
#PreserveSingleQuotes(SQL)#
</cfquery>

BUT... It's very dangerous to accept a form variable and use it without further validation directly in your query. Seems like an invitation for SQL injection attacks to me.

I'd rather use <cfqueryparam> like so:

<cfquery name="results" >
SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title 
from events 
where title LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.event_name#%"> 
</cfquery>

回复收藏 0 原文

~没有更多了~