cfqueryparam in cfset

Bagoose wrote:
<cfquery...>
#selectStatement# #whereStatement#
</cfquery>

But I can't use the cfqueryparam tag in the cfset tag. It busts. I can't do :

<cfset whereStatement= "where id = <cfqueryparam value="#form.id#" cfsqltype="CF_SQL_NUMERIC"> ">

What's a good work around for me. I want to prevent sql injection attacks, but I create my sql as variables outside the cfquery tag.

Putting a Coldfusion tag within quotes like this "<cfqueryparam>" defangs it. It no longer works. The one for the select statement works because it's just text. A good workaround is to do the obvious:

<cfquery...>
#selectStatement# where id = <cfqueryparam value="#form.id#" cfsqltype="CF_SQL_NUMERIC">
</cfquery

All of Adam's explanation is of course, correct. But it does not necessarily mean that one must absolutely use cfqueryparam 100% of the time.

The situations I alluded to before are not straightforward. My approach results in simpler code. There are times when that is more important than efficient code.

> the cfquery tag. Like the op, I have never gotten a cfqueryparam tag to work
> when doing this.
>
> But I don't worry about it.

Well you should, but not for the reasons you're worried about. Err, I
mean...

>
> I've tested various ways of injecting sql

But this is not the chief reason one would want to be using <cfqueryparam>
tags.

If one passes parameters to the DB AS parameters, rather than embedded in
the SQL string, then it saves the DB a fair chunk of work and RAM. Every
query executed by the DB needs to be first compiled, and then subsequently
checked again each time its executed. The compilation process is pretty
slow, as all dependencies need to be checked, and an execution plan
written. The subsequent validation is fairly quick (and unavoidable).

So consider two queries:

SELECT col FROM tbl WHERE id = 1;
SELECT col FROM tbl WHERE id = 2;

These are two different queries, need separate compilation, validation,
execution plan, and both need to be stored in RAM. Performance hit and DB
bloat.

Now consider how many different versions of that sort of query one's going
to have lying around in the DB... one for every ID value.

Consider one different query:

SELECT col FROM tbl WHERE id = {parameter}

This needs to be compiled and validated ONCE, then reused for any possible
{parameter} value. Because the value being passed in as been identified as
being that: just a value, the DB server KNOWS that there's not going to be
any need to recompile anything; the only check needing to be made is the
datatype of the value.

The fact that this protects against SQL injection is a *side effect* of the
main reason to use <crfqueryparam> tags; not the main reason.

--
Adam

When I'm processing a wierd and wonderful form, I do exactly what the op is attempting. I do all the logic up front and build variables that get used in the cfquery tag. Like the op, I have never gotten a cfqueryparam tag to work when doing this.

But I don't worry about it.

I've tested various ways of injecting sql and the only time I could get it to execute was when using MS Sql and a numeric field. Any sql that was submitted to a char or varchar field would be treated as text. And as far as those numeric fields go, it's pretty simple to ensure that you are receiving numbers when you expect numbers.

I've also done some other testing and can tell you that cfqueryparam by itself does not protect you from cross site scripting attacks. But you all knew that, right?

Bagoose wrote:
> Funny.

But true.


> This type of technique is useful in eliminating having to maintain code that
> does pretty much the same thing existing in more than one place.

At the cost of significant complexity.


> Anyways, I hope there is a work around. In java with hibernate I use a map of
> parameters and then a .setProperties() function on the hql to do it.

So why not use hibernate with your CF code and stop writing your own SQL
altogether?

Jochem


--
Jochem van Dieten
Adobe Community Expert for ColdFusion

> Ok, I am finding that many of my queries are cfqueryparamable, but not without
> some painful work arounds. Consider this one.

[etc]

I don't see where you're seeing any complexity or need for duplication
here.

<!--- validate URL params --->
<cfparam name="URL.sort1" default="lastname" type="regex"
pattern="id|firstname|lastname">
<cfparam name="URL.sort2" default="firstname" type="regex"
pattern="id|firstname|lastname">
<cfparam name="URL.sort3" default="id" type="regex"
pattern="id|firstname|lastname">
<cfset urlRest = "&sort2=#URL.sort1#&sort3=#URL.sort2#">

<!--- fetch data --->
<cfquery name="qGridData" dbtype="query" result="st">
select id, lastname, firstname
from users
order by #URL.sort1#
<cfif URL.sort2 neq URL.sort1>
, #URL.sort2#
</cfif>
<cfif not listFindNoCase("#URL.sort1#,#URL.sort2#", URL.sort3)>
, #URL.sort3#
</cfif>
</cfquery>

