0

I've got a stored procedure that returns data for a grid control. Given a table name, the grid will display data from that table. The user can sort and filter this data. There is also paging logic for large data sets.

The names of the tables that data is pulled from is not known until runtime, so dynamic SQL was used. This works well, but is vulnerable to SQL injection - the tableName, sortExpression and filterExpression variables are generated clientside and passed through to the server.

Below is a simplified version of the procedure:

create procedure ReadTable (
    @tableName as varchar(128), 
    @sortExpression as varchar(128), 
    @filterExpression as varchar(512)
) 
as 
begin 
    declare @SQLString as nvarchar(max) = 
        'select * from ' + @tableName + 
        ' where ' + @filterExpression + 
        ' order by ' + @sortExpression

    exec Sp_executesql @SQLString
end

I'm struggling to find a way to easily prevent SQL injection in this case. I've found a good answer explaining how to check the @tableName is legitamite (How should I pass a table name into a stored proc?), but the approach won't work for the filtering or sort strings.

One way would be perhaps to do some sanitizing server side before the data is passed through to the database - breaking the expressions down into column names and checking them against the known column names of the table.

Would there be an easier way?

Community
  • 1
  • 1
John
  • 1,502
  • 2
  • 13
  • 40
  • 1
    Possible duplicate of: [Cleanse Dynamic SQL to Prevent SQL Injection](http://stackoverflow.com/questions/4102387/how-to-cleanse-dynamic-sql-in-sql-server-prevent-sql-injection). Also this [SQL_Injection_Prevention_Cheat_Sheet](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet) looks useful: – goodguy5 Mar 10 '16 at 16:53
  • From that second link - [Dynamic SQL & SQL injection](http://blogs.msdn.com/b/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx) – stuartd Mar 10 '16 at 17:04

0 Answers0