C#: SQL Injection Protection Using Parameterized Queries
Overview
Assuming you have two TextBoxes, where you ask the user to input a username and a password. Your concatenated SQL Query could look like this...
"SELECT userName FROM Users WHERE userName='" + TextBox1.Text + "' AND passWord='" + TextBox2.Text + "'"
Now, if TextBox1.Text = "admin" and TextBox2.Text = "admin", then that SQL Query becomes...
"SELECT userName FROM Users WHERE userName='admin' AND passWord='admin'"
Which is a correct Query, and will return userName if there is a user with userName 'admin' with a passWord 'admin' in your Users Table.
But SQL allows multiple Statements in one Query, and also allows bypassing usernames and passwords with String Concatenation, so if
TextBox1.Text = "' OR 'a' = 'a" and TextBox2.Text = "' OR 'a' = 'a", then that SQL Query becomes...
"SELECT userName FROM Users WHERE userName='' OR 'a'='a' AND passWord='' OR 'a'='a'"
Which would always return true, and give access to the first record in the Users Table. This is known as SQL Injection.
More malicious commands could also be executed if TextBox1.Text = "' OR 'a' = 'a" and TextBox2.Text = "' OR 'a' = 'a'; DROP TABLE Users-- ", then that SQL Query becomes...
"SELECT userName FROM Users WHERE userName='' OR 'a'='a' AND passWord='' OR 'a'='a'; DROP TABLE Users"
Which when executed would delete the Users Table. But obviously, you'd need more information about the structure of the database to do much more than logging in by SQL Injection.
Parameterized Queries don't insert user input directly into the SQL String. Values obtained from the user are parsed to ensure SQL injection doesn't happen.
Code for String Concatenation
public static string insecureLogin(string userName, string passWord)
{
MySqlConnection conn = getConnection( "details" );
MySqlCommand command = new MySqlCommand("SELECT userName FROM Users WHERE userName='" + userName + "' AND passWord='" + passWord + "'", conn);
object o = command.ExecuteScalar();
conn.Close();
return ((o == null) ? "Error logging in" : "You successfully logged in as " + o.ToString());
}
Code for Parameterized Queries
public static string secureLogin(string userName, string passWord)
{
MySqlConnection conn = getConnection( "details" );
MySqlCommand command = new MySqlCommand("SELECT userName FROM Users WHERE userName=@userName AND passWord=", conn);
command.Parameters.AddWithValue( "@userName" , userName);
command.Parameters.AddWithValue( "@passWord" , passWord);
object o = command.ExecuteScalar();
conn.Close();
return ((o == null) ? "Error logging in" : "You successfully logged in as " + o.ToString());
}
Conclusion
This example shows the weaknesses of concatenated SQL Query strings, and demonstrates how to use Parameterized Queries when using user input in an SQL statement, which prevents SQL Injection Attacks.