在 ColdFusion 中替换损坏的 SQL 查询

Question

我有下面的 MySQL 查询，它导致了错误，错误也在下面。

    SELECT DISTINCT s.id as id, s.auctioneer as auctioneer, s.advertType as advertType, s.saleType as saleType, an.name as auctioneerName, st.entryCopy as saleTypeName, at.entryCopy as advertTypeName, s.heading AS heading, sl.city AS city, sd.id AS sdId, sd.startDate AS startDate
    FROM    sales s LEFT JOIN saleloc sl ON sl.saleId = s.id LEFT JOIN saledates sd ON sd.saleLoc = sl.id,
            auctioneers an,
            lookupcopy st,
            lookupcopy at
    #replace(findWhere,"''","'","all")# AND
    s.id = sd.saleId AND sl.saleId = s.id
    AND an.id = s.auctioneer
    AND st.id = s.saleType
    AND at.id = s.advertType
    GROUP BY id     
    ORDER BY startDate, auctioneerName, city

来自数据库的错误

SELECT DISTINCT s.id as id, s.auctioneer as auctioneer, s.advertType as advertType, s.saleType as saleType, an.name as auctioneerName, st.entryCopy as saleTypeName, at.entryCopy as advertTypeName, s.heading AS heading, sl.city AS city, sd.id AS sdId, sd.startDate AS startDate
FROM sales s 
LEFT JOIN saleloc sl ON sl.saleId = s.id 
LEFT JOIN saledates sd ON sd.saleLoc = sl.id, auctioneers an, lookupcopy st, lookupcopy at 
'WHERE s.advertType > 0 
AND s.saleType > 0 
AND sl.region = "2" ' 
AND s.id = sd.saleId 
AND sl.saleId = s.id 
AND an.id = s.auctioneer 
AND st.id = s.saleType 
AND at.id = s.advertType 
GROUP BY id 
ORDER BY startDate, auctioneerName, city 

我没有编写此代码，我不确定为什么使用 #Replace()#，任何人都可以看到如何修复它导致的语法错误吗？

Answer 1

Before the query code, do a replace as follows:

<cfset findWhere = Replace(findWhere, "''", "'", "ALL")#
<cfif Left(findWhere, 1) EQ "'">
    <cfset findWhere = Right(findWhere, Len(findWhere) - 1)>
</cfif>
<cfif Right(findWhere, 1) EQ "'">
    <cfset findWhere = Left(findWhere, Len(findWhere) - 1)>
</cfif>

<cfquery name="qry" datasource="mysql">
SELECT DISTINCT s.id as id, s.auctioneer as auctioneer, s.advertType as advertType, s.saleType as saleType, an.name as auctioneerName, st.entryCopy as saleTypeName, at.entryCopy as advertTypeName, s.heading AS heading, sl.city AS city, sd.id AS sdId, sd.startDate AS startDate
FROM    sales s 
LEFT JOIN saleloc sl ON sl.saleId = s.id 
LEFT JOIN saledates sd ON sd.saleLoc = sl.id,
        auctioneers an,
        lookupcopy st,
        lookupcopy at
#findWhere# AND
s.id = sd.saleId AND sl.saleId = s.id
AND an.id = s.auctioneer
AND st.id = s.saleType
AND at.id = s.advertType
GROUP BY id     
ORDER BY startDate, auctioneerName, city
</cfquery>

Answer 2

findWhere 中存储的值在字符串的开头和结尾包含单引号。

Answer 3

另请注意：除非您在没有任何直接用户输入值的情况下创建了 findWhere，否则您需要保护它。

最好这样做：

...
WHERE 1= 1
<cfif listFind( 'foo' , findWhere )>
foo= 2

<cfelseif listFind( 'bar' , findWhere )>
bar= 209

</cfif>
...

Answer 4

只是为了澄清，我不相信您可以在同一查询中执行不同的语句和分组语句。

他们都做同样的事情，但出于不同的原因。