Problems passing in params to a query

[jdhunter]

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

[Adam Cameron. · Accepted solution]

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

[ilssac]

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.

[Dan_Bracuk]

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>

[jdhunter]

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

[ilssac]

You can't pass in the entire SQL string and use the <cfqueryparam....> tag.   This tag provides ColdFusion's method to create paramertized queries where the SQL statements and the data are sent speratly to the database so that the database knows NOT to run any SQL commands that maybe in the data which may have come from insecure sources such as a web form.

When you pass in the string to the <cfquery...> block it is escaping the single quotes for the database by doubling them.  It is assuming that you are passing data like the name O'Hare for example.  I presume you know that if you where to pass that name into the database to be inserted or as a search filter, you would need to double the single quote as O''Hare.

By default ColdFusion does this for you automatically.  When you do not want it to do this behavior you would use the preserveSingleQuotes() function mentioned by Adam in his first post.  This function tells CF to NOT escape any single quotes found in the string passed to the function.

Just be aware, as I said before, if you are going to pass in the entire SQL statement as a variable and that statement includes data from unsecure sources, the CFML built in tag to parametize the data <cfqueryparam...> can not be used.  It will be upon you to sanitize and secure your SQL from injection attacks.

[Adam Cameron.]

<cfqueryparam> is for passing parameters, not SQL: anything passed as a parameter is intrinsically not considered SQL, this is the entire point of parameterising SQL statements.

It might be a good idea to read up a bit:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-7c36.html

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

Editorial (ie: not specifically directed at the OP):

The chief problem (and it's a significant one) in this whole "generic SQL function" idea is that most implementations I see completely ignore the idea of separating parameters from the SQL string, which adds a fair bit of DB overhead.  And it's usually done for the reason that are cited here: to save the developer some time.  This is a pretty poor rationale for one's application architecture (IMO).  The developer only needs to write the code once... every subsequent request to the code needs to run it.  One should be looking to make economies & optimisations when executing the code, not writing it.

--

Adam

[jdhunter]

Thanks Adam for the commentary. In this case, the table structure stands a very high probability of getting modified by my end user and I needed to write a backend/frontend system that was content/structure agnostic, except for key fields like ID and a few other main fields that won't get changed. I needed to be able to allow the user to massage the data, add new data, etc without the UI needing to know anything about the fields in the table. My background is being part of a development team that wrote a very large scale program that is used to build very large scale things, like airplanes and space shuttles. Every one of our clients customizes the tables to their own specific need and our program just works and handles it flawlessly. I was simply trying to create a small scale version of this for a new project. So the concepts of what I am attempting to do have been done before in a much larger scale then what I am doing. And when the system is up and running, there isn't going to be a large amount of data, so the queries are going to fire off quickly, that part I am not worried about. Your insight on how the queryparam is used will help me out, I much appreciate it. And quite frankly, I missed the comment in the first post about the quotes, I think that one function is going to fix my problem and allow me to move forward.

Thanks everyone for your assistance! Once I test the quote function tonight I will update the status of this thread.

Jim

[Dan_Bracuk]

You can do as much or as little as you want or need to do. For example, you can do this:

WhereClause = "where 1 = 1";

if something

WhereClause = WhereClause & "

and something";

etc

You won't be able to use cfqueryparam in this situation.  That may or may not be important.  The major benefits of that tag are increased query speed and escaping special characters such as apostrophes.  If using them introduces a quantum leap in the complexity of your code, maybe it's not worth it. Here is some sample code from one of my pages.

// build sql clauses and display variables

// These are for normal clinic visits
SelectClause = "select ";  // count(registration_number) VisitCount will follow this
FromClause = "
from clinic_fact join visit using (registration_number)
join clinic using (clinic_code)";
WhereClause = "
where date between #DateObjectStart# and #DateObjectEnd#";

// these are for the Z15 visits (shorter registration on kidcom)
UnionSelectClause = "select ";  // count(registration_number) VisitCount will follow this
UnionFromClause = "from admit_clinic_visit join clinic using (clinic_code)";
UnionWhereClause = "
where date between #DateObjectStart# and #DateObjectEnd#";

// These are for both
GroupByFields = "";  // This will also serve as display fields
OrderByFields = "";
UnionQuery = true;  // gets set to false if user specifies outpatients only
ClinicNameClause = "";
ClinicGroupClause = "";
ClinicGroupList = ListQualify(ssn.ClinicGroups, "'");

if (form.groups is "All") {
WhereClause = WhereClause & "
and clinic_group_mcase in (#ClinicGroupList#)";
UnionWhereClause = UnionWhereClause & "
and clinic_group_mcase in (#ClinicGroupList#)";
}

etc

Then a couple of hundred lines later, we get this:

// assemble final query
if (UnionQuery is true and GroupByClause is not "") {
sql =
"select " & Replace(GroupByClause, 'group by', '') & ", sum(visits) ClinicVisits
from
("
& SelectClause & " count(registration_number) Visits " & FromClause & WhereClause & GroupByClause & "
union
" & UnionSelectClause & " count(registration_number) Visits " & UnionFromClause & UnionWhereClause & GroupByClause &
") x
" & GroupByClause & OrderByClause;
}
else if (UnionQuery is true) {
sql =
"select  sum(visits) ClinicVisits
from
(select count(registration_number) Visits " & FromClause & WhereClause & GroupByClause & "
union
" & UnionSelectClause & " count(registration_number) Visits " & UnionFromClause & UnionWhereClause &
") x" ;

}

else
sql = SelectClause & " count(registration_number) ClinicVisits " & FromClause & WhereClause & GroupByClause & OrderByClause;

Then this:

<cfquery name="GetResults" datasource="dw">
#PreserveSingleQuotes(sql)#
</cfquery>