0

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();`
        }
    }
}
7
  • Parameters cannot be used to represent table and column names. Commented May 13, 2017 at 15:52
  • What is happening instead of the intended result? Commented May 13, 2017 at 15:56
  • @Sty it says near "@naam": syntax error... Commented May 13, 2017 at 16:02
  • 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... Commented May 13, 2017 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". Commented May 13, 2017 at 16:08

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

4 Comments

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?
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).
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 comes to mind).
Sure. But just because you can doesn't mean you should.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.