<!--- display data --->
<cfoutput>
<table>
<thead>
<tr>
<th><a href="#CGI.script_name#?sort1=id#urlRest#">ID</a></th>
<th><a href="#CGI.script_name#?sort1=lastname#urlRest#">Last
Name</a></th>
<th><a href="#CGI.script_name#?sort1=firstname#urlRest#">First
Name</a></th>
</tr>
</thead>
<tbody>
<cfloop query="qGridData">
<tr>
<td>#id#</td><td>#lastname#</td><td>#firstname#</td>
</tr>
</cfloop>
</tbody>
</table>
</cfoutput>

Of course one would not normally have all that on the one template: instead
separating it out into discrete M, V and C components. But you get my
point.

I think your problem here is that you're NOT attempting to separate things
out into MVC tiers, instead trying to mishmash everything together, simply
working from top to bottom of your requirements as it occurs to you: "oh, I
need some output... [starts writing output code]... uh oh, I need some data
... [fetches some data]... right, back to the rest of the output...". This
is really bad practice.

You should NOT be outputting your <th> tags, then fetch your data, then go
back to doing the rest of your table.

Even preserving your own sense of logic in combining your URL handler with
your ordering handler to save yourself duplicating some <cfif> statements,
all you need to do is to move that logic into <cfquery> tag, and move the
<cfquery> tag out from in the middle of your output:

<cfquery>
select id, lastname, firstname
from users
order by

<cfif sort1 neq "" and sort2 neq "">
#url.sort1#, #url.sort2#
<cfset url="&sort2=#url.sort1#&sort3=#url.sort2#">
<cfelseif sort1 neq "" >
#url.sort1#
<cfset url="&sort2=#url.sort1#">
</cfif>
</cfquery>

<!--- output code here --->

I hasten to add... that's awful code, but it's better than what you were
suggesting.

--
Adam

whoops, please excuse somewhat erronious code...

Ok, I am finding that many of my queries are cfqueryparamable, but not without some painful work arounds. Consider this one.

I have a grid with 3 columns, "id, firstname, lastname"

I allow the users to click on the title of the column to sort by that column

The url to sort by the id column would look like this:

index.cfm?sort1=id&sort2=#url.sort1#&sort3=#url.sort2#

after that url is generated the query has to have the select statement like this:

select id, firstname, lastname from users order by #url.sort1#, #ur.sort2#

The thing is of course, the links I just mentioned need to be generated before the query is, but they use the same information.

So I do something like this above the <table>

<cfset orderBy="order by lastname, firstname, id">
<cfif url.sort1 neq "" and url.sort2 neq "">
<cfset orderBy=" order by #url.sort1#, #url.sort2#">
<cfset url="sort2=#url.sort1#&sort3=#url.sort2#">
<cfelseif sort1 neq "" >
<cfset orderBy=" order by #url.sort1#">
<cfset url="sort2=#url.sort1#">
</cfif>


Then I can create my links:
<th><a href="index.cfm?sort1=id&#url#">Id</a></th>
<th><a href="index.cfm?sort1=lastname&#url#">LastName</a></th>
etc

And then below that, when I want to iterate thru my data, I make my cfquery like this:

select id, lastname, firstname from users #orderBy#

So since I need the same logic to produce the url and the orderBy statement, I don't want to reproduce it once for each. But apparently I'll need to because I cant untaint my url variables outside the query tag.

I'm sure I'll come up with some more examples of where building sql outside of the query is useful if this thread stays active enough.

Thanks for your thoughts. My dba's and other developers are recommending I not use stored procedures here.

> This type of technique is useful in eliminating having to maintain code that
> does pretty much the same thing existing in more than one place. These
> techniques make it so don't have to go looking around for all the places
> something gets done.

How do you figure that? You either have a place for your insert and update
(as per your example) <cfquery> tags, or you have a place for your insert
or update strings. It's still two places. Oh, except you still need a
place for the <cfquery>#mySqlString#</cfquery>, so that's MORE places in
your model than "just do it using inline-SQL within the <cfquery>".

Obviously separating presentation logic from DB access is a good thing, but
your justification for separating SQL-string-creation from the queries
executing the SQL is nowt to do with that.

Back to your original issue: no, you cannot have a run-time variable
containing compile-time source code. How would you expect that to work?

You should run with procedures, as per Phil's suggestion.

--
Adam

Funny.

This type of technique is useful in eliminating having to maintain code that does pretty much the same thing existing in more than one place. These techniques make it so don't have to go looking around for all the places something gets done.

Say you have a user registration screen. Do you want to have two files: one for when a user is registering and another for when they are editing their registration information later? If some one wants to change the registration screen, they'll have to make sure to go to both files.

Then on the back end, do you want to have two files: one that saves the edits and one that adds the new user? better make sure any changes to the front end get made to both back end files.

Over the course of an application where there are many more data types than user registration information, it can get very difficult to manage all those extra files.

Anyways, I hope there is a work around. In java with hibernate I use a map of parameters and then a .setProperties() function on the hql to do it. No problem.

This sure is going to make for some very lonnng cfquery tag bodies.