cfqueryparam

<cfquery 
   name = "query_name"
   dataSource = "ds_name"
   ...other attributes...
   SQL STATEMENT column_name = 
   <cfqueryparam value = "parameter value"
      CFSQLType = "parameter type"
      maxLength = "maximum parameter length"
      scale = "number of decimal places"
      null = "Yes" or "No"
      list = "Yes" or "No"
      separator = "separator character">
   AND/OR ...additional criteria of the WHERE clause...
   </cfquery>

<!--- This example shows cfqueryparam with VALID input in Course_ID. --->
<h3>cfqueryparam Example</h3>
<cfset Course_ID = 12>
<cfquery name = "getFirst" dataSource = "cfsnippets">
   SELECT * 
   FROM courses
   WHERE Course_ID = <cfqueryPARAM value = "#Course_ID#"
   CFSQLType = "CF_SQL_INTEGER"> 
</cfquery>
<cfoutput query = "getFirst">
   <p>Course Number: #Course_ID#<br> Description: #descript#</p>
</cfoutput>

<!--- This example shows the use of CFQUERYPARAM when INVALID string data is
   in Course_ID. ----> 
<p>This example throws an error because the value passed in the CFQUERYPARAM 
tag exceeds the MAXLENGTH attribute</p> 

<cfset LastName="Peterson; DELETE employees WHERE LastName='Peterson'">
<!------- Note that for string input you must specify the MAXLENGTH attribute 
   for validation. --------------------------------------------------> 
<cfquery 
   name="getFirst" datasource="cfsnippets"> 
   SELECT * 
   FROM employees 
   WHERE LastName=<cfqueryparam 
                        value="#LastName#" 
                        cfsqltype="CF_SQL_VARCHAR" 
                        maxlength="17"> 
</cfquery> 
<cfoutput 
   query="getFirst">       <p>
      Course Number: #FirstName# #LastName# 
      Description: #Department# </p> 
</cfoutput> 

Comments

MaestroFJP said on Dec 3, 2003 at 1:43 PM :

There needs to be better documentation regarding CFSQLTYPE in the CFQUERYPARAM tag. The CFSQLTYPES don't map according across most RDBMS. There needs to be descriptions for each SQLTYPE listed in the livedocs. Sam Neff has produced a list for this problem.
This message can be seen at:
http://www.mail-archive.com/cf-talk@houseoffusion.com/msg157220.html

The list he produced can be found at:
http://www.blinex.com/~sam/CF_SQL_TYPES.cfm

WillRocks! said on Dec 3, 2003 at 5:11 PM :

I beleive there is a bug with this tag. I have a query like a this:

SELECT * FROM page_
WHERE page_page = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#page#">

It works fine, then other times it will produce an error "Unable to convert data type", I output the value to check that it's not null or something similar - it's definately not. The only way i've found to fix it is to recycle the Coldfusion MX Application Server service on the web server... Then it works fine again - until next time!

Frustrating as hell, please get back to me macromedia if you want more details. I've found nothing in the known issues for this.

No screen name said on Jan 27, 2004 at 12:09 PM :

hear, hear MaestroJP.

Thanks for the link as to the CFSQLTYPE matrix....this type of thing needs to be made available and koshered by Macromedia (and, an MS Access one added as well, since there are plenty of us using that DB)

rev_robert said on Apr 10, 2004 at 5:49 AM :

Based on the documentation and the recommendation contained within the same, I modified many of my queries to use the <cfqueryparam>. Most of these queries also used the cached-within parameter. I guess I should have tested one before changing all, but on testing I got the following error:

Using "cachedWithin" or "cachedAfter" in CFQUERY with CFQUERYPARAM is not allowed.

It would have been nice if this was stated in the documentation for <cfqueryparam>. Now I have to undo all those changes.

We trust the documentation to identify all the attributes, properties, behaviors, and limitations for tags. Failure to do so is frustrating and is a breech of trust.

Thank you.

