Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Faster and Safer Database Queries Using the <CFQUERYPARAM> Tag

Faster and Safer Database Queries Using the <CFQUERYPARAM> Tag

Databases and database access are fundamental elements of just about every ColdFusion application ever created. Database access makes applications real and live and dynamic and valuable, but it's also a major source of performance problems and a primary potential security target. In this article I discuss an oft overlooked tag, <CFQUERYPARAM>, designed to help address both potential problems.

Understanding <CFQUERYPARAM>
Database access (queries, insertions, updates, etc.) occurs by issuing SQL statements - plain text strings containing combinations of special statements, keywords, clauses, operators, and more. These strings are passed to the DBMS for processing (usually via a database driver) and are sometimes referred to as being ad hoc.

So far so good. But this is where it gets a little complicated. There are an infinite number of possible SQL statements (when you take into account the values passed to them), so once a DBMS receives a SQL statement, the DBMS must first analyze it to determine if it's valid, and then work out the best way to process it. Often, the process of parsing, validating, and analyzing the query (the term query refers to all SQL queries, even if they're not SELECT query operations) takes longer than processing the query itself.

Once the query is validated and analyzed, the DBMS tries to cache it so that the process won't need to be repeated unnecessarily. Simple queries without dynamic elements, like the following one, can be cached safely and easily:

SELECT *
FROM Customers

However, queries containing other clauses, like the one that follows, are a little harder to cache. After all, with an infinite number of variations it wouldn't be practical for the DBMS to cache them all:

SELECT *
FROM Customers
WHERE CustID=100

To cache this type of query the DBMS needs to know which part of the statement is dynamic and which is not, and that's where <CFQUERYPARAM> comes into play.

<CFQUERYPARAM> is a tag used within SQL statements (inside your <CFQUERY>). It replaces parameters (or any passed values) with placeholders that indicate the dynamic portions of a statement. For example:

<CFQUERY ...>
SELECT *
FROM Customers
WHERE CustID=<CFQUERYPARAM
CFSQLTYPE="CF_SQL_INTEGER"
VALUE="100">
</CFQUERY>

The <CFQUERYPARAM> tag replaces the passed value (100), and does two things: it defines the type of data that will be passed here and then passes the value to be used.

The actual SQL generated by this query will differ from DBMS to DBMS, but that's somewhat irrelevant. The important thing is that the DBMS knows that the query containing this block of text won't change (and can therefore be cached):

SELECT *
FROM Customers
WHERE CustID=

In DBMSese these variable portions of SQL statements, the ones bookmarked with <CFQUERYPARAM>, are known as bind parameters.

Specifying SQL Types
The data type passed to CFSQLTYPE tells the DBMS to expect data of a particular type. The supported types are:

 

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

    CFSQLTYPE is an optional at-tribute, but as a rule you should always specify an explicit type. The default value is CF_SQL_CHAR (a string) and is frequently not what you need.

    Increased Performance
    As already implied, the first benefit of using <CFQUERYPARAM> is increased performance. If the DBMS doesn't have to parse, analyze, and validate as much text, it'll be able to respond to requests quicker and more efficiently.

    It's common knowledge that stored procedures execute faster than ad hoc SQL statements. Part of the reason for this is that DBMSs cache the compiled stored procedures so they won't need to be processed repeatedly and unnecessarily. By using <CFQUERYPARAM> you get some of the benefits of stored procedures without having to write one.

    If you're interested in determining what the actual performance benefit is, turn on ColdFusion debugging output and try the queries both ways. As the DBMS load increases, so should the benefit of using bind parameters.

    Protecting from Malicious URL Tampering
    Note: As a rule I'm staunchly opposed to documenting security issues and how they're used, but as this particular vulnerability has been documented extensively (there are even Knowledge Base articles about it at www.macromedia.com) I'm making an exception this time.

    Another benefit to using <CF-QUERYPARAM> is security against malicious URL tampering. Consider the following example, an adaptation of the one used previously:

    SELECT *
    FROM Customers
    WHERE CustID=#URL.custid#

    Here a WHERE clause is populated dynamically using a URL parameter. This type of code is common and popular, and is used any time data drilldown of any kind is used. If the URL was:

    http://domain/path/file.cfm?custid=100

    the resulting SQL statement would be:

    SELECT *
    FROM Customers
    WHERE CustID=100

    But what if someone tampered with that URL so that it read:

    http://domain/path/file.cfm?custid=100;DELETE+Customers

    The resulting SQL would be:

    SELECT *
    FROM Customers
    WHERE CustID=100;
    DELETE Customers

    Depending on the DBMS being used, the <CFQUERY> could end up executing two statements - the SELECT and then DELETE Customers (which would promptly delete all data from the Customers table).

    Scared? You should be. As I mentioned earlier SQL statements are not just used for queries. They're also used to create and drop tables, create user logins, change DBMS passwords, set security levels, manage scheduled events, even create and drop entire databases. All features supported by your DBMS may be accessible this way.

    Before I go further I must point out that this is not a ColdFusion vulnerability; it's not even a bug or a hole. This is truly a feature - many DBMSs do indeed allow queries to contain more than a single operation; this is legal and by design.

    Of course, you should always be checking parameters before passing them to your DBMS. Passing URL parameters through unchecked is programmatic suicide. As such, you should already be using code like this:

    <CFIF IsDefined("URL.CustID")
    AND NOT IsNumeric(URL.CustID)>
    ... throw an error or something ...
    </CFIF>

    But <CFQUERYPARAM> provides one extra line of defense. If the previous tampered URL was passed to the following query, the value would be rejected and an error thrown. The CFSQLTYPE value also doubles as a datatype validation check, and values that don't match the type are rejected. Using <CFQUERYPARAM> you can secure yourself against one of the oldest and best-known hacks in the book.

    Note: This type of hack is one of the reasons you should never configure ColdFusion to use administrative accounts for database access (the SA account, for example). As a rule, the login specified in the data source definition should have just the access it needs and nothing more. If administrative access is not available to a login, then some of the more dangerous commands wouldn't be executable.

    Summary
    <CFQUERYPARAM> is not a new tag; it's been in ColdFusion since version 4.5. However, many developers have yet to use <CFQUERYPARAM>, primarily because they don't understand it. Most good DBMSs (including SQL Server and Oracle) support the use of bind parameters, but some don't. Be sure to read the docs on this tag, and then start plugging it into any new code and even existing apps. With increased performance and improved security you'd be remiss not to.

  • More Stories By Ben Forta

    Ben Forta is Adobe's Senior Technical Evangelist. In that capacity he spends a considerable amount of time talking and writing about Adobe products (with an emphasis on ColdFusion and Flex), and providing feedback to help shape the future direction of the products. By the way, if you are not yet a ColdFusion user, you should be. It is an incredible product, and is truly deserving of all the praise it has been receiving. In a prior life he was a ColdFusion customer (he wrote one of the first large high visibility web sites using the product) and was so impressed he ended up working for the company that created it (Allaire). Ben is also the author of books on ColdFusion, SQL, Windows 2000, JSP, WAP, Regular Expressions, and more. Before joining Adobe (well, Allaire actually, and then Macromedia and Allaire merged, and then Adobe bought Macromedia) he helped found a company called Car.com which provides automotive services (buy a car, sell a car, etc) over the Web. Car.com (including Stoneage) is one of the largest automotive web sites out there, was written entirely in ColdFusion, and is now owned by Auto-By-Tel.

    Comments (7) View Comments

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    Most Recent Comments
    fgfrs 05/05/03 03:12:00 PM EDT

    b nv mn m

    Srinivasa 08/22/02 04:02:00 AM EDT
    Mo Hadi 02/13/02 12:07:00 PM EST

    There must be some kind of special set up necessary to do this, because I get the following error when after I change my code to use CFQUERYPARAM. Am I right?

    ODBC Error Code = S1C00 (Driver not capable)

    [Microsoft][ODBC SQL Server Driver]Optional feature not implemented

    David Shadovitz 02/12/02 03:28:00 PM EST

    Oops. CFDJ's code cut out my example CFML. Let's see if this gets through:

    WHERE id IN (<cfqueryparam value="#Form.ID#" cfsqltype="CF_SQL_INTEGER" list="yes">)

    David Shadovitz 02/12/02 03:25:00 PM EST

    As BF mentions, the default value of cfsqltype is CF_SQL_CHAR.

    If you get the error "ODBC Error Code = S1104 (Invalid precision value)", try using CF_SQL_VARCHAR instead.

    David Shadovitz 02/12/02 03:22:00 PM EST

    It's worth mentioning that CFQUERYPARAM can also be used with a list of values:

    ... WHERE ID IN ()

    Brad Howerter 02/12/02 01:06:00 PM EST

    Another benefit of is that you don't have to mess with preserveSingleQuotes().

    @ThingsExpo Stories
    "Akvelon is a software development company and we also provide consultancy services to folks who are looking to scale or accelerate their engineering roadmaps," explained Jeremiah Mothersell, Marketing Manager at Akvelon, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    "MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...
    "IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
    SYS-CON Events announced today that Telecom Reseller has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
    Large industrial manufacturing organizations are adopting the agile principles of cloud software companies. The industrial manufacturing development process has not scaled over time. Now that design CAD teams are geographically distributed, centralizing their work is key. With large multi-gigabyte projects, outdated tools have stifled industrial team agility, time-to-market milestones, and impacted P&L stakeholders.
    "Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
    "Cloud Academy is an enterprise training platform for the cloud, specifically public clouds. We offer guided learning experiences on AWS, Azure, Google Cloud and all the surrounding methodologies and technologies that you need to know and your teams need to know in order to leverage the full benefits of the cloud," explained Alex Brower, VP of Marketing at Cloud Academy, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clar...
    In his session at 21st Cloud Expo, Carl J. Levine, Senior Technical Evangelist for NS1, will objectively discuss how DNS is used to solve Digital Transformation challenges in large SaaS applications, CDNs, AdTech platforms, and other demanding use cases. Carl J. Levine is the Senior Technical Evangelist for NS1. A veteran of the Internet Infrastructure space, he has over a decade of experience with startups, networking protocols and Internet infrastructure, combined with the unique ability to it...
    It is of utmost importance for the future success of WebRTC to ensure that interoperability is operational between web browsers and any WebRTC-compliant client. To be guaranteed as operational and effective, interoperability must be tested extensively by establishing WebRTC data and media connections between different web browsers running on different devices and operating systems. In his session at WebRTC Summit at @ThingsExpo, Dr. Alex Gouaillard, CEO and Founder of CoSMo Software, presented ...
    "There's plenty of bandwidth out there but it's never in the right place. So what Cedexis does is uses data to work out the best pathways to get data from the origin to the person who wants to get it," explained Simon Jones, Evangelist and Head of Marketing at Cedexis, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
    WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, introduced two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a multip...
    Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...
    A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, whic...
    SYS-CON Events announced today that Evatronix will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Evatronix SA offers comprehensive solutions in the design and implementation of electronic systems, in CAD / CAM deployment, and also is a designer and manufacturer of advanced 3D scanners for professional applications.
    Leading companies, from the Global Fortune 500 to the smallest companies, are adopting hybrid cloud as the path to business advantage. Hybrid cloud depends on cloud services and on-premises infrastructure working in unison. Successful implementations require new levels of data mobility, enabled by an automated and seamless flow across on-premises and cloud resources. In his general session at 21st Cloud Expo, Greg Tevis, an IBM Storage Software Technical Strategist and Customer Solution Architec...
    To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. In his session at @BigDataExpo, Jack Norris, Senior Vice President, Data and Applications at MapR Technologies, reviewed best practices to ...
    An increasing number of companies are creating products that combine data with analytical capabilities. Running interactive queries on Big Data requires complex architectures to store and query data effectively, typically involving data streams, an choosing efficient file format/database and multiple independent systems that are tied together through custom-engineered pipelines. In his session at @BigDataExpo at @ThingsExpo, Tomer Levi, a senior software engineer at Intel’s Advanced Analytics gr...
    When talking IoT we often focus on the devices, the sensors, the hardware itself. The new smart appliances, the new smart or self-driving cars (which are amalgamations of many ‘things’). When we are looking at the world of IoT, we should take a step back, look at the big picture. What value are these devices providing? IoT is not about the devices, it’s about the data consumed and generated. The devices are tools, mechanisms, conduits. In his session at Internet of Things at Cloud Expo | DXWor...