New Attack Exploits "Safe" Oracle Inputs

Trailrunner7 writes "Database security super-genius David Litchfield has found a way to manipulate common Oracle data types, which were not thought to be exploitable, and inject arbitrary SQL commands. The new method shows that you can no longer assume any data types are safe from attacker input, regardless of their location or function. 'In conclusion, even those functions and procedures that don't take user input can be exploited if SYSDATE is used. The lesson here is always, always validate and prevent this type of vulnerability getting into your code. The second lesson is that no longer should DATE or NUMBER data types be considered as safe and not useful as injection vectors: as this paper (PDF) has proved, they are,' Litchfield writes."

Comments

heh

[stoolpigeon]

It's an interesting piece but when he points out that there really is little chance of it being used in the real world, that is an understatement. Using this method in the real world wouldn't even make sense.
 
In order to pull this off you need to have alter session priveleges. And you need to already have injected sql into the database- which means there is absolutely no point to taking the extra steps to modify some other data type to allow you to do what you have already done.
 
It's an interesting mental exercise but I don't think it really has an practical ramifications. If you've already handed out alter session to anyone using a form you've hosed yourself so many times over, playing with sysdate or number is the least of your worries.
 
Anything that reminds people to be careful about how they handle input is good, but I think a lot of people are going to think this is a bigger deal than it is.

Re:heh

[arth1]

Lack of input validation is usually a bigger problem than what you think it is -- the context might make the instance safe, but code tends to be re-used, coding practices repeated, and projects getting additions that might introduce a vector that weren't there before.

The only time when lack of validation is good practice is at extreme low level where you control the input. Otherwise, it usually signifies a coder that lacks the ability to think outside his own procedures.

Regards,
--
*Art

Re:heh

[morgan_greywolf]

Agreed. Handing out ALTER SESSION privs to anyone using a form is just plain dumb, dumb, dumb. You may as well put PLEASE HACK ME in flashing red letters at the top of the form.

Re:heh

[alan_dershowitz]

That's exactly what I was thinking, what retard grants the application's database user ALTER privileges on database objects?

Re:heh

[1001011010110101]

Correct me if I'm wrong, but even if someone has Alter session privileges, in order to execute "alter session", someone must be able to execute arbitrary stuff. In that case, trying to inject makes no sense, just plain insert/delete/whatever without using those date/number fields would do.

Re:heh

[stoolpigeon]

I'm not arguing that. All I'm saying is that this depends upon a chain of failures prior to becoming possible and those failures are even greater than the exploit itself.
 
I don't know enough about sysadmin to make a good comparison, but my dba brain thinks this is like, saying 'hey you might think ls is pretty safe- but not if you give people root privileges before they use it.' But that might be a bad example.

Re:heh

[Joe U]

Too many poor developers just make the web app run as dbo. They also tend to use 'select * from' all too often.

Drives me nuts, because I'm the exact opposite, you don't get any (yes including read) access except a few stored procedures you need to read/write data.

Re:heh

[Shados]

I guess a web based database development tool... But then, when you can execute ANYTHING and have admin priviledge on the database, you don't really need exploits :)

Re:heh

[moderatorrater]

he points out that there really is little chance of it being used in the real world, that is an understatement I believe it was George Guninski who saw the possible exploit in buffer overflows several decades ago and said something along the lines of "this is possible, but the difficulty in crafting the message makes this seems unlikely". If there's the possibility of an attack vector, then someone will use it. Computers are fast enough to try hundreds of attacks per second; "unlikely" often means "only works 1/1000 times, therefore used every day".

Re:heh

[arth1]

Not really a bad example, no. However, good sysadmins will try to do their best to limit the damage even if the unthinkable (like privilege escalation) should occur.
SELinux is a good example of this -- unless something is explicitly permitted and happens in a permissible context, it's forbidden. Even for root.
The downside is that the more draconian the measures, the more likely it is that laziness or lack of understanding will win. Instead of taking five steps to securely opening a window slot, someone with too much clout will sooner or later declare a system-wide "windows will be open" to make their small contribution work. And then it doesn't matter much whether the front door has all kind of security features.

For programming, it's more of whether you see beyond the project you're doing. Even though your code is safe in the context it is in, will it be safe once Idiot Coder copies it and reuses it somewhere else, or Idiot Manager decides that the front-end vetting engine is too slow or expensive and replaces it with something else? Or should you validate things that could not possibly happen, as long as it's not overly detrimental to performance, just in case somewhere along the line, human idiocy is added to the project?

There are situations where you should avoid input validation. But those are few and far between, and it should always be a conscious choice whether you do so or not. Not just default to thinking the system will do the right thing unless you have reasons to believe otherwise.

Re:heh

[Hoi Polloi]

Plus, anyone using this method of injection would probably cause widespread failures across the board in any app that depends on db procedures. The app would probably crash immediately. This isn't a very subtle way of hacking a db.

Re:heh

[alan_dershowitz]

