CODEWITHSUNDEEP
c#sqlasp.netsql-server
Hello World
Hello World

Reputation: 308

Allowing a user to pass table name and column name whilst preventing SQL injection

I have an (intranet) web page (ASP.NET C#) which allows users to create a table on a specific db in SQL-Server, table name and column name/s are free text fields and the actual structure is determined by a few dropdowns. I am aware passing the create table statement as pure string concatenated with the text from my textboxes is susceptible to SQL injection. Given that I am passing the actual names of db objects which don't yet exist rather than parameters is there any way to prevent the possibility of injection other than checking each text box for illegal characters before concatenating the string?

Upvotes: 1

Views: 1174

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

This is what QUOTENAME() was created to solve. You pass in your column and table names as parameters in to QUOTENAME() and then you use the output of it to represent objecs in your database in a dynamic sql query.

//The evil name tries to expliot code like:
//  set @sql = N'CREATE TABLE [' + @tablename + N'] (Foo int)'
var evilName = "someName] (Foo int); Drop table students --";

var query = @"
declare @sql as nvarchar(max)
set @sql = N'CREATE TABLE ' + QUOTENAME(@tablename) + N' (Foo int)'
exec sp_executesql @sql
";
using(var connection = new SqlConnection(ConnectionString))
using(var command = new SqlCommand(query, connection))
{
    command.Parameters.Add("@tablename", SqlDbType.NVarChar, 128).Value = evilName ;
    connection.Open();
    command.ExecuteNonQuery();
}

The query that will be executed on the server will be 

CREATE TABLE [someName]] (Foo int); Drop table students --] (Foo int)

which creates a table with a valid table name and does not drop my other table.

enter image description here

Upvotes: 1

Related Questions