Select from where MySQL Data in C#

Question

i have select query which i made a method off so i can call it anywhere instead of writing query command again and again

public string mysql_execute_selectfromwhere(string select ,string from, string where, string equalsto)
        {
            ConnMySql.Open();
            MySqlCommand com = ConnMySql.CreateCommand();
            com.CommandText = "SELECT @1 FROM @2 WHERE @3=@4";
            com.Parameters.AddWithValue("@1", select);
            com.Parameters.AddWithValue("@2", from);
            com.Parameters.AddWithValue("@3", where);
            com.Parameters.AddWithValue("@4", equalsto);
            string returnstring = Convert.ToString(com.ExecuteScalar());
            ConnMySql.Close();
            return returnstring;
        }

this is how im calling this method

string get = mysql_execute_selectfromwhere("label_name", "label_fields", "lable_id", "17");

im getting following mysql syntax error and i cant seem to understand it properly

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 "label_fields' WHERE 'lable_id'='17" at line 1

please also highlight if there is any other problem with my procedure. Thank you

please note that i have tried putting ' arround variables like com.CommandText = "SELECT '@1' FROM '@2' WHERE '@3'='@4'"; and still get syntax error — Usama Asif, Jul 06 '22 at 06:57
Could you print the generated query to see how it's build and to try to identify error in it ? — Clement, Jul 06 '22 at 07:00
The value 17 is passed as a string which makes the condition 'lable_id'='17'. The id is supposed to be int, isn't it? — Wael Moughrbel, Jul 06 '22 at 07:02
@WaelMoughrbel i tried ur version passing 17 as int and still get error.>> MySQL server version for the right syntax to use near ''label_fields' WHERE 'lable_id'=17' at line 1 — Usama Asif, Jul 06 '22 at 07:13
Parameters cannot be used to express Column or Table names. They can be used only to express values. SELECT @1 ... is not a valid syntax — Steve, Jul 06 '22 at 07:16
@Steve This is plausible.. How would i modify my query to pass down tables or column names? — Usama Asif, Jul 06 '22 at 07:23
In MySql I don't think there is any kind of workaround. Sql Server has "dynamic sql" but it creates more problems. I really suggest you to forget this "do_it_all" solution. They never works for every possible situation. Instead spend a bit of your time to learn an ORM (the simpler Dapper or the big guy EntityFramework) — Steve, Jul 06 '22 at 07:26

score -1 · Accepted Answer · answered Jul 06 '22 at 07:25

-1

Try:

    com.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}=@equalsto", select, from, where);
    command.Parameters.Add("@equalsto", SqlDbType.Int);
    command.Parameters["@equalsto"].Value = equalsto;

answered Jul 06 '22 at 07:25

Tan

149
1
9

2

This will be prone to Sql Injection. Don't do it – Steve Jul 06 '22 at 07:27
Okay this Works perfectly. but im afraid of what @Steve said. Any workaround? – Usama Asif Jul 06 '22 at 07:29
Alright im marking this as accepted answer with **waring** because it Does what i asked for. But please be aware of **sql injections** as suggested by @Steve . my application is strictly local and low risk so i suppose i would use this. – Usama Asif Jul 06 '22 at 07:37
This problem has been given a solution from previous topics, see [here](https://stackoverflow.com/questions/2775692) – Tan Jul 06 '22 at 07:40
I also agree with Steve about these risks, luckily for you there are frameworks that do this very well like EntityFramework, you can learn about them – Tan Jul 06 '22 at 07:47

score -2 · Answer 2 · answered Jul 06 '22 at 07:02

-2

com.CommandText = "SELECT @1 FROM @2 WHERE @3=@4"; try this instead of com.CommandText = "SELECT '@1' FROM '@2' WHERE '@3' ='@4'"; single quotation with variable which are you using in command text.

answered Jul 06 '22 at 07:02

Zafar Habib

1
2

As what I see in his post, he is already using com.CommandText = "SELECT @1 FROM @2 WHERE @3=@4"; – Clement Jul 06 '22 at 07:04
I am not talking about com.CommandText, I just told him/her that try to use a single quote with variables you are using in com.CommandText – Zafar Habib Jul 06 '22 at 07:08
@ZafarHabib i already tried using single quotes arround parameters as i said in comment to my original post. it doesnt change the error..>>for the right syntax to use near ''@2' WHERE '@3'='@4'' at line 1 <<.. i think my problems lies within query after "from" – Usama Asif Jul 06 '22 at 07:20
This answer is wrong. There is no way to use parameters in place of column or table names. – Steve Jul 06 '22 at 07:20
can you share your project ? – Zafar Habib Jul 06 '22 at 07:56
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 07 '22 at 09:35

Select from where MySQL Data in C#

2 Answers2