Passing query as parameter.

Question

I know, it is not recommended. The possible risk of SQL Injection.

In my present app., I created a class containing reusable functions. One such function is this:

public static Int32 InsertNewRecord(string myQuery)
{
    ModCon.OpenConnection();
    MySqlCommand cmdInsert = new MySqlCommand(myQuery, ModCon.myCN);
    try
    {
        Int32 RecordsAffected = cmdInsert.ExecuteNonQuery();
        return RecordsAffected;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error:", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return 0;
    }
    finally
    {
        cmdInsert.Dispose();
        ModCon.CloseConnection();
    }            
}

This class has many such methods that can be reused. There is a method to fill a DataGridView, where in I pass the DataGridView name and SQL query to populate it.

My application is at present a standalone Windows app. What could be a professional way to achieve this without the fear of SQL Injection?

The methods above are in a class and whenever I need these methods, I create an instance of this class in another class.

Answer 1

You have either two choices:

1. You can create descriptions of your queries. You can do this by creating an InsertQuery class with a table name. With this you can add filters where you e.g. create a class EqualFilter which you can then compose with an AndFilter to get two equal comparisons;

2. If you think that's a lot of work, it is. An alternative is to go with an ORM framework like NHibernate or the Entity Framework. This does all this stuff for you, and a lot more.

Answer 2

You probably want to research on usage of regex in C#. There is bunch of resources on Google that u can easily find out. Just prevent any suspicious characters like %,=,?,& ... (which may lead to SQL injection query) that being passed to parameter myQuery of your method. That should do find. Maybe it's not professional way to do this but... i am just saying. Good luck.

Answer 3

Don't pass your parameters with your query. Create System.Data.SqlClient.SqlCommand and use Command.Parameters.Add(... to add parameters. This completely prevents SQL Injection.