Cfqueryparam Fails When Using "WHERE EXISTS"
I have always been an advocate of Cfqueryparam. Binding your variables innoculates you against SQL injection attack, often results in speed improvements and lessens the load on your database server. It may even help with the laundry (that's the word on the street). This morning I ran across an error that is produced by the correct use of cfquery param. It has to do with using the clause "WHERE EXISTS" in your query. Here's the query in question.
WHERE EXISTS Sample
SELECT h.column1
FROM r_tableA h
WHERE EXISTS ( select 1
FROM R_tableA_tableB hf,
R_TableC f
WHERE hf.col_id = f.col_id
and f.columnBlah = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var1"/>
and f.colblah2 = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var2"/>
and hf.col2_id = h.col2_id
)
order by h.blah
</cfquery>
This query is not mine and I don't generally use this syntax. Perhaps a read can inform me as to when it is appropriate to use the "EXISTS" in this way. In any case the query resulted in an error - [Macromedia][SQLServer JDBC Driver][SQLServer]Could not find prepared statement with handle 1. Because of the nature of the error I surmised pretty quickly that the binding was not working (paramter 1 meaning the first declared parameter in the prepare statment). Removing Cfqueryparam fixed the error - but I wanted them back. So I rewrote the query as:
DECLARE @paramA char(25)
DECLARE @paramB char(25)
SELECT @paramA = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var1"/>
SELECT @paramB = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var2"/>
SELECT h.column1
FROM r_tableA h
WHERE EXISTS ( select 1
FROM R_tableA_tableB hf,
R_TableC f
WHERE hf.col_id = f.col_id
and f.columnBlah = @paramA
and f.colblah2 = @paramB
and hf.col2_id = h.col2_id
)
order by h.blah
</cfquery>
2 Comments
-
I personally would use a derived table. Never ran into this problem using them.
select
h.column1
from
r_tableA h
inner join
(
select
hf.col2_id AS col2_id
from
R_tableA_tableB hf,
R_TableC f
where
hf.col_id = f.col_id
and f.columnBlah = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var1"/>
and f.colblah2 = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="var2"/>
) AS h2
ON h.col2_id = h2.col2_id
order by h.blah -
That is odd. I have used cfqueryparam inside of WHERE EXISTS several times without any problem. Something to do with the database, perhaps?
Sites to Watch
Aggregators
RSS
