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 VB.NET 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:
…
Dim UserName as String = Ctx.getAuthenticatedUserName()
Dim Query as String = “SELECT * FROM items WHERE owner = “‘” _
& userName + “‘ AND itemname = ‘” _
& ItemName.Text + “‘”
Dim Sda as SqlDataAdapter = New SqlDataAdapter(Query, conn)
Dim Dt as DataTable = 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:
Dim Sql As String = “SELECT * FROM Users WHERE Username = @Username” &
” AND Password = @Password”
Using conn As New SqlConnection(“…”)
Using cmd As New SqlCommand (sql, conn)
‘ First parameter value
Dim P As New SqlParameter (“@Username”,SqlDbType.VarChar, 100)
P.Value = Username.Text
Cmd.Parameters.Add(P)
‘ Add the second parameter to parameters collection
Dim P2 As New SqlParameter(“@Password”, SqlDbType.VarChar, 100)
P2.Value = Password.Text
Cmd.Parameters.Add(P2)
Using Dr As SqlDataReader = Cmd.ExecuteReader()
…
End Using
End Using
End Using
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.