Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Why use CFQUERYPARAM?

Posted on 2003-03-26
9
Medium Priority
?
702 Views
Last Modified: 2013-12-20
I am a self-taught coldfusion website designer for my company, and although I have learned a huge amount about coldfusion, I have basically learned specific things I need to know to design my specific site and I find that there are "holes" or "foggy" areas in my coldfusion knowledge.

One of these is the tag CFQUERYPARAM.  It has been suggested to me to use this tag in every CFQUERY tag that has a variable passed to it.  The reasons I was given were for security purposes as well as speed, which sounds good.  I was just wondering if someone could explain this tag in more detail.  I have incorporated it into my code, I just want to understand why.

How does it improve the security of my site?
Should I use it in ALL my CFQUERY tags?
Does it actually speed things up?

Thanks

Max
0
Comment
Question by:MaxwellTurner
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 1

Accepted Solution

by:
ndintenfass earned 500 total points
ID: 8215228
The primary security issue is when you include user-generated variables in your query.  For instance, if you had a URL like this:

http://domain.com/yourpage.cfm?id=1234

And then you have a query like this:

SELECT * FROM table WHERE id = #url.id#

The user can then put SQL into the id variable.  Most RDBMS software allows you to pass multiple queries at once -- so, the end user could, for instance add this to the URL:

http://domain.com/yourpage.cfm?id=1234%20DELETE%20FROM%20TABLE

That would translate to:

SELECT * FROM table WHERE id = #url.id# DELETE FROM table

POOF!  Your data is gone!

CFQUERYPARAM solves this issue because it tells the database to expect just a single parameter of a specific type rather than any arbitrary SQL (which is what you get using "raw" variables in CFQUERY).

CFQUERYPARAM also allows you to define the data type, which allows for more rigorous data checking.

The speed aspect is probably less pronounced, but by doing explicit typing of the variables the database is better able to parse the query without needing to first figure out what type it's dealing with by looking at the tables.

CFQUERYPARAM can also come in handy when you are doing "Query of Query" on queries you built yourself -- allows you more control over explicit typing of your columns.
0
 
LVL 10

Expert Comment

by:substand
ID: 8215245
I never really use it, because I like to write my own validation, etc (everything the tag does)... however, here is what the writers of the tag have to say:

CFQUERYPARAM
CFQUERYPARAM checks the data type of a query parameter. The CFQUERYPARAM tag is nested within a CFQUERY tag. More specifically, it is embedded within the query SQL statement. If you specify its optional parameters, CFQUERYPARAM also performs data validation.

Note  For data, you must specify the MAXLENGTH attribute in order to ensure that maximum length validation is enforced.  



See the Usage section for details.

Syntax
<CFQUERY NAME="query_name"
    DATASOURCE="ds_name"
    ...other attributes...
>
    SELECT STATEMENT WHERE column_name=
    <CFQUERYPARAM VALUE="parameter value"
        CFSQLType="parameter type"
        MAXLENGTH="maximum parameter length"
        SCALE="number of decimal places"
        DBNAME="database name"
        NULL="Yes/No"
    >
    AND/OR ...additional criteria of the WHERE clause...
</CFQUERY>

VALUE
Required. Specifies the actual value that ColdFusion passes to the right of the comparison operator in a where clause. See Usage section for details.

CFSQLTYPE
Optional. This is the SQL type that the parameter (any type) will be bound to. The default value is CF_SQL_CHAR. The CFSQLTypes are as follows:

 CF_SQL_BIGINT  CF_SQL_IDSTAMP  CF_SQL_REFCURSOR  
CF_SQL_BIT  CF_SQL_INTEGER  CF_SQL_SMALLINT  
CF_SQL_CHAR  CF_SQL_LONGVARCHAR  CF_SQL_TIME  
CF_SQL_DATE  CF_SQL_MONEY  CF_SQL_TIMESTAMP  
CF_SQL_DECIMAL  CF_SQL_MONEY4  CF_SQL_TINYINT  
CF_SQL_DOUBLE  CF_SQL_NUMERIC  CF_SQL_VARCHAR  
CF_SQL_FLOAT  CF_SQL_REAL    


MAXLENGTH
Optional. Maximum length of the parameter. The default value is the length of the string specified in the VALUE attribute.

