How do I programatically sanitise coldfusion cfquery parameters.

Question

I have inherited a large legacy coldfusion app. There are hundreds of <cfquery>some sql here #variable#</cfquery> statements that need to be parameterized along the lines of: <cfquery> some sql here <cfqueryparam value="#variable#"/> </cfquery>

How can I go about adding parameterization programatically?

I have thought about writing some regular expression or sed/awk'y sort of solution, but it seems like somebody somewhere has tackled such a problem. Bonus points awarded for inferring the sql type automatically.

Thanks.

Answer 1

THere's a queryparam scanner that'll find em for you on RIAForge: http://qpscanner.riaforge.org/

Answer 2

There is a script referenced here: http://www.webapper.net/index.cfm/2008/7/22/ColdFusion-SQL-Injection that will do the majority of the heavy lifting for you. All you have to do is check the queries and make sure the syntax will parse properly.

There is no excuse for not using CFQueryParam, apart from it being much more secure, it is a performance boost and the best way to handle quoted values in character based column types.

Answer 3

<cf_inputFilter
            scopes = "FORM,COOKIE,URL"
            chars = "<,>,!,&,|,%,=,(,),',{,}"
            tags="script,embed,applet,object,HTML">

We used this to counteract a recent SQL injection attack. We added it to the Application.cfm file for our site.

Answer 4

I doubt that there is a solution that will fit your needs exactly. The only option I see is to write your own recursive search that builds a report for you or use one of the apps/scripts that people have listed above. Basically, you are going to have to edit each page or approve all of the automated changes.

Answer 5

Keep in mind that you may not be able to solve everything with <cfqueryparam>.

I've seen a number of examples where the order by field name is being passed in the query string, which is a slightly trickier problem to solve as you need to validate that in a more "manual" way.