is there a more efficient way to write this sql code?

Contrary to the suggestions in the comments, you shouldn’t need a stored procedure to protect against sql injection. You can use parameterized queries to dothat.

The following code should do the job:

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;

List<string> names = new List<string>();
for (int i = 0; i < symptons.Length; i++)
    names.Add("@Param_" + i);
    cmd.Parameters.Add(new MySqlParameter("@Param_" + i, symptons[i]));
cmd.CommandText = "select d.dname from disease d inner join diseasesymptom ds on ds.did = d.did inner join symptom s on s.sid = ds.sid where s.sname in (" + string.Join(",", names) + ")"; 

Basically you don’t inject the values, but inject the parameter for the query instead. The parameter names are generated in your code so they can’t be messed with. The values for the parameters are sanitized by the driver before the query is executed so those can’t be messed with either.

Leave a Comment