Slashdot Mirror
The History of SQL Injection, the Hack That Will Never Go Away (vice.com)
An anonymous reader writes with this history of SQL injection attacks. From the Motherboard article: "SQL injection (SQLi) is where hackers typically enter malicious commands into forms on a website to make it churn out juicy bits of data. It's been used to steal the personal details of World Health Organization employees, grab data from the Wall Street Journal, and hit the sites of US federal agencies. 'It's the most easy way to hack,' the pseudonymous hacker w0rm, who was responsible for the Wall Street Journal hack, told Motherboard. The attack took only a 'few hours.' But, for all its simplicity, as well as its effectiveness at siphoning the digital innards of corporations and governments alike, SQLi is relatively easy to defend against. So why, in 2015, is SQLi still leading to some of the biggest breaches around?"
Hackers use the integrated face system to inject SQL.
HIV is easy to defend against. Relatively so, also, are things like malaria. Yet they still manage to kill millions. Why, in 2015? Also, is that more or less of a concern than SQL injection attacks?
Each year brings a fresh crop of computer science graduates into the industry, barely any of them having a clue about attacks like this. Many of them will make these mistakes and learn about defending against them the hard way.
Maybe a few schools teach about this now. Maybe a few companies will pair senior devs with new devs to transfer this knowledge on the job. Even so, there will be enough new programmers who don't know this, and enough companies who eschew senior talent as a cost-savings measure, that this vulnerability will continue to rear its ugly head.
It is usually accomplished by accepting the lowest bidder.
Because typical PHP tutorials still teach old, broken ways of doing things and this shows no signs of abating. Go ahead and search the web for things like php mysql tutorial. The top hits are crap like this, written by incompetent developers who don't know what they are doing. PHP developers learn from crap like that, then they go on to write their own tutorials that are the same or worse.
And before you start, yes, this is something where PHP is stand-out bad. Go ahead and try the same searches with other languages. There is a vast difference in quality of learning materials. I mean, PHP had XSS vulnerabilities in its official tutorials until relatively recently. Newbies don't stand a chance in those circumstances.
Bogtha Bogtha Bogtha
Because companies keep hiring the lowest bidder.
...SQLi is relatively easy to defend against.
Relatively? It's trivial to defend against (spoiler: use prepared statements), and anyone creating software that has even the potential for SQL injection is an incompetent moron.
Hiring a programmer who doesn't know how to eliminate SQL injection is like hiring a surgeon who doesn't know how to use a scalpel, or a bridge builder who doesn't understand weight distribution. It's the first thing that a programmer should learn when learning to write database aware software.
You are all cows. Cows say moo. MOOOO! MOOOO! Moo cows MOOOO! Moo say the cows. YOU BOBBY TABLES COWS!!
Relevant: Exploits of a Mom
PHP and CGI make it too easy to write SQL-injection susceptible code.
Back in the day there was no such thing as malicious hackers. Just lads who would put a few funny comments into your db and leave you alone. Now we live in a more cut-throat interworld where everyone's out for profit or just to cause maximum damage. I think we should go back to the old days. We need to re-invent the way we communicate to get away from all the sh1tbags, packet ham radio might be the way to go.
You are absolutely right. I see this all the time as the hiring manager in a web shop. I always present candidates with this question:
1. Find and fix the potential SQL injection vulnerability: // .. /*@var \PDO */ //..
protected $dbh;
public function getOption($name)
$sql = "SELECT val FROM options WHERE name = {$name}";
$stmt = $this->dbh->prepare($sql);
$stmt->execute();
return $stmt->fetchColumn();
}
For those who don't know PHP, the answer is:
$sql = "SELECT val FROM options WHERE name = ?";
$stmt = $this->dbh->prepare($sql);
$stmt->execute(array($name));
Almost no candidates out of school even know what I'm asking them to do. About half of people with experience get it right; a quarter of them understand the question and get it wrong, and a quarter don't understand the question. I find that it doesn't matter how many years of experience they have, about a quarter of programmers just don't understand what SQL injection is.
I just don't get it. I've spent more than a decade programming in PHP, the language that really made SQL injection a thing because it lacked prepared statements for a long, long time and even then a lot of the input escaping functions were broken. Over those years, I've picked up a lot of bad habits; some were dictated by the shortcomings of PHP4 ("dependency injection? what's that?"), others are a side effect of spending all of my time cranking out single purpose scripts that had to work yesterday ("Ctrl+C; ctrl+V").
Nevertheless, it still blows my my mind when I encounter people in this day and age who aren't using prepared statements. Concatenating SQL is just so... messy. Seriously, it takes two minutes to write a nice, clean, understandable SQL statement as a string, and at most a three line loop to bind the values. If your are concatenating it together, you have a mess of loops and conditions (comma here?) and strings and array manipulations... It is so much more work.
Yet I still hire jokers who can't do it because I need bodies to fill seats.
The Hiawatha webserver can block SQL injection attacks. I like to hear what you think of it.
It doesn't have to be like this. All we need to do is make sure we keep talking.
So long as queries are composed of strings, there will be injection attacks. There are tools to perform escaping, but they're optional: " 'SELECT * FROM users WHERE id=' + userid" is always going to be something the programmer can choose to do. The only solution is to remove that option. Limit database interaction to framework ORMs or query building tools (e.g. Querydsl) and the programmer can no longer choose dangerous options.
If people used perl and DBI with properly prepared statements, instead of [censored] php.
Religion is what happens when nature strikes and groupthink goes wrong.
It was (and still is) a poor relational language to use with databases.
It MANDATES creating SQL queries on the fly...
Only when you sidestep SQL and use parameterized queries (and even then, they get screwed up) are even close to being better.
As long as queries have to be created by concatenating strings together to create a "valid" SQL syntax - you WILL have SQL injection attacks.
There is no way around it.
The syntax with SQL allows for breaks because of the usage of quotes. This danger can be mitigated by the use of proc's written to process each transaction. Attempting to write procs for each call is time consuming, and the syntax is very spaghetti making mistakes an issue.
Tying function into a string was a terrible idea, sql is a bad language, and it's a slow database.
Please end the scourge of these terrible databases.
Think about an object database eh? I'll give you an example to mull over.
var user={username:'bob',messages:[{username:'al',text:'hi bob!'}],contacts:[]}
As an object when I search in an object database I get the user and all of the fields such as contacts and messages in 1 search.
If this was an SQL database I would have had to search through all users to find this user, then search all messages to find messages for just this user, then search all contacts for contacts for just this user, that's 3 searches more than I had to do with having that directly logically connected data nested in the first place.
PHP's supported prepared statements for at least a decade. I bet you still test your websites in IE6.
'It's the most easy way to hack,'
Also know as 'easiest'
No, I didn't write "a lack of people who write code". I exactly mean what I wrote: A lack of programmers.
What we have today is a load of people who learned programming somehow, kinda-sorta, but without understanding just what they're doing. Now add how most of them gains information about how to do things. By looking it up on the internet. And taking the first solution that looks like it works.
Of course it's easier to simply concat strings than using prepared statements and parameters. It is simply more readily understandable and less convoluted for people who have little knowledge and less time to gain it. And they don't know anything about security and why this could possibly be a security problem.
These people are cheaper than people who know what they're doing. So much cheaper even that the additional time they need to get anything done is easily compensated. And whether it is a security problem is usually only found out after a security breach happens because, well, whoever hired them has even LESS knowledge about security.
And since every year a new batch of people comes out of schools that kinda-sorta learned how to kinda-sorta do queries, this problem will mean job security for me 'til retirement.
We used to have a Bill of Rights. Now, with the rights gone, all we have left is the bill.
Absolutely PHP was originally for for non-programmers. It was a CMS written in Perl, for people who couldn't use the Perl templating systems directly. That was a long time ago, of course.
It was abused as a general programming language, often by people who didn't know about file permissions and couldn't be bothered to learn how to chmod 755. That wouldn't have been a huge problem if they weren't putting the scripts on web sites, for everyone to attack. That was a big problem for several years - non-programmers who didn't want to learn an actual programming language wrote PHP scripts and PUT THEM ON THE INTERNET. It wouldn't really have been a big deal if they wrote scripts for their own desktop or for their local intranet.
Now that PHP has been used as a general-purpose web programming language for several years, it has been significantly updated to better fit that role. Current PHP is much, much better than it was a few years ago. It's still relatively easy, though, so it -can- be used by people who are clueless. But now it's also used, correctly, by people who are actually competent.
Same answer, both ways. Most people aren't very skilled at either sexual interaction or web site security.
There's no mystery here.
Our society is so bad at sexual education and so mired in sexual prohibition and repressive, backwards thinking it's a wonder we even manage to reproduce.
As for web site builders bothering to learn how to sanitize input, and make sure that the mechanism for fetching that input for sanitization is as clean and safe as possible... you have to understand that a very large number of them have no real programming skills. They know HTML and CSS, they can prod PHP a bit, and they may have spent some time sniffing at the buttcrack of that abortion they call UX design (which explains why so many websites demonstrate not a single fucking clue that rollover menus and popups are toxic and visitor/customer-unfriendly and just plain stupid) but they sure as hell don't understand programming. They only learn about injection vulnerabilities when someone rips off their website one way or another.
A programmer's "easy" is a script kiddy's completely unknown territory.
Because businesses think software development in general, and especially web development, is easy. They hire monkeys and pay peanuts (or sometimes even serious dollars that could get them quality of they could recognise they were being taken for a ride), and we continue to see the most basic errors being repeated across most web sites. Seriously, the quality of web developers generally is absolutely appalling.
Senior developers who have diversified experience have seen it all before, and are the most likely to know about these vulnerabilities and how to avoid them. That is why their price tag is high, which is also why companies try to cheap-out and avoid them.
Entry-level developers are the most likely to believe they know everything, precisely because they have too little experience to have a clue about how much they don't know, and also because all they have known is academia so all they have encountered are easy, academic problems (as opposed to hard, complex, real-world problems). Also, schools have proven that they are bad about teaching this to kids, so being freshly-trained is not the same as being well-trained.
It is true that some developers have been in it for a long time, and have been doing it wrong the whole time. It happens when someone finds a special niche where they can just solve the same problem over and over (non-diversified experience). It is also true that plenty of people who are simply not qualified to be architects bill themselves out as architects because they want that money....what they have are good interviewing and salesmanship skills and mediocre programming skills. These guys are toxic to the businesses that hire them.
Lastly, sometimes you get a green fresh-out-of-college kid who actually *is* of above-average talent and knowledge (they all think they are, and very few actually are). But such people can spot the mistakes that these poser-architects are making, but when they raise the challenge they are not respected due to their lack of experience. This is very rare (though nearly everyone believes it to be true of themselves). You might be one such. Maybe.
This is like asking why buffer overflows are still around. People forget to sanitize user input: In C it's letting user input flow past the end of a buffer (often exploiting the stupidity of the design choice known as the null-terminated string); in SQL it's injecting metacharacters into SQL statements that aren't escaping their user input (or using prepared statements).
So why, in 2015, is SQLi still leading to some of the biggest breaches around?"
Easy, idiots writing the apps because companies don't want to pay for skilled people that demand honest wages. they instead outsource ti for the lowest bidder and then bitch when they get crap quality because that is all they were willing to pay for.
Do not look at laser with remaining good eye.
There simply isn't any incentive for to build software that will last through some cyber attack some 10 months or 3 years into the future. The current incentives reward sloppily slapping together something that barely functions and gives a demo without crashing. If your demo crashes and makes the boss look bad, you're fired. If your demo works, has slick graphics and no spelling mistakes and the english dialog is polished, you get a raise. You're building software for the boss's demo, you're not building software that's robust, handles edge-cases, and input sanitizes everything. I meant, you could, but you're not getting paid any extra for it.
Where I work, we (or sometimes our opponent: the Internet) sometimes turn up injection bugs. The reason the bug is there, is because it was written over a decade ago by someone who did not care.
Removing them all isn't possible, because we don't know what all that code is intended to do. I can't fix the code because it doesn't make any sense to me, and I can't replace it with a rewrite, because nobody knows what the code is supposed to be doing. It's not that there's missing documentation; it's that there is no documentation at all. And the code can't serve as documentation, because .. well .. it was written by the kind of people who give zero fucks about injection bugs. So it's not like the injection bugs are the only thing wrong with it; it's nearly completely inscrutable.
So it's going to be there until the business rules change enough (which may be never) such that someone can say, "Oh, let's just totally replace that part." Until it's replaced, we'll have bugs. Period. Nobody can fix it.
There's a lot more client/server going on, and a lot of programmers haven't discovered that you can't trust the client and are doing all their input validation and sanitation on the client side. As long as that's going on, this sort of attack is going to be popular. '); drop table articles.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
By all means, learn. Just don't do it on a public-facing website containing other people's data, m'kay?
It's like walking onto a firing range with a fucking target painted on your forehead.
It isn't going to go well.
Because elitist fucks doesnt teach but only knows how to condemn noobies asking questions and the cycle goes on and on..
This is all due to incompetent programmers, but the reality is bad programmers are always going to make up a significant portion of the dev population. I would think the better solution here is for DB software to make it harder for devs to be incompetent. e.g. disable the ability to do remote dynamic SQL by default and make stored procs and parameterized queries the always the first method documented on how to retrieve and send data, make them jump through hoops to enable it, the same for DB libraries.
You can solve most SQL injection attacks by just passing a law banning PHP.
Mod StormReavers answer up
StormReavers answer is on the money.
Not only does it make the code readable and secure, it avoids the need to craft stupid string manipulation and data validation code. The reason that SQL injection is so prevalent is that people have never seen how easy it is to do this properly.
There is a ton of legacy code out there that was written before people worried about SQL injection. There's so much of it, going back and refactoring it would be as monumental as fixing the two-digit year problems of Y2K. Businesses really don't like to pay money for software development that doesn't lead to more sales, so it's a hard sell for IT departments. Sadly, this means that the problems don't get fixed until after a company loses money due to an actual attack.
Maybe you are all looking at this from the wrong angle, I mean if people don't lookup my faulty tutorials on MySQL, then how am I supposed to hack them to make a living?
You guys got it all wrong, if people educate themselves on how to properly defend against SQLi attacks instead of reading my purposely faulty tutorials, then how am I supposed to hack them to make a living?
SQL injection exists because SQL is a bad interface to databases. We should be calling functions or passing a data structure instead of converting your data into a string then sending the string to the database which will then parse it into another format. SQL isn't typed in by sectaries any more, it should be upgraded.
Good code has low visual appeal, only appreciated by a few. Some objective tests could help, to test the code against common attacks.
Which input characters that are allowed, and how many, should not be hard to put a limit on.
Somewhere along the way we dispensed with DBAs and anyone else who might freak out at insecure access to the database layer. Glad that's working out.
Unfortunately too many developers like to do all of the work themselves. That generally works out badly due to a lack of in depth knowledge in each layer of coding and often leads to code in the wrong place.
Layering code and restricting access in the right places won't necessarily stop some one from getting into a system but it will reduce the amount of damage to be done.
The biggest mistake in Web design is giving the web layer direct access to the underlying database tables. This means if the web server can be broken into then it becomes easy to steal every ones data.
On the other hand if the web layer can only access stored procedures within the database and not the tables themselves and these stored procs are paramaterised correctly it will only ever be possible to retrieve one item of data at a time and only if the correct keys are known in advance.
Accessing databases via stored procedures and locking down database accounts correctly will prevent large scale data protection. New developer especially, are more interested in doing everything themselves and using nosql and some of the more basic open source databases for the cool factor and its costing companies dearly.
I always wonder why people - even professionals (ableit only the non-DB pros) - think SQL is a feasible means for an application to utilise persistance. It isn't. In fact, it's a huge smelly turd for app-persistance and using it so broadly for this sort of work is a really harebrained and abysmally stupid idea.
That we have to deal with SQL injection problems is one of the countless pieces of crap based on this technology decision.
SQL was meant as an end-user interface for interacting with relational database - and for that it is absolutely perfect. End of story.
Using SQL as intermediate for application persistance is one of the most annoying and studidest things in the history of applikation development - for reasons to countless to list them. DB designers are among those who time and time again shake their heads in disbelief when they see the mess devs do with SQL.
We suffer more in our imagination than in reality. - Seneca
PHP.net says "1994 - Started writing CGI scripts in C and Perl".
http://talks.php.net/show/comm...
By the time PHP was gaining popularity, Text::Template and HTML::Embperl were available for Perl.
Rasmus's comments in this interview are revealing:
"I donâ(TM)t know how to stop it, there was never any intent to write a programming language [â¦] I have absolutely no idea how to write a programming language, I just kept adding the next logical step on the
way."
MP3 recording of these comments:
http://web.archive.org/web/201...
The sad thing is that most programmers are still using simple string concatenation to write their SQL programmatically. In this modern day, that is beyond silly. If you use a DSL like jooq, you get several advantages. First off, all strings will automatically be bounded and avoid the simple injection tactics that most people use. Second, you can change databases on a whim by just changing the dialect. This is great for testing and using in memory databases for unit tests. If people just took that simple step, very few attacks would remain, and they would probably be much happier programmers with far fewer bugs!
Don't most database systems allow you to change the statement and quote delimiters? Mine does.
Take a look at MVProc - http://mvproc.free-blog.net/ SQL Injection doesn't ever get a foothold, and it (the module) is very efficient with a tiny footprint. (Yes, I wrote it; and I'll take whatever feedback comes my way.)
Retired from software... maybe. Sort of.