create table with parameter as name mysql (c#)

Question

I want to dynamically create tables for each chat in a chatprogram but can't really find a way to do it. This is what i tried:

   string cmdct = "create table @parameter1 (message varchar(500), sender varchar(20), recieved varchar(5), sendtime varchar(60), deleted varchar(5))";
                MySqlCommand m2 = new MySqlCommand(cmdct, connect2);
            string name = "" +participant1+ "_" +participant3+ "";
            m2.Parameters.AddWithValue("@parametertb1", name);
           
       
                m2.Connection = connect2;
                connect2.Open();

                string MyConnection2 = connectionString2;
                MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
                
                errorbox.Content = "Loading...";

                MySqlDataReader MyReader2;
                MyConn2.Open();
                MyReader2 = m2.ExecuteReader();
               
                while (MyReader2.Read())
                {
                    errorbox.Content = "Creating chat...";
                }
                MyConn2.Close();

This is the error produced:

MySql.Data.MySqlClient.MySqlException: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''part1_part2' (message varchar(500), sender varchar(20), gotten varchar(5),' at line 1"

part1 and part2 are the users I want to have a chat.

The actual problem aside this seems like a horrible design choice to create a table for each chat message. — Filburt, Sep 18 '20 at 13:49
@Filburt I am just trying around, but I'm thankful for suggestions — Terry F., Sep 18 '20 at 13:51
Does this answer your question? [SqlParameter does not allows Table name - other options without sql injection attack?](https://stackoverflow.com/questions/17947736/sqlparameter-does-not-allows-table-name-other-options-without-sql-injection-at) — , Sep 18 '20 at 13:53

score 2 · Answer 1 · answered Sep 18 '20 at 13:48

I want to dynamically create tables for each chat

That's probably a bad idea. Having one big table for all of the chats and differentiating them by identifying data would likely be a better approach. Having said that...

Query parameters are for values, not schema objects. Dynamically including schema objects is a job for string concatenation. As you can imagine, this comes with a risk of SQL injection. So you need to control the values being used. To that end, where do these participant1, etc. variables come from?

If they're coming from user input or anything indirectly user-modifiable, you're at risk. But if they're values that you control (such as internal identifiers for users which they can't edit) then you can safely use them in your string concatenation for the query.

Ultimately you're probably better off with a static schema that you control rather than a dynamic one that your users control. For example, instead of creating a new table for a "new chat", why not have a table of "chats" with a simple auto-incrementing key and a table for "messages" with a foreign key to "chats"? Sure, "messages" can grow substantially over time. But that's a different problem with different solutions (and something you can measure before deciding if it even is a problem).

But how does the client recognize which message belongs to his chat in one big table? — Terry F., Sep 18 '20 at 13:53
@TerryF.: That's what the second "chats" table would be for. Any unique "chat session" would be a record in that table, and all of the "messages" would have a foreign key to that record. Any given user would be viewing the records which match that specific chat session. — David, Sep 18 '20 at 13:54

create table with parameter as name mysql (c#)

1 Answers1