Sql parameter are not getting replaced

Question

I am trying to create a new table in my databased based on a csv file. Since I don't know what columns are in the csv file I am creating a SqlCommand at runtime. Here is what I have so far.

public static void AddTableFromFile(string file)
{
    DefaultContext dbContext = DefaultContext.GetInstance();
    DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dbContext.Database.Connection);

    SqlCommand createTable = new SqlCommand();
    createTable.Connection = (SqlConnection)dbContext.Database.Connection;

    //Get all the fields from the file.
    TextReader reader = File.OpenText(file);
    string head = reader.ReadLine();
    reader.Close();
    head = head.Replace('\"', ' ');

    //Build the column paramaters for the Sql query.
    string[] fields = head.Split(',');
    if (fields.Length == 0)
        throw new Exception("No data to process; " + file);
    StringBuilder columnsBuilder = new StringBuilder();
    for (int i = 0; i < fields.Count(); i++)
    {
         columnsBuilder.Append("@column" + i + " char(25), ");
         createTable.Parameters.AddWithValue("@column" + i, fields[i]);
    }
    //Make the first field the primary key.
    columnsBuilder.Append("PRIMARY KEY(@column0)");

    createTable.Parameters.AddWithValue("@tableName", Path.GetFileNameWithoutExtension(file));

    createTable.CommandText = "CREATE TABLE @tableName (" + columnsBuilder.ToString() + ")";

    dbContext.Database.Connection.Open();
    createTable.ExecuteNonQuery();
    dbContext.Database.Connection.Close();

    DefaultContext.Release();
    Logger.Log("Table " + Path.GetFileNameWithoutExtension(file) + " added to the database.");
}

However everytime this runs I get a SqlException telling me that there is a syntax error around @tableName. I have tried this same command by just putting the raw values in the string, so I know that it works. Am I missing something obvious here?

Just in case it helps, I am working with MVC 4 and I believe that the database is Sql Server Express or whichever one comes bundled with Visual Studio 2012.

score 7 · Accepted Answer · answered May 21 '13 at 21:36

Am I missing something obvious here?

Unfortunately, I think you're missing something unsupported. While values can be parameterized, most databases (including SQL server) don't allow table names or even field names to be parameterized, as far as I'm aware.

So basically, if you really need to be able to accept fields etc as user input, you'll need to go through all the work of being super-careful about accepted characters, escaping the value etc - and then put it into the SQL directly.

score 2 · Answer 2 · answered May 21 '13 at 21:33

2

You can't use parameter for table name. You can use it for comparing values e.g.

WHERE UserName = @UserName

If you are afraid of SQL injection use classic .Replace("'","''").

answered May 21 '13 at 21:33

rocky

7,506
3
33
48

1

How does .Replace("'", "''") protect from SQL injection? – dkellycollins May 21 '13 at 21:41
Well, AFAIK it's not 100% safe but it causes sql query to be invalid when someone passes apostrophe (which is what people usually do when they are trying to inject a code). – rocky May 21 '13 at 21:49
See e.g. http://stackoverflow.com/questions/8506574/sql-injection-isnt-replace-good-enough for more info... – rocky May 21 '13 at 21:51

Sql parameter are not getting replaced

2 Answers2