Skip to main content
C
chazman113
Inspiring
October 22, 2008
Answered

Automatically parameretize code with a script

  • October 22, 2008
  • 4 replies
  • 835 views
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?
This topic has been closed for replies.
Correct answer chazman113
Bam! thanks a lot, exactly what I'm looking for


http://qpscanner.riaforge.org/

4 replies

D
Dan_Bracuk
Inspiring
October 23, 2008
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.
C
chazman113Author
Inspiring
October 23, 2008
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.
D
Dan_Bracuk
Inspiring
October 23, 2008
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.
C
chazman113Author
Inspiring
October 23, 2008
"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.
N
Newsgroup_User
Inspiring
October 22, 2008
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
C
chazman113Author
Inspiring
October 22, 2008
Yea I hear ya, inhereted code, thanks for the suggestion hah
N
Newsgroup_User
Inspiring
October 22, 2008
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.
C
chazman113AuthorCorrect answer
Inspiring
October 22, 2008
Bam! thanks a lot, exactly what I'm looking for


http://qpscanner.riaforge.org/
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices