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

by

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.

More Related Contents:

  1. Error: You have an error in your SQL syntax – but SQL statement works
  2. Quotes in string [closed]
  3. Parameterized Query for MySQL with C#
  4. LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression
  5. How to connect to MySQL Database?
  6. Exception: There is already an open DataReader associated with this Connection which must be closed first
  7. .NET Core 2.1 Identity get all users with their associated roles
  8. MySqlCommand Command.Parameters.Add is obsolete
  9. LINQ to SQL multiple tables left outer join
  10. Enable Entity Framework 6 for MySql (C#) in WinForms of Microsoft Visual Studio 2013
  11. Convert DateTime for MySQL using C#
  12. Dynamic MySQL database connection for Entity Framework 6
  13. C# with MySQL INSERT parameters
  14. Get the id of inserted row using C#
  15. Unable to connect to any of the specified mysql hosts. C# MySQL
  16. Why do I get “System.Data.DataRowView” instead of real values in my WinForms Listbox?
  17. Entity Framework creates a plural table name, but the view expects a singular table name?
  18. MySQL Entity Framework Error – The specified store provider cannot be found in the configuration, or is not valid
  19. Asking for legitimate example of calling stored procedure C#: MYSQL
  20. Using SqlDataAdapter to insert a row
  21. Authentication with old password no longer supported, use 4.1 style passwords
  22. Get affected rows on ExecuteNonQuery
  23. Index (zero based) must be greater than or equal to zero
  24. How to pass a table as parameter to MySqlCommand?
  25. Connection to MySQL from .NET using SSH.NET Library
  26. Testing an Entity Framework database connection
  27. How can I force a log out of all users for a website?
  28. Incorrect string value: ‘\xEF\xBF\xBD’ for column
  29. Could not load file or assembly ‘MySql.Data, Version=6.2.2.0
  30. MySQL C# async methods doesn’t work?

Leave a Comment