About Dan Wilson
Dan Wilson
Dan Wilson
Model-Glue:Gesture for Fun and Profit
Watch Video
Presentation Files
DataCurl LLC, My Company
2010 Speaking Engagements
Video Interviews
DZone Articles
CFQueryparam and Lists
Posted By : Dan Wilson
January 12, 2009 1:47 AM
A word on SQL Injection
SQL Injection is a pervasive problem in the Web Application World. A quick search for URLS that use raw SQL brings up hundreds of thousands of dangerously formed URLS. Any developer worth his salt knows to clean user input before using it.
Defend Against SQL Injection in ColdFusion
CFQueryparam is a recommended tag that helps to keep your queries safe from SQL Injection. Any ColdFusion worth his salt uses CFQueryparam to help keep malicious parameters from being executed by the database engine. I ran across some code today that used CFQueryparam in most cases, but there was a particular, recurring use case that used raw parameters.
Example 1
2SELECT productName,
3FROM product
4WHERE productID IN ( #productIDList# )
5</cfquery>
Note the use of the list. It is a common paradigm to pass a delimited list of data to an SQL statement. In this case, the developer chose not to use CFQueryparam because he/she was under the impression that the result would be a single parameter, not a chain of parameters.
However, CFQueryparam can be used successfully in this case by setting the optional attribute 'list' to true. This is a supported attribute on all database engines.
Example 2
2SELECT productName,
3FROM product
4WHERE productID IN (<cfqueryparam value="#productIDList#" list="true" cfsqltype="cf_sql_numeric">" )
5</cfquery>
The resulting query will be parametrized in such a way as to render the list as a list and the results of the second query are equal to the first. Except for the case of an SQL Injection attack.
In the case of an SQL Injection attack, the developer of the first code sample would have a lot of explaining to do...
There are no comments for this entry.
Add Comment
Subscribe to Comments
I don't have a lot of numeric or decimal columns, but in theory DataFaucet should handle the scale attribute automatically. Does a bunch of other checking to prevent sql injection too... reduces the list of things to check when using DataFaucet down to just 3 relatively uncommon things that you shouldn't do to prevent injection.
Good post! one thing to keep in mind is that you will get a different query plan cache for each query with a different number of list items.
For the decimals, I have found CF_SQL_FLOAT works very nicely with decimals, at least with oracle
That's interesting... The first time I worked with an Oracle DB I remember having a problem with a numeric field and having difficulty figuring it out... so we went to someone else in the company who was more familiar with Oracle and his answer was "don't use the float datatype because it's all kinds of hosed up and never works properly in Oracle". This was several years ago though.
just because the datatype isn't a float, doesn't mean you can't pass data as a float...
I do a lot of spatial mapping stuff and CF_SQL_FLOAT makes my life a lot easier.. all those darn co-orindate pairs
just because the datatype isn't a float, doesn't mean you can't pass data as a float...
I do a lot of spatial mapping stuff and CF_SQL_FLOAT makes my life a lot easier.. all those darn co-orindate pairs
Great post. Another benefit to parametrized queries is performance. In Oracle, your first example would be treated as a brand new query each time the sql is executed. Each and every time it will be parsed, qualified and optimized - in short, a brand new compile of the sql every time it is executed. Very CPU-intensive and nonscalable (parsing a query cannot be done concurrently with many other Oracle operations).
In your second example, Oracle would utilize the bind variable supplied by <CFQUERYPARAM>, the value of which is supplied at query execution time. The query is compiled once and only once and the query plan is stored in Oracle's Library Cache (a shared memory area) from which it can be retrieved and reused. From Oracle's perspective, the parameterized query is *exactly* the same no matter what value is supplied to <CFQUERYPARAM>
In a simple test on a local CF server / local DB setup, inserting 10000 rows into a table with the variables passed without <CFQUERYPARAM>, the load took about 50 seconds. Simply using <CFQUERYPARAM> to pass in the variable values dropped the load time down to 17 seconds.
Tracing the SQL in Oracle validated the drop in load time - the expensive parse phase of the query execution dropped by a factor of 4-5 for each row.
Suscribe
Follow Us
Contact
good post. another often-overlooked attribute (it is for me at least, until i have to debug an issue and go "oh yeah! now i remember") is the "scale" attribute.
from the docs:
Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.
i've lost quite a bit of time over the years trying to figure out what happened to my decimals.