I know, but altering the session isn't the only part of the exploit. You also have to be able to create or modify PL/SQL in the database. Still not strictly ALTER, but the point is, application user should only be able to read and maybe write to tables. If I can help it, I don't even use PL/SQL. The database is the worst place to put application logic anyway. I use PL/SQL for ad-hoc stuff.

Re:heh

[DazzaL]

It is not true to say that you need ALTER SESSION privilege granted to actually issue ALTER SESSION commands. Yes, that sounds counter-intuitive but it is true that you can issue SOME alter session commands if you can connect to a database regardless of what privs you have.

In this case setting NLS_DATE_FORMAT can be done by ANYONE regardless of whether they have ALTER SESSION granted.

some observations:

1. in most web apps you wont have access to the database, just the webserver...the database should be firewalled off.

2. it is RARE for PL/SQL developers to use resort to using dynamic SQL (execute immediate/DBMS_SQL) to run SQL, so this flaw, whilst interesting, is HIGHLY unlikely to be a problem...its certainly no where near as dangerous as developers not validating inputs where a application tier (java/php etc) does sql commands (esp if its not using bind variables) against a database [which by definition are dynamic sql calls].

Not to mention that using execute immediate without the USING clause and bind variables is again really rare by any half competent pl/sql developer.

3. the code also relies on another major error in the coding..type conversion. the date is implicitly converted to a string due to concatenation(||) i.e oracle rewrote that internally as to_char(v_date) and, as there was no supplied format it uses NLS_DATE_FORMAT.

