My idea is to create some generic classes for Insert/Update/Select via a C# (3.5) Winforms app talking with a MySQL database via 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 a query with/without user input by just passing a SQL query string.
Reading around on SO is starting to give me the indication that this may lead to SQL injection attacks (for any user-input values). Is there anyway of scrubbing the inputted strSQL or do I need to go and create individual parameterized queries in every method that needs to do a 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();
}}
Parametrization is very easy to do. Much easier than scrubbing SQL queries, and less messy or error prone than manual escaping.
Slightly edited copy/paste from this tutorial page because I'm feeling lazy:
// User input here
Console.WriteLine("Enter a continent e.g. '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.
}
That wasn't so hard, was it? :)