What is SQL Injection and how to avoid it?

One of the greatest security loop holes and causes of great impairment to computerized systems is a hacking technique called SQL injection.

As Wikipedia's definition, SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application (like queries). The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It happens from using poorly designed query language interpreters.

Hackers use this technique to inject malicious code into statements that is executed dynamically on SQL Servers, often from accounts with higher privileges. This attack can be done when you construct your SQL code by concatenating strings.

To avoid SQL Injection, for example in VB.Net code below:

Dim cmd As SqlCommand = New SqlCommand
Dim ID as Integer

'Opening database connection
con.Open()

With cmd
.CommandText = "SELECT Name FROM Customers WHERE ID= '" & ID & "'"
.CommandType = CommandType.Text
.Connection = con
.ExecuteScalar
End With

'Closing database connection
con.Close()
con = Nothing

In the example code above, do not use this when calling SQL statements on your SQLDataAdapter or SQL Command. Instead use an SQL parameter variable to pass the parameter instead of concatenating strings when you call your variable. Like this one below:

Dim cmd As SqlCommand = New SqlCommand
Dim ID as Integer

'Opening database connection
con.Open()

With cmd
.CommandText = "SELECT Name FROM Customers WHERE ID=@ID"
.CommandType = CommandType.Text
.Connection = con
.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int, 100)).Value = ID
.ExecuteScalar
End With

'Closing database connection
con.Close()
con = Nothing

The second one will avoid SQL Injection. Hackers don't have an entry point because you are not exposing your variable in your SQL Statement. I hope you get this one and you'll have a better implementation with this on your .Net code.