Insert query where table name is textbox text

Question

I have a problem in button1 click event of inserting data into a table which table name be determined by whatever text is in textbox1

Should mean something like that:

tablename = textbox1.text;

sql = "INSERT INTO tablename ([itemserial], [itemname], [itemcount],[itemimage]) VALUES (@itemserial, @itemname, @itemcount, @itemimage)";

do you just need to put the textbox text in the string? With C# 6 you can simply write: sql = $"INSERT INTO {tablename} ([itemserial],[itemname],[itemcount],[itemimage])VALUES(@itemserial,@itemname,@itemcount,@itemimage)"; [click here for string interpolation](https://msdn.microsoft.com/en-us/library/dn961160.aspx) — Tommaso Bertoni, Oct 10 '15 at 17:38

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

tablename = textbox1.text;

sql = string.Format("INSERT INTO {0} ([itemserial],[itemname],[itemcount],[itemimage])VALUES(@itemserial,@itemname,@itemcount,@itemimage)", tablename);

Although I would strongly recommend against this as it allows people to enter whatever they want into that textbox. Something like:

Robert; DROP TABLE Students;--

Which is discussed in more detail here: How does the SQL injection from the "Bobby Tables" XKCD comic work?

edited May 23 '17 at 11:44

Community

1
1

answered Oct 10 '15 at 17:39

Dustin_00

345
2
8

1

I don't understand why, if you know the danger of this solution, are you proposing this. – Steve Oct 10 '15 at 17:43
I answered his questions as fast as I could because I desperately needed upvotes to allow me to upvote and comment on other user's comments... which I have finally earned so I can now upvote your much more expansive answer. – Dustin_00 Oct 11 '15 at 04:22

Steve · Accepted Answer · 2015-10-10T18:06:05.077

Having a textbox containing the name of your table is challenging because you should add extra care in handling this value. You should implement some kind of checking on this textbox value. A possible solution is to check against your database schema if the table typed by your user really exists.

You don't tell us which database system are you using so I will show an example using Sql Server

string tableName = textbox1.text;
using(SqlConnection cnn = new SqlConnection(... connectionstring...))
{
    cnn.Open();
    DataTable dt = cnn.GetSchema("TABLES");
    DataRow[] rows = dt.Select("TABLE_NAME = '" + tableName + "'");
    if(rows.Length > 0)
    {
        // Now you are sure to have a valid table in your textbox
        // and could use the input value without risking an Sql Injection
        string sql = "INSERT INTO [" + tableName + "] ([itemserial]," + 
                     "[itemname],[itemcount],[itemimage]) " + 
                     "VALUES(@itemserial,@itemname,@itemcount,@itemimage)";

        .... the remainder of your code that use the query above....
    }
    else
        MessageBox.Show("Please enter a valid name for your table");

Extending this approach you could change your TextBox to a ComboBox with ComboBoxStyle set to DropDownList (to block typing) and fill the ComboBox with the names returned by the GetSchema call above....

Over time, other tables that don't conform to the data format could be added and users could cause errors. I would favor a white list approach: List allowedTables = new List() { "Table1", "table2" }; ... if (allowedTables.contains(tableName) { // then build the sql string and do the insert } — Dustin_00, Oct 11 '15 at 04:23

score 0 · Answer 3 · answered Oct 10 '15 at 17:38

0

Change your query like this

sql = "INSERT INTO "+tablename+" ([itemserial],[itemname],[itemcount],[itemimage]) VALUES (@itemserial,@itemname,@itemcount,@itemimage)";

answered Oct 10 '15 at 17:38

anas p a

383
5
20

Insert query where table name is textbox text

3 Answers3