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
    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. Jack Norris reviews best practices to show how companies develop, deploy, and dynamically update these applications and how this data-first...
    Intelligent Automation is now one of the key business imperatives for CIOs and CISOs impacting all areas of business today. In his session at 21st Cloud Expo, Brian Boeggeman, VP Alliances & Partnerships at Ayehu, will talk about how business value is created and delivered through intelligent automation to today’s enterprises. The open ecosystem platform approach toward Intelligent Automation that Ayehu delivers to the market is core to enabling the creation of the self-driving enterprise.
    Internet-of-Things discussions can end up either going down the consumer gadget rabbit hole or focused on the sort of data logging that industrial manufacturers have been doing forever. However, in fact, companies today are already using IoT data both to optimize their operational technology and to improve the experience of customer interactions in novel ways. In his session at @ThingsExpo, Gordon Haff, Red Hat Technology Evangelist, shared examples from a wide range of industries – including en...
    Consumers increasingly expect their electronic "things" to be connected to smart phones, tablets and the Internet. When that thing happens to be a medical device, the risks and benefits of connectivity must be carefully weighed. Once the decision is made that connecting the device is beneficial, medical device manufacturers must design their products to maintain patient safety and prevent compromised personal health information in the face of cybersecurity threats. In his session at @ThingsExpo...
    "We're a cybersecurity firm that specializes in engineering security solutions both at the software and hardware level. Security cannot be an after-the-fact afterthought, which is what it's become," stated Richard Blech, Chief Executive Officer at Secure Channels, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
    SYS-CON Events announced today that Massive Networks 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. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
    SYS-CON Events announced today that Grape Up 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. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
    Detecting internal user threats in the Big Data eco-system is challenging and cumbersome. Many organizations monitor internal usage of the Big Data eco-system using a set of alerts. This is not a scalable process given the increase in the number of alerts with the accelerating growth in data volume and user base. Organizations are increasingly leveraging machine learning to monitor only those data elements that are sensitive and critical, autonomously establish monitoring policies, and to detect...
    Everything run by electricity will eventually be connected to the Internet. Get ahead of the Internet of Things revolution and join Akvelon expert and IoT industry leader, Sergey Grebnov, in his session at @ThingsExpo, for an educational dive into the world of managing your home, workplace and all the devices they contain with the power of machine-based AI and intelligent Bot services for a completely streamlined experience.
    Because IoT devices are deployed in mission-critical environments more than ever before, it’s increasingly imperative they be truly smart. IoT sensors simply stockpiling data isn’t useful. IoT must be artificially and naturally intelligent in order to provide more value In his session at @ThingsExpo, John Crupi, Vice President and Engineering System Architect at Greenwave Systems, will discuss how IoT artificial intelligence (AI) can be carried out via edge analytics and machine learning techn...
    When shopping for a new data processing platform for IoT solutions, many development teams want to be able to test-drive options before making a choice. Yet when evaluating an IoT solution, it’s simply not feasible to do so at scale with physical devices. Building a sensor simulator is the next best choice; however, generating a realistic simulation at very high TPS with ease of configurability is a formidable challenge. When dealing with multiple application or transport protocols, you would be...
    With tough new regulations coming to Europe on data privacy in May 2018, Calligo will explain why in reality the effect is global and transforms how you consider critical data. EU GDPR fundamentally rewrites the rules for cloud, Big Data and IoT. In his session at 21st Cloud Expo, Adam Ryan, Vice President and General Manager EMEA at Calligo, will examine the regulations and provide insight on how it affects technology, challenges the established rules and will usher in new levels of diligence a...
    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 ...
    In the enterprise today, connected IoT devices are everywhere – both inside and outside corporate environments. The need to identify, manage, control and secure a quickly growing web of connections and outside devices is making the already challenging task of security even more important, and onerous. In his session at @ThingsExpo, Rich Boyer, CISO and Chief Architect for Security at NTT i3, discussed new ways of thinking and the approaches needed to address the emerging challenges of security i...
    SYS-CON Events announced today that Dasher Technologies 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. Dasher Technologies, Inc. ® is a premier IT solution provider that delivers expert technical resources along with trusted account executives to architect and deliver complete IT solutions and services to help our clients execute their goals, plans and objectives. Since 1999, we'v...
    There is only one world-class Cloud event on earth, and that is Cloud Expo – which returns to Silicon Valley for the 21st Cloud Expo at the Santa Clara Convention Center, October 31 - November 2, 2017. Every Global 2000 enterprise in the world is now integrating cloud computing in some form into its IT development and operations. Midsize and small businesses are also migrating to the cloud in increasing numbers. Companies are each developing their unique mix of cloud technologies and service...
    SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
    SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of 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. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
    SYS-CON Events announced today that Akvelon 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. Akvelon is a business and technology consulting firm that specializes in applying cutting-edge technology to problems in fields as diverse as mobile technology, sports technology, finance, and healthcare.
    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, will introduce 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...