Search code examples
c#.netsqlsql-injectionsqlparameter

Avoid SQL Injections on query with tablename

Possible Duplicate:
Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)

I have a query like so:

"SELECT * FROM MyTable_" + myID + " WHERE variable = @variable";

The SQL Parameterization works with variables, but how do I get it to work with table names? myID is an int I get passed in and changed (can be converted to string), but how do I protect against sql injections here?

Solution

  • I question why you are doing this, but you can look at sys.tables for a conclusive whitelist.

    DECLARE @TableName VARCHAR(100) = 'Table to Look for';
DECLARE @Exists BIT = ( SELECT CAST( COUNT(1) AS BIT ) FROM sys.tables WHERE name = @TableName AND type = 'U' );

    You could parameterize the initial input, but the whitelist approach is still important. Otherwise, a malicious user could pass any valid table name in the entire database and the query would run against it (assuming they had SELECT permissions).