Post navigation

PL/SQL Injection – The Doctor Will See You Now

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.

The Application

Just to recap, Application Users’ details are held in a table and we have a function to verify a user’s logon credentials. This is all very basic and intended only as an example. Holding password data in a table in clear text is really not a great idea.

The user verification function returns 1 if the user is valid or 0 if not.
Just suppose at this point that we assume that the use of PL/SQL, by it’s very nature, will mean that we can’t be injected. We could then choose to build the statement dynamically by simply concatenating the user input into the statement. This is the same technique we used originally in the previous post, just moved down a couple of application layers :

OK, I think it’s fair to say that anyone whose used PL/SQL for any length of time would not code the function in this way. However, a PHP developer who is finding their way as they go, may well see this as a reasonable approach.
In any case, it’s a valid PL/SQL function and it works :

Now, as we are referencing the variables in the query, rather than concatenating them in, they are quarantined – i.e. PL/SQL will only apply them in the context they were intended to be used. In short, they are bound into the query.

If we have a look at the query that’s actually issued when we call this function, we can see this clearly :

There’s no magic here. PL/SQL does all this binding stuff without needing to be told. It’s just the way it works by default.
Of course, there will be the odd occasion when you do need to dynamically build a query, but there are ways and means of binding variables in those circumstances. For the most part however, this method will do the job.
If you happen to be new to PL/SQL and want to know a bit more, I’ve written a programmer’s introduction to the language. That post also includes links to other sources of PL/SQL wisdom.
Speaking of which, Tom Kyte has written an article which explores a variation of this particular kind of attack using NLS_DATE_FORMAT. If you’re not feeling like a pin-cushion at this point, the gory details are here.

As you’ve been good, read all the way to the end, and not cried when getting your jabs, here’s a lolly.