Question
Using parameters for a column name
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
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
AdChoices