SQL Injection Blocker

[karlkrist]

Hello all-

I've got a server with a huge number of ColdFusion templates (over 10,000) which I really need to protect agains SQL Injection.

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 it would take weeks/months to fix the queries, then test to make sure I didn't screw something up.


So, I've come up with a plan that I wanted to get some input on.

Currently, I have a page on my server that is included in almost every page that runs. It is a simple page that I can modify to change the status of my systems in the event of a database changeover, or some other sort of failure. (The pages still run, but no updating is allowed, only reading)


Okay, so on this page which is always included, I was thinking about analyzing the variables that come over. I was thinking about looking for things that looked like a SQL injection attack and blocking the page from running.


I wanted to know if this would work- anyone have ideas? This would be great because I could protect the entire server in about an hour. But, I don't want to give myself a false sense of security if this won't really do the job.

[Dan_Bracuk]

Unless your db is ms sql or sybase, why are you worried about it?

[karlkrist]

Well, my database *IS* Microsoft SQL Server, so I am worried about it.

[Dan_Bracuk]

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.

[Newsgroup_User]

> 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.

--
Adam

[Newsgroup_User]

> 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
voices.

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.

--
Adam


(*) FWIW, my own tests have drawn the same results as yours, but I just put
that down to my own shortcomings.

[MikerRoo · Accepted solution]

First, here are some simple things you can do to protect all pages before you follow the other advice and plans in this thread:

1. In CF administrator, click on your datasources and then the "Advanced" button.
   There you will uncheck all but the read and stored procedure and (possibly) write permissions. "Drop", "Create", etc., are definite no-nos here.
   
2. 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.
   
3. 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.
   
4. In SQL server, setup at least two CF users. One, should have only the data_reader permission (plus any read-only stored procedures).
   
5. Find articles, such as this one: http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp, 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").