Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

New Attack Exploits "Safe" Oracle Inputs

Soulskill posted more than 6 years ago | from the it's-safe-until-it-isn't dept.

Security 118

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."

cancel ×

118 comments

Sorry! There are no comments related to the filter you selected.

To all you type safe ninnies (-1, Offtopic)

Anonymous Coward | more than 6 years ago | (#23201680)

Ha!

Ha ha!

Ha ha! Ha!

I've always felt that type safety had way too much overhead and that its actual safety was just a myth. In the future all languages will be considered type unsafe--the way nature intended (and the languages nature intended are of course assembly language, C, and early implementations of FORTRAN--COBOL is right out!).

I think we will eventually have to come to the realization that programming requires skilled people and that these super-secure language constructs won't work. While it may appear that you can hire half-trained code monkeys to write your apps, it is just an appearance. Reality begs to differ and real coders who actually understand what a language type actually means will be needed if you don't want your code to explode in your face.

Re:To all you type safe ninnies (4, Informative)

AKAImBatman (238306) | more than 6 years ago | (#23201962)

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.

heh (4, Interesting)

stoolpigeon (454276) | more than 6 years ago | (#23201688)

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 (4, Insightful)

arth1 (260657) | more than 6 years ago | (#23201832)

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 (1)

stoolpigeon (454276) | more than 6 years ago | (#23201936)

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 (1)

arth1 (260657) | more than 6 years ago | (#23202220)

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 (1)

ShieldW0lf (601553) | more than 6 years ago | (#23203386)

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

Re:heh (1, Insightful)

Anonymous Coward | more than 6 years ago | (#23204026)

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 (1)

hummassa (157160) | more than 6 years ago | (#23206796)

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".

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

hummassa (157160) | more than 6 years ago | (#23206810)

s/extendend/extended/

Re:heh (0, Offtopic)

JDizzy (85499) | more than 6 years ago | (#23203514)

Always assume your input is an arbitrary location and length of pi. *joking*

This is why assuming a datatype, even the ones advertised as constant, or immutable (strongly typed), are bad.

I might be missing some DBA jargon here though, about schema types, instead of language.

Re:heh (2, Informative)

morgan_greywolf (835522) | more than 6 years ago | (#23201854)

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 (1)

zehaeva (1136559) | more than 6 years ago | (#23206730)

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 (1)

alan_dershowitz (586542) | more than 6 years ago | (#23201894)

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

Re:heh (0)

Anonymous Coward | more than 6 years ago | (#23202076)

alter session isn't an object permission, it's a system privilege. There is no "SESSION" object. ALTER SESSION lets you do stuff like the the NLS language, set current schema name, enable session tracing(bad, bad, bad)...

Re:heh (1)

alan_dershowitz (586542) | more than 6 years ago | (#23202318)

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 (1)

Shados (741919) | more than 6 years ago | (#23202084)

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 (1)

1001011010110101 (305349) | more than 6 years ago | (#23201904)

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 (3, Informative)

DazzaL (1029774) | more than 6 years ago | (#23202708)

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 (1)

1001011010110101 (305349) | more than 6 years ago | (#23206478)

<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).

Thats what they said about cross site scripting. (3, Insightful)

Anonymous Coward | more than 6 years ago | (#23202028)

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 (1)

stoolpigeon (454276) | more than 6 years ago | (#23202126)

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

Re:heh (4, Insightful)

Joe U (443617) | more than 6 years ago | (#23202058)

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 (2, Informative)

martinmarv (920771) | more than 6 years ago | (#23202988)

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 (1)

Joe U (443617) | more than 6 years ago | (#23204288)

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 (2, Informative)

Kozz (7764) | more than 6 years ago | (#23203056)

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 (1)

Joe U (443617) | more than 6 years ago | (#23204260)

I see you met my old boss.

Re:heh (0)

Anonymous Coward | more than 6 years ago | (#23206534)

>> 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.
didn't take long for the usual overgeneralizing stored procedure nazi to show up and claim to have found the silver bullet.

Re:heh (4, Insightful)

moderatorrater (1095745) | more than 6 years ago | (#23202144)

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 (1)

glwtta (532858) | more than 6 years ago | (#23202994)

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 (1)

dreamchaser (49529) | more than 6 years ago | (#23204968)

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 (1)

Hoi Polloi (522990) | more than 6 years ago | (#23202240)

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 (1, Informative)

Anonymous Coward | more than 6 years ago | (#23203302)

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 (2, Informative)

blirp (147278) | more than 6 years ago | (#23203926)

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.

Dear Oracle fanboys: (-1, Offtopic)

Anonymous Coward | more than 6 years ago | (#23201812)

Suck it, bitches.

nothingforyoutoseehere (1)

davidwr (791652) | more than 6 years ago | (#23201816)

IF programmer_is_smart_and_validates_data THEN PRINT no_big_deal ELSE PRINT he_has_bigger_worries

Re:nothingforyoutoseehere (1)

BigJClark (1226554) | more than 6 years ago | (#23202590)


noob.

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

Re:nothingforyoutoseehere (1)

rjstanford (69735) | more than 6 years ago | (#23202732)

Seems pretty basic to me...

Itsatrap! (1, Offtopic)

esocid (946821) | more than 6 years ago | (#23201828)

Come on, do I have to tag this myself?

Use ORMs (2, Informative)

chrysalis (50680) | more than 6 years ago | (#23201834)

Interesting flaw.

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

Re:Use ORMs (3, Informative)

Shados (741919) | more than 6 years ago | (#23202050)

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 (1)

hawkinspeter (831501) | more than 6 years ago | (#23202626)

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 (0)

Anonymous Coward | more than 6 years ago | (#23203630)

Actually people hard-code dates into queries with skewed data as a performance optimization. With a bind variable you get a plan that't equally good for all dates. With a hard-coded date you get a plan optimized for the value you happen to use. So, for instance,

SELECT * FROM WEB_SERVER_TRAFFIC_LOGS WHERE LOGDATE > '22-APR-08'

will often run much better than

SELECT * FROM WEB_SERVER_TRAFFIC_LOGS WHERE LOGDATE > :someDate

Re:Use ORMs (1)

zifn4b (1040588) | more than 6 years ago | (#23203014)

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 (1)

Shados (741919) | more than 6 years ago | (#23203040)

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? (1)

Obsi (912791) | more than 6 years ago | (#23201850)

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? (5, Insightful)

0racle (667029) | more than 6 years ago | (#23202248)

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? (0)

Anonymous Coward | more than 6 years ago | (#23203562)

Well, that's a good way to start, but honestly, if you do this stuff for any length of time at all, you find yourself wishing you knew of a way to keep from repeating the same work over and over. (And we all know that repetitive tasks are prime targets for replacement with a small shell script...)

So your next step is to learn SQL (usually heavily favoring MySQL and its irregularities). And structured PHP. And why PHP sucks. Then Java. Then some premade Java framework, maybe even a few of them. Then some Java build tools. Then why Java sucks. Then ASP.NET and all the VS tools and SQL Server stuff. Then Winforms .NET stuff.

That's where I am now. I guess my next step is to learn why .NET sucks. (That seems logical, right?)

Maybe I'll try some non-academic C++ after that. Then I'll learn why it sucks.

Re:DB Programming 101? (4, Insightful)

Shados (741919) | more than 6 years ago | (#23202454)

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? (1)

Chirs (87576) | more than 6 years ago | (#23203300)

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? (1)

Shados (741919) | more than 6 years ago | (#23203398)

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? (2, Informative)

VGPowerlord (621254) | more than 6 years ago | (#23203866)

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 [thedailywtf.com] .

Re:DB Programming 101? (1)

Goyuix (698012) | more than 6 years ago | (#23203668)

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? (1)

Shados (741919) | more than 6 years ago | (#23203790)

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? (1)

cduffy (652) | more than 6 years ago | (#23204448)

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? (1)

Shados (741919) | more than 6 years ago | (#23204592)

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...

nTier validation (5, Insightful)

Joe U (443617) | more than 6 years ago | (#23201932)

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 (1)

EricWright (16803) | more than 6 years ago | (#23202066)

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 (1, Offtopic)

Joe U (443617) | more than 6 years ago | (#23202130)

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

Re:nTier validation (0, Offtopic)

EricWright (16803) | more than 6 years ago | (#23202202)

You must have different project managers than I do. Ours our completely obsessed with setting (and missing) milestone dates. They don't care HOW things get done, or even if they're done properly.

Brother, can you spare a decent PM?

Re:nTier validation (3, Insightful)

Kozz (7764) | more than 6 years ago | (#23203072)

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 (0)

Anonymous Coward | more than 6 years ago | (#23203182)

I work for a database vendor (not Oracle) and we are obsessed with having a bulletproof product that blows our competitors out of the water.

Re:nTier validation (2, Interesting)

Kjella (173770) | more than 6 years ago | (#23202252)

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 (1)

Joe U (443617) | more than 6 years ago | (#23204234)

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 (1)

gnuman99 (746007) | more than 6 years ago | (#23205308)

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.

No no no (1)

FranTaylor (164577) | more than 6 years ago | (#23203118)

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 (1)

Shados (741919) | more than 6 years ago | (#23203900)

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.

Again you miss the point (1)

FranTaylor (164577) | more than 6 years ago | (#23205152)

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?

security super-genius (0, Offtopic)

sm62704 (957197) | more than 6 years ago | (#23201944)

Ok, this may be barely on topic (and I've had more on-topic posts than this one modded "offtopic") but the summary describes David Litchfield as a "super-genius". Neither the dictionary [reference.com] nor Wikipedia [wikipedia.org] has entries on "super-genius". Well actually wikipedia does have it listed (linked) but it describes "a flash cartoon flash game flash animation web portal channel and studio" and a rock and roll band.

The wikipedia entry on IQ does not contain the word "genius", let alone "super-genius".

So if someone (preferably the super-genius who wrote the summary) can tell me what a "super-genius" is, I'd appreciate it. Actually I'd appreciate it more if submitters and editors wouldn't use jargon that I'm unfamiliar with and can find neither in the dictionary nor wikipedia.

Re:security super-genius (1)

ch-chuck (9622) | more than 6 years ago | (#23202154)

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

Re:security super-genius (1)

Macthorpe (960048) | more than 6 years ago | (#23202164)

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.

Re:security super-genius (1)

sm62704 (957197) | more than 6 years ago | (#23202716)

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.

Re:security super-genius (2, Informative)

BigBlueOx (1201587) | more than 6 years ago | (#23202268)

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 [wikipedia.org]

MOD PARENT UP... (1)

Urban Garlic (447282) | more than 6 years ago | (#23202486)

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 (0)

Anonymous Coward | more than 6 years ago | (#23202820)

Wile E. Coyote claimed to be a super-genius in an old Bugs Bunny cartoon.

Does it look like this? (2, Funny)

Anonymous Coward | more than 6 years ago | (#23201964)

delete from comments where 1");--

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

It's like Vulnerable Squared! (1)

GuntherAEPi (254349) | more than 6 years ago | (#23202024)

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!

From comic to reality (5, Funny)

GoNINzo (32266) | more than 6 years ago | (#23202192)

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

Re:From comic to reality (0)

Anonymous Coward | more than 6 years ago | (#23202468)

I think you have got it backwards. It made to xkcd because "sql injection" has already been a known concept in theory and reality for long before that particular episode.

But as they say, ignorance is bliss.

Re:From comic to reality (1)

metamatic (202216) | more than 6 years ago | (#23203116)

Re:From comic to reality (0)

Anonymous Coward | more than 6 years ago | (#23204574)

So? xkcd's was more fun to read.

Re:From comic to reality (1)

metamatic (202216) | more than 6 years ago | (#23205124)

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

Re:From comic to reality (1)

Devv (992734) | more than 6 years ago | (#23203000)

Second time that strip is posted today.

The real question is:
Would that be illegal?

This is not merit a whitepaper (4, Interesting)

Anonymous Coward | more than 6 years ago | (#23202302)

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.

mo3 down (-1, Offtopic)

Anonymous Coward | more than 6 years ago | (#23202306)

that sorded, stupi3. To the

Zen Quote (1)

alexborges (313924) | more than 6 years ago | (#23202754)

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 (1)

Shados (741919) | more than 6 years ago | (#23203294)

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? (1)

davidbrit2 (775091) | more than 6 years ago | (#23202844)

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? (4, Insightful)

Shados (741919) | more than 6 years ago | (#23203274)

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? (1)

gnuman99 (746007) | more than 6 years ago | (#23205328)

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? (1)

Alpha830RulZ (939527) | more than 6 years ago | (#23205812)

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? (0)

Anonymous Coward | more than 6 years ago | (#23205074)

the method your using is dangerous and assumes you know of every method for sneaking attacks in. For instance you didn't both to mention canonicalisation of web data etc? I asusme you are also checking for that too? The only truly safe way to validate is if you know what you expect in the data and disgard everything else. escaping/sanitizing is no more than a warm fuzzy blanket, might make you feel warm and safe against 99% of attacks but it doesn't do shit against the truly intent attacker.

Re:Why validate when you can sanitize? (1)

_Shad0w_ (127912) | more than 6 years ago | (#23206066)

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

yeh (0)

Anonymous Coward | more than 6 years ago | (#23203042)

Oracle is poo. Next!

Simpler prevention (1)

hanshotfirst (851936) | more than 6 years ago | (#23203084)

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.

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

aix tom (902140) | more than 6 years ago | (#23203958)

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.

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

FranTaylor (164577) | more than 6 years ago | (#23205170)

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 (1)

aix tom (902140) | more than 6 years ago | (#23206036)

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. ;-)

"New" attacks (rolling eyes) (0)

Anonymous Coward | more than 6 years ago | (#23205332)

Why is it that every time I see a headline detailing a new attack thinking I might actually learn something new I take the bite only to be severly disappointed moments later when I realize the author is in fact an idiot or simply spouting what should have been common knowledge.

I totally tuned out after seeing the line "execute immediate" and ALTER SESSION.. Anyone who doesn't keep their data tiers outside of their users grubby little hands deserves what they get especially concidering Oracles piss poor security record.

Anyone who builds strings in procedures that even go near user input and then has the audacity to execute them ... I don't much care what your execuse is deserve everything you get. I can go on and on but you get the picture I hope.

I realize bulk portions of Oracles code base are older than time itself and in some respects Oracle is brain damaged beyond comprehension -- '' IS NOT NULL FOR THE LOVE OF EVERYTHING HOLY but none of this even remotely passes as an execuse to ever be succeptable to such a "new" technique.

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>