This content has been marked as final. Show 6 replies
Unless your db is ms sql or sybase, why are you worried about it?
Well, my database *IS* Microsoft SQL Server, so I am worried about it.
I tested ms sql for vulnerabilty on numeric and char/varchar datatypes. I didn't test dates because we always subject our date inputs to some sort of function (createodbcdate, isdate, etc) before the variables ever see a cfquery tag.
I was able to get sql to run if the datatype was numeric. No matter how hard I tried, I couldn't get any to run for varchar/char datatypes. Everything I submitted was treated as simple text.
So, if you can figure out a way to detect non-numeric values, especially those containing semi-colons, in variables that should be numeric, you're in business.
> I've got a server with a huge number of ColdFusion templates (over 10,000)
> I know that CFQUERYPARAM is the best way to do this. I'd love to do it that
> way, but with so many pages, and so many queries
Obviously a lot of this is legacy code, and everyone's got a lot of legacy
code, so I empathise with your situation.
However I hope you've changed the way you create your templates at some
point to centralise your "database tier" so that any new query activity is
done in CFCs or modules (depending on CF version), and abstracted away from
your standard output-generating CF templates.
When this sort of thing is done, it makes DB changes like this *much*
easier (still a pain in the butt, but no where near as much).
If I was you... I'd take this as an opportunity to stand abck and look at
what you're doing with your queries, and perhaps trying to factor out as
much repetition as possible first, and THEN address the SQL injection
risks. It's good housekeeping, on both fronts.
> I tested
Dan, with as much respect as it warranted (*)... just because you can't do
something, doesn't mean it's not possible.
If the DB-using-world @ large identify an SQL injection risk when not using
bind paramters, it's probably not just to hear the sounds of their own
Over and above SQL injection, it's just better practice to use bind
parameters. Queries run faster, and consume less memory on the DB server.
I think it's irresponisble of you to discourage what's considered "best
practice", simply because you don't get it.
(*) FWIW, my own tests have drawn the same results as yours, but I just put
that down to my own shortcomings.
First, here are some simple things you can do to protect all pages before you follow the other advice and plans in this thread:
- In CF administrator, click on your datasources and then the
There you will uncheck all but the read and stored procedure and (possibly) write permissions. "Drop", "Create", etc., are definite no-nos here.
- If you haven't already, make one data source read-permissions
only and refactor your code to use it everywhere except for
carefully segregated updates, inserts and deletes.
- Now, in SQL Server itself, remove all permissions from the
users that CF uses except for data_reader and (selectively) data
writer and exec permissions on any procedures or functions you use.
- In SQL server, setup at least two CF users. One, should have
only the data_reader permission (plus any read-only stored
- Find articles, such as this one:
and follow their advice, start with locking down xp_cmdshell.
These measures require little or no CF code changes but will block all but the most determined and skilled hackers. You still need to follow Adam's advice though.
BTW, Dan is very wrong, ALL DB's are vulnerable to SQL injection.
SQL server is not even the most vulnerable anymore (Studies show that Oracle now has that "honor").
- In CF administrator, click on your datasources and then the "Advanced" button.