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:

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:

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:

FROM Customers

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):

FROM Customers

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:



    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:


    the resulting SQL statement would be:

    SELECT *
    FROM Customers
    WHERE CustID=100

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


    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 ...

    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.

    <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
    Web Real-Time Communication APIs have quickly revolutionized what browsers are capable of. In addition to video and audio streams, we can now bi-directionally send arbitrary data over WebRTC's PeerConnection Data Channels. With the advent of Progressive Web Apps and new hardware APIs such as WebBluetooh and WebUSB, we can finally enable users to stitch together the Internet of Things directly from their browsers while communicating privately and securely in a decentralized way.
    Amazon has gradually rolled out parts of its IoT offerings, but these are just the tip of the iceberg. In addition to optimizing their backend AWS offerings, Amazon is laying the ground work to be a major force in IoT - especially in the connected home and office. In his session at @ThingsExpo, Chris Kocher, founder and managing director of Grey Heron, explained how Amazon is extending its reach to become a major force in IoT by building on its dominant cloud IoT platform, its Dash Button strat...
    Two weeks ago (November 3-5), I attended the Cloud Expo Silicon Valley as a speaker, where I presented on the security and privacy due diligence requirements for cloud solutions. Cloud security is a topical issue for every CIO, CISO, and technology buyer. Decision-makers are always looking for insights on how to mitigate the security risks of implementing and using cloud solutions. Based on the presentation topics covered at the conference, as well as the general discussions heard between sessi...
    For basic one-to-one voice or video calling solutions, WebRTC has proven to be a very powerful technology. Although WebRTC’s core functionality is to provide secure, real-time p2p media streaming, leveraging native platform features and server-side components brings up new communication capabilities for web and native mobile applications, allowing for advanced multi-user use cases such as video broadcasting, conferencing, and media recording.
    Fifty billion connected devices and still no winning protocols standards. HTTP, WebSockets, MQTT, and CoAP seem to be leading in the IoT protocol race at the moment but many more protocols are getting introduced on a regular basis. Each protocol has its pros and cons depending on the nature of the communications. Does there really need to be only one protocol to rule them all? Of course not. In his session at @ThingsExpo, Chris Matthieu, co-founder and CTO of Octoblu, walk you through how Oct...
    Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
    Almost everyone sees the potential of Internet of Things but how can businesses truly unlock that potential. The key will be in the ability to discover business insight in the midst of an ocean of Big Data generated from billions of embedded devices via Systems of Discover. Businesses will also need to ensure that they can sustain that insight by leveraging the cloud for global reach, scale and elasticity.
    A critical component of any IoT project is what to do with all the data being generated. This data needs to be captured, processed, structured, and stored in a way to facilitate different kinds of queries. Traditional data warehouse and analytical systems are mature technologies that can be used to handle certain kinds of queries, but they are not always well suited to many problems, particularly when there is a need for real-time insights.
    One of biggest questions about Big Data is “How do we harness all that information for business use quickly and effectively?” Geographic Information Systems (GIS) or spatial technology is about more than making maps, but adding critical context and meaning to data of all types, coming from all different channels – even sensors. In his session at @ThingsExpo, William (Bill) Meehan, director of utility solutions for Esri, will take a closer look at the current state of spatial technology and ar...
    Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Arch...
    The IoT industry is now at a crossroads, between the fast-paced innovation of technologies and the pending mass adoption by global enterprises. The complexity of combining rapidly evolving technologies and the need to establish practices for market acceleration pose a strong challenge to global enterprises as well as IoT vendors. In his session at @ThingsExpo, Clark Smith, senior product manager for Numerex, will discuss how Numerex, as an experienced, established IoT provider, has embraced a ...
    Everyone knows that truly innovative companies learn as they go along, pushing boundaries in response to market changes and demands. What's more of a mystery is how to balance innovation on a fresh platform built from scratch with the legacy tech stack, product suite and customers that continue to serve as the business' foundation. In his General Session at 19th Cloud Expo, Michael Chambliss, Head of Engineering at ReadyTalk, will discuss why and how ReadyTalk diverted from healthy revenue an...
    SYS-CON Events announced today that Hitrons Solutions will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Hitrons Solutions Inc. is distributor in the North American market for unique products and services of small and medium-size businesses, including cloud services and solutions, SEO marketing platforms, and mobile applications.
    SYS-CON Media announced today that @WebRTCSummit Blog, the largest WebRTC resource in the world, has been launched. @WebRTCSummit Blog offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. @WebRTCSummit Blog can be bookmarked ▸ Here @WebRTCSummit conference site can be bookmarked ▸ Here
    SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in Embedded and IoT solutions, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 7-9, 2017, at the Javits Center in New York City, NY. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/Big Data, HPC and ...
    SYS-CON Events announced today that Streamlyzer will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Streamlyzer is a powerful analytics for video streaming service that enables video streaming providers to monitor and analyze QoE (Quality-of-Experience) from end-user devices in real time.
    You have great SaaS business app ideas. You want to turn your idea quickly into a functional and engaging proof of concept. You need to be able to modify it to meet customers' needs, and you need to deliver a complete and secure SaaS application. How could you achieve all the above and yet avoid unforeseen IT requirements that add unnecessary cost and complexity? You also want your app to be responsive in any device at any time. In his session at 19th Cloud Expo, Mark Allen, General Manager of...
    SYS-CON Events announced today that LeaseWeb USA, a cloud Infrastructure-as-a-Service (IaaS) provider, will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. LeaseWeb is one of the world's largest hosting brands. The company helps customers define, develop and deploy IT infrastructure tailored to their exact business needs, by combining various kinds cloud solutions.
    @ThingsExpo has been named the Top 5 Most Influential M2M Brand by Onalytica in the ‘Machine to Machine: Top 100 Influencers and Brands.' Onalytica analyzed the online debate on M2M by looking at over 85,000 tweets to provide the most influential individuals and brands that drive the discussion. According to Onalytica the "analysis showed a very engaged community with a lot of interactive tweets. The M2M discussion seems to be more fragmented and driven by some of the major brands present in the...
    The Open Connectivity Foundation (OCF), sponsor of the IoTivity open source project, and AllSeen Alliance, which provides the AllJoyn® open source IoT framework, today announced that the two organizations’ boards have approved a merger under the OCF name and bylaws. This merger will advance interoperability between connected devices from both groups, enabling the full operating potential of IoT and representing a significant step towards a connected ecosystem.