C#, sqlcommand string

Question

I have a SqlCommand string to create an SqlServer Data table as below:

`string EFTGeneral = @`"CREATE TABLE [EFT.GER2] (
                                [TestOrderName]   VARCHAR(20)  NOT NULL,    
                                [Template]        VARCHAR(50)  NULL,
                                [Purpose]         VARCHAR(100) NULL,
                                [Category]        VARCHAR(50)  NULL,
                                [DateCreated]         DATETIME NULL,
                                [CreatedBy]       VARCHAR(30)  NULL,
                                [TestObjectBatch] VARCHAR(20)  NULL,
                                [LoadDirection]   VARCHAR(2)   NULL,
                                [ElementType]     VARCHAR(10)  NULL,
                                [FatigueTestType] VARCHAR(50)  NULL,
                                [LoadAmplitude]            INT NULL,
                                [LoadStatic]      INT NULL,
                                [UntilBreakage]   VARCHAR(5)   NULL,
                                [NumberOfObject]           INT NULL,
                                [Remarks]         VARCHAR(200) NULL,
                                PRIMARY KEY CLUSTERED([TestOrderName] ASC));";

But I want to create the Table name dynamitically by replace the table name in the above string by the textbox.Text, How can I do it? Thank you very much

score 0 · Answer 1 · edited May 23 '17 at 12:00

Normally I would say "don't do it" as inserting user input into your queries opens up the SQL injection vulnerability. The correct way would be to use parameterized queries.

Unfortunatly, parameters can only be used for values, but not for table or column names. So to easily replace the table name in your query you could simply do a Replace() like that:

EFTGeneral = EFTGeneral.Replace("[EFT.GER2]", "[" + textBox.Text + "]");

But you should make sure that textBox.Text is a valid table name and not something like "; DROP TABLE Users --". This answer shows how you can validate the table name.

Dear All,Thank you very much for your quick answers. I fixed the problem: — Nam, Aug 05 '16 at 09:45

Paulo Amaral · Answer 2 · 2016-08-05T10:08:47.963

Use SqlCommand to dynamically build your query avoiding risks of injection:

string EFTGeneral = @"CREATE TABLE [EFT.@tableName] (
                                [TestOrderName]   VARCHAR(20)  NOT NULL,    
                                [Template]        VARCHAR(50)  NULL,
                                [Purpose]         VARCHAR(100) NULL,
                                [Category]        VARCHAR(50)  NULL,
                                [DateCreated]         DATETIME NULL,
                                [CreatedBy]       VARCHAR(30)  NULL,
                                [TestObjectBatch] VARCHAR(20)  NULL,
                                [LoadDirection]   VARCHAR(2)   NULL,
                                [ElementType]     VARCHAR(10)  NULL,
                                [FatigueTestType] VARCHAR(50)  NULL,
                                [LoadAmplitude]            INT NULL,
                                [LoadStatic]      INT NULL,
                                [UntilBreakage]   VARCHAR(5)   NULL,
                                [NumberOfObject]           INT NULL,
                                [Remarks]         VARCHAR(200) NULL,
                                PRIMARY KEY CLUSTERED([TestOrderName] ASC));";

using (SqlConnection connection = new SqlConnection(yourConnectionString))
{
    using (SqlCommand command = new SqlCommand(EFTGeneral, connection))
    {
        SqlParameter tableNameParam = new SqlParameter("tableName", SqlDbType.Varchar);
        tableNameParam.Value = textBox.Text;
        command.Parameters.Add(tableNameParam);
        command.ExecuteNonQuery();
    }
}

Plz notice I fixed `CREATE TABLE [EFT.@tableName] (` in the code — Paulo Amaral, Aug 05 '16 at 10:09

score 0 · Answer 3 · answered Aug 05 '16 at 09:46

Here is my code and now I can create SQL Table name as I want:

try
            {
               string EFTGeneral = @"CREATE TABLE [EFTGNR] (
                                [TestOrderName]   VARCHAR(20)  NOT NULL,    
                                [Template]        VARCHAR(50)  NULL,
                                [Purpose]         VARCHAR(100) NULL,
                                [Category]        VARCHAR(50)  NULL,
                                [DateCreated]         DATETIME NULL,
                                [CreatedBy]       VARCHAR(30)  NULL,
                                [TestObjectBatch] VARCHAR(20)  NULL,
                                [LoadDirection]   VARCHAR(2)   NULL,
                                [ElementType]     VARCHAR(10)  NULL,
                                [FatigueTestType] VARCHAR(50)  NULL,
                                [LoadAmplitude]            INT NULL,
                                [LoadStatic]      INT NULL,
                                [UntilBreakage]   VARCHAR(5)   NULL,
                                [NumberOfObject]           INT NULL,
                                [Remarks]         VARCHAR(200) NULL,
                                PRIMARY KEY CLUSTERED([TestOrderName] ASC));";
                ////

                SqlConnection conn = new SqlConnection(ConnString);
                conn.Open();
                EFTGeneral = EFTGeneral.Replace("[EFTGNR]", "[" + txtbox_testorder.Text + ".GERNERAL]");
                SqlCommand cmd = new SqlCommand(EFTGeneral, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

C#, sqlcommand string

3 Answers3