cfquery

How do I programatically sanitise coldfusion cfquery parameters.

I have inherited a large legacy coldfusion app. There are hundreds of <cfquery>some sql here #variable#</cfquery> statements that need to be parameterized along the lines of: <cfquery> some sql here <cfqueryparam value="#variable#"/> </cfquery> How can I go about adding parameterization programatically? I have thought about writing so...

How can I use query-of-query UNION on n-recordsets when var scoping is needed?

I would like to be able to do a query of a query to UNION an unknown number of recordset. However when doing a query-of-query dots or brackets are not allowed in record set names. For example this fails: <cfquery name="allRecs" dbtype="query"> SELECT * FROM recordset[1] UNION SELECT * FROM recordset[2] </cfquery> Using dy...

How do you use cfqueryparam in the ORDER BY clause?

I'm trying to be a good CF web developer and use <cfqueryparam> around all FORM or URL elements that make it to my SQL queries. In this case, I'm trying to allow a user to control the ORDER BY clause dynamically. <cfquery datasource="MyDSN" name="qIncidents"> SELECT IncidentID, AnimalID, IntakeDate, DxDate, OutcomeDate FROM Incide...

How to override SQL sanitization in ColdFusion

I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance. I am running into a problem because cfquery is automatically converting the single quotes to double-single-quotes. How can I override that behavior? More spe...

Can I get a query row by index in ColdFusion?

I want to get a specific row in a ColdFusion Query object without looping over it. I'd like to do something like this: <cfquery name="QueryName" datasource="ds"> SELECT * FROM tablename </cfquery> <cfset x = QueryName[5]> But it's giving me an error saying that the query isn't indexable by "5". I know for a fact that there are m...

Dynamic tablename in DAO.cfc?

I'm writing a subsystem that tables might be renamed from project to project. Instead of asking the user of my subsystem to search & replace before using it, does this work? <cfquery name="local.foo" datasource="#dsn#"> SELECT col1, col2, col3 FROM #tableName# </cfquery> Without <cfqueryparam>, will it become non-cacheable? or an...

CF Query appears to return incomplete data from text field

I'm using CF8 and SQL2000. I'm storing a bunch of HTML in a Text field in my SQL table. When I do a simple CFQUERY against that data, and CFDUMP it, it's truncated to 64000 characters. I've confirmed that my data is complete in the SQL table, by selecting the tail end of the data using SELECT Substring, and confirmed the length using...

How to print all the result without using Results.columnname in ColdFusion

How to print all the result without using Results.columnname in ColdFusion for ex:- I have <cfquery name="getProductId"> select productId from product </cfquery> In Product Table i have 2 columns with product_name and Product_id. How can I print them without using getProductId.product_name getProduc...

How do I handle null values from ColdFusion queries?

If one of the columns in the returned coldfusion query result set has a NULL, how do we check if the value of this column being NULL? Should we just say <cfif queryname.columnname[i] EQ ''> OR <cfif queryname.columnname[i] eq 'NULL'> ? ...

Strange mySQL Coldfusion Problem

Hey guys well I'm working on this system and it giving me sooo much trouble. I just want to quit at this point. Long story short I made a few changes to how a mySQL table works, I went from storing a comma separated list for a few different fields to storing the same data each in its own field. I then rewrote the code to work with the ne...

cfquery oracle stored procedure

I had to change the SQL stored procedure to ORacle stored procddure.I am able to successfully execute my modified stored procedure in oracle. But unable to obtain the query result in CF for the Oracle stored-Procedure.I have used <cfquery>. Any suggestions or tips to for using an Oracle stored proc/CF-8? ...

How to Execute 2 or more insert statements using CFQuery in coldfusion?

Is it possible to Execute 2 insert or Update Statements using cfquery? If yes how? if no, what is the best way to execute multiple queries in Coldfusion, by opening only one Connection to DB. I think every time we call cfquery we are opening new connection DB ...

Can Coldfusion use Java methods/objects to get better cfquery performance?

I am wondering if there are java methods/objects that would be alternatives to cfquery, that both allow variable sanitation, and better caching methods. I know that you can use information schema to get data types and char lengths, and then use that to validate data type & length of variables in a query. But with everyone converting to...

cfquery problem with complex update statements

Hello, I am trying to fire Update Query using cfquery like below <cfquery name = "UpdateRecord" dataSource = #DATASOURCE# username = #DBUSER# password = #DBPASSWORD# result="updateResult" > update table1 set field1=( select field1 from table2 where field3='Some Val...

mySql Query works in query browser but fails when I run in CFquery

At first I was thinking I was running into an issue with cfqueryparam and mysql. However when I change substitute them with static values I get the same error. This is stumping me, I'm too used to Microsoft SQL Server I guess. Any help would be greatly appreciated. Here's the query, this works perfectly in mySql query browser, but f...

Date Display problem in ColdFusion

When I retrived a Date field in TOAD it displayed like is '1/18/2038 9:14:07 PM', But when I rertrived in Coldfusion using cfquery and displayed using , then I got the date on screen like '2038-01-18 21:14:07.0'. Does anyone have idea why it displayed in different format? Is there anyway we can make it display like TOAD format? I am...

<cfquery> not retrieving DATA.

Hi, I am unable to retrieve any data from my cfquery. Same query when i run in sql developer i get the result. Any reason why ? Hi all, thanks for the responses. Sorry, it was my fault. It was a data issue. I was retrieving uncommited data from CF. ...

Combining query rows in a loop

I have the following ColdFusion 9 code: <cfloop from="1" to="#arrayLen(tagArray)#" index="i"> <cfquery name="qryGetSPFAQs" datasource="#application.datasource#"> EXEC searchFAQ '#tagArray[i]#' </cfquery> </cfloop> The EXEC executes a stored procedure on the database server, which returns rows of data, depending on what...

SQL Injection Protection for dynamic queries

The typical controls against SQL injection flaws are to use bind variables (cfqueryparam tag), validation of string data and to turn to stored procedures for the actual SQL layer. This is all fine and I agree, however what if the site is a legacy one and it features a lot of dynamic queries. Then, rewriting all the queries is a herculean...

Using cachedwithin attibute inside cfquery

When you use the cachedwithin attribute in a cfquery how does it store the query in memory. Does it store it by only the name you assign to the query? For example, if on my index page I cache a query for an hour and name it getPeople will a query with the same name on a different page (or the same page for that matter) use the cached res...