?
Solved

Why use CFQUERYPARAM?

Posted on 2003-03-26
9
Medium Priority
?
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

777 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