Robert

ASandstrom said on Apr 13, 2004 at 7:04 AM :

You are correct, rev_robert. I've entered a doc bug (#55016) on this issue. Thanks for your feedback.

comcd said on May 24, 2004 at 2:16 PM :

Does this tag support the Oracle xmltype?

ASandstrom said on May 25, 2004 at 9:15 AM :

No, it doesn't support the Oracle xmltype. This has been entered as an enhancement request for consideration for a future release.

No screen name said on Jun 2, 2004 at 12:08 AM :

<cfqueryparam> doesn't support double byte character. I think it will be a great barrier to the usage on it.

Doryphores said on Jun 16, 2004 at 1:01 PM :

You shouldn't use select * in queries using the cfqueryparam tag. It can cause unexpected errors when you add, remove or modify the table's columns as explained here: http://www.1pixelout.net/index.cfm/blog/entry/cfqueryparam.htm

rippo123456 said on Jun 22, 2004 at 2:35 AM :

Can anyone explain to me the use of the maxLength attribute? I really do not see the need to use it.

It does not automatically truncate a string! so all I can work out is...

An error is thrown in coldfusion if a string exceeds the maxlength, if you omit maxLength then the error is thrown from the database if the string exceeds the maxlength of a column. Is this all it does?

halL said on Jun 22, 2004 at 6:20 AM :

The maxlength attribute ensures that the length check is done by ColdFusion before the string is sent to the DBMS.
It can help ensure security by preventing the submission of malicious strings to the DBMS.
For more information on using the maxlength attribute see http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/queryd14.htm.
For more information on using cfqueryparam, see http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/queryd11.htm and the pages that follow it. (There is also a link to the information page in the Description section of this page.)

No screen name said on Aug 11, 2004 at 7:32 AM :

If you use coldfusion variables to specify field and table names, should you use cfqueryparam for that too? Will it make a difference?

halL said on Aug 17, 2004 at 1:22 PM :

I did a quick inquiry and a quick test and it does not appear that this will work.
However, you might be able to get more information from the ColdFusion Forums, http://webforums.macromedia.com/coldfusion/.

Daveb457 said on Sep 22, 2004 at 7:33 AM :

I find this a bit short on insert queries. You speak to select queries but I can find no exeplars for an Insert query, notably how cfqueryparam is used in the values line of the query statement.

thanks!

-ian said on Oct 4, 2004 at 3:21 PM :

? using LIKE in cfqueryparam. Is it possible to have a like statement with percent signs? Works fine without %, but pt of like-stmt is to match patterns? Ex:
<cfquery name="myQry" datasource="myDS">
SELECT * FROM job WHERE title LIKE
<cfqueryparam value="%#myTitle#%" cfsqltype="cf_sql_varchar">
</cfquery>

gmorphis said on Oct 13, 2004 at 12:38 PM :

Something's messed up with cfquerparam when used within the order by SQL statement.
It was like it was ignoring the order by entirely.

<cfif isdefined("arguments.sortby") and arguments.sortby neq "">
order by <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sortby#">
</cfif>

is what I had at the bottom of my query in the CFC. There was no CF Error Message or anything to assist me in finding out where the problem was.

I took the SQL code generated by ColdFusion (in the debug section),
dumped that into Oracle and BAM ordered by correctly.
I thought something was up with my <cfoutput>
I'm using a nested <cfoutput> with the group= attribute.
I tried everything I could think of.. finally asked a friend who
fortunately for me said that he ran into problems using the
<cfqueryparam> tag in the ORDER BY statement..
I took it out and replaced with
<cfif isdefined("arguments.sortby") and arguments.sortby neq "">
order by #arguments.sortby#
</cfif>
and it worked.

No screen name said on Nov 15, 2004 at 6:37 AM :

