CODEWITHSUNDEEP
c#.net.net-3.5
RKh
RKh

Reputation: 14159

Passing query as parameter

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.

Upvotes: 1

Views: 213

Answers (3)

Kamyar
Kamyar

Reputation: 18797

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.

Upvotes: 1

ForeverNights
ForeverNights

Reputation: 631

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.

Upvotes: 0

Pieter van Ginkel
Pieter van Ginkel

Reputation: 29640

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.

Upvotes: 1

Related Questions