SQL injection is used as a way to gain control over a web application by simply injecting some specially created SQL query via a parameter. This kind of the worst attack for a web application is caused by improper handling of string concatenation. The next code lines illustrate a typical problem related to SQL injection. For example the following C# code dynamically constructs and executes a SQL query that searches for items matching a specific name. The query restricts the items displayed to those where owner matches the user name of the currently-authenticated user:
…
string userName = ctx.getAuthenticatedUserName();
string query = “SELECT * FROM items WHERE owner =userName AND itemname =ItemName.Text “;
sda = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
…
The query that this code intends to execute follows:
SELECT * FROM items
WHERE owner =
AND itemname =;
However, because the query is constructed dynamically by concatenating a constant base query string and a user input string, the query only behaves correctly if itemName does not contain a single-quote character. If an attacker with the user name alan enters the string “name’ OR ‘a’=’a” for itemName, then the query becomes the following:
SELECT * FROM items
WHERE owner = alan’
AND itemname = ‘name’ OR ‘a’=’a’;
The addition of the OR ‘a’=’a’ condition causes the where clause to always evaluate to true, so the query becomes logically equivalent to the much simpler query:
SELECT * FROM items;
This simplification of the query allows the attacker to bypass the requirement that the query only return items owned by the authenticated user; the query now returns all entries stored in the items table, regardless of their specified owner.
In other words you want to know how to write SQL queries right way and you how to stay secure and avoid SQL injection, without losing functionalities. You can change your code by taking advantage of the SqlParameter class and using a parameterized query instead of string concatenation, as shown with the next code lines:
string sql = “SELECT * FROM Users WHERE Username = @Username” + ” AND Password = @Password”;
using (SqlConnection conn = new SqlConnection(“…”))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// Parameter value
SqlParameter p = new SqlParameter(“@Username”,SqlDbType.VarChar, 100);
p.Value = Username.Text;
cmd.Parameters.Add(p);
// Add the second parameter to parameters collection
SqlParameter p2 = new SqlParameter(“@Password”,SqlDbType.VarChar, 100);
p2.Value = Password.Text;
cmd.Parameters.Add(p2);
using (SqlDataReader dr = cmd.ExecuteReader())
{
…
}
}
}
Parameterized queries are similar to stored procedures: you pass the parameters explicitly, and their encoding is the responsibility of the underlying data access technology, not yours.
Important notes:
1. You can use the same techniques with Access, Oracle, and MySQL. The SQL server supports the format @param, known as named parameter. Access (and OLEdb) uses the sequential order and a generic ? placeholder. Oracle uses :param and MySQL uses ?param format.
2. When you are using a parameterized query, the conversion and escape of the value is done by the engine itself. You have to check for data type consistency to avoid runtime errors and to execute only legitimate queries.