Hi,
I need to build up a complex dynamic query statement. I have built methods
to add queryParam statements, and built up valid SQL.
If I do:
cfset sqlStatement = SELECT DISTINCT tableName.ID FROM tableName
LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID
WHERE ( tableName.clientID
If you just output the sqlStatement variable, how does it look? Also, any
particular reason you are not just building your sql statement inside the
cfquery tags?
On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer tmcn...@gmail.com wrote:
Hi,
I need to build up a complex dynamic query statement.
Ditto Steve's question. You're trying to use cfqueryparam outside of a
cfquery block. Coldfusion is just going to see #sqlStatement# as a block
of literal text, so it will pass
WHERE firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar
value=%bar% /
into the DB exactly as written. And SQL
You need to use the preserveSingleQuotes () pseudo function ie:
cfquery name=foo
#preserveSingleQuotes(sqlStatement)#
/cfquery
~|
Order the Adobe Coldfusion Anthology now!
@ Anyone building dynamic queries: PLEASE **DO NOT** follow that advice.
You will regret it, eventually. If the data you're dealing with is
sensitive enough, you might even go to prison over it.
The proper solution is the one already mentioned by Steve and Bill. Build
the statement - with
Thanks for the replies. The answer to Steve, Bill and Matt is, the query is
far too dynamic to be built inside a cfquery without a million cfif or
switch/case statements.
In fact, that sort of thing is what we're trying to replace. The existing
routine is a cfquery tag that has stacks of
It is a fact that using cfqueryparam outside of cfquery will result in
it being seen by CF as purely string text. It will never be executed as an
actual cfqueryparam.
I obviously do not know what you're dealing with, specifically, but it
sounds like you have a god query on your hands, and it
Hi Matt,
On Tue, Jan 22, 2013 at 5:22 PM, Matt Quackenbush quackfu...@gmail.comwrote:
It is a fact that using cfqueryparam outside of cfquery will result in
it being seen by CF as purely string text. It will never be executed as an
actual cfqueryparam.
Good. Or rather, not good, but
you might even go to prison over it.
;-))
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
If you really can't build it using conditions/loops/etc within your query tag
then build it like you are but eliminate the cfqueryparam tags, and HEAVILY
validate the variables being put into those tags. Then you can concatenate a
string together that will work. You'll just need to be super
If you really can't build it using conditions/loops/etc within your query tag
then build it like you are but eliminate the cfqueryparam tags, and HEAVILY
validate the variables being put into those tags. Then you can concatenate a
string together that will work. You'll just need to be super
What version of CF?
If it's 9+, you can use placeholders in the SQL string for the parameters,
rather than cfqueryparam tags, passing the param data to the query
separately. You can't use cfquery for this approach, but can use
Query.cfc instead. It's one of the few areas in which Query.cfc is
That protects against SQL injection, but it doesn't help caching compiled
execution plans, which is the main reason for not hard-coding values into
the SQL string instead of passing them as parameters.
Not parameterising queries properly is just a really poor practice. And it
should not ever be
13 matches
Mail list logo
