Using parameters for a column name

[JoshBeall]

Hi All,

I've got a legacy application (using CF 6,1,0,83762 and SQL Server 2000) that I've been brought on to support after it had several security penetrations. Standard SQL injection stuff, it looks like the original developer(s) who did it did not use cfqueryparam *anywhere*, so it was just waiting to be exploited.

One of the things I've discovered is that there are a number of places where they are doing something like this:

<cfquery name="qryData" datasource="#Application.Global.dsn#">
SELECT #url.column# as data
FROM someTable
WHERE id = <cfqueryparam value="#trim(url.id)#" cfsqltype="cf_sql_numeric">
</cfquery>

As you can see, the column name they are requesting is taken right out of the query string. I can't just get rid of this mechanism for specifying the column without a major rewrite of all their code. So, I need some way of escaping the column name to make sure that SQL Injection will not work. Any suggestions?

The best I can think of is to put brackets around the column name, and then use string functions to ensure that the column name specified does not have a closing bracket. SQL Server uses brackets to delimit column names. So it would look like this:

<!--- strip out any ] characters passed in url.column --->
<cfquery name="qryData" datasource="#Application.Global.dsn#">
SELECT [#url.column#] as data
FROM someTable
WHERE id = <cfqueryparam value="#trim(url.id)#" cfsqltype="cf_sql_numeric">
</cfquery>

But I'd like feedback from others--what is the best way to handle this? Would what I'm suggesting cover all situations?

The client is not going to pay for the rewrite that would be needed to purge the code of this "column-name-in-the-url" technique, so that's not an option. We need to make it safe as is.

-Josh

[JoshBeall]

p.s. In that situation above I added the cfqueryparam tag in the where clause, originally it didn't even have that!

[-__cfSearching__-]

I am not an expert in this area. But you might also consider adding a validation layer in your Application.cfm. You could run a series of checks on user submitted variables (url, form) to check for potential sql injection attacks and either clean the variables or just cancel the request. Cflib.org has a few functions that you might find useful like:
http://www.cflib.org/udf/IsSQLInject

Obviously it is not as good as just removing the offending code, but it is a start.

[JoshBeall]

Judging by what I found here:

http://msdn.microsoft.com/en-us/library/aa224033(SQL.80).aspx

I think my idea of putting brackets around the column name, and stripping out any bracket characters in user-specified column names, should work. It says in the docs:

"The body of the identifier can contain any combination of characters in the current code page except the delimiting characters themselves."

So the only possible issue is what the behavior is when a character "outside the current code page" is used. I tried using some Chinese characters and just got "invalid column name" errors.

-Josh

[-__cfSearching__-]

> I think my idea of putting brackets around the column name, and stripping out any bracket
> characters in user-specified column names, should work.

Possibly. But I would still recommend an added layer of security in the Application.cfm. It is easy to implement ( a single application file or two). Plus it attempts to stop the attack before it ever reaches the application. So you would have two layers of security which IMO is better. In the event that one layer fails, the second might do the trick. That said, if the application is using dynamic sql (ie exec ) probably nothing will help if bad sql somehow makes it into the database:

INSERT INTO SomeTable (Column) VALUES ( '#badSQLString#')
..
EXEC ( @strContainingBadSQL)

[Newsgroup_User]

Is it legal in the ensuing logic for more than just a single specific
column name to be specified in the URL param, eg:

url.column = "col1, col2"

It looks like only a single column is allowed. If so, check to see if the
value corresponds to an actual column first, and unless it does, halt
processing and throw an exception.

select name
from syscolumns c
inner join (
select id
from sysobjects
where name = 'nameOfTableHere'
) t
on c.id = t.id
where name = <cfqueryparam value="#URL.column#">


--
Adam

[-__cfSearching__-]

> select name
> from syscolumns c
> inner join (
> select id
> from sysobjects

A minor comment. Sql 2000 should support the information_schema views. So the query would be even simpler using information_schema.columns.