SCALE
Optional. Number of decimal places of the parameter. The default value is zero. Applicable for CF_SQL_NUMERIC and CF_SQL_DECIMAL.

NULL
Optional. Specify Yes or No. Indicates whether the parameter is passed as a NULL. If you specify Yes, the tag ignores the VALUE attribute. The default value is No.

Usage
The CFQUERYPARAM is designed to do the following things:

Allows the use of SQL bind parameters.
Allows long text fields to be updated from an SQL statement.
Improves performance.
The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. However, at present, the ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters.

If a database does not support bind parameters, ColdFusion still performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. The validation rules follow:

For types CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT, data values can be converted to a numeric value.
For types CF_SQL_DATE, CF_SQL_TIME and CF_SQL_TIMESTAMP, data values can be converted to a date supported by the target data source.
For all other types, if the MAXLENGTH attribute is used, data value cannot exceed the maximum length specified.
The SQL syntax generated by the ColdFusion server is dependent on the target database.

For an ODBC, DB2, or Informix data source, the generated syntax of the SQL statement is as follows:

    SELECT *
    FROM courses
    WHERE col1=?

For an Oracle 7 or Oracle 8 data source, the syntax of the SQL statement is as follows:

    SELECT *
    FROM courses
    WHERE col1=:1

For a Sybase11 data source, the syntax of the SQL statement is as follows:

    SELECT *
    FROM courses
    WHERE col1=10

Example
<!--------------------------------------------------------------------
This example shows the use of CFQUERYPARAM when valid input is given in
Course_ID.
----------------------------------------------------------------------->
<HTML>
<HEAD>
<TITLE>CFQUERYPARAM Example</TITLE>
</HEAD>

<BODY>
<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: #number#<br>
 Description: #descript#
</P>
</CFOUTPUT>
</BODY>
</HTML>

<!----------------------------------------------------------------------
This example shows the use of CFQUERYPARAM when invalid numeric data is
in Course_ID.
----------------------------------------------------------------------->
<HTML>
<HEAD>
<TITLE>CFQUERYPARAM Example</TITLE>
</HEAD>

<BODY>
<h3>CFQUERYPARAM Example With Bad Numeric Data</h3>
<CFSET Course_ID="12; DELETE courses WHERE Course_ID=20">
<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: #number#<br>
 Description: #descript#
</P>
</CFOUTPUT>
</BODY>
</HTML>


The CFQUERYPARAM tag returns the following error message when this example is executed.

VALUE
Invalid data '12; DELETE courses WHERE Course_ID=20' for
        CFSQLTYPE 'CF_SQL_INTEGER'.

<!----------------------------------------------------------------------
This example shows the use of CFQUERYPARAM when invalid string data is in
Course_ID.
----------------------------------------------------------------------->
<HTML>
<HEAD>
<TITLE>CFQUERYPARAM Example</TITLE>
</HEAD>

<BODY>
<h3>CFQUERYPARAM Example with Bad String Input</h3>

<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>
</BODY>
</HTML>

The CFQUERYPARAM tag returns the following error message when this
example is executed.
VALUE
Invalid data 'Peterson; DELETE employees WHERE
        LastName='Peterson'' value exceeds MAXLENGTH setting '17'.

0
 
LVL 1

Expert Comment

by:ndintenfass
ID: 8215270
It's worth noting, in relation to substand's comment that CFQUERYPARAM does not just do error checking -- it actually explicitly types the variable and identifies it as a parameter of the query to the underlying database engine.

Thus, using CFQUERYPARAM and doing your own type checking is not mutually exclusive.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 10

Expert Comment

by:substand
ID: 8215306
thats true, and what I posted (i think) says that.  I just meant that I prefer to do my error (as well as type) checking beforehand.

so if I don't get a SQL integer from a form field and I am expecting it, I'm not going to insert "ABC" into a field that is defined as integer.

Just my preference, however.

0
 
LVL 17

Expert Comment

by:anandkp
ID: 8215678
hi,
i will give u a small eg - which will tell u the reason why u need to use CFQUERYPARAM

have a page that accepts a querystring called as "id" with value = 1

code on the "chk.cfm" page
<cfquery name="x">
select * from usertable where userid = #id#
</cfquery>

