Anatomy of a SQL Injection Attack

SQL injection has become perhaps the most widely used technique for compromising Web applications, thanks to both its relative simplicity and high success rate. It’s not often that outsiders get a look at the way these attacks work, but a well-known researcher is providing just that.

SQL injection has become perhaps the most widely used technique for compromising Web applications, thanks to both its relative simplicity and high success rate. It’s not often that outsiders get a look at the way these attacks work, but a well-known researcher is providing just that.

Rafal Los, a senior Web security specialist at HP, has put together a description of just how simple these attacks can be, including a look at the psychology behind their success. Talking to a group of executives about the problem of Web application security, Los found that many of them were skeptical about the extent of vulnerabilities in their code. So he decided to demonstrate the problem right in front of them.

“It’s often the case that the best teacher is experience – and I wish
for nothing more than my doubting Thomases to ask me to show them these
issues on their sites … without even asking this crowd
obliged.  A fellow in the back of the room just yelled ‘Well, if these
issues are so prevalent, let’s see if they exist on one of our sites’. 
I left auto-pilot and went into cautious attack mode,” Los wrote in a blog post describing the attack.

After a quick glance at the volunteer’s site, Los noticed an error that showed evidence that someone else had attacked the site. So he then appended a single tick mark to the end of the URL, which caused a SQL statement to fail and generate an error message explaining exactly what the problem was. And that was more than enough information for Los. He then added ” ‘ OR ‘1’ = ‘1 ” to the end of the original URL.

“First, there was an obvious error in the database, to me it meant I
wasn’t the first to hack at it and someone previous to me had messed
with it and broken it.  Hopefully they broke it BEFORE they stole all
the information out of it.  Next, the single-tick generating an error,
I hope that’s self-explanatory … I simply caused a SQL statement to
go awry and fail, and SQL politely and graciously told me all about it,” Los wrote. “Lastly, the ‘OR’1’=’1 statement append worked because it didn’t
cause an error… why?  It’s because 1=1 evaluates to true and the
database won’t throw an error if the result is TRUE!”

With the permission of the site’s owner, Los exploited the vulnerability and was able to dump the entire contents of the back end database to his laptop. Looking through the tables, he found that the database had in fact been compromised previously and that the attacker had injected some strings that would serve malware from the site. The site was now trying to serve the Zeus Trojan to visitors.

Suggested articles

