Fixing SQL Injection in JSP and Oracle

Posted by on May 26, 2011 0 Comment

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 JSP can be used to interact with an Oracle database to retrieve values based on user input. Using the following methods you can filter any malicious input from the user thereby preventing any possible SQL Injection.

 

Parameterized Query

<pre>
// Prepare a query containing a bind variable.
   String sql = "SELECT * FROM emp WHERE dept = ? ORDER BY empno";
   PreparedStatement stmt = conn.prepareStatement(sql);

// Bind the value into the prepared statement.
   stmt.setInt(1, new Integer(dept).intValue());

// Execute the completed statement.
   ResultSet rs  = stmt.executeQuery();

Fixing SQL Injection in .NET and Oracle

Posted by on May 26, 2011 0 Comment

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);

Fixing SQL injection in ASP and Oracle

Posted by on May 26, 2011 0 Comment

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 a malicious input in ASP by filtering user input before it is passed to Oracle.

Parameterized query

Dim cmd, rs

Response.Write "Return employees for department " & dept & ".<br />"
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM emp WHERE dept = ? ORDER BY empno"
cmd.CommandType = adCmdText

'name, type, direction, size, value
cmd.Parameters.Append cmd.CreateParameter ("dept", adInteger, adParamInput, , CInt(dept))

Set rs = cmd.Execute

Fixing SQL Injection in PHP and Oracle

Posted by on May 26, 2011 1 Comment

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 a malicious input in PHP by filtering user input before it is passed to Oracle.

Parameterized Query

<?php

// Accept a parameter called "dept" from a form.
$dept = $_POST['dept'];

// Connect to the Oracle databse
$conn = oci_connect("scott", "tiger", "DB10G");

if (!$conn) {
    echo "Unable to connect: " . var_dump(oci_error());
    die();
} else {
    echo "Connected sucessfully.n";
}

// Parse a query containing a bind variable.
$stmt = oci_parse($conn, "SELECT * FROM emp WHERE dept = :dept ORDER BY empno");

// Bind the value into the parsed statement.
oci_bind_by_name($stmt, ":dept", $dept);

// Execute the completed statement.
oci_execute($stmt, OCI_DEFAULT);

while (oci_fetch($stmt)) {
    $empno = oci_result($stmt, "EMPNO");
    $ename = oci_result($stmt, "ENAME");
}
oci_free_statement($stmt);
}
?>