Oracle … as usual

Oracle by Laurent Leturgez

Bind variables and SQL Injection

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 :

bind_1

and the result for employee King:

bind_2

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:

bind_3

 

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:

bind_4

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

bind_5

 

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: