Every Oracle DBA knows that using bind variables in SQL statements improves database performance. At the opposite, using literals increases hard parsing and usually causes problems in memory management.
But using literals can induce the opportunity to inject malicious SQL code.
Now imagine, a simple app to know the commission percentage of an employee (in HR schema).
This (very simple) app is based on 2 script :
- form.php which design the form (ok it could have been a basic html scripts
<HTML>
<HEAD>
<TITLE>Employees Commissions</TITLE>
</HEAD>
<BODY>
<H1>Employees Commissions</H1>
<FORM action="resp.php" method="POST">
Last Name : <INPUT type="text" name="lname" style="width:500px">
<BR/><BR/>
<INPUT type="submit" value="Submit">
</FORM>
</BODY>
</HTML>
- resp.php which is the php script that connects to the database and format html response.
<?php
## Database OCI Connection
$conn = oci_connect('hr', 'hr', '192.168.99.8/orcl');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
## Get HTML Post variables
$referer = $_SERVER['HTTP_REFERER'];
$lname = $_POST['lname'];
# Exception trap for HTML Post variable
if( ( !$lname ))
{
header( "Location:$referer");
exit();
}
$stmt = "select FIRST_NAME, LAST_NAME,COMMISSION_PCT FROM employees where last_name='".$lname."'";
echo "DEBUG (EXECUTED STATEMENT) = ".$stmt."<br><br><br>";
# OCI STATEMENT PARSE AND EXECUTE
$stid = oci_parse( $conn, $stmt);
$r=oci_execute($stid);
# Exception trap
if (!$r) {
$e = oci_error($stid); // For oci_execute errors pass the statement handle
echo htmlentities($e['message']);
echo "\n<pre>\n";
echo htmlentities($e['sqltext']);
printf("\n%".($e['offset']+1)."s", "^");
echo "\n</pre>\n";
}
# preparing output text (FETCH)
$html_txt= "<table border=1>\n";
$ncols = oci_num_fields($stid);
echo "<tr>\n";
for ($i = 1; $i <= $ncols; ++$i) {
$colname = oci_field_name($stid, $i);
$html_txt = $html_txt . " <td><b>".htmlentities($colname, ENT_QUOTES)."</b></td>\n";
}
echo "</tr>\n";
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
$html_txt = $html_txt . "<tr>";
$html_txt = $html_txt . "<td>" . $row['FIRST_NAME'] ."</td>\n";
$html_txt = $html_txt . "<td>" . $row['LAST_NAME'] ."</td>\n";
$html_txt = $html_txt . "<td>" . $row['COMMISSION_PCT'] . "</td>\n";
$html_txt = $html_txt . "</tr>";
}
$html_txt = $html_txt . "</table>";
?>
<HTML>
<HEAD>
<TITLE>Application Main Page</TITLE>
</HEAD>
<BODY>
<?php echo($html_txt); ?>
</BODY>
</HTML>
which produces the next form :
and the result for employee King:
Identifying the sql statement that produced this result is not a difficult thing to do.
As the fact it used literals, the statement is build by a concatenation of a SQL text and the HTML Post variable.
</pre>
$stmt = "select FIRST_NAME, LAST_NAME,COMMISSION_PCT FROM employees where last_name='".$lname."'";
So SQL injection becomes easy, indeed if I replace “King” in the form by ” ‘ union select table_name as first_name,null as last_name,0 as commission_pct from user_tables where ‘x’=’x ”
It will produce this SQL Statement : ” select FIRST_NAME, LAST_NAME,COMMISSION_PCT FROM employees where last_name=” union select table_name as first_name,null as last_name,0 as commission_pct from user_tables where ‘x’=’x’ ” and the result will be the entire list of the schema’s tables:
Interesting, there’s a table which is named “PASSWD”.
Now I will inject this in the form : ” ‘ union select column_name as first_name,data_type as last_name,0 as commission_pct from user_tab_columns where table_name=’PASSWD ” and this will produce this SQL Statement : ” select FIRST_NAME, LAST_NAME,COMMISSION_PCT FROM employees where last_name=” union select column_name as first_name,data_type as last_name,0 as commission_pct from user_tab_columns where table_name=’PASSWD’ ” and this result:
Now it’s easy to read the content of the PASSWD table, let’s inject this string in the form ” ‘ union select username as first_name,password as last_name,0 as commission_pct from PASSWD where ‘x’=’x ” … wonderful, all users and password (uncrypted or not but it’s another thing):
If I use a modified resp.php script which uses bind variables (see above), SQL injection is not possible:
<?php
## Database OCI Connection
$conn = oci_connect('hr', 'hr', '192.168.99.8/orcl');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
## Get HTML Post variables
$referer = $_SERVER['HTTP_REFERER'];
$lname = $_POST['lname'];
# Exception trap for HTML Post variable
if( ( !$lname ))
{
header( "Location:$referer");
exit();
}
$stmt = "select FIRST_NAME, LAST_NAME,COMMISSION_PCT FROM employees where last_name=:name";
echo "DEBUG (EXECUTED STATEMENT) = ".$stmt."<br><br><br>";
# OCI STATEMENT PARSE AND EXECUTE
$stid = oci_parse($conn,$stmt);
oci_bind_by_name($stid,':name',$lname);
$r=oci_execute($stid);
# Exception trap
if (!$r) {
$e = oci_error($stid); // For oci_execute errors pass the statement handle
echo htmlentities($e['message']);
echo "\n<pre>\n";
echo htmlentities($e['sqltext']);
printf("\n%".($e['offset']+1)."s", "^");
echo "\n</pre>\n";
}
# preparing output text (FETCH)
$html_txt= "<table border=1>\n";
$ncols = oci_num_fields($stid);
echo "<tr>\n";
for ($i = 1; $i <= $ncols; ++$i) {
$colname = oci_field_name($stid, $i);
$html_txt = $html_txt . " <td><b>".htmlentities($colname, ENT_QUOTES)."</b></td>\n";
}
echo "</tr>\n";
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
$html_txt = $html_txt . "<tr>";
$html_txt = $html_txt . "<td>" . $row['FIRST_NAME'] ."</td>\n";
$html_txt = $html_txt . "<td>" . $row['LAST_NAME'] ."</td>\n";
$html_txt = $html_txt . "<td>" . $row['COMMISSION_PCT'] . "</td>\n";
$html_txt = $html_txt . "</tr>";
}
$html_txt = $html_txt . "</table>";
?>
<HTML>
<HEAD>
<TITLE>Application Main Page</TITLE>
</HEAD>
<BODY>
<?php echo($html_txt); ?>
</BODY>
</HTML>
To conclude, using bind variables improves database performance and, in some cases, improves security.
Data ... as usual 