Skip to main content
J
jdhunter
Participant
April 20, 2010
Answered

Problems passing in params to a query

  • April 20, 2010
  • 2 replies
  • 1362 views

Wasn't exactly sure how to word the title. What I am attempting to do is create a set of queries that I can pass in the table name and any additional code besides the base query. If I write the code directly like this it works:

select * from mytable where name = 'some name'

and if I shorten it and pass in the table name like this it works:

select * from #tablename#

but, if I pass in the where clause it fails, I don'teven need to pass it in from the client, simply turning the where clause into a variable fails. This fails:

<cfset whereC = "where name = 'some name' " >

...

select * from #tablename# #whereC#

Please, can someone tell my why this fails? there should be no reason for it, or am I overlooking something very simple?

Thanks,

Jim

This topic has been closed for replies.
Correct answer Adam Cameron.

Look up preserveSingleQuotes() in the docs.

But before you start writing your own generic DB abstraction layer, possibly have a look at some of the very well-trod ground in that area (TransferORM, Reactor, CF9's Hibernate integration...).

--

Adam

2 replies

ilssac
ilssac
Inspiring
April 20, 2010

Adam, again, beats me to the punch.

But to add a bit.  If you plan to include user input into that string you dynamically build for the SQL.  Be aware that you will seriously be open to SQL injection and take the proper precautions.  The normal CFML protection of <cfqueryparam....> does not work easily if you pass in the entire SQL as a string variable.

D
Dan_Bracuk
Inspiring
April 20, 2010

You can do it piecemeal.  Something like this:

<cfquery name="TheQuery" datasource="dw">
select #field1# myfield
from #arguments.table#
where #field2# in (<cfqueryparam cfsqltype="#datatype#" value="#arguments.values#" list="yes">)
</cfquery>


J
jdhunterAuthor
Participant
April 20, 2010

Thanks everyone who replied, but I am not trying to create a database abstraction layer, just a simple sql replacement system to reduce the number of methods I need to call on the back end.I'm not new to database development or web programming, just new to CF. I use a system silimar to what I am doing here all the time with other technologies, but for this project I am constrained to use CF.

So Dan, what you are saying is that I can't send in the entire where clause as a param but I can pass in each piece? Hmm, this is going to make things a little harder to do what I wanted. I wanted flexibility to pass in any where clause or none at all. Do you think it would work if I used the <cfqueryparam method to pass in the entire where clause? And what is CF doing to my passed in string to make it not work in the query?

Thanks,

Jim

A
Adam Cameron.Correct answer
Inspiring
April 20, 2010

Look up preserveSingleQuotes() in the docs.

But before you start writing your own generic DB abstraction layer, possibly have a look at some of the very well-trod ground in that area (TransferORM, Reactor, CF9's Hibernate integration...).

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices