Oracle – for when it was like that when you got there

Main menu

Post navigation

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

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

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 ?

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.

There still doesn’t appear to be a definitive explaination of how the Sony attack happened, although SQL Injection may well have played a part.
I found a pretty good article on it athttps://www.veracode.com/blog/2011/05/possible-playstation-network-attack-vectors/ if anyone’s interested.
One of the possibilities mentioned in the article is that part of the attack could have been initiated from inside the firewall. I wrote something about this kind of attack a while ago. The post can be found here.

Essentially, this is because the database engine recognises the $query value as a PL/SQL block.
It then binds the input variables to the input values for the function. Therefore, whatever values the user enters, they’ll be treated as parameters to the function and nothing else.

This is great, thanks! I’m new to Oracle, and many of my coworkers go slack-jawed when I start talking attack vectors, so it’s really reassuring to find a clear explanation of the Oracle defence. Thanks!