December 24, 2008 at 10:08 am
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 query parameterization*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 (this is Cold Fusion but the concepts are similar to ASP.NET, and it's really the database side that I want feedback on.):
SELECT #url.column# as data
FROM someTable
WHERE id =
You can use the url.whatever notation the same as you might use Request.QueryString["whatever"] notation in C#. The # marks indicate a variable that should be inserted into the string at that point. So [font="Courier New"]SELECT #url.column# as data[/font] is the same as [font="Courier New"]"SELECT " + Request.QueryString["column"] + " as data"[/font] in C#.
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:
SELECT [#url.column#] as data
FROM someTable
WHERE id =
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.
It's been my experience that SQL Server will not less you pass in a parameter to specify the column name. In other words, I couldn't get anything like this T-SQL to work:
[font="Courier New"]DECLARE @FirstName VARCHAR(25)
SET @FirstName = 'FName'
SELECT @FirstName,[LName]
FROM [staff][/font]
-Josh
December 24, 2008 at 10:30 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply