0

Trying to create SQL tables in ado.net with code. I've been using the parameterized query to create the table which works but im also getting an second table with the name @name which is obvious because im passing it too. Now my question is how do i prevent the table creating of @name ?

Here is my query

public void CreateTable(string name)
        {
            string connectionString = @"Data Source=tcp:SRV-SKYSPARK,1434;Initial Catalog=Trevi-NV;Integrated Security=True;Pooling=False";
            string query =
                @"CREATE TABLE [dbo].[@name] (
                [Id]          INT         IDENTITY (1, 1) NOT NULL,
                [TimeStamp]   DATETIME    NOT NULL,
                [TagName]     NCHAR (64)  NOT NULL,
                [Value]       NCHAR (32)  NOT NULL,
                [TagType]     NCHAR (64)  NULL,
                [Description] NCHAR (128) NULL
                );";
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(query, cn))
            {
                cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = name;
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
Charlieface
  • 52,284
  • 6
  • 19
  • 43
UwUs
  • 67
  • 1
  • 9
  • Check this https://stackoverflow.com/a/28598876/18452174 – Victor May 20 '22 at 09:23
  • Thats not working for me. – UwUs May 20 '22 at 09:30
  • Parameters work only for values, not identifiers. – Fildor May 20 '22 at 09:39
  • Whats the best way to do it ? – UwUs May 20 '22 at 09:42
  • If you replace @name with {0}, use string.Format to set the name in the query string and remove the use of Parameters, it must work. – Victor May 20 '22 at 09:50
  • Alright, it's working thanks! forgot the {0}. – UwUs May 20 '22 at 09:54
  • Does this answer your question? [How to create a table with name as parameter?](https://stackoverflow.com/questions/28598828/how-to-create-a-table-with-name-as-parameter) – Markus May 20 '22 at 10:20
  • Does this answer your question? [A table name as a variable](https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable) – Charlieface May 20 '22 at 13:10
  • 1
    @Victor That doesn't provide the protection against SQL injection offered by [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql). Nor does having square brackets around the inserted string. – HABO May 20 '22 at 13:31
  • @HABO You'r right! I wasn't thinking about an attack. I had assumed that the creation of the table is under his control, not as a parameter obtained from the user that he need to validate. – Victor May 20 '22 at 14:15
  • Try this Nuget library to simplify Ado.net code : https://www.nuget.org/packages/Ado.Entity – Nihar Sarkar Aug 03 '22 at 22:00

0 Answers0