Fixing SQL Injection in .NET and Oracle

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. The following example shows how to prevent SQL injection in .NET languages.

/*Parameterized Query for VB.NET*/


Response.Write("Return employees for department " & dept & ".<br />")

Dim strSQL As String = "SELECT * FROM employees WHERE dept = :dept ORDER BY empno"
Dim objCmd As OracleCommand = New OracleCommand(strSQL, objConn)

Dim objParam1 As OracleParameter = New OracleParameter("dept", OracleDbType.Int32)
  objParam1.Direction = ParameterDirection.Input
  objParam1.Value = dept
  objCmd.Parameters.Add(objParam1)

/*Parameterized Query for C#*/

Response.Write("Return employees for department " + dept + ".<br />");

String strSQL = "SELECT * FROM emp WHERE dept = :dept ORDER BY empno";
OracleCommand objCmd = new OracleCommand(strSQL, objConn);

OracleParameter objParam1 = new OracleParameter("dept", OracleDbType.Int32);
         objParam1.Direction = ParameterDirection.Input;
         objParam1.Value = dept;
         objCmd.Parameters.Add(objParam1);