Prevent SQL Injections using SQL Parameters on VB.Net

Making a good quality software application does not focus only on delivering a complete working program with an awesome user interface but must also consider the most ever important topic – Security.

In this article, I will discuss on how to secure your codes from SQL Injection attacks. But first let me give you a clear definition of what is a SQL Injections and why is it important not to ignore it?

What is SQL Injection?

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). – Wikipedia.org

How to know if my SQL statements are vulnerable to these attacks?

If the data input was not properly validated and sanitized. Let say, If a user inputs a single quote (‘) character, the original SQL statement could be modified and could show sensitive information or otherwise compromise the server.

Example of a Vulnerable Code:

conn = New SqlConnection(constr)

conn.Open()

comm = New SqlCommand("Insert into tbl_info(name,age,gender,address) 

values('" & txtname.Text & "','" & txtage.Text & "','" & txtgender.Text 

& "','" & txtadd.Text & "')", conn)

affector = comm.ExecuteNonQuery

How to prevent these attacks?

To prevent this to happen, you may use a parametized statements.

• Example of a Parametized SQL statement to save data.

 conn = New SqlConnection

conn.ConnectionString = constr

conn.Open()

comm = New SqlCommand("Insert into tbl_info 

values(@id,@name,@age,@gender,@address)", conn)

comm.Parameters.Add(New SqlClient.SqlParameter("id", txtid.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("name", txtname.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("age", txtage.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("gender", txtgender.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("address", txtadd.Text))

comm.ExecuteNonQuery()

• Example of a Parametized SQL statement to update data.

conn = New SqlConnection

conn.ConnectionString = constr

conn.Open()

comm = New SqlCommand("Update tbl_info set 

name=@name,age=@age,gender@gender,address@address where id ='" & 

get_old_ID & "')",conn)

comm.Parameters.Add(New SqlClient.SqlParameter("name", txtname.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("age", txtage.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("gender", txtgender.Text))

comm.Parameters.Add(New SqlClient.SqlParameter("address", txtadd.Text))

comm.ExecuteNonQuery()

Try to implement parametized SQL statements the next time you develop an application to avoid the consequences of being attacked by a malicious SQL statement that could happen by accident or the worst - intentional.