Oracle, bind variables and SQL Injection – Keeping out unwanted guests

My son, Michael ( yes, it is the only name I can spell), is currently following in his father’s footsteps and studying Computer Science.
As is only natural, he does occasionally have the urge to rebel against all that his parents hold dear. In his case he’s rejected the path of light and Linux and has become … a Microsoft Certified Professional. Oh the shame. Where did I go wrong ?
All of which links, if somewhat tenuously, to the subject at hand. When he took his first steps into the world of programming, we had a look at PHP ( as part of a LAMP set-up, naturally).
In one of the introductory manuals, we came across an example of how to authenticate web users against a database.
The author was clearly trying to introduce various language concepts and would certainly not claim that his example was intended for production use. However, with a bit of tweaking for use against an Oracle database, it does offer a very clear illustration one area of the potential vulnerabilities of web applications to SQL Injection attacks. It also offers the opportunity to illustrate a major benefit of using bind variables in queries against Oracle – i.e. protection against SQL Injection.

I know that a fair few people who stumble across this site are new to Oracle and want to play around with Oracle XE. These people are also usually pretty experienced in other technologies (hi Wayne, hope you’re still enjoying all that sunshine).

So, the purpose of this post is to :

• Illustrate the way in-line SQL statements can be injected
• Show how this can be countered in an Oracle database by use of bind variables
• Have a look at letting Oracle handle user authentication
• Celebrate the visionary genius of Messrs Young, Young and Johnson. “For Those About to Rock” was not merely an album of raucous Blues-based Heavy Metal, but a prophecy about the potential pitfalls of Web Application development.
• Oh, and give you the chance to laugh at my PHP prowess ( or lack thereof)

What we’re not going to cover here :

There really are some quite naughty things in these examples ( hard-coding of login credentials and by-passing of proper error-handling to name but two).
The point of this post is SQL injection and how to protect against it by using bind variables in statements sent to an Oracle database . It’s not going to cover how to secure all of the potential vulnerabilities in a PHP application.

Another point to note – I’m not picking on PHP here as being unique in this respect, any mid-tier code firing dynamically built statements at the database is potentially vulnerable. Yes, even “good old” Oracle Forms. If you take user input and just drop it straight into a SQL statement, there’s a potential issue.

For Those About to Rock – the development Team

Meet the team. First off, the Database Developer/DBA, basically the database guy. That’ll be me.
Next, the PHP Developer – keen but something of a neophyte in PHP terms. That’ll be me again.
Hang on, we’ve forgotten someone. There is an additional team member, although he is somewhat “ex-officio”. None of the other team members have even met him. Harry the Hacker. Sporting a black hat, he’s coming to a website near you. What’s that Brian ? “Evil Walks behind you ?” Hmmm.

Let’s Get it Up – the environment

I’m using my OPAL set-up here – Oracle Database (10g XE), PHP, Apache and Linux (Ubuntu desktop). If you want to play along, the steps for setting up the environment can be found here.

Snowballed – The Application

The design is fairly simple – the user inputs their user name and password. This is then passed on to an authentication routine which checks a database table to verify that the user name and password are valid. On successful authentication, the user is granted access to the application. If authentication fails, they are given an appropriate message.

Database table first (remember, this is for demonstration purposes only – storing passwords in clear-text is not big and it’s not clever) :

CREATE TABLE app_users( 
    user_name VARCHAR2(30), 
    password VARCHAR2(30)) 
/ 

INSERT INTO app_users( user_name, password) 
VALUES( 'MIKE', 'SENSIBLE') 
/

Now for the login form

<HTML> 
    <HEAD> 
        <TITLE>Login Page</TITLE> 
    </HEAD> 
    <BODY> 
        <H1>Login to the Application</H1> 
        <FORM action="menu.php" method="POST"> 
            Username : <BR/> 
            <INPUT type="text" name="uname"> 
            <BR/><BR/> 
            Password : <BR/> 
            <INPUT type="password" name="pwd"> 
            <BR/><BR/> 
            <INPUT type="submit" value="Login"> 
        </FORM> 
    </BODY> 
