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.

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.

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.

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.

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.