c# mysql DELETE FROM STRING WHERE.... I try use string as table name

Question

so I have string which is named as column name how should I write string as a column name into query? Here is my code :

string pas;
MySqlConnection cnn = new MySqlConnection(connectionString);
cnn.Open();
string likvidavimas = "DELETE FROM '"+ pas +"' WHERE ID='"+ ID +"'";
MySqlCommand cmd = new MySqlCommand(likvidavimas, cnn);
cmd.ExecuteNonQuery();

ok so you are passing the Delete from SQL Text.. where are you calling the `likvidavimas.ExecuteNonQuery` ? please show all relevant code also I would change that query to utilize Parameters it's called `Parameterized Query` or look up how to use the `string.Format` command — MethodMan, Dec 17 '14 at 15:34

score 4 · Accepted Answer · answered Dec 17 '14 at 15:34

Remove the apostrophes from the tablename

MySqlCommand likvidavimas = "DELETE FROM "+ pas +" WHERE ID='"+ ID +"'";

But i would also use sql-parameters to prevent sql injection.

MySqlCommand likvidavimas = "DELETE FROM "+ pas +" WHERE ID=@ID";
likvidavimas.Parameters.AddWithValue("@ID", ID);

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

Don't leave yourself open to security issues, this is really not a good idea in my opinion. I would personally avoid the use of dynamic sql and just write out a different query for each column you need to edit. Check out this thread if you are dead set on this approach.

Like this...

string pas;
using (MySqlConnection cnn = new MySqlConnection(connectionString))
{
    string likvidavimas = string.empty;
    if (pas == "Table1")
    {
        likvidavimas = "DELETE FROM [Table1] WHERE ID=@ID";
    }
    else if (pas == "Table2")
    {
        likvidavimas = "DELETE FROM [Table2] WHERE ID=@ID";
    }
    likvidavimas.Parameters.AddWithValue("@ID", ID);
    cnn.Open();
    using (MySqlCommand cmd = new MySqlCommand(likvidavimas, cnn))
    {
        cmd.ExecuteNonQuery();
    }
}

Also, I always suggest using using to help clean up your resources at the end of execution. Not sure what MySqlCommand will be, but if it is IDisposable I'd say wrap it as well.

edited May 23 '17 at 12:20

Community

1
1

answered Dec 17 '14 at 15:36

Volearix

1,573
3
23
49

The OP is deleting rows from a table, not editing columns. Though I agree it would be better to hard code the table names if possible. – juharr Dec 17 '14 at 15:42
I know, it's just a terrible idea to go this route. Someone could still fairly easily wipe out their db with this wide open query. Taking these kinds of risks scare me! :o Not worth the "convenience". – Volearix Dec 17 '14 at 15:44
It really depends on how the `pas` variable is set. This might be inside of a private function and all calls to it pass a hard coded value for `pas`. – juharr Dec 17 '14 at 15:48
This is true, but based on the above code, I doubt it. – Volearix Dec 17 '14 at 15:49
Actually based on the code `pas` is `null`, but I assume the OP has left something out. – juharr Dec 17 '14 at 15:50
Then ACTUALLY based on the code we can safely deduct that there is no private function and `pas` is `null`. :D – Volearix Dec 17 '14 at 15:54
How can you delete from a column? If you want to delete a value you do `UPDATE TableName SET ColumnName = NULL WHERE ID = @ID` – Tim Schmelter Dec 17 '14 at 16:07
@TimSchmelter Hehe... Oops! Through `SQL` magic of course! I updated it to `Table`s. – Volearix Dec 17 '14 at 16:08

c# mysql DELETE FROM STRING WHERE.... I try use string as table name

2 Answers2