Does anyone have answers on the issue with CFQUERYPARAM originally posted by WillRocks? We've had the same problem with our servers. The problem appears to originate when we change table structure on a large table in our database (We're running MSSQL Server 2000 Enterprise). We tested this out and as soon as we added a column to a large table (2+ million records), we immediately got CFQUERYPARAM errors (unable to convert data type). This is quite frustrating because we have 7 servers on which we have to cycle ColdFusion when this happens (we have ColdFusion MX 6.1 on each of them). I hope someone has an answer for this...

mitchelj said on Nov 15, 2004 at 8:15 AM :

Does anyone know how to use the CFQUERYPARAM tag when you want to use one of the db functions? I want to insert a date using Oracle's to_date function & use CFQUERYPARAM but I get different Oracle errors each way I try.

jrunrandy said on Nov 15, 2004 at 11:39 AM :

I checked with one of our technical people and this is what he said:
*****
Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries.

Here are three possible solutions. Try #1 first - it may be enough to fix the problem.
With #2 and #3 efficiency is reduced because the SQL statement will be compiled more often.

1. Avoid using wildcard SELECT * expressions.

The expansion of the "*" is the part of the SQL statement most likely to change when the schema is altered, making the compiled SQL statement become invalid. If the specific fields are listed (and presuming they are still valid fields after the schema change) the compiled statement might still be useable.

2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero.

3. Use inline arguments instead of <cfqueryparam> to cause the SQL statement be re-compiled by SQLServer for every request.
*****

FuseDesign said on Nov 28, 2004 at 10:21 PM :

I am under the impression that the cfqueryparam will escape the sql characters that are passed in through a variable but will not do so for strings you enter in.

For instance:

<cfset variables.string = "%">

<cfqueryparam value="%#variables.string#%">

This would search for... %\%%

but....

if it seems to seach for %%%.

Is this intended?

FuseDesign said on Nov 28, 2004 at 10:24 PM :

I forgot to mention in my above post that if I do the exact same situation but without the '%' entered in the value area like...

<cfset variables.string = "%">

<cfqueryparam value="#variables.string#">

that this seems to search for... \% and not %

This is not true with my above comment.

topol_sheap said on Mar 4, 2005 at 3:25 AM :

I've had a problem using query param... it's a bit wierd.

I have a query
select float_colum from table where id = cfqueryparam cfsqltype="CF_SQL_INTEGER" value="45" null="No"

which works OK

if I change the query:
select * from table where id = cfqueryparam cfsqltype="CF_SQL_INTEGER" value="45" null="No"

the float value get converted to an integer.

is this a bug?

I'm using sql2000 on win2k3 with mx6.1

jrunrandy said on Mar 9, 2005 at 11:17 AM :

I suppose you might call it a bug. However, it's easy to imagine the cfqueryparam might have a different processing path depending on whether you specify * or name columns specifically.

If you feel it's a bug, you can report it at http://www.macromedia.com/support/email/wishform/main.cgi

boughtonp said on Jun 9, 2005 at 8:38 AM :

It would be really nice if there could be a function version of this tag.

Consider:
---
INSERT
INTO member
(title, forename, surname)
VALUES
(
<cfqueryparam value="#Arguments.title#"
cfsqltype="CF_SQL_CHAR" maxlength="15"/>,
<cfqueryparam value="#Arguments.forename#"
cfsqltype="CF_SQL_CHAR" maxlength="30"/>,
<cfqueryparam value="#Arguments.surname#"
cfsqltype="CF_SQL_CHAR" maxlength="30"/>
)
---

compared to:

---
INSERT
INTO member
(title, forename, surname)
VALUES
(
#QueryParam(Arguments.title, 'CHAR', 15)#,
#QueryParam(Arguments.forename,'CHAR', 30)#,
#QueryParam(Arguments.surname, 'CHAR', 30)#
)
---

Much much nicer, easier to read and understand, and doesn't have such a significant impact on the width/height of large queries...

jrunrandy said on Jul 18, 2005 at 2:06 PM :

Great idea! I entered enhancement request 60636 into our system. FYI, you can enter enhancement requests yourself through http://www.macromedia.com/go/wish/.

