How do I securely create queries in ADO.NET where the tables being selected from change?

Question

In ADO.NET you can add parameters to a command object to securely add user input to a SQL query. What is the equivalent for the other predicates common to a SQL query?

I am writing a program that is essentially a very limited O-R mapper and SQL generator (it's focused heavily around a database with meta-information and other databases that conform to that meta-data). As a result I need to be able to call stuff like:

string sql = "select " + USER_SELECTED_COLUMNS + 
            " from " + USER_SELECTED_TABLE + 
            " where " + USER_CRITERIA;

Some of it (like the criteria) is literally entered into my program by trusted users (other developers in my company), while other data is entered into my program by untrusted users (clients) through their searches, etc.

I'd like to make this program secure, and I'm aware that the above is not. Currently I have the USER_SELECTED_COLUMNS replaced with command parameters, but I've not been able to find the equivalent for the CRITERIA and TABLEs. (Or the order-by columns). Are there any ADO.NET features similar to SqlParameter that I can use for non-selection predicates?

Answer 1

I dont think I could tell you how to avoid SQL Injection in 1 response, however, the main pointer I can give you is this:

USE WHITELISTS, NOT BLACKLISTS.

That is to say, when sanitizing the users input for USER_SELECTED_TABLE, the possible input should only be the possible tables. Equaly, the input for USER_SELECTED_COLUMNS should be limited to the possible columns for USER_SELECTED_TABLE.

Answer 2

when you build the screens that allow the user to select the table and columns don't use the actual names. Kind of how you would have a UserID but show the UserName. Use the object_id and column_id (like form sys.tables.object_id, and sys.columns.object_id+column_id). Pas those into your procedure and build your SQL using only the numeric IDs that join to the system views:

sys.tables (Transact-SQL)
sys.columns (Transact-SQL)

you can concatenate the string table and column names, but they will come from the system views and not from the user input.