</HTML> 

And finally, the authentication routine

<?php 
    $uname = strtoupper($_POST['uname']); 
    $pwd = strtoupper($_POST['pwd']); 
    $referer = $_SERVER['HTTP_REFERER']; 
 
    # if either field is empty return to the login page 
    if( ( !$uname) or ( !$pwd)) 
    { 
        header( "Location:$referer"); 
        exit(); 
    } 
    # To authenticate the user we connect to the database and 
    # then check the uname password in a database table
    # Note replace the password for the hr user with the one you've got set on your database 
    $conn = oci_connect( "hr", "hr", "127.0.0.1/XE"); 
    $query = "SELECT 1 FROM app_users WHERE user_name = '$uname' AND password = '$pwd'"; 
    # dump out the resulting query so we can see what actually runs against the database 
    var_dump($query); 
    $stid = oci_parse( $conn, $query); 
    oci_execute($stid); 
    $count = oci_fetch_all($stid, $result); 
    if( $count == 1) 
    { 
        $msg = "Hi ".$uname.". We salute you"; 
    } 
    else 
    { 
        $msg = "You're not on the list. You're not coming in"; 
    } 
?> 
<HTML> 
    <HEAD> 
        <TITLE>Application Main Page</TITLE> 
    </HEAD> 
    <BODY> 
        <H1> <?php echo($msg); ?></H1> 
    </BODY> 
</HTML>

When we test this, all seems to work as expected :

On successful login :

The backstage pass is valid

However, what will happen when Harry comes across this particular page ?

Inject the Venom – the attack

Harry can’t believe his luck. He has two options to launch his attack :
If he chooses the username field to attack :

harry 'or 1=1 –

This gives us a statement that reads :

SELECT 1 FROM app_users WHERE user_name = 'HARRY \'OR 1=1 --' AND password = 'A'

If he wants to be properly addressed by the application, he can simply switch his attention to the password field and enter :

a 'or 1=1 –

This will give us the statement :

SELECT 1 FROM app_users WHERE user_name = 'HARRY' AND password = 'A \'OR 1=1 –

In each case, the string input by the user simply closes the quotes, adds an expression to the predicate that will always return true, and comments out the rest of the statement.

The end result is predictable

Seems they'll let anyone in

Time for a re-think.

Put the Finger On You – Using bind variables

The Development team have managed to avoid a “Night of the Long Knives” and have come up with a solution.
This consists of two components. The first is a PL/SQL function on the database :

CREATE OR REPLACE FUNCTION validate_app_user_fn( 
    i_uname IN app_users.user_name%TYPE, 
    i_pwd IN app_users.password%TYPE) 
    RETURN NUMBER IS 
    l_count PLS_INTEGER; 
BEGIN 
    SELECT COUNT(*) INTO l_count 
    FROM app_users 
    WHERE user_name = i_uname 
    AND password = i_pwd; 
     
    RETURN l_count; 
END; 
/

The second is a change to the PHP to call the function rather than construct and pass a simple SQL string :

