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)

Continue reading

Configuring OPAL on Ubuntu Desktop – without the Oracle Instant Client

Many years ago, my son had more-or-less worked out that Santa was a myth, but hadn’t wanted to say anything for fear of decreasing the number of Christmas presents he might get.
Taking my parental duties as seriously as I do, I took him to one side and explained the truth…
After Return of the Jedi, Darth Vader fell upon hard-times. There weren’t many film roles about for Dark Lords of the Sith. Eventually he decided upon a change of career and bought the round off Father Christmas, who was retiring.
Obviously, Darth Vader has a rather more direct approach to naughty children and if my son didn’t behave himself, not only would he not get any presents but he might get something cut off.
It is for this reason that Simon has the Darth Vader theme as the ringtone on his phone for when I call.

All of which has at best, a tenuous link to the theme of this post ( but I thought it was time to get into the festive spirit).

Following on from last week’s introduction to PL/SQL, some people have asked about using PL/SQL a web application (without all that mucky APEX stuff). In order to start working up some examples of this, I thought it would be a good idea to use PHP as a front-end. Yes – Oracle’s version of a LAMP system – Oracle, PHP, Apache, Linux (OPAL). After all, how hard could it be ?
Continue reading