C# - using a variable as a MySQL table name

Question

I want to use a variable and pass this value into an SQL statement as part of the table name, however, I'm not sure whether the method I've used is secure (even though it works). If not, how should I go about it?

    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    string table = "1";
    MySqlCommand comm = new MySqlCommand("INSERT INTO " + table + "_tests (user_id, score, time) VALUES (@user_id, @score, @time)", conn);
    comm.Parameters.Add("@user_id", MySqlDbType.VarChar);
    comm.Parameters["@user_id"].Value = "2";
    comm.Parameters.Add("@score", MySqlDbType.VarChar);
    comm.Parameters["@score"].Value = txtScore.ToString();
    comm.Parameters.Add("@time", MySqlDbType.VarChar);
    comm.Parameters["@time"].Value = txtTime.ToString();

Updated:

    int tableID = Convert.ToInt32(Session["TestModuleID"].ToString());
    MySqlCommand comm = new MySqlCommand("INSERT INTO " + tableID.ToString() + "_tests (user_id, score, time) VALUES (@user_id, @score, @time)", conn);

@juergend Currently it's hardcoded as "1" but I plan to change that to string table = Session["TestModuleID"].ToString(); — Bhav
– Bhav, Commented Jul 12, 2014 at 19:16

O. Jones · Accepted Answer · 2014-07-12 19:18:46Z

2

You are correct that what you have works. As you have probably guessed, you can't use a Parameter for a table name.

The safety issue raised by your code can be removed by redeclaring your table variable as an integer, like so...

int table = 1;
MySqlCommand comm = new MySqlCommand (
       "INSERT INTO " + table.ToString() +   
       "_tests (user_id, score, time) VALUES (@user_id, @score, @time)", conn);

This will keep anything non-numeric from creeping into your query. There are other ways to control the value of your table variable as well.

answered Jul 12, 2014 at 19:18

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

1 Comment

Bhav Over a year ago

I've included the revised code in my original post. The variable value is derived from a Session object so nothing should creep in but I guess that is sufficiently robust?

Collectives™ on Stack Overflow

C# - using a variable as a MySQL table name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related