<?php 
    function conn_err() 
    { 
        # Just an empty error handler to hide any warning if the 
        # login fails 
        return true; 
    } 
    # assign the error handler we've just defined 
    $err_handler = set_error_handler("conn_err"); 

    $uname = strtoupper($_POST['uname']); 
    $pwd = strtoupper($_POST['pwd']); 
    $self = $_SERVER['PHP_SELF']; 
    $referer = $_SERVER['HTTP_REFERER']; 
 
    # if either field is empty return to the login page 
    if( ( !$uname) or ( !$pwd)) 
    { 
        header( "Location:$referer"); 
        exit(); 
    } 
    # To authenticate the user we connect to the database and 
    # then check the uname password in a database table 
    $conn = oci_connect( "hr", "hr", "127.0.0.1/XE"); 
    $query = "BEGIN :l_count := validate_app_user_fn('$uname','$pwd'); END;"; 
    $stid = oci_parse( $conn, $query); 
    if(! stid) 
    { 
        # If Harry's hacking results in an invalid statement, we'll drop into 
        # here. 
        # invoke the error handler - saves the warning text appearing 
        # on the page 
        trigger_error(); 
        $result = 0; 
    } 
    $r = oci_bind_by_name( $stid, ":l_count", $result, -1); 
    $r = oci_execute($stid); 
    
     

    if( $result == 1) 
    { 
        $msg = "Do come in ".$uname." The free bar is this way"; 
    } 
    else 
    { 
        $msg = "Before you even begin\n"; 
        $msg .="With that SQL Injection thing\n"; 
        $msg .="The back door has been closed\n"; 
        $msg .="Right on your nose\n"; 
        $msg .="Name's not down so you're NOT coming in"; 
    } 
?> 
<HTML> 
    <HEAD> 
        <TITLE>Application Main Page</TITLE> 
    </HEAD> 
    <BODY> 
        <H1> <?php echo(nl2br($msg)); ?></H1> 
    </BODY> 
</HTML>

Now, when Harry tries the same trick, he’ll get a short insulting limerick rather than a warm welcome :

Emphatically refused entry...and in iambic pentameter

This is simply because the user supplied values are now bound in the database function call. The variables are bound by the function itself, which then executes the query rather than the query being constructed on the mid-tier then fired-off to the database.
This solution makes the DBA particularly happy as he knows that bind variable queries result in far more efficient memory use by Oracle.

Breaking the Rules – just let the database sort it out

There seems to be a widespread tendency in web applications to connect users to the database using the account of the schema owner.
This tends to make DBAs a little nervous.
Of course, for high-volume, public-facing websites, generic user accounts are the only practical solution. However, this could also be achieved by creating a generic account with sufficient privileges on the application owners objects. This approach has the advantage that users do not get highly privileged access to the database each time they connect.
For smaller applications, there is always the option of handling authentication of individuals inside the database itself.
Let’s imagine our application is actually intended to replace an Oracle Forms app with say, 20 users.
As things stand, they all connect to the database as themselves, so why not let this situation continue ?

<?php 
    function conn_err() 
    { 
        # Just an empty error handler to hide any warning if the 
        # login fails 
        return true; 
    } 
    # assign the error handler we've just defined 
    $err_handler = set_error_handler("conn_err"); 
    # pick up the uid/pwd strings 
    $uname = strtoupper($_POST['uname']); 
    $pwd = $_POST['pwd']; 
    $referer = $_SERVER['HTTP_REFERER']; 
 
    # if either field is empty return to the login page 
    if( ( !$uname) or ( !$pwd)) 
    { 
        header( "Location:$referer"); 
        exit(); 
    } 
    # To authenticate the user ... well, just let the database 
    # do it's thing. 
    $conn = oci_connect( "$uname", "$pwd", "127.0.0.1/XE"); 
    if( $conn) 
    { 
        $msg = "Do come in ".$uname." The free bar is this way"; 
    } 
    else 
    { 
        $msg = ' Brian says : “You've been Snowballed"'; 
        # invoke the error handler - saves the warning text appearing 
        # on the page 
        trigger_error(); 
        
    } 
?> 
<HTML> 
    <HEAD> 
        <TITLE>Application Main Page</TITLE> 
    </HEAD> 
    <BODY> 
        <H1> <?php echo($msg); ?></H1> 
    </BODY> 
</HTML>

That’s fairly straightforward. We don’t need to worry about any database code here as the RDBMS itself authenticates the users.

Spellbound – because I couldn’t work it in anywhere else

There are a number of advantages to using bind variables when talking to Oracle databases, of which security is only one. However, this is of no interest to Michael who, last time I saw him, was eyeing the SQLServer manual covetously whilst muttering something about .Net. Honestly, I blame his mother.