C # and MySQL.NET Connector - Any way to prevent SQL Injection attacks in a generic class?

Question

My idea is to create some common classes for Insert / Update / Select through a C # (3.5) Winforms application that talks to a MySQL database through MySQL.NET Connector 6.2.2.

For example:

public void Insert(string strSQL) { if (this.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(strSQL, connection); cmd.ExecuteNonQuery(); this.CloseConnection(); } } 

Then, from anywhere in the program, I can run the query with / without user input, simply passing the SQL query string.

Reading on SO is starting to give me an indication that this could lead to SQL injection attacks (for any custom input values). Is there a way to clear the entered strSQL, or do I need to go and create individual parameterized queries in each method that should execute the database function?

Update1:

My final solution looks something like this:

 public void Insert(string strSQL,string[,] parameterValue) { if (this.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(strSQL, connection); for(int i =0;i< (parameterValue.Length / 2);i++) { cmd.Parameters.AddWithValue(parameterValue[i,0],parameterValue[i,1]); } cmd.ExecuteNonQuery(); this.CloseConnection(); }} 

Answer 1

You must be sure to use parameterized queries to stay safe.

You do not need to manually create parameterized queries every time. You can change the general method that you provided to accept the MySqlParameters collection:

 public void Insert(string strSQL, List<MySqlParameter> params) { if(this.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(strSQL, connection) foreach(MySqlParameter param in params) cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); this.CloseConnection(); } } 

I should also mention that you should be VERY careful in clearing your connections after you finish using them (usually handled in the using block, but I don't see this level of detail in your sample code).

Answer 2

Parameterization is very simple. Much easier than clearing SQL queries and less messy or error prone than manual escaping.

A little edited copy / paste from this page of the tutorial because I feel lazy:

 // User input here Console.WriteLine("Enter a continent eg 'North America', 'Europe': "); string userInput = Console.ReadLine(); string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent=@Continent"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@Continent", userInput); using (MySqlDataReader dr = cmd.ExecuteReader()) { // etc. } 

It wasn’t that hard, was it? :)

Answer 3

It is not possible to detect SQL injection after the fact (in other words, once you have built a dynamic query string, it is impossible to distinguish what "real" SQL is from any SQL entered).

If you intend to allow users to execute arbitrary SQL, then it seems that you will not worry too much about SQL injection (since this is the purpose of SQL injection).

Answer 4

I would expect it to be quite difficult to clean up the source text that will be used for SQL. If at all possible, I will try to use parameterized operations.

One exception would be if you didn’t publish this function publicly and never passed a string created from the original user input.

Answer 5

if you use MySqlParameter and do not generate simple string queries that you are safe.

Answer 6

You cannot do this - you need to write an SQL parser that is at least non-trivial and error prone.

Bite a bullet and parameterize your queries.

Answer 7

I would suggest using IDataParameter objects to parameterize your queries.

Answer 8

YES you need to create parameterized queries, something else will lead to the risk of SQL injection