CF Query in Function

[fobos]

Hello,
well i have a problem, i was wondering if there is a way to return a query in a function?
This is what i have so far, but i get an error.

<cffunction name="select_query" access="public" returntype="any">
    <cfargument name="as" type="string" required="yes">
    <cfargument name="status" type="string" required="yes">
    <cfset var sql = "">
    <cfset sql = "SELECT count(status) AS " & ARGUMENTS.as & " FROM table WHERE status = '" & ARGUMENTS.status 7 "'">
    <cfquery name="get" datasource="Database">#sql#</cfquery>
    <cfreturn get>
</cffunction>

<cfoutput>#select_query('New','New')#</cfoutput>

What im trying to do is return a number from the count statement, but im getting an error saying "You have an error in your SQL syntax; use right syntax near 'new''' at line 1. I didnt know why it has 3 apostrophes after new, so i tried deleting the apostrophes in the sql statement and i got the same error but now it had 'new' not 'new'''. How can i do this? Thanks for any help.

[arrgh]

The problem is dynamic sql. Save yourself some headaches and don't use dynamic sql. It's never as easy as it looks ;-) Plus it's hard to debug and is a big sql injection risk if done wrong ...

But .. it doesn't look like you even need to return the query. You can just return the count. But dont' forget to VAR your cfquery's and use cfqueryparam

...
   <cfset var qry = "">
   <cfquery name="qry" ...>
      SELECT  count(status) AS Result 
      FROM    table 
      WHERE   status = <cfqueryparam value="#arguments.status#" cfsqltype="cf_sql_varchar">
   </cfquery>
   <cfreturn qry.Result>
...

I didnt know why it has 3 apostrophes

CF escapes single quotes to protect you from sql injection. The only way around it is to use PreserveSingleQuotes(). But then you're at risk for sql injection. Since you don't seem to need dynamic sql anyway, my advice is to avoid it altogether.

[fobos]

Well i did avoid this all together, because i didnt know how to do it this way. Basically i used 3 query statemets vice a function. I just wanted to learn different ways on how do things so im not tied down to one way. Ill try what you have posted, but if sql injection is a major factor then ill just forget about it and use what i have. I really appreciate the help that you provide for me everytime i post a problem.

[arrgh]

I just wanted to learn different ways on how do things so im not tied down to one way. Ill try what you have posted, but if sql injection is a major factor then ill just forget about it and use what i have.

That's always a good attitude to have. But with this one I'd say the lesson learned is "don't do it" ;-)

Every time I use dynamic sql - I end up regretting it. Though it's possible to make it safe from sql injection ... it takes a lot of work. Much more than with non-dynamic sql. Plus debugging syntax errors can give you a major migraine!