Oracle Schema Differences – keeping up with the Prefix Pixie

Saturday 11th September 1976. That was the day that my Dad first took me to see the (occasionally) mighty West Ham United.
The opponents, the rather more often mighty Arsenal.
I still have vivid memories of that game. The noise from the crowd. The fact that the grass looked so green, brighter than on the TV.
West Ham not playing very well. Frank Stapleton putting a bit of a downer on the day by having the temerity to score twice in a 2-0 win for the Gunners.
My Dad recently celebrated his 70th birthday.
His present from his first-born son ? A trip to see the (previously) mighty Luton Town take on the ( probably must have been from time to time) mighty Nuneaton Borough.
Now, this may seem poor reward for my dear old Dad – he takes me to see two of the top teams in the country and he gets the Blue Square Premier League in return.
Additionally, these days it’s less the colour of the grass that assaults the senses than the colour of the boots.
These are various flourescent colours, virtually none of them black.
Mind you, as Deb pointed out, if you play for Luton and spend most of your working life dressed in bright orange, then accessorising must be a bit of a challenge.
The game itself however, is another matter.
Typical English Football – very quick, lots of commitment. You can tell it’s not the Premiership by the absence of millionaires rolling around the floor in apparent agony because they’ve broken a finger-nail.
Two late goals sends the Hatters home happy.
All of which has nothing to do with the subject of this post, apart from my choice of examples.
Comparing the table structure between different schemas is standard functionality for any self-respecting IDE. However, things get a bit more tricky if you’ve had a visit from the Prefix Pixie. He, she (or it if you’re table relationship diagram dropped out of a design tool) thought it’d be a good idea to give the same prefix to every table in the schema.

The result of this is that the tools in the IDE can’t recognize that tables with different names are meant to have identical structures.
So much for the “Premiership” of Database Development, it looks like we’ll just have to do a bit of D.I.Y. to see through the poxie pixie dust.
Dad would approve. Continue reading

PL/SQL Injection – The Doctor Will See You Now

Following on from my recent post about SQL Injection I had an excellent comment from Gary.

Whilst we’ve been able to establish that the PL/SQL solution we implemented does not suffer the same vulnerability to injection as the concatenated SQL statement, this does lead us to a further question – does using PL/SQL automatically render us immune from injection attacks ?

For the purposes of this post, I’m going to leave PHP to one-side and concentrate on the PL/SQL side of the matter. Continue reading

Self-Inflicted SQL Injection – don’t quote me !

After my recent post about escaping quotes in SQL scripts, I was surprised and delighted to receive a mail from Alexander Kornbrust, CEO of Red Database Security.
In it, he said he’d read the post and pointed out that the code therin was vulnerable to SQL-Injection.
I was fortunate enough to work with Alex, before he went off to become famous, so I know that he’s a bona fide expert in all things Oracle, especially security. Even so, I was initially puzzled by his assertion.
After all, the code I’d posted was an example where the code is held in a script and NOT in the database so wouldn’t be vulnerable to being executed by someone who’d hacked into the database itself. After all, SQL Injection happens interactively doesn’t it ? The hacker needs to be probing for weaknesses via a web front-end or similar. Don’t they have to be physically typing stuff in somewhere for this to work ? Er….apparently not.
Having asked around a number of Oracle developers, this would seem to be a widely held misconception. In terms of SQL Injection attacks, we’re all familiar with the classic HTML login form which POSTS to some mid-tier script or program which in turn, simply concatenates the username and password strings supplied by the user into a query then fires it off unthinkingly at the database. But how can you be attacked when the attacker isn’t even around at the time ? Continue reading