Automatically parameretize code with a script

[chazman113]

I have several thousand queries to add cfqueryparams to... using regular expressions and pattern matching I was thinking it is very possible to write something to crawl and entire hard drive of CFM pages to find code within cfquery and then find things such as (somename = '#form.whydidntiparametizethis#') and replace it with the appropriate. I guess one problem would be disitnguising between dates, ints, floats, ect... but couldn't we just make the type varchar? wouldn't be perfect but would prevent injection. has anyone heard of something like this or written some code I could work off of?

[Newsgroup_User]

Yes it is very possible

So possible that somebody has already done it and published the code at
the riaforge site.

http://qpscanner.riaforge.org/
http://www.hybridchill.com/projects/qpscanner.html

It doesn't fix them I guess, but it finds them.

[chazman113 · Accepted solution]

Bam! thanks a lot, exactly what I'm looking for


http://qpscanner.riaforge.org/

[Newsgroup_User]

Ian's pointed you at an answer to this, but I'd just like to refer to this
comment:

> I have several thousand queries to add cfqueryparams to

That's really a lot of queries. Has your approach in your code been "oh, I
need some data here: I'll write a <cfquery> now"? Sounds that way.

Whilst you're revisiting this code, it might be a good idea ot investigate
whether it's practical to factor out some of those <cfquery> blocks into
reusable code, so the same query can be re-used in multiple places.

Anyway, this is nowt to do with your question, I just thought I'd throw it
in there.

--
Adam

[chazman113]

Yea I hear ya, inhereted code, thanks for the suggestion hah

[Dan_Bracuk]

Regarding the thought, "why not make everything varchar?". My opinion is that database design should be based on what the application is supposed to accomplish. Data entry considerations are irrelevent.

[chazman113]

"Data entry considerations are irrelevant"
Not sure what you mean, are you saying that making the param type varchar is fine because the database is typed and therefore its constraints will take care of it? My goal is to prevent SQL injection attacks on an old application. If I had my way I would remake the entire thing with properly typed database fields, parametized queries, and possibly a paper clip that would help you through everything. Unfortunately, I dont have that luxury 🙂 Also, to be clear the queryParamScanner does not automatically replace things, but it can give you xml output to run your own scripts to deal with the queries.

[Dan_Bracuk]

I'm saying nothing of the sort. I'm saying that the data types in the db should be based on the needs of the application and that user interfaces should be based on those data types.

The suggestion of making everything varchar to save some work is an example of basing a database design on a data entry consideration.

[chazman113]

Ahhh, I wasn't implying that I make every database field actually varchar, just to make the cfqueryparam be of the type varchar because a string can really be any data type (ie the string 100 will work for an int field). Only now I realize you dont have to specify the sql type in cfqueryparam so it isn't a big deal anyway.