none
Is it possible to parameterize a table name in SQL SqlCommandText? RRS feed

  • Question

  • I have this C# code and I get an exception on @tableName.

            private void NumericUpDown_PG1_ValueChanged(object sender, System.EventArgs e)
            {
                string tableName = combo_SQLCodePackages_PG1.Text;
           
                if (tableName == System.String.Empty)
                {
                    TSP.textToSpeech2("Please select a database first");
                    return;
                }
                numericUpDown_PG1.Maximum = 1000000;
                int max = GetMaxRecord_ID_Number();
                tLabRecordCount_PG1.Text = max + " records";
                decimal value = numericUpDown_PG1.Value;
                Service service = new Service();
                Server srv = service.GetServer("SqlCodeSamples");
                using (SqlConnection conn = new SqlConnection
                                    (srv.ConnectionContext.ConnectionString))
                {
                    conn.Open();
                    SqlCommand cmdm = new SqlCommand();
                    cmdm.Connection = conn;
                    cmdm.CommandType = CommandType.Text;
                    cmdm.CommandText = "SELECT * FROM [dbo].[@tableName] WHERE [RECORD_ID] = @record_ID";
                    cmdm.Parameters.Clear();
                    cmdm.Parameters.Add("@tableName",SqlDbType.Text).Value = tableName;
                    cmdm.Parameters.Add("@record_ID",SqlDbType.Int).Value = this.numericUpDown_PG1.Value;
                    try
                    {
                        using (SqlDataReader rdr = cmdm.ExecuteReader(CommandBehavior.SequentialAccess))
                        {
                            if (rdr.HasRows == true)
                            {
                                foreach (System.Data.Common.DbDataRecord row in rdr)
                                {
                                    numericUpDown_PG1.Value = (int)row["RECORD_ID"];
                                    tBoxRecords_PG1.Text = (string)row["CODE_TITLE"];
                                    richTextBox1_PG1.Text = (string)row["SQL_CODE"];
                                }
                            }
                            if (rdr.IsClosed == false)
                            {
                                rdr.Close();
                            }
                        }
                    }
                    catch (System.Exception ex)
                    {
                        System.Console.WriteLine(ex.Message + "  " + ex.InnerException);
                        TSP.textToSpeech2("Operation failed");
                    }
                }
            }     // NumericUpDown_PG1_ValueChanged

    I get an Exception that

    Invalid object name 'dbo.@tableName'

    That's it. Why is it so?

    Thanks, -MyCatAlex


    • Edited by MyCatAlex Friday, January 17, 2020 9:36 PM
    Friday, January 17, 2020 9:36 PM

Answers

  • I get an Exception that

    Invalid object name 'dbo.@tableName'

    That's it. Why is it so?

    The way you have written your code, it is actually possibly to get the code to run. You would need to create the table @tableName first:

    CREATE TABLE [@tableName] (RECORD_ID int NOT NULL)

    But please don't that, unless you really like confusing stuff. :-)

    To answer the question in the subject line, no you cannot pass a table name as a parameter. The basic idea for a relational database is that each table models a unique entity with its own specific set of attributes (i.e. columns). So from this model, it does not really make much sense of making the table name a parameter. So in this case you need to inline the value:

    "SELECT * FROM [dbo].["  + @tableName + "] WHERE [RECORD_ID]"

    (And even better would be to have a function that adds the brackets and doubles any right brackets.)

    This can also be legal syntax:

    SELECT * FROM @tbl

    In this case @tbl is a table variable, that is a form of a local temp table. And these can be passed as parameters. But that is obviously not what your are asking about here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MyCatAlex Saturday, January 18, 2020 1:13 AM
    Friday, January 17, 2020 10:21 PM
  • (And even better would be to have a function that adds the brackets and doubles any right brackets.)

    What do you mean by that? Could you explain in detail?

    It's all about SQL injection. I don't really think that the table name in your case comes from user input, but as programmers we should always have SQL injection in mind. That is, a user can enter data which causes your program to something else than intended.
    I have written abour it here: http://www.sommarskog.se/dynamic_sql.html#sqlinjection
    you would need to need to read section 3.2 as well, to see an example how the brackets can be exploited.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MyCatAlex Saturday, January 18, 2020 1:32 PM
    Saturday, January 18, 2020 10:25 AM

All replies

  • I get an Exception that

    Invalid object name 'dbo.@tableName'

    That's it. Why is it so?

    The way you have written your code, it is actually possibly to get the code to run. You would need to create the table @tableName first:

    CREATE TABLE [@tableName] (RECORD_ID int NOT NULL)

    But please don't that, unless you really like confusing stuff. :-)

    To answer the question in the subject line, no you cannot pass a table name as a parameter. The basic idea for a relational database is that each table models a unique entity with its own specific set of attributes (i.e. columns). So from this model, it does not really make much sense of making the table name a parameter. So in this case you need to inline the value:

    "SELECT * FROM [dbo].["  + @tableName + "] WHERE [RECORD_ID]"

    (And even better would be to have a function that adds the brackets and doubles any right brackets.)

    This can also be legal syntax:

    SELECT * FROM @tbl

    In this case @tbl is a table variable, that is a form of a local temp table. And these can be passed as parameters. But that is obviously not what your are asking about here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MyCatAlex Saturday, January 18, 2020 1:13 AM
    Friday, January 17, 2020 10:21 PM
  • (And even better would be to have a function that adds the brackets and doubles any right brackets.)

    What do you mean by that? Could you explain in detail?

    Thanks, - MyCatAlex

    Saturday, January 18, 2020 1:17 AM
  • (And even better would be to have a function that adds the brackets and doubles any right brackets.)

    What do you mean by that? Could you explain in detail?

    It's all about SQL injection. I don't really think that the table name in your case comes from user input, but as programmers we should always have SQL injection in mind. That is, a user can enter data which causes your program to something else than intended.
    I have written abour it here: http://www.sommarskog.se/dynamic_sql.html#sqlinjection
    you would need to need to read section 3.2 as well, to see an example how the brackets can be exploited.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MyCatAlex Saturday, January 18, 2020 1:32 PM
    Saturday, January 18, 2020 10:25 AM