i.e. in the example in the paper: stmt:='select object_name from all_objects where created = ''' || v_date || ''''; dbms_output.put_line(stmt); execute immediate stmt;

would undoutably be written PROPERLY as (in the dynamic case) execute immediate 'select object_name from all_objects where created = :b1' using v_date;

which is not susceptible to injection (NLS_DATE_FORMAT cant even come into play here).

Re:heh

[martinmarv]

In the environments I've worked in (enterprise applications and large CMS-based websites), using stored procedures for everything can be a pain. For me, the best approach is a happy medium:-

• Don't restrict yourself to stored procedures, but do use them for updates, or database-side processing
• Do use a dedicated account for database access and make sure only appropriate permissions are granted
• Use parameterised queries (seems like most common frameworks support this)

Also

• Always validate user input
• Always escape user input that will end up in the database

Re:heh

[glwtta]

Computers are fast enough to try hundreds of attacks per second; "unlikely" often means "only works 1/1000 times, therefore used every day".

What does that have to do with anything? It's unlikely to be used not because it's unlikely to succeed, but because it's just a neat trick you can do with an already compromised system.

There's a big difference between "difficult to pull off" and "not worth bothering".

Re:heh

[Kozz]

Reminds me of a webapp I worked on once. The programmer, in his infinite wisdom, would "SELECT * FROM TABLENAME", then stuff all 2500 records into a PHP array. Then he would promptly iterate over this array, selecting only two columns (of about thirty) he wanted from the desired rows matching his criteria.

I held my gag reflex long enough to perform only the requested change and make it functional. Then I declined all work after that.

Re:heh

[Anonymous Coward]

Neither ALTER SESSION nor creating PL/SQL stored procedures is a highly privileged operation in Oracle. Users use ALTER SESSION to set a preference for date formatting and you don't need any system privileges for this. Each database user has their own schema in which they can create stored procedures if they have the CREATE PROCEDURE system privilege. And you can create a stored procedure that is invoked using the rights of the caller or the rights of the owner. Most procedures execute using the rights of the owner. Your provided stored procedure would be set to run with invoker rights and so when it's called by the hijacked stored procedure it runs with the rights of the owner of that procedure.

This is essentially an privilege escalation attack, potentially allowing an ordinary Oracle user to run arbitrary code using the rights of another, more privileged user.

The real limitation here is that you need to find a procedure that you have rights to execute, that is owned by a privileged user and which converts a date to a string without using the TO_STRING function, and then pastes it into a SQL query and runs it. Usually the stored procedure would just bind the date variable directly in the query and avoid the string conversion. You simply shouldn't be pasting parameter values into queries in any tier.

Re:heh

[ShieldW0lf]

My DBA brain thought "What kind of idiot assembles SQL as a string and runs it inside a stored procedure like that?"

Re:heh

[blirp]

In order to pull this off you need to have alter session priveleges.

No, you don't. What you need is to somehow be able to modify NLS_NUMERIC_CHARACTERS or NLS_DATE_FORMAT. This is easily demonstrated with ALTER SESSION. But there might be a bug/exploit somewhere down the road that allows this in some other manner. Each of the two exploits are unusable, but combined ...

M.

Re:heh

[Anonymous Coward]

The kind of idiot who wouldn't have a paper to publish on their website if they actually weren't creating their own security holes.

This is IT security equivalent of a strawman argument.

Re:heh

[Joe U]

I see you met my old boss.

Re:heh

[Joe U]

I agree completely.

While I do try to use stored procedures for all projects, parameterised queries is a very good second choice. I don't understand why they're not used as much.

When you get into unvalidated inserts and selects, that's what drives me nuts.

Re:heh

[dreamchaser]

What does that have to do with anything?

It has everything to do with exploits. It means that if there is the slightest chance that something will be exploited then it will be exploited even if the attacker has to rely on brute force methods to find the right entry.

Re:heh

[1001011010110101]

<quote>It is not true to say that you need ALTER SESSION privilege granted to actually issue ALTER SESSION commands. Yes, that sounds counter-intuitive but it is true that you can issue SOME alter session commands if you can connect to a database regardless of what privs you have.

In this case setting NLS_DATE_FORMAT can be done by ANYONE regardless of whether they have ALTER SESSION granted.
</quote>

Still, you need to be already able to inject in order to issue a modified "ALTER SESSION".

<quote>1. in most web apps you wont have access to the database, just the webserver...the database should be firewalled off. </quote>
Sure, but you need to connect to the database to use it from the web app. SQLnet port for Oracle must be enabled, and I think its less than bulletproof. It has had several vulnerabilities in the past.

<quote>
2. it is RARE for PL/SQL developers to use resort to using dynamic SQL (execute immediate/DBMS_SQL) to run SQL, so this flaw, whilst interesting, is HIGHLY unlikely to be a problem...its certainly no where near as dangerous as developers not validating inputs where a application tier (java/php etc) does sql commands (esp if its not using bind variables) against a database [which by definition are dynamic sql calls].
</quote>

Perhaps at your place, but some people will always try to ease work by creating procs that handle some logic and they end up using dynamic SQL...for some things, there&#180;s no other way (try multiple conditional where clauses). Tricky stuff to avoid vulnerabilities.

<quote>
3. the code also relies on another major error in the coding..type conversion. the date is implicitly converted to a string due to concatenation(||) i.e oracle rewrote that internally as to_char(v_date) and, as there was no supplied format it uses NLS_DATE_FORMAT.
</quote>
Implicit conversion sucks. For anything other than anonymous blocks you might run interactively, all conversions must be explicit. For dates, you have to do it neutrally (there are ways to do it that are I18n independent). If you don&#180;t, you must be sure the other part is covered (NLS DATE FORMAT explicitly set).

Re:heh

[zehaeva]

on the bright side, if you did put PLEASE HACK ME in flashing red letters at the top of the form you'd figure out where you security is lacking pretty fast. ^_^

Re:heh

[hummassa]

The kind of idiot who wouldn't have a paper to publish on their website if they actually weren't creating their own security holes.

This is IT security equivalent of a strawman argument. No, the kind of idiot who wants to leave this kind of hole open to drop all your tables (or best, fill them with junk/wrong/malicious records) if he's fired. It's a nice way to create an "extendend severance package".

Re:heh

[SQLGuru]

This isn't a problem of type safety. The types are actually still as safe as they were ever considered. This is a problem of IMPLICIT dataconversion. The NLS settings affect how dates and numbers are converted to strings if you don't specify the formatting. I always EXPLICITLY convert my values to a known format (when I convert to/from string).

string_var := 'blah blah blah' || num_var; --or even just to_char( num_var )
vs
string_var := 'blah blah blah' || to_char( num_var, '9999d99' );

I've always contended that using explicit conversion was bad and it should be removed from languages. It great for convenience, but it masks too many problems that you could overlook (like when an index isn't being used but seems like it should be). But like ShieldW0lf says, dynamic SQL (while having a place) is best avoided.....especially when exposed to the untrusted world.

Layne

Re:heh

[SQLGuru]

Actually, SOME logic should live in the DB and SOME should live in the app. Otherwise, bypassing the app would make it too easy to create data that violates the business cases. I won't say it all should live in one layer or the other because some logic just works better at different layers. If you are just using a database as a data storage mechanism, you might as well be using a mainframe and ISAM files. Take advantage of the power provided to you.

Layne

Re:heh

[SQLGuru]

ALTER SESSION only changes the value for that one session. To affect multiple users, you need ALTER SYSTEM privs which (should be) very tightly controlled. Each connection would need to reset the NLS parms each time they connect for this to matter. So, really, it's only a good way to try to get some of your own code executed.

And while the doc doesn't say it, you could put an anonymous block in there, so you don't need privs to create anything....you just rely on the privs of the connection to execute code.

Layne

Re:heh

[Kozz]

Was the app related to selling music CDs online? If so, then yep. I met him.

Re:heh

[TheRealBurKaZoiD]

Lord, I feel your pain. I maintain an application that I inherited from the original developers. There were too many places in the code where the query was returning too many columns, and they were only consuming a subset. In one instance, it was a 37 to 6 ratio (37 returned, 6 consumed) dataset that was being bound to a datagrid. Only the six needed columns were visible, but the entire dataset was still being bound. The resulting web page was weighing in at just under 1MB. Talk about a performance killer!

Re:heh

[alan_dershowitz]

Primary and foreign keys and in some cases check constraints, but past that not usually. One thing I do use PL/SQL for is general-purpose string utilities to make things like name matching easier and formatting addresses. So I agree that some app-logic works best in the database, but from my viewpoint general purpose functionality and not app-specific functionality if you can help it.

Re:heh

[SQLGuru]

Most processing involving multiple rows will be more performant on the database (assuming it's writting in set-based logic instead of cursors, but even then, saving on return trips from the network may even make that a better performer).

Consider this pseudo-code at the app layer.

Select ROWSET from DB
Foreach row, perform some update
Update ROWSET in DB

now consider that call as a proc call that does a single, paramterized UPDATE.

And most nightly batches should live on the DB for that same reason.

And in order to support all of that logic, there are some basic business rules that should be defined at the database level.

Also, definitions for computed values should be considered for definition as database functions. While not required, it makes reporting simpler (define once, reuse many -- at both the app and reporting layers).

Since the DB is the common layer, code that will be used from multiple places (app layer 1, app layer 2, web service A, web service B) you might consider pushing certain code to the db.

It's always on a per case basis, but there are valid reasons for business logic to live in the db. But I'm not DB bigot that believes that all code needs to live in the DB.....and I'm not an App bigot that thinks all code needs to live in the App. I think code needs to go in the right place for the situation and priorities.

Layne

nothingforyoutoseehere

[davidwr]

IF programmer_is_smart_and_validates_data THEN PRINT no_big_deal ELSE PRINT he_has_bigger_worries

Re:nothingforyoutoseehere

[BigJClark]

noob.

SELECT decode( programmer_is_smart_and_validates_data, 1, 'no_big_deal', 'he_has_bigger_worries' ) FROM dual;

Re:nothingforyoutoseehere

[rjstanford]

Seems pretty basic to me...

Itsatrap!

[esocid]

Come on, do I have to tag this myself?

Use ORMs

[chrysalis]

Interesting flaw.

However, don't ORMs (and database-independant abstraction layers like AdoDB) protect against this?

Re:Use ORMs

[Shados]

Yup. Basically, the only real way this could be exploited would be something like a stored procedure which takes one of the "vulnerable" types as parameters, exposed directly to the clients, and concatenate the types with little to no casting.

Something like (pseudocode, the following wouldn't even pass syntax check, obviously, but its stupid hard to find a working case)

DECLARE @blah SOMEVULNERABLETYPE

Exec "select * from stuff where stuff.Blah =" + @blah;

If @blah was a string, everyone would realise its vulnerable...but in this case, numbers, dates, etc, would be assumed safe (how do you put code in a number??), when it supposingly was discovered its not safe.

However, if you went through a database driver (not even an ORM!), and made a prepared statement, passed a Java (for example) variable as parameter to a query, well, no invalid input will be able to get through. If you add an ORM layer on top of that which does extra validation, then even if all of the types (both java and database) were vulnerable, it wouldn't go through either...

This is really more of a theoritical vulnerabilty than a real one... it can't realistically be exploited in the wild, and its hard to even -imagine- a scenario in a well coded app.

Re:Use ORMs

[hawkinspeter]

I think this is more of a programming error than a vulnerability. The examples are concatenating the session's representation of a date and then running the resultant sql. It doesn't make much sense to use dynamic sql when you can just directly compare the date variable without having to represent it as a string. An easy way to bypass this kind of problem (if you really want it to be a dynamic query) is to explicitly specify what date format you want e.g. to_date(@date, 'YYYYMMDD') Of course, you wouldn't want dynamic sql anyway unless the database statistics were poorly set up (no histograms on skewed data sets).

Re:Use ORMs

[zifn4b]

I can't think of a reason why anyone would want to use a stored procedure that builds dynamic SQL. For this type of thing, you should use a parameterized SQL query with a good provider like the Oracle Data Provider for .NET. If you always use the Parameter objects instead of concatenating them to the query text, you should be ok.

This seems to only really affect poorly written code. If developers were trained in best practices, we would have more secure software. Sadly, a lot of the books out there that teach you how to program use bad examples that indirectly promote writing insecure code.

Re:Use ORMs

[Shados]

In this case, it doesn't even affect poorly written code: it affects some theoritical near-impossible scenario. I only skimmned the description, but as far as I can understand, you almost need a researcher to be able to write code that can be exploited... a normal (or bad) dev wouldn't be able to pull it off (neither would I, as far as I can tell!).

I agree with the rest of your point, however.

DB Programming 101?

[Obsi]

Isn't a central premise taught in database programming 101 about NEVER assuming any input valid, and doing your own validation even if it was validated prior by a supposedly trustworthy source? I've never taken any classes in programming, so I would have no idea.

Re:DB Programming 101?

[0racle]

People learn database programming now? I thought they just threw together whatever SQL and PHP they could find online and called themselves programmers.

Re:DB Programming 101?

[Shados]

DB Programming (even the science part, such as the relational model) is virtually never taught in colleges. When it is, its as an elective class most of the time, even in the big name tear-through-your-wallet colleges.

Still cracks me up how in every interview I pass, I always get asked "Ok, so can you explain to me the difference between an inner and an outer join?" or "What is the main benefit of an index on a database table?". Shows the state of the workforce...

Re:DB Programming 101?

[Chirs]

Heck, I couldn't answer the first question, so it's not universal knowledge.

But then I'm an embedded realtime systems guy, not a database guy.

Re:DB Programming 101?

[Shados]

It isn't universal knowledge because its not taught in schools =P But considering how pervasive databases are in the industry, and that, after all, people go to school to (at least, quite often) become productive and get a job, it definately should be taught.

Its (for databases) on the same level as "Whats the difference between a WHILE and a FOR loop". Since the basics aren't taught, well...you have 500000 servers getting hacked (see article a couple notch down in today's list).

I'm sure there's a lot of things in embedded realtime systems that are required to know and aren't taught in school, too, but lets be honest... the DB Developer vs Embedded System Dev ratio is probably skewed toward the former, hehehe!

Re:DB Programming 101?

[Goyuix]

Still cracks me up how in every interview I pass, I always get asked "Ok, so can you explain to me the difference between an inner and an outer join?" or "What is the main benefit of an index on a database table?". Shows the state of the workforce... So, since you have identified questions you might ask a college intern - what questions would you or have you [been?] asked that were really good database questions?

Re:DB Programming 101?

[Shados]

In a good interview for a job thats beyond entry level, you don't ask silly questions. You start a discussion on the challenges and solutions involved in a given situation, and go from there.

Something like: "Have you ever had to work as a backend database developer?" "Yeah, I did in a 2 year contract at company XYZ" "Sweet, can you describe to me what you were doing?" ::potential employe described it:: "Interesting. See, we're trying to do something similar here, but have stumbled on problem XYZ while trying to integrate some technology, we had performance issues in a query that did ABCD. What would you have done in that case?"

Of course, thats just an example, and a bad one, but I hope its enough to explain what I mean :)

Re:DB Programming 101?

[VGPowerlord]

Since Shados didn't say what the difference is, I will.

Inner and outer joins always have a join condition.
An INNER JOIN only returns the records that satisfy the join condition.
An OUTER JOIN always returns all the results of one (LEFT or RIGHT) or both (FULL) tables, returning nulls for all the requested data in the other table when the join condition is not met.

Maybe that's not clear enough. I'll make a pair of contrived tables to demonstrate.

people
id | name
01 | Bill
02 | Tina
 
items
id | item
01 | candy
01 | ice cream
03 | milk

Seems simple, right? Here's the various queries and what they'd return:

SELECT name, item FROM people INNER JOIN items USING (id)
name | item
Bill | candy
Bill | ice cream

SELECT name, item FROM people LEFT OUTER JOIN items USING (id)
name | item
Bill | candy
Bill | ice cream
Tina | NULL

SELECT name, item FROM people RIGHT OUTER JOIN items USING (id)
name | item
Bill | candy
Bill | ice cream
NULL | milk

SELECT name, item FROM people FULL OUTER JOIN items USING (id)
name | item
Bill | candy
Bill | ice cream
Tina | NULL
NULL | milk

Note that if you ever used real tables like this, your work would probably end up on The Daily WTF.

Re:DB Programming 101?

[cduffy]

Meh. I went to CSU Chico (certainly not a tear-through-your-wallet school) about a decade ago. Database programming was required (IIRC), and reasonably comprehensive for what I think (rightly? wrongly?) was a 200-level class (the softballs you mentioned, the various normal forms and transformations between them, etc etc).

There might be schools that graduate folks without that knowledge, but I'd sure hope there wouldn't be many of them.

Re:DB Programming 101?

[Shados]

Yup, I agree with you, but its actually the majority of schools, -especially- the purist "CS" schools. My girlfriend comes from CMU (which is rated fairly high as far as CS schools go), and while she did take a database course like the one you describe, it was an elective!

I can't say I've surveyed all of the schools, but I know enough people from various schools to beleive its the monitory that requires it (and that ironically, the lower rated schools are more likely to have it as mendatory, go figure!).

I really hope it changes. When I did my degree, I had to take 3 database classes mendatory, and 1 software development class which used databases extensively, so I'm good to go...

Re:DB Programming 101?

[SQLGuru]

Was your degree CS or MIS? My degree is CS and for me database classes were electives (which I didn't take).....in fact, I've learned through the school of "trial by fire"....a friend of mine has an MIS degree and he did get exposed to databases as a required class (might have just been Access, but the fundamentals are there).

I think the main reason that most CS courses don't require it is that CS is a very broad discipline that could be used for embedded programming, OS programming, game programming, etc. MIS is focused on business solutions which are very database heavy. So the broad case doesn't require a class that might not be used in the focus you choose and the narrow case requires it because it is fundamental to your focus.

Layne

Re:DB Programming 101?

[cduffy]

(might have just been Access, but the fundamentals are there)

I'm very skeptical of any MIS class having adequately covered the fundamentals -- schema normalization rather than form building, for example. That might just be a symptom of the school where I went to, OTOH, where the MIS courses (in the view of the CS department, at least) had nothing which in any way could be associated with "rigor".

Frankly, I think that inadequate coverage of a topic is worse than none at all -- if folks have the impression that they know something well enough to be effective in it, whereas in fact they only know enough to be dangerous, those are the people who are going to go out and build unmaintainable or insecure systems.

Re:DB Programming 101?

[SQLGuru]

My comment was more about Access providing the fundamentals of a database engine with which to teach, not that the content of the course was more or less adequate for teaching. That's usually just as much a factor of the curriculum as it is the individual professor.

Layne

nTier validation

[Joe U]

The 3 minimum levels of validation:

Validate at the client tier. (To save a return trip)
Validate at the application server tier. (to save a database trip)
Validate at the data tier. (to save your data)

Why is this so hard for developers to understand?

Re:nTier validation

[EricWright]

Because too many database developers are restricted to working on one tier. They rely on the (incorrect) assumption that the guy working on the next lower tier has done their job properly.

Re:nTier validation

[Joe U]

Then the project manager should be lectured on proper development techniques.

Re:nTier validation

[Kjella]

Developers? No. Try making a PHB understand that. Or a project manager, which either cuts that or some feature the client will notice right away. Or the guy that gets the ungrateful job of coordinating three teams of completely different teams in different subprojects with different managers, trying to keep a common model of "valid data". The real way it works is more like:

1. User validation = stupid "have you filled out these fields" validation
2. Application validation = application logic validation
3. Data logic = field validation and foreign keys etc. to not leave dangling data that's invalid or inconsistant

There's no point in making more client-side validation than that, because you can assume an attacker will send raw data at you anyway. The database layer is rather fucked if the commands are already injected - the best a database can do is to treat data types as expected and not fall for that kind of tricks.

Re:nTier validation

[Kozz]

Preaching to the choir, I'm sure!

I was recently criticized for taking the time to do something "right" (i.e. verify and understand the problem and the technology needed to create a reliable solution). My boss indicated that his (crappy) code was meant as an "emergency fix". But come on, we all know that if his code had accomplished the job (however terribly), he'd have left it right there and never attempted to improve it.

Re:nTier validation

[Joe U]

A properly designed database layer shouldn't have the ability to take in bad data from any source and shouldn't trust the app server (if possible).

One method that I used that worked amazingly well was to use stored procedures with strict validation for all data access to/from the databases. No one had any access except explicit execute access to the stored procedures. Yeah, it was harder to design, but it flew. Porting to other app servers was really easy too, the procedures did all of the data work.

Some designers think this is overly complex, it's not really. It's just a matter of letting your database do your data work (what it was designed for), not your application server (what it wasn't designed for).

Re:nTier validation

[gnuman99]

Sometimes the DB layer is too restrictive (load balancing,data logging,etc.) so you end up with another API "layer". For example,

DB backend <--> DB API on app server #1 <--> Apps on app server #2 <--> GUI/web/whatever

The DB API is the general glue between your apps and the server. But then on most apps you can just stick that in the DB as stored procedures, as you've done.

Re:To all you type safe ninnies

[AKAImBatman]

Type safety and "safe" data types are two different things. One is a language construct intended to prevent errors in code through compile-time checking (though you pay in flexibility), the other is types of data that theoretically can be used in a database without doing validation checks.

I'll leave it as an exercise for you to figure out which one is which.

Does it look like this?

[Anonymous Coward]

delete from comments where 1");--

First Post!!!!!!!!!!

It's like Vulnerable Squared!

[GuntherAEPi]

So, in order to pull this off, you must first already have elevated permissions and the ability to execute arbitrary code. In other words, if you've been compromised, you can be compromised in new and exciting (well, maybe not exciting) ways. Wow, what an earth-shattering revelation!

Thats what they said about cross site scripting.

[Anonymous Coward]

Your comment "he points out that there really is little chance of it being used in the real world, that is an understatement" is reminiscent of those who proclaimed no one would need more than one 360k floppy.

It best concluding non vulnerability without time and personal investment is naive and at best considering the large volume new security measurements in evidence prove that statements like these are foolish usually false and cause much more damage by breeding a false sense of security and complacency and ignorance.

Re:Thats what they said about cross site scripting

[stoolpigeon]

I don't think so. This is dependent upon circumstances where the horse is already out of the barn so to speak.

Re:security super-genius

[ch-chuck]

Super-genius - that's when a super-villian switches to the light side of the force.

Re:security super-genius

[Macthorpe]

Or, and here's a thought, you could just treat as it is, a little bit of exaggeration to add some spice to what would otherwise be a fairly boring news piece.

Just because it's not rigourously defined it doesn't mean you can't use it. You clearly know what they intended, and that's what is important here.

From comic to reality

[GoNINzo]

This makes it clear it's only a matter of time before xkcd predictions become reality.

Re:From comic to reality

[Devv]

Second time that strip is posted today.

The real question is:
Would that be illegal?

Re:From comic to reality

[metamatic]

I posted the exact same joke to my own site before the xkcd comic...

Re:From comic to reality

[metamatic]

So the point is it's not an idea that xkcd invented.

Re:security super-genius

[BigBlueOx]

The term "super-genius" was coined in modern English in 1952 in "Operation: Rabbit". The fact that the supposedly encyclopedic Wikipedia refuses to index on this term, despite my frequent repeated submissions of well thought out and quite lengthy protest emails, just goes to show their blighted pig-ignorance.

http://en.wikipedia.org/wiki/Operation:_Rabbit

This is not merit a whitepaper

[Anonymous Coward]

First off, this isn't a new class of attack. This type of attack is already known as second order SQL injection. Second, as several people have noted, you need to be able to execute the ALTER SESSION command. That means you're already issuing SQL commands directly. So, this attack is really only useful when can already inject, but need SQL to run in the context of a more privileged stored procedure. Finally, this attack relies on a very abnormal statement form. All said, that's a whole lot of dominoes that need to line up for a simple elevated SQL privilege.

This whole thing just sounds like an odd bug that someone at NGS found somewhere. It's certainly clever, but it's not a common pattern or new class of bug--and definitely not worthy of a white paper. What I find really odd is that Litchfield and the NGS guys used to do really impressive work. This is way below the bar of what they've produced in the past.

MOD PARENT UP...

[Urban Garlic]

And to think I wasted my mod points on trivialities in the "Developer" section. If only I'd known something important was coming.

Wile E. Coyote, Sooper Jeenyus -- I like the sound of that.

Re:security super-genius

[sm62704]

a little bit of exaggeration to add some spice

Exaggeration and spice belong in entertainment, not news. News should be factual and concise.

to what would otherwise be a fairly boring news piece.

If it's too boring to read it's too boring to post. However, the submitter (IINM) commented with a link.

Zen Quote

[alexborges]

If an unbreakable server breaks, and everyone has their hands over their heads chanting the oracle "unbreakable" mantra whilst picking each other asses with their toes, did it actually break?

Re:Zen Quote

[Shados]

What will break is your company's check book when the Oracle guys are done billing you for support :)

Why validate when you can sanitize?

[davidbrit2]

Honestly, I never understand the people that constantly trumpet "Validate! Validate! Validate!" whenever they're dealing with a web/database app. If you're escaping/sanitizing your inputs properly, then you don't need to chase your tail making sure your users haven't entered something "evil".

The specifics vary by platform, but if you're building a dynamic SQL statement in SQL Server, for instance, you'd use the Replace function on the concatenated values to change any occurrence of ' to ''. For MySQL, change ' to \'.

It's very simple, very consistent, and very safe. This is what any decent parameterized query API will do behind the scenes. It's like the old anecdote: you can either spend a lot of time building an exhaustive filter list of offensive words that you don't want showing up in your tracking/order/confirmation codes, or you can just not use any vowels and be done with it.

Re:Why validate when you can sanitize?

[Shados]

No no no. This has a tons of potential holes, such as an encoding based attack in UTF16 or similar encoding. Use -prepared statements-.

Escaping/sanitizing is just one step up from validating. Let the -driver- do it for you, not the language or the framework. The database itself is the only one who truly knows how to handle itself, and drivers tap into that in prepared statements. -THAT- will protect you. Parameterized query APIs do -not- simply escape stuff in the back. Things are done at the level of the connection, chatting with the database API to create a cached/compiled version of the query, then plug in parameters -after- the query was parsed (so at that point its impossible to modify it).

That is -much- safer than just cleaning up a string (because it cannot abuse encoding/string related features), and has the extra advantage in many DBMS to also allow you to reuse query plan cache, thus improving performance and making it easier to benchmark and profile queries.

Re:Why validate when you can sanitize?

[gnuman99]

How I wish Ruby on Rails actually did parameters, but all it does is stupid escaping. Even for parameterized queries like,

find( :first, :conditions => [ "test_column=?", my_test ])

Ruby on Rails will *replace* ? with the my_test that it escapes! I have no idea what they were thinking. And I do agree with you that parameterized queries are the only safe way, and should be the ONLY way to actually query databases. Heck, it is faster for recurring queries because all you have to do is pass the parameters and the query is already parsed and ready for execution.

Re:Why validate when you can sanitize?

[Alpha830RulZ]

I found your quote interesting. The font you choose is hard for these old eyes to read. Must you use something smaller and harder to read than the default font? It interferes with your message.

Re:Why validate when you can sanitize?

[_Shad0w_]

You're still using dynamic SQL; you should use parametrized queries.

Re:Why validate when you can sanitize?

[davidbrit2]

I agree, but there are some situations where you simply can't. Take for instance dynamically specifying the database to use in an SQL Server query. You get various syntax errors if you try to do "SELECT * FROM @database.dbo.MyTable", so you have to concatenate. For all other situations, stick to stored procedure parameters.

Simpler prevention

[hanshotfirst]

Of course this is prevented by the simplest way to prevent SQL injection in Oracle - USE BIND VARIABLES. Each of his examples is thwarted by binding the parameters, instead of simple string concatenation.

No no no

[FranTaylor]

You are missing the point of the attack. The validation IS done, but the attack subverts it.

Validating dates, especially international ones, is enormously complex, and the validation process itself is extremely prone to errors and undesired behavior. If you expect all database clients to do full validation of all input data before passing it on to the database, you are going to see some really miserably performing clients, and not a lot of extra security. Each validation step would also have its own points of vulnerability.

Re:No no no

[Shados]

Actually, its not that tough in modern environments... Java, .NET, whatever, will have globalization enabled datetime objects, along with one or more parsing method, that most likely throw an exception if it cannot, and will return a datetime object otherwise...

So get the culture from the request, parse the date, if it throws an exception, return an error, otherwise pass the -datetime object- (not the string!) to the database API, and there really isn't anything that can happen. Even if an invalid date was to go through... its a datetime object (basically, a number of tick or something, depending on the environment), not a string or a vulnerable complex type, so not much to do.

Now if you were to try to validate the string yourself...good luck indeed.

Re:No no no

[jedidiah]

That's *ss backwards. The clients are what can trivially scale,
the database isn't. This is the perfect example of something that
can be easily done in parallel. The real problem is that you
have distributed enforcement of business rules and the
possibility that rules aren't properly enforced when they are
pushed out away from the database.

Although even this is fairly easy to manage.

Bog down the database and you bog down everyone.

Re:No no no

[Joe U]

I've always maintained that the database should enforce data integrity. Not just hold data, but actually stop bad data from ever getting saved.

That being said, I might be spoiled using SQL server. I've done some setups where a SQL server (sometimes MSDE) runs on the app server and enforces the rules (with some server caching tables as well) and passes data out to a SQL server cluster which holds the actual data. (It's really hard to explain without showing diagrams and violating my NDA, but it worked well for the project at the time, saved a good chunk of money too)

This is either "Nothing to see here, move along."

[aix tom]

Or an "We told you so". This attack vector relies on dynamic SQL inside pl/sql procedures.

Something that EVERY developer I ever learned from, and EVERY knowledgeable person on every Oracle forum I have frequented will tell you to avoid like hell.

Since :

1. It is an attack vector (as was shown now again)

2. It breaks compile time syntax checks and might put unverifiable errors into production code.

3. It breaks the built-in package/function dependency checks of pl/sql.

4. It is pretty much impossible to debug.

5. Is a possible disastrous resource hog because of multiple additional context switches between SQL and PL/SQL.

In my 8 year career of programming in pl/sql I have use dynamic SQL exactly at TWO times, and both times I programmed it in a way that dynamic SQL was executed in a read-only schema and returned exactly one result which was put into a variable and then sanity checked.

Again you miss the point

[FranTaylor]

We are not talking about using some kind of framework. We are talking about THE ACTUAL FRAMEWORK. You folks imagine that there is some magic perfect code down there that you just call to parse a date and you forget that it actually has to implemented somewhere. Oracle has been ported to roughly a zillion different platforms. Do you really think they are going to rely on the platform to parse their dates?

Re:This is either "Nothing to see here, move along

[FranTaylor]

Your argument is compelling except for the fact that many Oracle customers are pretty clueless and will not program with the excellent rigor that you do. This is compounded by Oracle's overblown security claims, which gives a false sense of security to less skilled developers.

Re:This is either "Nothing to see here, move along

[aix tom]

That is unfortunately true.

I remember one case where I had a discussion with one "developer" on a discussion board who tried to change his application which :

• 1. Assembled the SQL in PHP
• 2. Sent it to Oracle
• 3. Read the resultset

to

• 1. Assemble the SQL in PHP
• 2. Send it to an Oracle procedure as a ONE STRING bind variable
• 3. Executed it in the procedure with EXECUTE IMMEDIATE
• 4. Returned the result as a cursor bind variable.

And he claimed he made it "more secure by using bind variables"

Thats like making your front door more secure by buying a very sophisticated $1000 lock and then putting that lock on a pedestal beside your door and dusting it weekly. ;-)

[OT] yes, I saw the typo (too late)

[hummassa]

s/extendend/extended/

Does it work on Oracle 10.2.0.3 ?

[Keun]

Did anyone actually tried this vulnerability ? I tried it on one of our test systems (Oracle 10.2.0.3.0) but can't reproduce the examples (can't hack the SYSDATE function with ALTER SESSION). Has this been fixed in recent Oracle versions ?

Re:Does it work on Oracle 10.2.0.3 ?

[Keun]

Working now ... blame TOAD :-)

Re:security super-genius

[sm62704]

Ah, I found the reference in the wikipedia article on the cartoon. That would neab that the term is a joke! There is, in fact, no such thing as a "super-genius". "Genius" refers to someone with super-intelligence (that would be Einstien, whose picture illustrates the Wikipedia entry on "genius").