CODEWITHSUNDEEP
c#sql.netsql-server
SledgeHammer
SledgeHammer

Reputation: 7680

SqlDataAdapter -- prevent SQL injection on table name?

I have the following code:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 0 * FROM [tableName]", conn);
DataTable dt = new DataTable();
adapter.FillSchema(dt, SchemaType.Mapped);

I am using this to build a DataTable of the table schema.

Normally, when people are asking about SQL injection, they are talking about query params :), my question is about the table name:

SELECT TOP 0 * 
FROM [tableName]

[tableName] is actually going to be dynamic / determined at runtime (this is for a framework btw)...

The tableName passed into the method is not trusted, so I want to make sure there isn't any funny business going on?

Do I have to manually scrub the table name (and I'm sure I'll miss something)? Or is there a built in method for that? Or somehow to prevent the SQL injection on the table name?

Upvotes: 0

Views: 895

Answers (4)

Dan Guzman
Dan Guzman

Reputation: 46203

The tableName passed into the method is not trusted, so I want to make sure there isn't any funny business going on?

One method is to first execute a parameterized query, passing the table name as a parameter (nvarchar max length 128) to the QUOTENAME function:

SELECT QUOTENAME(@TableName) AS ScrubbedTableName;

The returned value can then be used in your TableAdapter query without the risk of SQL injection. However, this will not prevent disclosure of meta data if a table other than one expected is specified.

Upvotes: 1

Jaekx
Jaekx

Reputation: 46

You can keep sqlDataAdapter and use

Adapter.selectcommand.parameters.add(@"whatever", value)

Edit: Sorry! I missed the part about this being related to a table name, this code doesnt work. :( I apologize.

Upvotes: -2

HaukurHaf
HaukurHaf

Reputation: 13796

You can use the following query to get a list of all tables in the database and use that as a white list:

SELECT TABLE_NAME FROM <DATABASE_NAME>.INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE='BASE TABLE'

Replace 

<DATABASE_NAME>

with your database name.

If someone attempts to input a table name which is not in that list, then don't execute the query.

Update

As for the "multiple connection strings" case, you can also use a simple query to check the current database name:

SELECT db_name()

So you should be able to easily craft a method that simply gets a list of valid table names from the current database, no matter if the connection-string/db-name is dynamic or not. And in the grand scheme of things, I doubt that these two queries are going to affect the performance of your application, like you said, you could easily cache the table list, if necessary.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

You could just go with the rules for valid table names

128 char - letters, numbers, and limited other special characters

In those rules I don't think you could do an injection

CreateTable see table_name
ValidIdentifier

Upvotes: 1

Related Questions