SQLite, create a table with a variable name in C#

Question

I searched for an answer but the only solutions I found didn't work for SQLite. I want to create different tables in my SQLite database with the name and columns defined by the user who creates the table.

I'm working on a project where users can test their knowledge of a language by creating a list that can be tested later. The list should be saved in a database as an apart table.

Image of the UI

So I want to save each list in the database with the table name equal to the "txtNaamLijst.Text" textbox. Beside that I want the columns to have the name of "txtTaal1.Text" en "txtTaal2.Text" as defined by the user.

The script that I have written doesn't work. Scripts I found on the web did not work either. I have to write my SQLite commands in a single line because I use C#:

protected void btnSave_Click(object sender, EventArgs e)
{
    using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=C:/Users/elias/Documents/Visual Studio 2017/WebSites/WebSite7/App_Data/overhoren.db"))
    {
        using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
        {
            string naam = txtNaamLijst.Text;
            string taal1 = txtTaal1.Text;
            string taal2 = txtTaal2.Text;
            string veld1 = txtVeld1.Text;
            string veld1v2 = txtVeld1v2.Text;
            string veld2 = txtVeld2.Text;
            string veld2v2 = txtVeld2v2.Text;
            string veld3 = txtVeld3.Text;
            string veld3v2 = txtVeld3v2.Text;
            string veld4 = txtVeld4.Text;
            string veld4v2 = txtVeld4v2.Text;
            string veld5 = txtVeld5.Text;
            string veld5v2 = txtVeld5v2.Text;
            string veld6 = txtVeld6.Text;
            string veld6v2 = txtVeld6v2.Text;

            conn.Open();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Parameters.AddWithValue("@naam", naam);
            cmd.Parameters.AddWithValue("@taal1", taal1);
            cmd.Parameters.AddWithValue("@taal2", taal2);
            cmd.Parameters.AddWithValue("@veld1", veld1);
            cmd.Parameters.AddWithValue("@veld1v2", veld1v2);
            cmd.Parameters.AddWithValue("@veld2", veld2);
            cmd.Parameters.AddWithValue("@veld2v2", veld2v2);
            cmd.Parameters.AddWithValue("@veld3", veld3);
            cmd.Parameters.AddWithValue("@veld3v2", veld3v2);
            cmd.Parameters.AddWithValue("@veld4", veld4);
            cmd.Parameters.AddWithValue("@veld4v2", veld4v2);
            cmd.Parameters.AddWithValue("@veld5", veld5);
            cmd.Parameters.AddWithValue("@veld5v2", veld5v2);
            cmd.Parameters.AddWithValue("@veld6", veld6);
            cmd.Parameters.AddWithValue("@veld6v2", veld6v2);

            cmd.CommandText = "CREATE TABLE IF NOT EXISTS @naam (@taal1 VARCHAR(50), @taal2, VARCHAR(50))";
            cmd.CommandText = "INSERT INTO @naam (@taal1, @taal2) SELECT @veld1 , @veld1v2 UNION ALL SELECT @veld2 , @veld2v2 UNION ALL SELECT @veld3 , @veld3v2 UNION ALL SELECT @veld4 , @veld4v2 UNION ALL SELECT @veld5 , @veld5v2 UNION ALL SELECT @veld6 , @veld6v2";

            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            conn.Close();`
        }
    }
}

Parameters cannot be used to represent table and column names. — Steve, May 13 '17 at 15:52
This question was tagged with `mysql` -- in that, you should be able to do what you want with a prepared statement. Not sure about `sqlite`. Perhaps you could build the string in C# instead of SQL. I'm more concerned about your approach -- while I don't quite follow what you're trying to do, dynamically naming columns/tables is rarely the right solution... — A C, May 13 '17 at 16:04
@AC what I'm trying to do is letting users create a table with a name they inserted in the text box. For example if user1 wants to test his knowledge about French chapter 3 he creates a table named "frenchChapter3". — ielaajez, May 13 '17 at 16:08
@ielaajez In that case, it might make sense to have a table called `subjects` and possibly one called `chapters`, and then have a table called `words` which connects those words with `user`s, `subjects` and `chapters`. Instead of creating a new *table* for a new subject/chapter, you add a row to the appropriate tables and then add that to the `words` table. (I'm assuming you're trying to store vocabulary words, and note that `chapters` might just be an integer that doesn't link to its own table...) — A C, May 13 '17 at 16:16
@AC thank you. I think that's the best thing to do right now. Your answer really helped me out! — ielaajez, May 13 '17 at 17:53

score 2 · Accepted Answer · edited May 23 '17 at 11:47

2

In both your queries you use @naam as the parameter for the table name.

cmd.CommandText = "CREATE TABLE IF NOT EXISTS @naam..."
cmd.CommandText = "INSERT INTO @naam..."
cmd.Parameters.AddWithValue("@naam", naam);

Unfortunately, parameterizing table names is not possible. User Soner Gönül put it well in this answer. He has quite a bit of information but the gist of it is:

You can not parameterize your table names, column names or any other databse objects. You can only parameterize your values.

Now, it is possible to use plain old string concentration/formatting to have a variable table name, but that is NOT a good idea, as it leaves you vulnerable to SQL injection.

edited May 23 '17 at 11:47

Community

1
1

answered May 13 '17 at 16:02

stelioslogothetis

9,371
3
28
53

So if creating a table with variable name and columns what is the best thing I could do to let users create tables for their lists? – ielaajez May 13 '17 at 16:05
It's not possible because, in general, it's not a good idea. It's a security nightmare, for one thing, and the support effort will be horrendous. Look into things like pre-defined custom fields. (i.e. CustomString1, CustomString2, CustomString3, CustomMoney1, etc.) or something like Entity-Attribute-Value (EAV). – Robert Harvey May 13 '17 at 16:06
If you are willing to put in the effort, you can use the string method and sanitize the inputs on your own (plenty of material on examples and prevention of SQL injection, [Bobby Tables](http://bobby-tables.com/) comes to mind). – stelioslogothetis May 13 '17 at 16:08
Sure. But just because you can doesn't mean you should. – Robert Harvey May 13 '17 at 16:09

SQLite, create a table with a variable name in C#

1 Answers1