CachedWithin not allowed with cfqueryparam 4

[DrumAt5280]

I get the following error:
"Using "cachedWithin" or "cachedAfter" in CFQUERY with CFQUERYPARAM is not allowed."

With this query within a CFC:
<cfquery name="GetCs" datasource="#DSN#" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
SELECT csId, csName, stateAbv
FROM Cs INNER JOIN State ON State.stateId = Cs.stateFk
WHERE csName = <cfqueryparam value='#secondDomain#' cfsqltype='cf_sql_char'>
</cfquery>

I understand that using cfqueryparam is good for security, why won't CF let me use it within my CFC?

 

[ecobb]

As the error says, cachedWithin can't be used with cfqueryparam, mainly becuase cachedWithin will store the entire query in memory, while cfqueryparam will store part of the query and use the database's bind variables for the param part. cachedWithin is basically used for a query that's not going to change, so it can be cached since it's going to be pulling the same results each time it's run.

I know it's a pain, I wish they worked together too, but basically CF sees it as "Store this entire query in memory...no, wait...let's just store part of it and keep changing the rest..." It doesn't like that.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

nice explanation, ecar

:star:

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

nice "breaking it down barney style"

you should FAQ that.

Beware of programmers who carry screwdrivers.

 

[ecobb]

Thanks guys!

Hmmm...maybe it is FAQ worthy, I had never thought of that, thanks for the suggestion.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[DrumAt5280]

Thanks for the detailed answer.

Since i need the caching, i am going to drop the CFQUERYPARAM. All my queries are within a CFC.

How worried should i be that i am not using CFQUERYPARAM within a CFC in terms of security?

 

[TruthInSatire]

well you can write your own cfqueryParam-like script that will validate the data. the only thing you CAN'T do that cfqueryparam does is hide the values incase of an error.

however you could check the len of a string, or even type... do some simple validation before running the query. there are more ways than cfqueryparam to prevent sql injunction.

<cfif len(trim(secondDomain) lte 20 and find(".", secondDomain)> <!--- and any other validation here --->
<cfquery name="GetCs" datasource="#DSN#" CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
   SELECT csId, csName, stateAbv
   FROM Cs INNER JOIN State ON State.stateId = Cs.stateFk
   WHERE csName = '#secondDomain#'>
</cfquery>
<cfelse>
quit tryin' to mess with my db you jerk
</cfif>

Beware of programmers who carry screwdrivers.

 

[TruthInSatire]

also wrap the query in try/catch and provide a custom error message if you have any errors as to not display the query.

Beware of programmers who carry screwdrivers.

 

[DrumAt5280]

Thanks!

I must say you guys amaze me on your depth of CF knowledge!

I gotta think that all of you "do" CF for a living. For me it is just a fun hobby.

Maybe some day i will get rich off my websites and then i can work on CF all day you you guys.

 

[TruthInSatire]

it's all i've done all day for about 3 years now. AND i do it for fun when I get home. hehe

simulatedFun <-- geek

Beware of programmers who carry screwdrivers.

 

[DrumAt5280]

I you don't mind me asking, what industry are you using CF in?

For instance, do you have clients you do web sites for, or do you only work on your own sites, or do you work in a company with several other CF people?

Just curious what environment you are in to assemble so much CF know how.

I am just by myself with my own 2 sites working on them a night after the kids go to bed.

 

[TruthInSatire]

This should be a new thread but, ok.

At home I have my personal site and 5 sites I designed from the ground up with small to mid-sized CF CMS'. I have a huge CF CMS @

http://www.cannedradio.com

I work full time as a CF developer for the Department of Navy. CF is very popular in government and military intranets and internet websites. .net takes 2nd place.

Beware of programmers who carry screwdrivers.