locked
Parameterizing the colum name portion of a query RRS feed

  • Question

  • 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 SELECT #url.column# as data is the same as "SELECT " + Request.QueryString["column"] + " as data" 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:

    DECLARE @FirstName VARCHAR(25)
    SET @FirstName = 'FName'

    SELECT @FirstName,[LName]
      FROM [staff]


    -Josh

    -jb
    Wednesday, December 24, 2008 5:14 PM

All replies

  • 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
    -jb
    Wednesday, December 24, 2008 5:30 PM
  •    I am not familiar with Cold Fusion, but based on your description, encapsulating the user-defined data (in this case the variable #url.column#) will not be enough, as the attacker only needs to change the escaping character ( “]” ) for his attack. It would also be necessary to escape the “]”s from input; for example (I am using C#, as I am not familiar with CF): 

    string query = String.Format(@"SELECT [{0}] as data FROM ... ", column.Replace("]", "]]"));

      As you already mentioned, there is no parameterization for column names in T-SQL, I would recommend using Microsoft SQL Server Connect (http://connect.microsoft.com/sql) and vote for an existing feedback case or to submit a new one for the need of such feature.

      I am including some additional links related to SQL injection:
    • http://msdn.microsoft.com/en-us/library/ms161953.aspx
    • http://msdn.microsoft.com/en-us/library/ms998271.aspx
    • http://msdn.microsoft.com/en-us/magazine/cc163917.aspx
    • http://blogs.msdn.com/raulga/archive/tags/sql+injection/default.aspx

      I hope this information helps.

      -Raul Garcia
       SDE/T
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, December 24, 2008 10:04 PM
  • Raul Garcia - MS said:

       I am not familiar with Cold Fusion, but based on your description, encapsulating the user-defined data (in this case the variable #url.column#) will not be enough, as the attacker only needs to change the escaping character ( “]” ) for his attack.

    I don't think this is really an issue, since we're not talking about escaping characters, we're talking about delimiters, [ for "start" and ] for "stop".  There's no way to change the end delimiter, as far as I can tell from reading the docs.  If you know otherwise please link me to the appropriate doc section.

    As far as escaping ] characters in the input, I will instead simply be stripping them out entirely as per my previous post--there's no time when ] would be a valid part of a column name, so any ] characters can simply be removed from the input (and in which case the request is probably invalid anyway).

    Thanks for your feedback, let me know if you have any further thoughts.

    -jb
    Wednesday, December 24, 2008 10:19 PM
  •  

      Delimiting a sysname value (in this case a column name) using “[“ and “]” is correct, but it is necessary to escape any “]” that may be present in the user-controlled data in order to avoid injection by replacing the single “]” for “]]”, using your example:

      SELECT [#url.column#] as data
    FROM someTable
    WHERE id = … 

        Imagine and adversary sending the following  value for #url.column: “myData], [Secret Data” . The query to be executed would become:

      SELECT [myData], [Secret Data] as data
    FROM someTable
    WHERE id = … 

      In this case the adversary will select two columns instead of one, but as you can see, the adversary still can inject any other T-SQL payload.

      By escaping the “]” from #url.column# (I ignore how to replace strings in CF, but in CLR you can easily use the String.Replace method), this T-SQL injection vector would be mitigated:

      SELECT [myData]], [Secret Data] as data
    FROM someTable
    WHERE id = … 

      In this case there is only one column being selected:  “myData]], [Secret Data”, this column may not exist, but there is no SQL injection in this particular case.

      The links I included in my previous post should contain all of this information in more detail, but if you have further questions on SQL injection we will be glad to assist.

      Thanks,
    - Raul Garcia
      SDE/T
      SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, December 26, 2008 6:34 PM