A technique for breaking databases

There is joke which goes something like: those who know how to do it, do it. Those who don't, teach it. Those who don't even know how to teach it, supervise it. Sadly this is true for many tech journalists who make up sensationalized titles both because of lack of comprehension and because they have to sell their writing. Of course people pitching topics to the journalists aren't all that innocent themselves.

One such example would be the "New attack technique threatens databases" piece from The Register. What this boils down to if a plain SQL injection attack, at a different level.

The summary of the paper (warning, pdf!) is: suppose someone, who should know better, writes the following stored procedure (because I don't know Oracle, it will be written in pseudo SQL, but you will get the point):

CREATE PROCEDURE test1(stuff DATE) RETURNS varchar AS
BEGIN
query = "SELECT * FROM products WHERE arrival > '" || stuff || "'";
EXECUTE query;
END;

The thought process (if there was any) behind it probably was along the lines: I know that constructing dynamic SQL queries is bad (both because I expose myself ot SQL injection attacks and because syntax errors aren't verified during the creation of the procedure - given that "query" is just a string from the point of view of the parser), but I've put the value between quotes and I know that Oracle will validate the parameter before passing it to the procedure. As dates can't have quotes in them, I'm ok.

The problem is (as the paper describes) that by altering a session variable, you can define the format of a date for Oracle, making these types of procedures exploitable. Solution: don't create SQL queries using string concatenation, because it will bite you in the rear sooner or later.

As I mentioned earlier I'm no Oracle guru (in fact I haven't used Oracle in my life), but being curious and all I looked how Postgres and MySQL would behave in a similar situation. Postgres had a flawless behavior, you can write queries which include input variables without the need to construct it in a string having the dual benefit of proper quotation and syntactical verification at procedure creation time. With MySQL you have to use 5.0.13 at least (this is not a big deal at all, given that you have to use at least version 5 if you want stored procedures), from which version onwards you can take advantage of prepared statements inside of stored procedures.