Oracle … as usual

Oracle by Laurent Leturgez

Monthly Archives: March 2015

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.

 

ITL slots high water mark

Recently, one of my customer had some performance issues on a batch ran every night.

After analyzing AWR, I found that the top wait event was “enq: TX – allocate ITL entry” on a particular table.

“enq: TX – allocate ITL entry” wait time is increased when an Interest Transaction List (ITL) can’t grow due to lack of space in the block header. (for more information, see this post from Arup Nanda : http://arup.blogspot.fr/2011/01/more-on-interested-transaction-lists.html). If you want to resolve it, you have to increase the INITRANS parameter of the object, and then rebuild it.

Another thing to point out: this wait event is counted by object (in V$SEGMENT_STATISTICS for example), but ITL allocation issues are specific to blocks, and ITL slots count can be different from a block to another one.

So far so good, but … but which value for initrans? To know it, you have to dump every block that contain data (block type : 0x06), and get the block that have the highest number of itl slot allocated. (you can make an arbitrary choice too … 😉 ).

Well, my customer ITL allocation issue was located on a table with more than 60000 blocks of data, so to get the best value for initrans, I had to write few lines of code.

First, I used some code from this blog post (from Oracle Diagnostician : http://savvinov.com/2015/02/26/analyzing-segment-content-by-block-type/) to dump all the blocks of my object:

begin
 for rec in (select file_id, block_id start_block, block_id + blocks - 1 end_block from dba_extents where segment_name = '&segname' and owner = '&ownname') loop
 execute immediate 'alter system dump datafile ' || rec.file_id || ' block min ' || rec.start_block || ' block max ' || rec.end_block;
 end loop;
end;
/

Note : This operation can take some time to be executed, be patient (and don’t forget to check space in your ADR trace directory)

Now, I have a look into my ADR trace directory to get the dump file (which is quite huge).

Then, I wrote a small perl script to analyze this dump file and print high water mark of itl slots.

This very simple perl script can be downloaded here : https://app.box.com/s/ghvb86rg8mk3sb2tieadnie9hbrhxgdf

Here’s a little demo of this script upon a block dump file (which contains many dump blocks)

$ ./cnt_itl.pl p orcl_ora_14878.trc
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0011.01f.00000095  0x00c0364a.00c6.11  ----    1  fsc 0x03fe.00000000
0x02   0x0018.00a.00000044  0x00c0317b.0038.17  ----    1  fsc 0x03fe.00000000
0x03   0x0015.01b.0000004a  0x00c03b10.0044.02  ----    1  fsc 0x03fe.00000000
0x04   0x000e.007.00000077  0x00c03716.008f.03  ----    1  fsc 0x03fe.00000000
0x05   0x0010.020.00000081  0x00c030de.00b1.0d  ----    1  fsc 0x03fe.00000000
0x06   0x0019.01c.00000043  0x00c03555.0042.1c  ----    1  fsc 0x03fe.00000000
0x07   0x0014.01b.0000004c  0x00c036b1.005f.35  ----    1  fsc 0x03fe.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.23  --U-    7  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.24  --U-    6  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

$ ./cnt_itl.pl c orcl_ora_14878.trc
7

Now I can set INITRANS to a value of over 8 (and don’t forget to rebuild it to set this on all data blocks) :

SQL> alter table spl initrans 8;

Table altered.

SQL > alter table spl move tablespace users;

Table altered.