CODEWITHSUNDEEP
c#
user386258
user386258

Reputation: 1953

table name sql injection

I am working with C#. I need to write a select inline query.

The table name should be taken from config. I cannot write a stored procedure.

SqlCommand myCommand= new SqlCommand();
myCommand.CommandText = "Select  * from " + tableName;
myCommand.CommandType = CommandType.Text;
myCommand.Connection = connString;

How to avoid sql injection ?

Upvotes: 0

Views: 3219

Answers (4)

mike nelson
mike nelson

Reputation: 22136

I would ensure table name contains only these characters:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[]. -_0123456789

E.g.,

Regex regex = new Regex(@"^[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\[\]. -_0123456789]{1,128}$");
if (!regex.IsMatch(tableName)) throw new ApplicationException("Invalid table name");

To do a more comprehensive job including non-English languages see this reference on what a valid table names: http://msdn.microsoft.com/en-us/library/ms175874.aspx

Upvotes: 1

Yahia
Yahia

Reputation: 70369

Just create a query with a real param and check for the existence of the tablename - somthing like:

SELECT COUNT(*) FROM SYS.TABLES WHERE NAME = @pYOURTABLENAME

IF that returns 1 then you know that the table exists and thus can use it in the SELECT you showed in the question...

However I strongly recommend to try anything to get rid of the need for any code prone to SQL injection!

Upvotes: 3

Mr Moose
Mr Moose

Reputation: 6344

I'd look at moving the SQL to a stored proc and review this article by Erland Sommarskog as it has some great ideas for using dynamic SQL within stored procs. I'd certainly look into it. It discusses a lot of the issues around SQL injection and possible alternatives to dynamic SQL.

He also has another great article on ways to use arrays in stored procs. I know you didn't ask for that, but I often refer to these two articles as I think they are quite insightful and provide you with some useful ideas with regards to writing your procedures.

In addition to some of the suggestions linked above, I still have some basic parameter sanitisation mechanisms that I use if I am ever using dynamic SQL. An example of this is as follows;

        IF LEN(@TableName) < 5 OR LEN(@TableDisplayName) < 5
    BEGIN
        RAISERROR('Please ensure table name and display name are at least 5 characters long', 16, 1)
    END

    IF NOT (@TableName not like '%[^A-Z]%') 
    BEGIN
        RAISERROR('The TableName can only contain letters', 16, 1)
    END

    IF NOT (@TableDisplayName not like '%[^0-9A-Z ]%') 
    BEGIN
        RAISERROR('The TableDisplayName can only contain letters, numbers or spaces', 16, 1)
    END

This combined with using parameters within your dynamic sql and then executing using sp_executesql certainly help to minimise the possibility of a SQL injection attack.

Upvotes: 0

Jonathon Reinhart
Jonathon Reinhart

Reputation: 137418

You need to verify that tableName is appropriate. After some sanity checking (making sure it has no spaces, or other disallowed characters for table names, etc), I would then first query the database for the names of all tables, and programmatically verify that it is one of those table names. Then proceed to run the query you show.

Upvotes: 0

Related Questions