<cfoutput>
 #x.recordcount#
</cfoutput>

now run this page on a MSSQL DB & see what happens - u ahve one record seelcted & it gets displayed as the recordcount - right ?

now since u can see the querystring in teh addressbar - chnage teh value of id from
http://localhost/test/chk.cfm?id=1
to
http://localhost/test/chk.cfm?id=1;delete from usertable

& now press <enter> key & see what happes

all teh records r delelted from the usertable - right ?

now if u had a CFQUERYPARAM - checking for the value to be NUMERIC - the string wld have had passed & the query wld have returned 0 records [avioding the delete of all reocrds from the table]

this is where teh security issue comes in [though the same may not happen on ORACLE or other DB types]

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8215689
hi,
i will give u a small eg - which will tell u the reason why u need to use CFQUERYPARAM

have a page that accepts a querystring called as "id" with value = 1

code on the "chk.cfm" page
<cfquery name="x">
select * from usertable where userid = #id#
</cfquery>

<cfoutput>
 #x.recordcount#
</cfoutput>

now run this page on a MSSQL DB & see what happens - u ahve one record seelcted & it gets displayed as the recordcount - right ?

now since u can see the querystring in teh addressbar - chnage teh value of id from
http://localhost/test/chk.cfm?id=1
to
http://localhost/test/chk.cfm?id=1;delete from usertable

& now press <enter> key & see what happes

all teh records r delelted from the usertable - right ?

now if u had a CFQUERYPARAM - checking for the value to be NUMERIC - the string wld have had passed & the query wld have returned 0 records [avioding the delete of all reocrds from the table]

this is where teh security issue comes in [though the same may not happen on ORACLE or other DB types]

K'Rgds
Anand
0
 
LVL 17

Expert Comment

by:Jesse Houwing
ID: 8217060
Appart from the error checking, cfqueryparam also allows your database server to cache execution paths, which enables it to fetch data much faster. And because the typechecking is done beforehand, the database server does noet have to do type conversions and such which also speeds up the parsing time of your statement.

The largest problem with cfqueryparam is that you cannot use it in conjunction with cached queries in coldfusion itself. In many cases cached queries also give you a large performance improvement.

You should use the cache attributes when you have statements that are executed very often in only a singular fashion.

If you have a statement that takes parameters, use cfqueryparam, unless you really need the results to be cached by coldfusion. In that case make sure you have very rigorous error checking and type validation before inserting variables directly into your SQL statements.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8218116
Well, it looks like you've already got a lot of really good comments here.

It's all about speed and security as everyone said.

As far as the speed aspect of it, using it in ALL of your queries isn't going to show a huge performance increase. The best palce to see the speed increase is to use it on a query that takes a long time to execute, and is executed using varied parameters. Such as a report (which is where I ALWAYS use cfqueryparam). You're running this HUGE SQL statement that may take a second or 2 to complete, and each time you're running it with just a few different IDs. Without using CFQUERYPARAM, the DB server thinks it's getting a different SQL statement each time it's run. But with the CFQUERYPARAM it realizes that this is a familiar SQL statement and grabs the old execution plan, and simply uses the parameters sent to it. It's kindof half way between a basic SQL statement and a stored procedure.

I've been able to shave seconds off of our extensive reports by adding it to all of the SQL statements. In some cases, cut the processing time in half. Which not only makes the page load faster for the user, but took less processing away from the DB which is already overloaded when reports are run.
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 8220558
Well I appreciate all the comments and they all deserve points, but I guess in fairness, I should give the points to the first comment that answered my question, and ndintenfass's simple example was helpful to understand the difference between using CFQUERYPARAM and not using it.  I actually have seen the examples from substand before, but they only showed invalid input for CFQUERYPARAM, not what can happen if it is not used.

If I now understand correctly, when it comes to security,  CFQUERYPARAM provides security against visitors DELETEing, INSERTing, or APPENDing my data by putting these statements within the url.  

I do have 1 quick question about your comment. When you say,

"explicitly types the variable and identifies it as a parameter of the query"

am I to understand that the variable is stored in "temporarily" in memory and can be re-used again by THAT query until the browswer is closed (laymans terms)

Thanks to all for the help, and I apologize to the others that offered their comments, but I can only accept one answer.

Max
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question