|By Ben Forta||
|February 5, 2002 12:00 AM EST||
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.
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:
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:
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:
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):
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.
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:
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:
But what if someone tampered with that URL so that it read:
The resulting SQL would be:
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:
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.
|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().
SYS-CON Events announced today that Creative Business Solutions will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Creative Business Solutions is the top stocking authorized HP Renew Distributor in the U.S. Based out of Long Island, NY, Creative Business Solutions offers a one-stop shop for a diverse range of products including Proliant, Blade and Industry Standard Servers, Networking, Server Options and Care Packs. As a trusted supplier, CBS guarantees quality controlled stock levels thanks to an Auto...
Apr. 27, 2015 09:15 AM EDT Reads: 4,363
SOA Software has changed its name to Akana. With roots in Web Services and SOA Governance, Akana has established itself as a leader in API Management and is expanding into cloud integration as an alternative to the traditional heavyweight enterprise service bus (ESB). The company recently announced that it achieved more than 90% year-over-year growth. As Akana, the company now addresses the evolution and diversification of SOA, unifying security, management, and DevOps across SOA, APIs, microservices, and more.
Apr. 27, 2015 09:15 AM EDT Reads: 2,437
GENBAND introduced its Real Time Communications (RTC) Client for Lync* to seamlessly combine real-time communications with Lync Instant Messaging (IM) and Presence. “We’re shaking up the economics of delivering Unified Communications (UC) and offering a compelling way to integrate previously bespoke communications technologies,” said Carl Baptiste, GENBAND’s Senior Vice President, Enterprise Solutions. “We’re offering enterprises the best of both worlds by combining our own high availability voice, video and collaboration with Lync’s IM and Presence; creating a single, web centric, client. O...
Apr. 27, 2015 09:00 AM EDT Reads: 1,732
The list of ‘new paradigm’ technologies that now surrounds us appears to be at an all time high. From cloud computing and Big Data analytics to Bring Your Own Device (BYOD) and the Internet of Things (IoT), today we have to deal with what the industry likes to call ‘paradigm shifts’ at every level of IT. This is disruption; of course, we understand that – change is almost always disruptive.
Apr. 27, 2015 09:00 AM EDT Reads: 1,530
After making a doctor’s appointment via your mobile device, you receive a calendar invite. The day of your appointment, you get a reminder with the doctor’s location and contact information. As you enter the doctor’s exam room, the medical team is equipped with the latest tablet containing your medical history – he or she makes real time updates to your medical file. At the end of your visit, you receive an electronic prescription to your preferred pharmacy and can schedule your next appointment.
Apr. 27, 2015 09:00 AM EDT Reads: 1,443
SYS-CON Events announced today that kintone has been named “Bronze Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY, and the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. kintone promotes cloud-based workgroup productivity, transparency and profitability with a seamless collaboration space, build your own business application (BYOA) platform, and workflow automation system.
Apr. 27, 2015 09:00 AM EDT Reads: 1,724
Can call centers hang up the phones for good? Intuitive Solutions did. WebRTC enabled this contact center provider to eliminate antiquated telephony and desktop phone infrastructure with a pure web-based solution, allowing them to expand beyond brick-and-mortar confines to a home-based agent model. It also ensured scalability and better service for customers, including MUY! Companies, one of the country's largest franchise restaurant companies with 232 Pizza Hut locations. This is one example of WebRTC adoption today, but the potential is limitless when powered by IoT.
Apr. 27, 2015 09:00 AM EDT Reads: 5,294
SYS-CON Events announced today that Optimal Design, an Internet of Things solution provider, will exhibit at SYS-CON's Internet of @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Optimal Design is an award winning product development firm offering industrial design and engineering services to the consumer, medical, and defense markets.
Apr. 27, 2015 09:00 AM EDT Reads: 1,691
Chuck Piluso will present a study of cloud adoption trends and the power and flexibility of IBM Power and Pureflex cloud solutions. Speaker Bio: Prior to Data Storage Corporation (DSC), Mr. Piluso founded North American Telecommunication Corporation, a facilities-based Competitive Local Exchange Carrier licensed by the Public Service Commission in 10 states, serving as the company's chairman and president from 1997 to 2000. Between 1990 and 1997, Mr. Piluso served as chairman & founder of International Telecommunications Corporation, a facilities-based international carrier licensed by t...
Apr. 27, 2015 09:00 AM EDT
There are lots of challenges in IoT around secure, scalable and business friendly infrastructure for enterprises. For large corporations, IoT implementations are one of the top priorities of the decade. All industries are seeing a competitive need to sustain by investing in IoT initiatives. The value addition comes from improved customer service, innovative product and additional revenue streams. The data from these IP-connected devices can be leveraged for a variety of business applications as well as responsive action controls. The various architectural building blocks of an IoT ...
Apr. 27, 2015 09:00 AM EDT
@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 M2M space. This really allows some room for influential individuals to create more high value inter...
Apr. 27, 2015 08:45 AM EDT Reads: 5,132
SYS-CON Events announced today that Cisco, the worldwide leader in IT that transforms how people connect, communicate and collaborate, has been named “Gold Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Cisco makes amazing things happen by connecting the unconnected. Cisco has shaped the future of the Internet by becoming the worldwide leader in transforming how people connect, communicate and collaborate. Cisco and our partners are building the platform for the Internet of Everything by connecting the...
Apr. 27, 2015 08:30 AM EDT Reads: 5,840
SYS-CON Events announced today that Liaison Technologies, a leading provider of data management and integration cloud services and solutions, has been named "Silver Sponsor" of SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York, NY. Liaison Technologies is a recognized market leader in providing cloud-enabled data integration and data management solutions to break down complex information barriers, enabling enterprises to make smarter decisions, faster.
Apr. 27, 2015 08:15 AM EDT Reads: 3,902
Participants will reach the final if their IoT solution is liked. A community vote will determine the best solutions submitted in each country, after which an expert jury will select the national winners and the best international IoT solution. Each country's best solution can win a national marketing campaign worth up to €30,000 and become a partner in Deutsche Telekom's participating markets. The winning international solution can become partner of Deutsche Telekom Group across all eight countries and reach out to a potential of 10,8 million business customers. Deutsche Telekom Group has a...
Apr. 27, 2015 08:00 AM EDT Reads: 1,252
Recent technology advances in miniaturization has positioned the wearables as the pinnacle of technology convergence with the human body. We inquire if wearables are mere standard miniaturized devices extended with the connectivity and present our views on considerations like design, applications, performance, efficiency, interoperability, usage scenarios, human device interaction and consequent trade-offs enabling wearables to impart optimal value.
Apr. 27, 2015 08:00 AM EDT Reads: 1,174
WebRTC Summit has announced today that Peter Dunkley has been named summit chair of WebRTC Summit 2015 New York. The 4th International WebRTC Summit will take place on June 9-11, 2015, at the Javits Center in Manhattan, New York. @ThingsExpo anticipates 90% of WebRTC companies & developers will monetize their products & services through IoT by 2016. Peter Dunkley is Technical Director at Acision. He graduated from The University of Edinburgh in 2000 with a BSc (Hons) in Computer Science. After graduation Peter worked on a PSTN switch developing signalling stacks for SS7, ISDN and simi...
Apr. 27, 2015 08:00 AM EDT Reads: 2,194
In this session we look at creating interactive communications via the web by adding messaging, file transfer, and group communication (group chat and audio/video conferencing) into the web experience. We will also discuss potential applications of this technology in areas including B2B, B2C, P2P, and gaming. Peter is Technical Director at Acision. He graduated from The University of Edinburgh in 2000 with a BSc (Hons) in Computer Science. After graduation Peter worked on a PSTN switch developing signalling stacks for SS7, ISDN and similar protocols and creating advanced routing and serv...
Apr. 27, 2015 08:00 AM EDT Reads: 1,232
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, phone and digital TV services to consumers primarily in rural areas.
Apr. 27, 2015 08:00 AM EDT Reads: 5,219
SYS-CON Events announced today that SoftLayer, an IBM company, has been named “Gold Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place June 9-11, 2015 at the Javits Center in New York City, NY, and the 17th International Cloud Expo®, which will take place November 3–5, 2015 at the Santa Clara Convention Center in Santa Clara, CA. SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from ...
Apr. 27, 2015 08:00 AM EDT Reads: 2,817
The WebRTC Summit 2015 New York, to be held June 9-11, 2015, at the Javits Center in New York, NY, announces that its Call for Papers is open. Topics include all aspects of improving IT delivery by eliminating waste through automated business models leveraging cloud technologies. WebRTC Summit is co-located with 16th International Cloud Expo, @ThingsExpo, Big Data Expo, and DevOps Summit.
Apr. 27, 2015 07:45 AM EDT Reads: 2,468