No screen name said on Jul 27, 2005 at 11:32 AM :

I found that the bug is caused by using a return immediately after a star. So instead of the following:

SELECT Note.*,
Name.FirstName
FROM Note
INNER JOIN Note ON Name.ID = Note.CreatedByUserID
WHERE Name.ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">

I used the following to workaround the problem:

SELECT Note.*, Name.FirstName
FROM Note
INNER JOIN Note ON Name.ID = Note.CreatedByUserID
WHERE Name.ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">

It seemed to have worked for the one instance I had the problem. I'm not sure if the problem will return.

dorange said on Aug 24, 2005 at 1:27 PM :

I didn't try other databases but this tag doesn't support double-byte characters on MS SQL 2000. MSSQL is peculiar in that it requires prefixing double byte text with an N, for example:
...
SET dbtxt= N'double-foo-text'
....
When i put this in cfqueryparam, it inserts N and quotes and text. Well, documents should say something about usage with double-byte chars. If tag doesn't support them, docs should say so.

ASandstrom said on Aug 25, 2005 at 7:58 AM :

In response to dorange;
You might try enabling Unicode for the data source.
To do so:
1. Open the ColdFusion Administrator.
2. Go to the data sources page.
3. Click the name of the data source.
4. Click Show Advanced Settings.
5. Check the “Enable Unicode for data sources configured for non-Latin characters” check box.
6. Click Submit.

No screen name said on Sep 2, 2005 at 6:37 AM :

the cfqueryparam tag causes an 'Invalid parameter binding(s)' error, when used on an update query to set fields. The update query works fine if i just pass in the variables normally, but the error occurs when I use the cfqueryparam tag. Also, I can use the cfqueryparam tag on the 'wehere' clause in the query with no problems. It is just on the 'set' clauses that the error occurs.

GregorC said on Apr 4, 2006 at 7:38 PM :

LIKE Workaround for CFQueryParam


DECLARE @tempDriverName varchar(100);
SET @tempDriverName = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.DriverName#" maxlength="100">;

SELECT ...
WHERE sr.DriverName LIKE '%' + @tempDriverName + '%'

No screen name said on Apr 26, 2006 at 8:38 AM :

MS SQl Server 2000 mappings of data type to CFSQLTYPE:

cf_sql_bigint bigint
cf_sql_bit bit
cf_sql_char char, nchar
cf_sql_date [smalldatetime]
cf_sql_decimal numeric, decimal
cf_sql_double double, float
cf_sql_float double, float
cf_sql_idstamp timestamp
cf_sql_integer int
cf_sql_longvarchar text
cf_sql_money money
cf_sql_money4 smallmoney
cf_sql_numeric numeric, decimal
cf_sql_real real
cf_sql_refcursor cursor
cf_sql_smallint smallint
cf_sql_time
cf_sql_timestamp datetime
cf_sql_tinyint tinyint
cf_sql_varchar varchar, nvarchar,uniqueidentifier


This can be useful for people who are confused with DATETIME and etc...

heashon2000 said on Jun 7, 2006 at 9:39 AM :

I've also recently experienced something in addition to the wildcard (select *) bug. Even if you have explicitly defined your column list in your SQL statement (stored procedure or otherwise), and append a column to that list, the use of <cfqueryparam will still retrieve the old columnlist. I'm going to guess that it's really the way that <cfqueryparam uses the bind variables in certain DB's which retrieve an existing execution plan for the given query. In this case the use of explicitly named columns still causes and error when adding a column for use in a CFQUERY resultset.

timschot said on Sep 4, 2007 at 1:36 PM :

To gregorC's comment on cfqueryparam LIKE clause workaround... that's a bit overkill.

You can just do:

SELECT ...
WHERE sr.DriverName LIKE '%' + <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.DriverName#" maxlength="100" /> + '%'

CF just appends another variable declaration to yours.. so it's a bit redundant.