Dynamic Queries with CFQueryParam and Render

language
1

I develop a lot of applications that use dynamic queries and my SQL statements are constructed in a way that’s not conducive to enclose them in a CFQuery tag. As such, I’ve usually resorted to checking my variables myself instead of using CFQueryParam. Looking at the option “Variable Usage in Queries” in Lucee administrator, I wondered if I could set it to restrictive (throw an error) AND still build dynamic queries outside of CFQuery. So, I set out to use “Render” with CFQueryParam and was surprised that it actually worked:

<CFSet i=‘select * from junk where name=<CFQueryParam CFSQLType=“CF_SQL_VARCHAR” Value=“Ross was here”>’>
<CFQuery Name=“Recs” DataSource=“dev”>
<CFSet WriteOutput(Render(i))</CFQuery>
<CFDump Var=Recs>

I don’t know whether that ended up as a prepared statement before it got submitted to the database server or not. Debug output doesn’t show this query any differently than a hard-coded query, where as Adobe CF used to show a place holder in the query with its value after the query.

My two fold question is whether, inefficiencies aside, the query is being executed as a prepared statement and, if so, can there be an indication in the debug that it indeed was executed as such?

And thank you for creating such a wonderful implementation of the CFML language.

2

You can always check using a database query to see what statements are being executed

If the queryparam wasn’t being evaluated, the db would be throwing an error right?

Lucee 6 will be optionally handling the Params in query debugging differently, showing the prepared statement minus the parameters (useful for aggregate analysis, you can’t group sql statements with Params)

3

I guess the db would be throwing an error if the literal CFQueryParam tag was being passed to it, so it must be working. Thanks!

4

@Zackster @Ross_Naheedy

DA-damn!! How have I never read about render()? Mind is officially blown.