Slashdot Mirror
How Prevalent Are SQL Injection Vulnerabilities?
Krishna Dagli writes to tell us of an investigation, by Michael Sutton, attempting to get an estimate of how widespread SQL-injection vulnerabilities are among Web sites. Sutton made clever use of the Google API to turn up candidate vulnerable sites. You might quibble with his methodology (some posters on the blog site do), but he found that around 11% of sites are potentially vulnerable to SQL injection attacks. He believes the causes for this somewhat alarming situation include development texts that teach programmers insecure SQL syntax, and point-and-click tools that allow the untrained to put up database-backed sites.
; SELECT * FROM users WHERE uid=1;
destroys thousands of lives a year. Sure, it starts small - a SELECT there, a few INSERTS on the weekend. Eventually, though, you're using stored procedures and trying to score triggers in the middle of the night.
Just say no, kids.
This is a possibility that was obvious back when I was developing web applications as far back as 1996 using CGI. The approach in TFA was a similar approach we used "back when" to demonstrate the need for (a) not using GET, (b) turning off verbose error reporting, (c) controlling *how* queries were made (e.g. architecture of the app and DB I/O), and (d) storing sensitive data encrypted. The sad part is that it is *still* a problem. I guess it underscores the need for a decent architect as opposed to letting whiz-bang do-it-yourselfers start coding without design, and the need for security analysis, et. Al. Just my 2 cents.
This may be heresy, but the SQL injection problem makes one wonder if it might be a better idea to make use of flat files for data storage more often. That might be overkill, just to avoid SQL injection issues, but for simple systems intended to allow set up and maintenance by novices, this might reduce the number of vulnerable sites considerably.
To the making of books there is no end, so let's get started
I take one every day - It's GOOD for you
The simple solution is to use parameterized queries. I don't know why more books don't know why more books don't push this methodology, as it makes you program faster, easier to read, and also makes you invulnerable to SQL injection attacks.
Anthropic principle: We see the universe the way it is because if it were different we would not be here to see it.
This certainly seems to me to be a problem that needs to be addressed at the html standard level, not by insulting beginning programmers. If the obvious fix is to exclude special characters from password fields, then why allow them by default to begin with? Take away this opportunity, and provide a work-around for knowledgable folks who need to add it back in, and bada-bing, 11% turns into .011% or less. There are a lot of new programmers (or whatever we're calling people who make websites these days), who are not naturally paranoid and sensitive to the exploitation of their code. They shouldn't need to be. The paranoid people should be making the standard, not writing the SQL scripts.
"Don't you know you're going to shock the monkey?"- Peter Gabriel
our magazine site got hit by one a while ago. i was called around 2am and told "we've been hacked." i'd been here for about 5 months. it was not my code that was the vulnerability, it was the last guy's. the "hacker" changed every headline to a javascript block that redirected to a simple text message saying something like "you've been hacked."
it was a problem with the ID value of the query string in the article reader page's URL. i was aware that manipulating the URL could cause it to break, but it hadnt occured to me that it could be used maliciously. i noticed the problem very early (probably a few weeks after i started) but i didnt feel comfortable enough to change the code at that point, since it was working fine. i knew i could write better code, but i didnt feel like i had gained the priveledge yet to edit the 2nd most important page on the site.
The fact that tools can be used to put up insecure sites is not exactly a failing of the tool. The tool will have had a spec (even an informal spec) - which may have been "put sites up fast and let users sort out the security".
It's only a failure of the tool, or the developer of the tool, if the tool is marked as being a one step solution. Of course a lot are, there is no shortage of snake oil salesmen, and in that case they take 100% of the blame. However most rapid deployment tools contain a clear disclaimer of what it does or doesnt do and a guide to helping you with the rest of the steps.
When those things exist the fault is with the user and not with the tool - elitism aside. It's like using a hammer to kill a man (despite the do not kill people with this hammer label and 100 page guide to not killing people with hammers - now with pictures!) and blaming people killed with hammers on the hammer designer.
Think of the Children; Sleep with your Sister
Stupid application construction isn't a good reason to make the design of the app even stupider.
In the old days, everyone used flat files, because that's what there was. Then someone (several someones, most notably Codd and some others at IBM) realized that breaking the flat data into sets of discrete data that related to each other reduced redundancy and allowed for an overall better quality of data. And it wasn't app specific.
The answer to SQL injection is to test apps more completely (including tests for this kind of attack), to provide extra checks at the database level (for integrity issues, perhaps in the form of constraints, etc. dependent on the scale and structure of the database), and to develop tools/libraries for data access that make this kind of thing hard to do accidently.
How do a) and b) help?
Which "decent architect" designs a system where verbose error logs are sent to untrusted public users.
And which "decent architect" writes web apps where GET would inherently causes security problems.
In fact, post causes more problems if the target page doesn't issue a redirect, because then the form could be reposted. This shouldn't be a security problem of course (unless it's a login page), since duplicate posts should be handled gracefully. However with most browsers, users would be annoyed with "click ok to repost" prompts.
Of course one shouldn't use GET when confidential parameters are being sent (since it could appear in url histories etc), but GET allows users to bookmark a page or query, and in many cases that's a plus.
There should be some kind of government run website somewhere.
You would answer questions and it would give you license keys to software that you were qualified to use. For example, I might tick:
Engineer (check)
Artist ( )
Manager (check)
Linux (Check)
Mac ( )
Windows ( )
And it would issue keys for website point and click installation software, Vi, apache and Latex - but deny me keys to powerpoint thereby saving the lives of people who might otherwise have to gnaw off their own leg to survive my 8 hour presentation on optimising synergisyms in a web 3.0 environment by sub molecular interactions.
Beep beep.
Hello? Has the Net gotten so crime-ridden that instead of blaming criminals for doing illegal things, that we're now blaming developers? I mean, sure developers need to do all they can, but at what point do we, as a society say, "Hey, let's prosecute some of these assholes that are making life online a royal pain in the ass?" It's pretty absurd that today, you can still do whatever you'd like online (hack sites, networks, steal information), and there are really no repurcussions. We see what, one person getting jail time every 6 months? That's fucking insane. It's time for our government's law enforcement to step up to the plate, learn what the fuck a mouse is, and start prosecuting some of these people. Email is already useless. The web is getting there pretty quickly. SQL injection, firewalls, blah, blah blah.
If I have somebody who is constantly trying to break into my house, to the extent that I have to pay a security professional just to keep them bastards out, I'd be down at the police station talking to the police chief, angry as hell. As is, people have come to accept that there's almost more crime than legitimate traffic on the Net these days. That's insane. Something has to be done about this mess before the entire Net is just useless.
This 11% was determine by a weak testing mechanism. For every site that baltently spews sql errors to the user there are two that silently return a generic sanitized error, and another two that return no error at all. It would produce more results if you take it a step further and ask yes no questions, such as:
?id=99999' OR '10
and see if the page returns the results of id=10 as expected. It's also common for people to use weak regexp (regexp should NEVER be used to protect against sql injection, see mysql_real_escape_string) and miss some characters:
?id=99999)
or fail to sanitize non us language encoding. Also, get variables are often the most protected. It is much more common to find sql injection in <input type=hidden variables, or in cookie data. The number 11% is extremely low. I'd guess more like 80%.
From the article:
...
* Many development texts actually teach programmers insecure SQL syntax.
* Many sites are exposed to SQL injection attacks but don't know it.
I agree completely! I've seen the texts, I've seen the hordes of VB+SQL programmers that learned from said texts moving to the web porting the same "vices" to the new platform.
And I've seen the "oh-sh*t" face on a couple of developers after demonstrating to them that their software is vulnerable to SQL Injection. In both cases the vulnerabilities exposed the customers to the posibility of serious financial damage.
So far, the stupidest work arounds i've seen have been:
Developer: It's ok, I'll switch to post instead of get so the user can't forge the request.
Developer: It's ok, I'll write a method that removes sinlge quotes for every string i get from the user.
Developer: It's ok, I'll write some java script that will validate user input.
Writing secure software is never easy.
moi
I mean seriously.
If anything, I'd question how FEW sites they claim are vulnerable to SQL injection. It's an insidious problem that just creeps up on you anytime you don't think about it sufficiently (as when writing something quickly, on a deadline... not that this ever happens!). I know that at my workplace we fell victim at one point to a SQL injection attack on one of our (many) custom PHP scripts. We eventually found out how it worked through the web logs and were able to fix it, but honestly even after we did our best to clean things up... I'm dead certain that there are still probably hundreds of places that we're still vulnerable. This is due to a number things including the sheer volume of PHP code in use, the fact that the code has been written at various points in time over a period of six years or so, and the fact that this code has been written by at least twenty different people. It's like trying to plug holes in a dam.
I was a TA for an upper-level web programming course, and the textbook used was a great example of this - some 500something page Deitel textbook whose index, under 'Security' had all of two pages listed, one in the foreward. Their login code pretty much an example of "how SQL injection happens", not to mention having all the logins available in a drop-down menu in what was probably a vain attempt to curb the risk (i.e. someone had to modify the page locally and submit or generate their own request instead of just typing the offending SQL in the text field, and the password wasn't in the query). No mention anywhere of the risks.
I remember taking up most of that tutorial explaining what SQL injection attacks were and why this text was so badly wrong. Probably a better lesson for them anyway.
For a blog with 15,000 entries and 5 MB of data, why does it matter? It fits in memory either way, and doing stupid mechanical queries is going to be 'fast enough', and not terribly complicated in the code. Sure, if you know SQL it's dandy, but it smacks of overengineering for a blog app.
Nerd rage is the funniest rage.
Why are the point and click or turn key solutions so vulnerable to SQL injection in the first place? I had a friend with a PHPBB site that got shot to all hell when some cracker came along and defaced it. Why wasn't it secure out of the box? Second of all, why is it that every website has to worry so much about security. I know about databases but I don't know the first thing about preventing an SQL injection attack and why should I have to. There is nothing sensative on my sites. Let me throw out this analogy.
Let's say I own a house and around Christmas time I put out an inflatable snow man. Then some vandals come along and pop it. Are you going to walk up to me while I'm sulking over my snow man and say "Don't you know you have to wrap your snow man in kevlar to prevent vandalism and then put up an electified fence with constantine wire on it."? I would give you the strangest look if you did. Then I'd probably say something pertaining to the fact that the police should catch these bastards and presecute them.
So why is it with technology that no emphasis is put on catching vandals and bringing them to justice and a ton of emphasis is put on protecting your site from attack?
For a blog with 15,000 entries and 5MB of data, use Blogger or Typepad.
And, frankly, I don't think learning how to use the tools properly and how to test one's code is "overengineering." I think of it as competently programming the application. But I'm old fashioned.
I've worked in web development a while and I find a SQL injection vulnerability in about 90% of the sites I've seen.
It is extremely common to have people just cut and paste the bare-bones tutorial code they find on the web and reuse that same pattern on every page in the site rather than centralizing it in a wrapper. So not only is the string not being cleaned, but it's also a huge pain to fix.
Absolutely. Here are some rules to defend at multiple layers against SQL injections (using SQL Server as an example; YMMV on other DB servers):
- Run your web application as a user that connects to the database server and has rights only to SPs and views on the database; this works because the SPs and views have full access to database data, but the user can't access the data except through those pre-defined means
- Encapsulate calls to those SPs and views inside carefully constructed functions/objects/etc. and force all developers to communicate with the DB through those functions/objects
- Inside the functions/objects, use parametrized queries for calling all SPs and SELECTs on views in order to avoid any potential for evil
This way, if someone messes something up at one level, the other two levels are hopefully there to protect against any potential vulnerability. Also remember to, if possible, never store DB authentication information in plain text.
I claim first use of "Error No. 0B" - or "No. 0B error." It'll be the new ID 10T!
Going through my bachelors, we all learned the same things, yet there were always people that had unsecure code. It didn't come down to "just not getting it". It was more that they were lazy and wanted it done fast.
Your post made me curious about how Google does it via their Search by Number feature. For example, if you search Google for "usps " plus your tracking number (or just the number), the results will be preceded by a link to "Track USPS package..." I suppose your widget could use Google's custom redirect script, but why is the functionality restricted to Google?
Sounds like even more incentive to use a good object to relational framework like Toplink or Hibernate.
SQL like a pig, boy!
The point is that anyone can talk directly to the server and send whatever values they feel like sending. This means that the solution cannot be limited to the web browser. If the problem were restricted to password fields (it's not), the http server itself still wouldn't know which fields are password fields. To do that you'd have to have some sort of framework that was familliar with not only the names of the fields expected but also with the ways in which those fields will be used. Blocking special characters in all fields is impractical. Sometimes you need to transmit those characters. The http query that will process this post contains several risky characters.
The solution is to have your application never allow tainted data to be used in a way that can change the meaning of its intended interaction with other code. Don't use user input direcly when: building pathnames, executing system commands, building code to be interpreted or compiled, or building DB querries. This is not an exhaustive list.
Always treat all input as malicious and process before passing it along to other code in order to eliminate the risk of harm. When dealing with SQL the answer is to used prepared statements since the parameters are transmitted as data and can't possibly alter the meaning of the query. With HTML output, make sure that the code can't contain tags that would alter your site -- particularly <script> tags!
I've written some boneheaded code. I've since learned a lot about security. Every developer does, or they won't be developing for long.
The trick to exploiting SQL injection is being able to figure out the right sequence of input characters needed to gracefully terminate the intended SQL command, while also being able to craft a subsequent SQL query that does what you intend. Alternatively, you may want to modify the intended command. For example, "SELECT orderstatus FROM table where orderid = $FORM{'orderid'}" expects the orderid form field to be some value that's in the table somewhere. If instead you enter "1 OR 1=1" as your input, you'd get every row back since 1=1 for all rows. Using this example, you could also try to append a new query, by entering something like "1; drop database;". Many times an attacker will need to find various escape characters, quotes, etc. to get their input crafted properly to exploit the app.
SQL errors in web apps are what web crackers like to see for the same reason that exploit authors like to see segfaults where EIP=0x41414141.. it means that they were able to get their input down to the execution point in a program that got past the boilerplate protections that the app had in place (if any).
I am pretty new at PHP development, but not new to programming. I had to learn SQL on-the-spot and I'm aware of SQL injection attacks and the risks they pose.
We have implemented a DB wrapper that "escapes" user input for things like ' that would break out of the input. I also check the length on the server side to make sure they're not trying to overflow any of the variables. By the looks of many of these posts, that isn't an "acceptable" amount of protection and that procedures are a must.. Anyone care to inform me or refer me to a good website?
I went to eat some animal crackers and the box said, "Do not eat if seal is broken." I opened the box and sure enough..
If you search for "mdb" you can download the entire database without too much trouble.
I recently came across a commercial site where you could substitute, for instance, "(select first_name from users where id=1)" into the page url and a nice error screen came up telling you that it couldn't convert "George" into an Integer.
It's not the SQL Injection per se that is the biggest problem, but the nice error messages you get back giving you, more or less, a SQL command line interface. Errors should be detected and redirected to a sanitized page, or if you can't be bothered, an unceremonious crash.
I notified the owners of that site by the way.
Once I was a four stone apology. Now I am two separate gorillas.
I've written about this before. Basically, SQL injection vulnerabilities would completely disappear if better APIs were used. The problem is that queries are composed as strings, which have no intrinsic structure. The programmer creates structure by inserting certain characters (in particular, single quotes) in the string. However, the exact same mechanism is used to add user-supplied data to the queries. Unless the programmer is very careful, this allows the user to affect the structure (and thus the effect) of the query. An example is in the essay I linked to.
Please correct me if I got my facts wrong.
More and more, sql engines are becoming, themselves, resistent to sql injection attacks. The simple '; select * from user attacks simply no longer work in oracle, mysql, and possibly others. These databases are configured to accept only one sql query at a time, thus ignore the end-string ; other sql attacks.
What other attacks should we worry about?
Repeat after me kids: "GET and POST are equally tamperable and equally (in)secure. The only thing they are not, is equally logged".
Blame MySQL.
MySQL is not a database. It does not support Transactions. It doesn't use normal syntax. Why? Because it's easy. So a programmer who has better things to do than learning some arcane syntax can just get the job done and move on. Good? No!
It is because of this laziness that such attacks occur by someone who did take the time to learn the syntax. If people want a quick and dirty solution, so be it, but don't pretend to be a database and open all sorts or vulnerabilities.
The ease of SQL is made for people who take the time to learn it, and naturally code it securely. For those who don't have the time, just don't use SQL.
So, i blame MySQL (and its ilk) for this. And i can only laugh at the sites that get attacked because of their own laziness.
Have you read my journal today?
Comment removed based on user account deletion
But anyway, web developers should not be using SQL any more, except in some very rare cases. We now have things like EJB3 and Hibernate which remove 99% of the need for any SQL within an application. If you use EJB3 for everything, the only way there could be an injection is if there is a bug within EJB3 itself. A whole lot of people have scoured that code, and it is carefully written, and any bug will affect a lot of sites so any bug will get a lot of attention quickly.
Using SQL directly these days is about as good as using C directly in your web application. It is no longer needed and should no longer be used for such tasks.
SELECTing `userID` when it's undefined really isn't a problem. Had you WHERE'd correctly (`userID` again, not just `user`), though, it'd have been a perfect demonstration of one of the most fundamental injection attacks.
Remember kids, NEVER trust user input. Escape things until the key is broken, then make the computer do it for you automatically.
How are sites slashdotted when nobody reads TFAs?
You can do far, far better than that in Lisp. A SQL query is essentially a list of column expressions (the SELECT), a list of table expressions (the FROM), and a predicate over the tables/aliases named in the FROM (the WHERE). It's pretty straightforward to represent these as embedded language in Lisp that constructs three-part objects that represent queries with symbolic expressions, and then write a little interpreter to generate SQL statements from the objects in question.
Once you have this, you get the really, really big win: you can write an operation to merge multiple query objects into one, that works as follows:
This approach allows you to write code that generates really complex queries in a piecemeal fashion; you can generate separate query objects for individual parts of your query in separate parts of the code, and then merge all the bits at the end into the finished query object, which you translate into a SQL string just before execution. Your embedded language can also take care of escaping automatically for you. You never deal with queries as strings at all.
And this is of course an approach that is limited by the need to support SQL as your target query language; if you didn't have that, you could do a much better query language embedded within Lisp to start with.
Are you adequate?
I just can't imagine that many web developers being so careful. Popular sites are relatively secure, and open source code like online forums are secure, but the security of write-once code you find on average sites is just horrible. Just search google for things like 'allinurl: "isadmin=0"' and you'll know what I mean.
MySQL doesn't support transactions? Boy, they really fooled the shit out of me! I must have been using something else in the last few applications I wrote using MySQL that allowed me to rollback my non-transactions. It must also not support prepared statements because I sure haven't been using those to eliminate SQL injections, either.
The people that modded the parent post Insightful are idiots. BLAM THIS BLATANTLY FALSE TRASH, PLEASE!
Magic quotes! No more problem\'s!
henry -- the human evolution news relay
If you ever find yourself writing SQL queries without prepared statements and bind variables, erase the code and rewrite it with prepared statements and bind variables.
If you still find yourself writing SQL queries without prepared statements and bind variables, slap yourself twice across the face--once for each cheek--and then erase the code and rewrite it with prepared statements and bind variables.
If you still find yourself writing SQL queries without prepared statements and bind variables, obtain a hammer and smash all of the fingers on one hand until they are no longer usable. Find a friend to smash the fingers on your other hand until they are no longer usable. Then, you can write no more lousy, insecure code.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
The fact is, code vulnerable to SQL-injection is database platform-independent. The vulnerability is at a higher level. Do you really think forcing these lazy people to use a "harder" database that takes more time to administer and learn will make them write secure front-end code? Hardly. It'll probably make the front-end code even more rushed.
You want no more SQL-injection? Use prepared statements and bind variables. If you roll your own input-escaping code, you'll foul it up. Doesn't matter what database you're using on the backend. If you foul it up, your Oracle database will be just as vulnerable as your MySQL database.
But please don't let that stop you from mindlessly ranting about MySQL.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
i'm starting to learn this SQL thingy.
...
it seems to be a problem between how certain "extentions/modules"
of basic HTML servers delivers the "data" to the real database program (interfaces
with it).
not everybody uses a HTML-form to access the SQL/databse server. i think
many people in a organisation just use SQL/database-client program to access
the database(server).
i'm learning to put up a web-form to a sql-server via apache and have been
reading about this SQL code injection.
i think i will just do it like a "normal" SQL-client program does it
(reverse coding da sql-client program to do the same bad" like
webserver-module possible?)
for legit user maybe better build a TCP/IP tunnel via which a SQL-client
can access the SQL-server then a web-form?
In terms of usability, I agree, but in terms of safety, I cannot. For any programmer, it is trivial to create requests using either GET or POST (or GET and POST). The only real difference to my mind is in terms of whether POST requests are likely to be looged by Apache.