Slashdot Mirror
Mass SQL Injection Attack Hits Sites Running IIS
Trailrunner7 writes "There's a large-scale attack underway that is targeting Web servers running Microsoft's IIS software, injecting the sites with a specific malicious script. The attack has compromised tens of thousands of sites already, experts say, and there's no clear indication of who's behind the campaign right now. The attack, which researchers first noticed earlier this week, already has affected a few high-profile sites, including those belonging to The Wall Street Journal and The Jerusalem Post. Some analyses of the IIS attack suggest that it is directed at a third-party ad management script found on these sites."
its not 'sql'. its IIS. sql accepts any query given to it by the program. its the script's job not to let in any malicious queries. its a script fault.
Read radical news here
Bobby Tables strikes again.
DROP DA TABLE AND REACH FOR IT MISTER
You are not logged i
n. You can log in now using the convenient form below, or Create an Account, or post as Anonymous Coward.
how webserver could work with SQL? Maybe its scripts problem?
Anyone writing scripts that don't use parametrized stored procedures for the database or Linq needs to find a new line of work.
... That the volume of Apache and PHP downloads are about to go up.
This is my opinion. To make sure you don't steal it, it's covered by the DMCA.
With their families watching.
I hate people. No, not you, but them. Over there. Next to the oranges.
Sent from your iPad.
why are only IIS sites affected?
There is no "-1 offended" or "-1 you don't agree with me" mod options for a reason.
But I thought platform independence was a good thing!
There's no -1 for "I don't get it."
What is wrong with using regular parameterized queries instead of SPs?
Be wary of any facts that confirm your opinion.
The threatpost article says "which then installs malware on the victims' machines" -- anyone have info as to what malware they're referring to? Is it known? Detected?
It was nice of them to deallocate the cursor when done. Thanks!
from the article:
"What do all these sites have in common? They are all hosted on IIS servers and using ASP.net. This is the output of our scanner against www.intljobs.org:"
Reading through the Sucuri article, the script piggy backed on a utm_content query parameter. Aren't these used in metrics tracking like Google Analytics? Has anyone mentioned a name of the 3rd party that let this one slip?
it is due to sql...
Huh? It happened because they use sql?
Following that line of thought,
isn't it really due to the use
of CPU's in those servers?
If it is a SQL Injection attack - then it's sloppy development by a sloppy developer. Platform/DB... independent!
http://dealbook.blogs.nytimes.com/2010/06/11/morgan-stanley-jpmorgan-to-take-lead-on-a-g-m-i-p-o/?hpw
george orwell taught these guys well. everything will be doublegood soon?
I run a site that got hit by this. It's hosted by Rackspace Cloud, so one presumes that IIS and MSSQL were patched up. We aren't using any kind of ad network, so I think the attackers were just looking for ASP sites that used queries. We got hit because we failed to sanitize inputs in one spot.
We were lucky, though. Since the attack blasts the script code into every column of every table it can get its hands on, it actually broke the SQL queries that pull up the page content, so users just saw an error message instead of page content + malware.
I think I recall reading the *exact* same story in 1996 and 1998 or so. And... about once a year every other year too.
A Stackoverflow answer explains the advantages very nicely.
Generating SQL statements in a script is fine...
Here's a more accurate version: Anyone writing code that doesn't validate input needs to find a new line of work.
http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
How many times do we need to say the same thing before people start listening? Oh, that's why we still have STDs. Because people don't take basic steps to reduce risk by orders of magnitude.
This is SQL injection, and they play it out as though it is a fault of IIS. If today's news media knew anything about security they would know its just poorly written websites that are at fault.
They make out as though this is a vulnerability that will affect everyone running IIS - without some level of competence, the average reader would deduce that "CRAPPY MS SOFTWARE FUHFUHFUH!"
Not a day goes by where a popular online news outlet gets the whole story ass-backwards. This is the second one today (the first was a "Zero-day" five-day vulnerability on computerworld).
Seriously, when are we going to get people that actually know what they are talking about writing the news??? Or at the very least, slashdot mods, stop linking to the retards
</rant>
Of which you then leave out:
So it looks like a SQL injection attack against a third party ad management script.
Here's a more accurate version: Anyone writing code that doesn't sanitize input needs to find a new line of work.
Fixed that for you
AccountKiller
Someone please mod parent clueless rather than insightful. Neither IIS nor SQL are at fault in this case, it is a third party add-on that is being exploited. There is nothing here that uniquely makes IIS or SQL more or less vulnerable than, say, Apache and MySQL. It is perfectly possible to code a LAMP application that is equally vulnerable, and recent attacks on applications such as Wordpress prove this is true. The article IMHO is being needlessly attacking towards IIS. It's like saying 'Mass SQL injection attack hits x86 servers' therefore somehow implying that servers running on Arm would be secure.
I was in my first week or so on the job with a firm about 2 years ago when this same thing happened. The IIS servers they ran were getting hit with a few thousand attempts / sec. There were a bunch of old coldfusion sites with SELECT * FROM table WHERE id=#url.id# floating around and many sites were compromised. Getting rid of the IIS header and the .cfm extension got rid of the bots attempts as we added filters to coldfusion. It was one of the worst weeks ever, I don't work there any more. There was even a slashdot post about the massive attack then to.
neorush
IBM seems to think that "validate input" is an appropriate term for this
http://www.ibm.com/developerworks/library/l-sp2.html
I think he was more asking about a parameterized-SP vs parameterized-TSQL, not a SP vs LINQ debate (which is what you linked)
WSJ runs Apache, this article is probably bogus.
Or at least Prepared Statements (if your DBA is a PITA and won't let you go heavy on the Stored Procedures).
Sp's provide a significantly higher degree of control over allowed values without having to resort to app-level scrubbing. They also tend to perform a little bit quicker.
That said, things like multiple optional parameters will cause you to tear your hair out as a bad execution plan can be chosen. You have dynamic SQL inside sp's but there's no way I would use that on a public-facing site. Maybe 2008 solves some of this; I am anxious to try it out after skipping the mess that was 2005.
Slashdot is about:
1) dropping memes in at every available opportunity.
2) making puns based on something unrelated
3) ???
4) PROFIT!!
you dont know what you are talking about. there is no 'rogue' query here. the query that is passed in in a sql injection attack has NO difference from the queries that are put in by the script itself. ie, UPDATE users SET group = admin WHERE user_id = X. this is an example of a possible VALID query that passes in by the system when an admin decides to make someone admin.
IF, you find a hole in the script, and are able to break the script code, and insert this query with your own userid and send it to sql, IT IS NOT SQL'S PROBLEM. sql is just doing what it is given to it, properly. it is the script's fault for allowing you to be able to break the script, and run a query that was limited to only admins.
Read radical news here
Why? If my postcode is " ' ; DROP DATABASE master; " I should be perfectly able to enter it.
Way to open up a techno religious debate. You might as well have said what's wrong with using Linux over BSD or Vi over Emacs or vice versa.
A libertarian shat on my carpet once. Claimed the free market would sort it out. -Ford Prefect(8777)
Here's a more accurate version: Anyone writing code that doesn't validate input needs to find a new line of work.
How often does this happen in a one-developer situation (outside newbie projects)? The problem is that there's supposed to be exactly one sanitizing layer, for example if you percent encode or HTML encode or whatever twice, you often get a wrong result like "Barnes & Noble" instead of what you expected. Every function validating everything is likely to be both a performance killer and the results being plain wrong. On the drawing board it's easy, draw a big line and say dirty strings on this side, clean strings on the other and a sanitizing layer in the middle. But in practice nobody notices if you don't, a string is a string is a string because someone forgot or took a shortcut, in many cases being completely oblivious to the problem. In an application with heavy user interaction, I'd almost be tempted to have a dirty string and clean string subclass. But I figure someone will just cast it to a clean string to make it work anyway.
Live today, because you never know what tomorrow brings
why is anybody running IIS anyway... Is this not another outdated technology from mickey$oft.
Called it,
http://news.slashdot.org/comments.pl?sid=1683322&cid=32538476
enjoy being a freetard.
What this attack is doing is hitting sites that may utilize Google's' email campaign tracking features. They are probably taking advantage of sloppy developers who are storing the parameter values from the campaign locally for their own analytics, probably by building dynamic SQL. Now there could be some form of Open Source HttpModule code out there that actually does this auto-magically, which might account for why it's such a specific attack vector. So in reality it could affect MS SQL Server or MySQL, it's just so far they are just calling pages that have aspx on the end.
I'm getting REALLY sick of website mass hacks. I've removed different versions of that stupid fake antivirus crap and the last couple I've seen seem to have had the TDSS rootkit piggybacked on it too. Now that they're using java and reader as entry points, I can't just tell people to use firefox. I can't tell them use ____ antivirus because it still stop it because nothing will right now. At this rate, I'm going to have to tell people to just not randomly surf the web period. They either need to disconnect certain countries from the internet entirely or get their shit together and stop leaving gaping holes in software that millions of people run.
Google's Super Secret Search Algorithm: SELECT @search_results FROM internet WHERE @search_results = 'good'
"The SQL injection attacks that allow the systems to be compromised are occurring due to vulnerabilities in third-party web applications and do not demonstrate vulnerabilities in Microsoft software," Bryant told McMillan.
SQL injections don't target SQL servers or web servers. They target poorly written scripts. This does not "target Web servers running Microsoft's IIS software" as the summary says. It targets some stupid advertising script that all the sites had in common. Look at the analysis of the attack. It does s GET on /page.aspx and passes it utm_content which contains an escaped SQL statement.
A quick Google search shows that utm_content is part of Google Analytics. Does this mean it is a bug in Google Analytics?
Wow, Imagine if the payload included a .js file exploited this as well: http://tech.slashdot.org/story/10/06/11/0143255/Google-Researcher-Issues-How-To-On-Attacking-XP Talk about a worst-case scenario, that could potentially 0wn a LOT of xp machines really fast.
Remember this next time you assert that Windows only has more malware than linux/osx due to its popularity.
From the "analysis", it appears to be a request parameter injection at the end of an Urchin parameter (utm_content). From my reading it appears that it is either an older version of Urchin or they manually set the database as SQL server. Can anyone who was hit by this verify they are running Urchin 6 or manually pointed it to SQL Server?
It happened again since the 8th: http://blog.sucuri.net/2010/06/mass-infection-of-iisasp-sites-2677-inyahoo-js.html
From a security standpoint, PHP developers are the worst of the web app world. By far.
The problem is doing web security diagnosis on /. and the fact that it was written by someone stoned on PHP in the first place.
If most people on Slashdot can't even tell how a fucking SQL injection works, no fucking wonder there are so many vulnerable websites.
I mean, what the Christ, people?
You have a webserver. Any webserver.
The webserver hosts a script. Any script.
The script takes input. Any input.
The script passes the input to a database. Any database.
SQL INJECTION.
The right way to do it, is this:
You have a webserver. Any webserver.
The webserver hosts a script. Any script.
The script takes input. Any input.
THE INPUT IS CLEANED UP BY THE SCRIPT.
The script passes the CLEANED UP input to a database. Any database.
Has nothing to do with IIS. Has nothing to do with ASP. Has nothing to do with .NET. Has nothing to do with MSSQL. Has nothing to do with Microsoft.
Stupid fucking morons. Pull that stick out of your ass and move along now.
How can you even possibly *not* sanitize the inputs? Python's mysql module, for instance, does the work for you:
cursor.execute("select grade from grades where student = %s", ("bobby; drop table grages"))
would be completely fine.
NewslilySocial News. No lolcats allowed.
Validation also protects you from your own stupidity.
As far as I know using Google Analytics is nothing more then including a strong on your html output that instructs the browser to get some javascript from google to record the traffic. The server itself never executes anything from google, it would be the same as if you included the google logo on your pages. Your server ain't involved.
For a 3rd party script to have an effect it somehow needs to be run or the server or have at a minimal its values parsed. Google Analytics (at least for small customers, don't know about big ones) is not installed on the server. I could not call their script on any of my servers despite all my sites use it because it is not there. Not only is there no connection to my database, there is no script. You can't call any URL on my site that even handles "utm_content". Unhandled variables just get ignored. Try it with slashdot. Add "&slashdotters=weenies" to the url and nothing whatsoever will happen.
What is most likely is that this is some ADVERTISING script (GA isn't used for advertising but tracking for the site owner) that is used and that has its own installation including database access for some reason or at least can be made to access the database. Websites ain't magic. I can't just replace "op=reply" in the slashdot url with an SQL command and expect a result. It must be a variable that is used in a query. GRANTED, it could be the case where every query is logged into a database straight without sanitation (did you remember to think of this oh you mighty logger of all data?) but that is silly (and very common).
A bug in Google Analytics would affect Google but not endusers.
A bug in an other script that you have to install COULD affect you. Doubleclick (owned by Google) might work that way.
Remember, javascript rarely runs on the server and therefor can't affect the server.
MMO Quests are like orgasms:
You may solo them, I prefer them in a group.
Well yes... yes it could. I read the article completely. It is caused by a weakness in some ad placement code... ad placement code that runs on IIS through ASP or something like that. It sounds as if this could just as easily happen to a LAMP server if the same shoddy code were inserted into a Linux/Apache/MySQL/PHP stack. But why hasn't it happened?
The article doesn't go into what ad company put the code out there or if that company also supports LAMP servers. I think those bits would be good to know. But as many others, I am inclined to blame Microsoft for this somehow.
Let's face the fact, though, that Microsoft didn't have a direct hand in this. SQL did what it was told to do. IIS did what it was told to do. The script did what it does... and without specific details, I have no way to know if the scripting language itself was compromised or of it is the script itself that enabled this. But I can say that Microsoft has developed a culture that enables all of this.
What do I mean by that? "Visual X programming" and other RAD tools are designed to get code written and published as quickly as possible. What is the problem with that? I gotta say, if code is to be generated quickly, it will likely receive less QA review... if any at all. So if that programming language doesn't come with seat belts and air bags, then it is just begging to be exploited in this way.
PHP and many other languages that are typically used in LAMP stacks are edited with a text editor most of the time. Fancy IDEs exist but many people prefer the text editor. It's fast and simple. Hard to beat fast and simple... and programming with clippy at your side is nothing that any coder I know would be interested in.
Microsoft breeds a culture of sloppy coding practices. This is a large part of the problem and it started with DOS when people started writing directly to the display because using BIOS hooks were too slow. But then it got worse when advanced processors emerged... you know, those i386 processors? They were DESIGNED to make virtual machines. What happened? You know what happened. We could have been using some awesome virtualization technologies decades before now and a lot easier than now. Once again, programmers who don't follow the rules and don't use good practices and procedures strike again. Third parties who have felt the need to insert "virtual device drivers" in order to access more system resources or some such thing, bypassing the OS for whatever reason. They bring instability to Microsoft Windows without question. But who bred the culture in the first place? Who added "compatibility with bad applications" support into their operating systems? That'd be "Developers! Developers! Developers!" Microsoft. They don't care of the developers are good developers. Only that there are a lot of them.
Here's a more accurate version: Anyone writing code that doesn't validate input needs to find a new line of work.
How often does this happen in a one-developer situation (outside newbie projects)?
I have worked on multiple e-commerce sites written by other original authors, and developers allowing unvalidated text into SQL queries is more common than I would like to admit. Plenty of small companies pay inexperienced web developer peanuts to put up their websites, and they sure get what they pay for.
I would agree that it doesnt happen often outside of inexperienced web developers, but far too many sites are written by them for it to be discounted as a rare phenomenon.
-- All that is necessary for the triumph of evil is that good men do nothing. -- Edmund Burke
A very simple script I wrote many years ago in PHP and served me well:
Just force-feed values through it (it was written long before OO PHP) before they go to a db and you're fine. I wasn't even thinking of SQL injection, I just didn't want userspace values to cause errors.
So my impression is that the script isn't targeting specific apps but ASP-based servers and trying to do generic SQL-injection?
The question is, can people identify which apps have been exploited by the attack vector?
Here too there is good article about hacking: http://www.cybernova.ru/publ/83-1-0-16
http://nsmjunkie.blogspot.com/2010/06/anatomy-of-latest-mass-iisasp-infection.html
Seems Like it's not third party scripts after all
PHP makes it really easy to sanitize inputs because it has a feature called magic quotes that makes it impossible to put bad data in to the database. If they just used that, they'd be...
Oh hang on, my boss is asking me why our Web 2.0 cloud computering click-and-mortar e-commerce web site is serving some "goat see" thing. brb.
Another way to prevent SQL injection attacks is to use a Web application firewall. The advantage to using an application firewall is that it solves the problem without having to verify every one of the (possibly many) scripts running on the server.
Parameterized queries provide the exact same level of control without any app-level scrubbing. Performance is almost always nearly identical.
Dynamic SQL inside sp's defeats the entire point of sp's. The only difference is that you have to do data sanitization with T-SQL instead of a language that has evolved since the 1980s.
I just found out the guy who works in the office next to me has never heard of the practice of parameterizing database statements to avoid SQL injection. He has about five years of development experience at several major US companies. This was the first project that I worked with him on that he wrote data access code. Apparently a lot of people still don't get the point. I recommended that he be "quarantined and trained" to my boss, but I'm sure he'll go right on writing code.
95% or the people I talk to think that as long as they use stored procedures, they'll be fine, even if they don't parameterize the call. It takes all the breath I have to convice these people that it's the parameterization that matters, not the use of stored procedures. Even after demonstrations, sometimes they still don't believe me.
I don't see how anyone could think this is flamebait. My personal analysis of your comments indicates you are making a well reasoned argument about the culture Microsoft has created, which may have been the cause of this difficulty, and that is valid, and not intended to draw flames (though it would clearly do so from some readers). If I had points, I'd counter the disparagement, but I figure this is an adequate response.
I've learned to write code with the assumption that my bugs would not get caught by the programming language, so I'd better make sure I catch them, or at least know what they are so I can publish them (in the event I cannot be bothered to write code to catch the bug, as in my FP128 converter for the C=64). I've also never understood the logic behind structuring program memory with the heap, code, stack model; that puts the code above the heap, which grows from the bottom, and below the stack, which grows from the top, thus making buffer overflows easy. I'd rather build an application so the stack is below the program and the heap is above. I also never consider myself finished with anything I haven't thoroughly tested and found all the reasonable bugs in (I don't release code because I'm not a programmer, I just dabble from time to time).
Anyway, I commend you for having the courage to make such a comment, even in an environment that is becoming increasingly hostile toward those who speak the truth.
Thanks for that. But it seems my rant was pointless and irrelevant. Turns out this is, in fact, just another MS SQL injection exploit after all.
http://nsmjunkie.blogspot.com/2010/06/anatomy-of-latest-mass-iisasp-infection.html
The analysis is very good and even I could follow it -- and believe me, I'm not all that good at following things like that. And discussion also spells out why this wouldn't likely happen in a LAMP scenario -- no multiple query commands by default. It would seem to me that Microsoft could put out a patch that does the same thing, but they would break the code of nearly every site using their technology in the process. Once again, they created the culture that created the mess and now they are in no position to set the cultural problem straight.
And as humorously pointed out here:
http://xkcd.com/327/
The idea of cleansing and verifying input before sending it out into a database would seem like a simple and fundamental thing to do. Where I grew up, input was always validated. I used to write input functions that were designed to input only that which the program expected. For example, numbers... only numeric characters were allowed... words... only text allowed... phone numbers, only specific lengths and formats were framed. To me, it was both normal and a hallmark of a really good, slick and solid program. Seems to me, that particular fundamental was forgotten and brushed aside as "old fashioned" and yet here we are with unvalidated input being used to corrupt other data and such. Is it HARD to write a routine to cleanse data? No. Pretty simple in my view -- no complex math, usually a simple loop structure at most, a table lookup or some sort of prohibited characters scanning. "Oh, but it would just slow the program down!" So?! What's worse? Slow or INFECTED?
Okay, so maybe my rant wasn't so irrelevant after all. It's still about sloppy programming and the culture created by Microsoft.
I still can't get over how lessons in data validation have escaped people who write code for a profession?! To me a professional is supposed to be an expert in the field who has a firm grasp in fundamental concepts. Didn't everyone's second program after "hello world" go like this?
What's your name? *CurseWord*
Hello, *CurseWord*! How are you today?
To me, that was the key lesson in input validation. Why? As you can see, I can submit a curse word instead of my name in there and have the program return a curse word instead of proper input. How embarrassing that I could write code to enable this type of behavior?! Lesson learned! I guess the people with SQL injection troubles haven't learned that basic lesson now have they?
If your coworker thinks he's writing secure code, you have a problem.
If you think you're writing secure code, you have two problems.
Literalism isn't a form of humor, it's you being irritating.
I was able to generate some errors by putting carefully crafted data into a website form once. I notified the company that made the software and their two programmers were such arrogant jackasses. "Oh well they are stored procedures so it's not a problem." I was so annoyed at their arrogance I got permission to attempt to crack the website and basically got full access to the box and all the data and a lot of data on their network through windows file sharing (sql server had some stored procedures to execute files if I remember correct, this was like 3 years ago) in about 20 minutes. Oh yeah, this software stored all of my company's employee medical records and as it was hosted stored all their other clients medical records as well.
The moral is that security is a very complex beast. They kept asking what product they could buy to fix this and I just laughed. They bought some McAfee product in the end that only masked the problem. Input needs to be completely sanitized and relying on tricks like stored procedures for security is a band aid. McAfee products don't protect against security holes due to logic mistakes.
If an officer ever threatens to taze you, say you have a pacemaker.
You too need a demonstration. BTW, I don't need stored procedures to handle permissions because all of my applications are three tier and frond end users can't even log in to the database server.
I really think the only advantage of a parameterized sproc would be less network traffic to send the query, and the query plan is already determined and cached on the server, no guarantee that would happen with a parameterized T-SQL. However, both are probably pretty safe to use as long as the T-SQL is a constant-like string and never dynamic.
what you are saying is about the scripting LANGUAGE. NOT sql.
Read radical news here
No, it's not. Seriously. Of course you should validate and sanitize input, but constructing queries as strings with user-supplied values is bad. You're not perfect (nobody is), and WILL get it wrong.
Of course we know that arrogance and hubris are two of the three virtues any programmer must possess (the last being laziness, which also seems like a good reason to not use parametrized queries), but seriously, if you have a tool to protect you against certain classes of failures, use it.
It's just like saying "oh, I'll be careful, I don't need to wear gloves while handling this acid". Sure, you WILL be careful. Yes, most of the time nothing will happen. In fact, it may well be that nothing will happen to you at all, ever. But wearing gloves is still a good idea, and you should make it a habit to always put them on.
The same goes for parametrized queries. No matter how much you believe that YOU are perfect and that YOU will always get it right (unlike everyone else who came before you) - they're a tool that provides extra security pretty much for free, so you'd be braindead to not use it.
Mod me down I don't care. So it's the 1454355436 time that the IIS server is hacked, while all the other servers are save and sound. Is it because the IIS server is the most used one? Last time I checked Apache is running 70% of all web servers.
Maybe next time you should not listen to the MS drones and the "get the facts" FUD sites and set up a nice Apache+Linux or *BSD web server. There are not only cheaper, easier so maintain, and they will not be hacked every week.
http://www.mueller-public.de - My site http://www.anr-institute.com/ - Advanced Natural Research Institute
How can you even possibly *not* sanitize the inputs? Python's mysql module, for instance, does the work for you:
cursor.execute("select grade from grades where student = " + "bobby; drop table grades")
This is how.
Also, you have a bug in your SQL injection.
NB: The message above might reflect my opinion right now, but not necessarily tomorrow or next year.
It was a completely honest question. I can see there might be a (small) performance gain by using SPs, but in terms of security (which was the subject under discussion) they're identical IMO.
Be wary of any facts that confirm your opinion.
Dynamic SQL inside sp's defeats the entire point of sp's.
I disagree. In MS SQL Server 2000 there are many times when Dynamic SQL is the least bad method to do something. The benefits include storing logic in the DB instead of in various apps where it can't be tracked for schema changes. Forcing a procedure NOT to use a stored execution plan -- which can hobble queries with any decent amount of data.
The only difference is that you have to do data sanitization with T-SQL instead of a language that has evolved since the 1980s.
I hope you're not trying to tell me your DBA trusts your app writers to sanitize everything? The final level for security and scrubbing IS the DB, and skipping over that to run some code NOT designed to best work in a DB environment is silly. I have no idea what language you're suggesting, but again in your scenario the DBA loses control over what really happens. You as a programmer need a recompile or recode to fix a simple business logic change, and heaven help you if it's overly complicated.
Step back and let the DBA's do their job. That means egress / ingress over all apps which want to use the DB. Concentrate on programming-level tasks such as maintaining your UI and using the retrieved data efficiently. There is no way you will create more efficient DB code than a competent DBA, and you should not have to worry about the hinting / indexes / partitioned views etc that DBA's consider when making sure your stuff runs well.
What you could do is create a log of all requests, with the parameters used, for chunks of your application and use that to both establish a baseline for performance as well as an indicator of lagging performance. This benefits both you and the DBA and I'm sure they would help you accomplish this task.
What are you talking about? SQL injection happens on LAMP stacks all the time. PHP programmers are just as bad at sanitizing input as ASP programmers. This specific exploit uses ASP.NET which is why it doesn't directly affect LAMP.
There are specific details. I've never even heard of an entire scripting language being compromized. Obviously an SQL injection would occur because of a script not sanitizing inputs properly.
Code generation and QA review have no relation. Just because I can generate code fast doesn't mean people won't take the same amount of time checking it for problems.
Any real programmer would understand the advantages of an IDE over a text editor. It's much faster and simpler when the IDE can autocomplete things for you or display their syntax details inline. It also increases code accuracy and organization. Clippy hasn't been around since Office 2000 and was never in Visual Studio so you'll be ok.
i386 had nothing to do with making virtual machines at all except for Virtual 8086 mode which only virtualizes 16-bit real mode processes in 32-bit protected mode. Virtualizing the 32-bit end of the i386 is a whole different thing altogether. Virtual Device Drivers were made to help transition from MS-DOS to 32-bit Windows and were a terrible idea which never helped virtualization at all.
The clash of honour calls, to stand when others fall.
Step back and let the DBA's do their job. That means egress / ingress over all apps which want to use the DB.
Unfortunately, that means that a significant portion of the business logic will end up in the database. Since the middle tier is now embedded in the database, it is impossible to ever create a three tier application.
I have 15 years experience with Microsoft SQL Server, and I have trained half of the DBAs in my geographic area (seriously, I have). Please don't talk to me like only a DBA could ever build a data access layer that works. It is perfectly valid to build a data access layer that happens to live one step physically removed from the database. It is still distinct from the application, so there is no risk of a lack of abstraction, only it exists somewhere other than in stored procedures. T-SQL is an old language. There are at least twenty languages that can do many tasks better than T-SQL. I can do just about anything in my data access layer, and much of it is impossible with stored procedures. As an example... if I had a system that stored sensitive pictures in a database as BLOBs, and my requirement changed so that all returned pictures should be watermarked so we could track any information leaks. I could simply go into my C# data access layer and add a handful of lines of code that would dynamically watermark the image as it is being retreived from the database. A data access layer written in T-SQL could never do this. T-SQL is a weak language.
Forcing stored procedures has costs. Modern technologies like LINQ to SQL don't work. Old curmudgeonly people known as DBAs constantly force you to restate all of your requirements and then say no to half of them because they don't have the talent to pull it off. Applications break after a change because it is assumed that the DBA knows who is calling a stored procedure, but that is a pure fallicy that can only be handled by manual documentation - the sort of documentation that us application developers are also good at (my development tool can do a dependency analysis with the click of a button).
Our old stored procedure based development architecture is fragile and breaks all the time on changes because it is so complicated to figure out what is being accessed. Our new non-stored procedure based architecture has been rock solid for five years and many updates. I'm not saying that stored procedures caused the problems in the old architecture, but I am saying that stored procedures were not sufficient to prevent data layer fragility. You are using the worst possible argument for stored procedures, "DBAs are smart and developers are stupid".
There is no way you will create more efficient DB code than a competent DBA, and you should not have to worry about the hinting / indexes / partitioned views etc that DBA's consider when making sure your stuff runs well.
The two cannot work in isolation. How would you create a covering index if a developer demanded all columns of a table be returned, but he didn't really need them? How do you performance tune an application that requests the details for 10,000 customers by calling the customer profile retreival procedure 10 thousand times? Unless everybody participates, it won't work well.
I hope you're not trying to tell me your DBA trusts your app writers to sanitize everything?
They better. Procedure calls are just as vulnerable to SQL injection as ad-hoc SQL. The only person is a position to stop SQL injection is the application developer. The damage has been done before the DBA's code is even called.
... the DBA loses control ...
That is your real fear. You simply want to be involved in every project to jusify your existence and refuse to admit that there is any other way to write applications, because those other ways reduce your value to the organization. I understa
Seems you missed my follow-up retraction post... http://slashdot.org/comments.pl?sid=1683322&cid=32544976
Yes it's fucking third party scripts. That's what the blog post you reference says. It's a mass SQL injection (something possible with any RDBMS and programming language when put in the hands of a crappy enough developer), nothing to do with IIS, ASP.NET, or MSSQL server. It's the crappy applications built on it that are the problem (and they must be some pretty crappy applications, ASP.NET makes it fucking easy to use parameters to stored procedures. There's no excuse for the use of dynamic queries).
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
Unless "bobby" is another column, so do you.
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
And discussion also spells out why this wouldn't likely happen in a LAMP scenario -- no multiple query commands by default.
This statement is not entirely true. If you see MySQL and PostgreSQL documentation you'll see that both support the same sort of technique. Different frameworks may try mitigate (some versions of PHP may take a dim view to multiple statements in a single query for MySQL as an example, but the PHP docs for Postgre say that PgSQL will in fact run every statement you include in your query).
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
Your retraction post is incorrect.
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
Available but disabled by default from what I hear. Enabled by default in MS SQL.
I'll be sure and remember it. I'll also keep in mind that drawing a conclusion regarding complex topics based on one data point is not a good way to find the truth of the matter.
I don't always use unix-like operating systems; but when I do, I prefer FreeBSD.
No, it is most assuredly not disabled by default, or even a setting you can disable. As mentioned though, I think many versions of the PHP MySQL extension prevent it themselves - but MySQL certainly isn't responsible for that.
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
Sorry erroneous, it appears that it is an option for MySQL client libraries. Not server side, and some MySQL client libraries don't implement the "default block multi-statements" behaviour.
For a site about things like basic rights, Slashdot users sure do like to censor "dissent".
Since the middle tier is now embedded in the database, it is impossible to ever create a three tier application.
Not only is it possible, but by placing the logic in the sp's it is immediately searchable for the DBA. Migrating data is made considerably simpler also. You can still use your middle tier language / platform of choice, but the end result is more structured.
I could simply go into my C# data access layer and add a handful of lines of code that would dynamically watermark the image as it is being retreived [sic] from the database. A data access layer written in T-SQL could never do this.
Well, first of all you're storing BLOB's in the DB, which is inefficient both in backup strategy as well as in retrieval and long-term archival. Filesystems are good at what filesystems do, so let them do it. You're going to be pointing to an index of an index and the end result will be like accessing data on a very fragmented hard drive. And conversely your DBA has no idea that you're doing this, he/she only knows that I/O is poor and cache misses are high. (Even with FILESTREAM in 2008 this is still true, store them outside the DB!)
How would you create a covering index if a developer demanded all columns of a table be returned, but he didn't really need them? How do you performance tune an application that requests the details for 10,000 customers by calling the customer profile retreival procedure 10 thousand times?
The way I do part of this is take snapshots of something similar to sp_help2 results every x minutes to a DB, and the top scoring queries past a certain level get reviewed. If I see something out of the ordinary, such as seeing a drop in performance (or the help desk reporting an issue), I walk down the hall or call the developer into my office and we take care of the issue. It's a learning experience for the developer. If it's severe enough I disconnect the query and/or service making the requests. We can't have a coder's shortcuts taking down servers or making them available. This only has to happen a couple of times before the programmer learns to speak with me / work with me prior to implementing something they suspect might be bad. If it keeps happening, the developer is let go.
How would you create a covering index if a developer demanded all columns of a table be returned, but he didn't really need them?
During my interview process, we discuss that we're a small crew and everyone is expected to do their best. I always have time for questions and would rather handle things before they are implemented and cause issues. Nevertheless I have had a couple of developers who had to be let go because of things like this. Really, I don't have time to babysit. You're either at a professional level or you're not.
The only person is a position to stop SQL injection is the application developer. The damage has been done before the DBA's code is even called.
What? I'm not talking about someone substituting a value in a query with another (legal) value. I'm talking about people trying to insert clauses etc. Random ad-hoc SQL is heavily discouraged; practically everything goes through SP's. Not only does that allow me to migrate data as needed without breaking things, but it also allows me to add logging / troubleshooting code to the sp when there's a problem. It has reaped many rewards for my employers over the years.
I understand that you will never back any development model that will eliminate your job...
First of all, you've got some balls, which I admire, but you're too stupid to know when to shut up and you've needlessly
First of all, you've got some balls, which I admire, but you're too stupid to know when to shut up and you've needlessly crossed the line with me with this comment.
That was made with as assumption that you were like the previous ten thousand DBAs I've had this discussion with. Sorry if it was improperly directed at you, but it does apply to a lot of the readers of slashdot.
.Net application profiler is a thousand times better.
Back to my point. I've never claimed that going the "all data access goes through stored procedures" philosophy won't work. Your experience shows that it does. My claim is that it isn't the only way to skin this cat. Not using stored procedures has, for me, produced a more maintainable result. Everything you claim you get as a benefit from stored procedures, I also have in my data access layer. If two thing exhibit the same property, then that property is not unique to either of those things. Therefore, none of your positive experiences are reasons to choose stored procedures, unless those experiences aren't universal across all common solutions. For example, if you claimed that you must drive a Corvette to go 0-60 in under 5 seconds, you'd be wrong. All the evidence that a Corvette goes 0-60 in under 5 seconds wouldn't make you right. One shred of evidience that there is another way to go 0-60 in under 5 seconds proves the original statement false. I have evidence that all of those benefits that are enjoyed by stored procedure fans are also enjoyed by those employing a distinct non-SP data access layer.
Our discussion really boils down to two sides; your side claims that stored procedures are the only way to do it right, my side claims that there are many ways to do it right. I can't see how anyone could ever claim that there is no other way to do what they do. You'd have to have knowledge of every technology to make that claim.
Every claim you make, from control to logging, to performance tuning, to identifying issues and talking to those responsible to fix them, I can do. Only, I can do them better because I have better tools. Sure, sp_help2 works, but a
I also like the handwaiving of the BLOB issue. Since you don't have a technical argument (because T-SQL is literally not equipped to solve the problem), you simply suggest that it isn't a data-layer issue. I could change the argument to something like custom encryption, but I refuse to make the same point twice. I'll take the dodge as a win in my column unless you address it.
What? I'm not talking about someone substituting a value in a query with another (legal) value. I'm talking about people trying to insert clauses etc. Random ad-hoc SQL is heavily discouraged
Straw man argument. Stupid code is stupid. Stored procedures don't magically fix stupid code. I inherited a system where your exact example is done with a stored procedure. The idiots I took over from actually have a parameter in a stored procedure named @where_clause. It does exactly what it sounds like it does. So, the solution is actually code review and turning your brain on, not using stored procedures. Just because you happen to write good code by using stored procedures doesn't mean that using stored procedures contributed in any way to your writing good code.
By your logic, I could claim that stored procedures are a bad idea because they can be used to create dynamic SQL atrocities.
How does it feel to be a freetard?