Fixing SQL Injection in PHP 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 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);
}
?>