Discussion

  • Nimrod Yonatan Ben-Nes on

    There is a simple solution for this, use prepared statements (the query is seperated from the values).

  • Anonymous on

    That's the sort of attitude that gets you in trouble. Prepared statements can mitigate yes, but they are also still vulnerable to attack.

  • Anonymous on

    yes, use parameters to get the values to the server!

  • ' OR 1=1; on

    Hi there, I like your' OR '1' = '1'; -- 'article!

  • Chad Stinner on

    My favorite is the USDA who put entire SQL statements in the URL. When I found them and told them about it... they replied "It's perfectly safe". I then emailed the director of the unit and gave him his personal information I found.Things like his home cell phone, and address...

    All this from a Staff information page. Most of the site was configured this way. Who in their right mind passes SQL statements through the URL. Oh yeah... the USDA still does.

  • Anonymous on

    Ah, yes. Little Bobby Tables.

    http://xkcd.com/327/

     

  • Anonymous on

    Which is why I simply don't allow certain characters (', ", =, <, >, etc) to be processed on my website. They respond to expected values ONLY, and value/bounds checking is performed. Any out of bounds value takes you to an error page.

  • Chris on

    I saw the same thing in an application the Navy was using.  Not only passing SQL in the URL but the administrator user name and password! 

  • Anonymous on

    Tools like "Exploit Scanner" and "SQLi Helper" are great SQL Injection examples of how to rip off websites databases if you know how to use them. Securing your site from this so of attack is the most important thing developers SHOULD cater for. In the real world thou, this is not happening and most coding courses don't even touch on the subject.

  • Anonymous on

    You can automate sql-injection audits against classical attack patterns using the following tool: http://sqlmap.sourceforge.net/

  • Anonymous on

    Looks like the company was using a vulnerable CMS solution...

    http://www.google.com/search?q=inurl%3A.com%2Fmenu.asp%3Flocationid%3D

    I wonder if all 4600 of these are also vulnerable? :)

  • Anonymous on

    Validating for <, >, =, etc., is in no way a "safe and effective means" of protecting against SQL injection.  All you need to do is encode the query as HEX and the CAST it back out, which you can do by urlencoding the = sign, etc.  Your best bet is a combination of paramaterized values, and whitelisting values in your stored procedures.

  • Anonymous on

    I use 3 methods to prevent SQL injection

    1) SQL stored parameters

    2) Don't insert the SQL statement or part of the criteria (WHERE CLAUSE) in a URL

    3) Most important, use a weak database account for the web server, my web account can't access the meta tables or system tables so it can't get even the names of critical tables forget about access them directly, in fact it doesn't even have SELECT rights..

     

  • Anonymous on

    I structure my applications and the datastores to only ever pass ints for its routine information and validate the uri of passed variables when possible. Also use a restricted database user that only has access to what it needs. Any user submitted information is heavily sanitized and encoded before ever touching an sql process and even then the information goes into a dmz table with a restricted user that can not select from that table.  I also implement proper error handling so to not show application errors, instead it sends an error notification and informs the end user with a nice message.  I won't get started on server level precautions. Yes I am paranoid but with all the toolkits and script kiddies, to not take proper precautions is either lazy or management does not understand the potential risk and its impact on the bottom line.

  • Anonymous on

    Disallowing characters can be a false sense of security since attackers can use unicode versions of the characters (depending on your db) and trick your if (!string.contains(''')) check into accepting a ' even though you are checking against it. 

  • Anonymous on

    Simple solution: hold developers civilly liable for creating these bugs and the software manufacturers need to be held responsible for not finding them, not fixing them, and delivering them regardless. The software industry is the only instance where you can ship a plainly defective and even dangerous product and face near-zero liability.

  • Anonymous on

    Right and then you either get zero applicants or a few applicants demanding three times the normal salary and four times the normal budget for the department.

  • Anonymous on

    Anyone who inserts user-entered data into an SQL table without checking for and appropriately escaping obvious things like embedded quotation marks is an idiot and should be fired. You just compromised your database: A URL with a query which injects malicious SQL into your query could dump your entire database to the hacker’s browser, as described in the article.

    Anyone who echoes user-entered data in the HTML of a document without checking for and appropriately escaping obvious things like embedded HTML tags, quotation marks, etc. is an idiot and should be fired. You just made your users vulnerable to cross-site scripting and phishing: A URL with a query that injects a malicious script into the page could steal the login credentials, session cookies… even run a keylogger / clicklogger on any activities performed on the page.

  • Graham J on


  • Jazz on

    Anonymous's solution -- holding developers liable for bugs -- is one of those solutions that sounds great, and even makes sense, if you know absolutely nothing about the way software is written.

    I've been a developer. I've worked on an enterprise application to support thousands of users of one of the nation's largest and most successful banks. (I can't say who, or what consulting firm I was working for at the time, but they are both very large firms.) Let's examine what would have happened in this hypothetical scenario:

     

    Me: Hey, boss, I think we've got a problem here. If a user types these things in to their address bar, they can access information in the database they're not supposed to. They might even be able to change it. We should write code to protect against–

    Boss: Absolutely not. Our clients at WealthyBank have given us a precise specification of the software they want us to write, and that isn't in it.

    Me: Right, but isn't it our duty to make sure our software is secure? If someone–

    Boss: We have a signed contract with WealthyBank that says we will provide exactly what is in that spec. If we add this security feature, we're not getting paid for it. Which means I'm not paying you to do it.

    Me: Well, is there a way that we could get this security added into the spec? Then we could–

    Boss: We just spent five months renegotiating this contract with them, and the software is due in seven months. There's no way we can take the spec back now, then we'd have to renegotiate the cost for the additional work and get the client to sign off on it again. And that would take us another three-to-five months.

    Me: I'm just not comfortable knowingly letting a security issue go–

    Boss: Look, Jazz, you want to be a big-shot consultant like me someday, right? Making 100k instead of whatever laughable salary you entry-level analysts make? Then you better get used to this. Get a spec from the client, negotiate the contract, then build exactly what's on the spec– that's how we do things.

     

    Now fortunately, that conversation is hypothetical. My bosses were always on-the-ball about security. But that same conversation very nearly happened several times about other issues or problems in a spec I was handed, and it's part of why I don't work at that firm anymore. Developers simply aren't given the choice about whether or not to close holes like this– if the client (or, for in-house work, whatever department is writing specs) doesn't explicitly ask for the code to do something or not do something, you won't be paid to do it. Holding them liable for bugs under this situation is a joke. If developers were held liable for security holes but given no power to decide when security holes should be closed, nobody would be a developer -- it would be too risky an occupation.

  • Black Hat on


  • Black Hat on

    "For every complex problem there is always ine, simple, easy to understand wrong answer."  -H.L. Menkin

  • Lou on

    Holding developers liable?  Software systems, especially very large ones, are enormously complex.  To run through every possible test scenario, if anyone can even take the time to think up and design a test case for every possible scenario, would add years to the development of large software systems and untold costs.  All this wonderful software-based technology we enjoy that makes our lives easier?  It would come to a halt if developers and software companies faced liability threats.  Not to mention loss of the bright and talented people who stay away from software development because any one mistake could bankrupt them.

    Bugs are to software as loose threads are to a sweater.  Sometimes you see them, sometimes you don't, sometimes they're insignificant, and sometimes they'll ruin the whole thing.  But they're always there - and it's near impossible to detect them all.

  • Anonymous on

    most. all of the restaurants. they must have bought the same sw.

  • cjoki on

    I liken holding the developer or developer company being liable to a sql-injection/hacker (AKA cracker) as to a home builder being sued by a home owner because a thug broke into there house and stole everything and set it on fire.

    Why not instead devote more time to the people that do sql inject/hacking with the intent of destruction, theft or alteration?

  • Anonymous on

    If we were to hold developers liable then Microsoft would have been out of business along time ago

  • Anonymous on

    "I liken holding the developer...to a home builder being sued by a home owner because a thug broke into"

    That analogy is flawed.  Suppose you paid a company to install a security system.  Suppose there was a special combination that disabled the system and let anyone in, and the security company's policy was just not to tell anyone about it.  But the combination was well known amongst security people.  There were books with the combination, and even pre-made tools that could guess the combination just by looking at the faceplate.

    Now, someone breaks-in using the special combination.  Do you sue the company or not?

  • Michael on

    I think your comparison to the combination is flawed--largely because this 'combination' is not in the manual, you have to root around to figure it out. The burglar/thug analogy is better. You get what you pay for. If you don't pay to have in-depth security analysis of your software you'll get hacked, just like if you buy a $1.50 lock for your house you are much more likely to be robbed.

    Any good developer knows that it is impossible to QA your own work. A company that is serious about it's sites needs at the very least a seperate department to do QA/QC as well as security analysis of websites and webapps/tools.

    Ideally pen-testing and 3rd party security company's would be the best way to go. Even the bottom end pen-testing programs will try adding single quotes to URLs and there are dozens of firms that can have a human do what Rafal Los did in his test.

    I think the moral of the story is that there is no big soft safety net out there. If you put stuff on the web it's up to you to do what it takes to make sure it's secure.

  • Lou on

    I like the burglar analogy.  You build a good, solid home (no missing walls or windows), but the burglar finds out how to pick the lock and gets in.  Should the maker of the lock or home be liable?

    I think the flaw in the "secret combination" analogy is that sometimes developers don't know the secret combination exists - they are so difficult to find, it takes a burglar to find it. 

    There are situations where the developer is inexperienced and leaves obvious flaws (what I would call "missing windows" from the home analogy above) - which actually supports the case for liability (I liken that situation to malpractice).

    Now that I think about it - maybe it's reasonable to define a set of minimum standards - minimum security precautions a website developer must take when dealing in sensitive information.  If it can be shown that the developer (or company) was grossly negligent, perhaps they should be liable.  But for this, I'm talking grossly negligent - this would be up to lawyers, courts, and experts to decide.  Be assured - I'm not saying that developers be liable for any and every bug, or even a majority.  I'm only talking grossly negligent.

    As a web developer myself, of course I would hate this.  But is the argument for it good?  Yes.  It would force the development world to produce higher-quality, more secure work.  Perhaps this is a good idea.  It'll happen someday - someday a software development company (or person) will get sued for the equivalent of malpractice.

  • Anonymous on

    Suppose you paid a company to install a security system.  Suppose there was a special combination that disabled the system and let anyone in, and the security company's policy was just not to tell anyone about it.  But the combination was well known amongst security people.

    You mean like a bump key?

  • Aaron Greenlee on

    ColdFusion's <cfqueryparam /> tag or Query.addParam('...'); prevents this and automatically formats the SQL value for what every DB type is required.

  • Anonymous on

    I am willing to be held civilly liable for break-ins, but the purchaser will need to adhere to a few rules, like never connecting the machine to any kind of network, and never letting anyone other than himself near the machine. The purchaser can connect to the network or let someone else use the machine, but must give up me being liable for any damage that occurs.

  • Anonymous on

    The best way is being liable... if you are a software developer you MUST (REALLY MUST) have a extensive curse of security in the language you are programming your software in. If you don't have you can not even make one (at least for the others to have / buy)! Also you should prove you REALLY understand the security problems and you can do things the right way.

    Company's not wanting things to be secure because that costs more money to be made (or something else)... well the software developer person could accept that, but should be obligated to inform one federal security autority in IT Security that software from company "X" is being developed, was found this bug "X" and the manager of the project, company owner or the final client did not wan't the problem fixed. This way when problems where found (like vulnerabilities, attacks, defacement's, etc.) the software developer person would not be liable for it because he spot the problem, he informed who ever was responsible and was told not to solved that problem.
    That federal autority should place in the public Internet the company name, software, software version and what as received, so that any possible client can see before buying / install what problems are in side the product and if it can accept that for it needs... and the possible liabilities.

    That's what I think has a costumer. If software development is that problematic, and is dificult to do things the right way maybe you (software developer person) are in the wrong area... you should be in other area.

  • ChrisCicc on

    Hi Chad,

    I am a software engineer with the federal government. I work to remove these vulnerabilities. Could you point me to the USDA site that continues with this horrendus practice? I will do my best to stamp it out.

    Thank you,

    Chris

  • Anonymous on

    I think there is a much larger inherent problem here. Software developers, managers, etc are not taught or trained on the large scale to implement security practices in their development or business practices. If you look at most universities out there training future IT professionals, there are separate tracks and one of those is in IT Security. Very rarely do you see IT Security integrated into a networking or developer track in these institutions.

    If you want to make a developer accountable, at this point in history you would be left holding the company accountable for not providing training, the school accountable for not educating, etc. Compared to software development, security is a relatively new and growing field. I definitely don't disagree with the concept that this is a problem, but people must realize articles like this are used to educate and everyone in this thread should be working to educate there respective business locations if you want to see a fix.

  • Michael on

    I think something that needs to be kept in mind with this is that there are two tracks this will ultimately go down. The first is that some civil suits will occur and this will move into the realm of case law. The second is that the federal government will put in place some mandates regarding software security and provide some governance and oversight.

    I sincerely hope the second does not occur. Can you imagine having to submit all software for federal review, or suffer from periodic federal oversight. Software development cycles would go from months to decades--it would be like the drug industry.

  • AlaxH on

    How to Detect  SQL Injection Attacks with Sax2

    The process of detect and revert SQL Injection Attacks with Sax2

    Some IDS software will execute effective detection for SQL Injection Attacks, though, firewall can not. Now, we go to the process of detect and revert SQL Injection Attacks with IDS software Sax2.

    The steps of SQL Injection Attacks are:

    a) Determine environment to find the injection point.
    b) Determine the type of database.
    c) Guess datasheet.
    d) Guess the field.
    e) Guess the content.

    The steps “Guess datasheet”, “Guess the field” and “Guess the content” are very important fro SQL Injection Attacks during the full process. Let’s analyze these there steps.

    Sax2 will detect and alarm the attacks in network real-time. It will show the in the table Event when there is SQL Injection Attacks, see the figure 1.

    Sax2 alarm the MS_SQL Injection Attacks real-time

    Figure 1 Sax2 alarm the MS_SQL Injection Attacks real-time

    The selected event in the Figure 1 shows the attacker’s IP 192.168.21.103, the victim’s IP 125.65.112.10. And the original message is “select * from [dirs]”, means enquire whether there is a datasheet named “dirs” in current database, in the Original Communication view.
    The attacker will repeat the operation to gain the expected datasheet. He will try to guess the filed in the datasheet if found the corresponding datasheet in the database.

    Sax2 analysis the attacker is guessing the filed in the admin 
database

    Figure 2 Sax2 analysis the attacker is guessing the filed in the admin database

    The code in the red circle in the Figure 2 show the attacker is guessing the “paths” filed in the admin database. Also, the attacker will repeat the operation till find the corresponding filed.

    The attacker will determine the length of the filed and guess the content after found the corresponding filed. It will be a SQL Injection Attacks after the attacker guess the content in the filed successfully. Sometimes, the attacker has to decryption the content if it in MD5 encryption.

    Above is the whole process of SQL Injection Attacks and we detect it with Sax2. As we know, Sax2 can effectively detect and alarm the SQL Injection Attacks when it occurs. IDS software Sax2 is a useful tool for SQL Injection Attacks and make your network security combine with firewall software.

Subscribe to our newsletter, Threatpost Today!

Get the latest breaking news delivered daily to your inbox.