Sql Injection

SQL Injection Prevention

By Adrian Lane

The team over at Dark Reading was kind enough to invite me to blog on their Database Security portal. This week I started a mini-series on threat detection and prevention by leveraging native database features. This week's post is on using stored procedures to combat SQL injection attacks. But those posts are fairly short and written for a different audience. Here, I will be cross-posting additional points and advanced content I left out of those articles.

My goal was to demystify how stored procedures can help combat SQL injection. There are other options to detect and block SQL injection attacks, many of which have been in use with limited success for some time now.

What can you do about SQL injection? You can patch your database to block known threats. You can buy firewalls to try to intercept these rogue statements, but the application and general network firewalls have shown only limited effectiveness. You need to have a very clear signature for the threat, as well as a written a policy that does not break your application. Many Database Activity Monitoring vendors can block queries before they arrive. Early DAM versions detected SQL injection based on exact pattern matching that was easy for attackers to avoid, back when DAM policy management could not accommodate business policy issues; this resulted in too many false negatives, too many false positives, and deadlocked applications. These platforms are now much better at policy management and enforcement. There are memory scanners to examine statement execution and parameters, as well as lexical and content analyzers to detect and block (with fair success). Some employ a hybrid approach, with assessment to detect known vulnerabilities, and database/application monitoring to provide 'virtual patching' as a complement.

I have witnessed many presentations at conferences during the last two years demonstrating how a SQL injection attack works. Many vendors have also posted examples on their web sites and show how easy it is to compromise and unsecured database with SQL injection. At the end of the session, "how to fix" is left dangling. "Buy our product and we will fix this problem for you" is often their implication. That may be true or false, but you do not necessarily need a product to do this, and a bolt-on product is not always the best way. Most are reactive and not 100% effective.

As an application developer and database designer, I always took SQL injection attacks personally. The only reason the SQL injection attack succeeded was a flaw in my code, and probably a bad one. The applications I produced in the late 90s and early 2000s were immune to this form of attack (unless someone snuck an ad-hoc query into the code somewhere without validating the inputs) because of stored procedures. Some of you might say note this was really before SQL injection was fashionable, but as part of my testing efforts, I adopted early forms of fuzzing scripts to do range testing and try everything possible to get the stored procedures to crash. Binary inputs and obtuse 'where' clauses were two such variations. I used to write a lot of code in stored procedures and packages. And I used to curse and swear a lot as packages (Oracle's version, anyway) are syntactically challenging. Demanding. Downright rigorous in enforcing data type requirements, making it very difficult to transition data to and from Java applications. But it was worth it. Stored procedures are incredibly effective at stopping SQL injection, but they can be a pain in the ass for more complex objects. But from the programmer and DBA perspectives, they are incredibly effective for controlling the behavior of queries in your database. And if you have ever had a junior programmer put a three-table cartesian product select statement into a production database, you understand why having only certified queries stored in your database as part of quality control is a very good thing (you don't need a botnet to DDoS a database, just an exuberant young programmer writing the query to end all queries). And don't get me started on the performance gains stored procedures offer, or this would be a five-page post ...

If you like waiting around for your next SQL injection 0-day patch, keep doing what you have been doing.

–Adrian Lane

Recent Breaches: We May Have All the Answers

By Rich

You know how sometimes you read something and then forget about it until it smacks you in the face again?

That's how I feel right now after @BreachSecurity reminded me of this advisory from February.

To pull an excerpt, it looks like we now know exactly how all these recent major breaches occurred:

Attacker Methodology: In general, the attackers perform the following activities on the networks they compromise:

1. They identify Web sites that are vulnerable to SQL injection. They appear to target MSSQL only.

2. They use "xp_cmdshell", an extended procedure installed by default on MSSQL, to download their hacker tools to the compromised MSSQL server.

3. They obtain valid Windows credentials by using fgdump or a similar tool.

4. They install network "sniffers" to identify card data and systems involved in processing credit card transactions.

5. They install backdoors that "beacon" periodically to their command and control servers, allowing surreptitious access to the compromised networks.

6. They target databases, Hardware Security Modules (HSMs), and processing applications in an effort to obtain credit card data or brute-force ATM PINs.

7. They use WinRAR to compress the information they pilfer from the compromised networks.

No surprises. All preventable, although clearly these guys know their way around transaction networks if they target HSMs and proprietary financial systems.

Seems like almost exactly what happend with CardSystems back in 2004. No snarky comment needed.

–Rich

Heartland Hackers Caught; Answers and Questions

By Rich

UPDATE: follow up article with what may be the details of the attacks, based on the FBI/Secret Service advisory that went out earlier this year.

The indictment today of Albert Gonzales and two co-conspirators for hacking Hannaford, 7-Eleven, and Heartland Payment Systems is absolutely fascinating on multiple levels. Most importantly from a security perspective, it finally reveals details of the attacks. While we don't learn the specific platforms and commands, the indictment provides far greater insights than speculation by people like me. In the "drama" category, we learn that the main perpetrator is the same person who hacked TJX (and multiple other retailers), and was the Secret Service informant who helped bring down the Shadowcrew.

Rather than rehashing the many articles popping up, let's focus on the security implications and lessons hidden in the news reports and the indictment itself. Let's start with a short list of the security issues and lessons learned, then dig into more detail on the case and perpetuators themselves:

To summarize the security issues:

• The attacks on Hannaford, Heartland, 7-Eleven, and the other 2 retailers used SQL injection as the primary vector.
• In at least some cases, it was not SQL injection of the transaction network, but another system used to get to the transaction network.
• In at least some cases custom malware was installed, which indicates either command execution via the SQL injection, or XSS via SQL injection to attack internal workstations. We do not yet know the details.
• The custom malware did not trigger antivirus, deleted log files, sniffed the internal network for card numbers, scanned the internal network for stored data, and exfiltrated the data. The indictment doesn't reveal the degree of automation, or if it was more manually controlled (shell).

The security lessons include:

• Defend against SQL injection -- it's clearly one of the top vectors for attacks. Parameterized queries, WAFs, and so on.
• Lock databases to prevent command execution via SQL. Don't use a privileged account for the RDBMS, and do not enable the command execution features. Then, lock down the server to prevent unneeded network services and software installation (don't allow outbound curl, for example).
• Since the bad guys are scanning for unprotected data, you might as well do it yourself. Use DLP to find card data internally. While I don't normally recommend DLP for internal network traffic, if you deal with card numbers you should considering using it to scan traffic in and out of your transaction network.
• AV won't help much with the custom malware. Focus on egress filtering and lockdown of systems in the transaction network (mostly the database and application servers).
• Don't assume attackers will only target transaction applications/databases with SQL injection. They will exploit any weak point they can find, then use it to weasel over to the transaction side.
• These attacks appear to be preventable using common security controls. It's possible some advanced techniques were used, but I doubt it.

Now let's talk about more details:

• This indictment covers breaches of Heartland, Hannaford, 7-Eleven, and two "major retailers" breached in 2007 and early 2008. Those retailers have not been revealed, and we do not know if they are in violation of any breach notification laws.
• This is the same Albert Gonzales who was indicted last year for breaches of TJ Maxx, Barnes & Noble, BJ's Wholesale Club, Boston Market, DSW, Forever 21, Office Max, and Sports Authority.
• A co-coconspirator referred to in the indictment as "P.T." was not indicted. While it's pure conjecture, I won't be surprised if this is an informant who help break the case.
• Gonzales and friends would identify potential targets, then use a combination of online and physical surveillance to identify weaknesses. Physical visits would reveal the payment system being used (via the point of sale terminals), and other relevant information. When performing online reconnaissance, they would also attempt to determine the payment processor/processing system.
• In the TJX attacks it appears that wireless attacks were the primary vector (which correlates with the physical visits). In this series, it was SQL injection.
• Multiple systems and servers scattered globally were used in the attack. It is quite possible that these were the part of the web-based exploitation service described in this article by Brian Krebs back in April.
• The primary vector was SQL injection. We do not know the sophistication of the attack, since SQL injection can be simple or complex, depending on the database and security controls involved.
• It's hard to tell from the indictment, but it appears that in some cases SQL injection alone may have been used, while in others it was a way of inserting malware.
• It is very possible that SQL injection on a less-secured area of the network was used to install malware, which was then used to attack other internal services and transition to the transaction network. Based on information in various other interviews and stories, I suspect this was the case for Heartland, if not other targets. This is conjecture, so please don't hold me to it.
• More pure conjecture here, but I wonder if any of the attacks used SQL injection to XSS internal users and download malware into the target organization?
• Custom malware was left on target networks, and tested ensure it would evade common AV engines.
• SQL injection to allow command execution shouldn't be possible on a properly configured financial transaction system. Most RDBMS systems support some level of command execution, but usually not by default (for current versions of SQL Server and Oracle after 8 -- not sure about other platforms). Thus either a legacy RDBMS was used, or a current database platform that was improperly configured. This would either be due to gross error, or special requirements that should have only been allowed with additional security controls, such as strict limits on the RDBMS user account, server lockdown (everything from application whitelisting, to HIPS, to external monitoring/filtering).
• In one case the indictment refers to a SQL injection string used to redirect content to an external server, which seems to indicate that malware wasn't necessarily always used.
• The malware attempted to hide itself. While details aren't available, the indictment indicates it probably erased log files, at a minimum.
• The attacks both sniffed traffic and attempted to identify stored card numbers. They targeted data at rest and in motion.
• I've heard rumors from trusted sources that the exfiltrated data was not encrypted or otherwise obfuscated in at least one case.

Just as the TJX hack was based on well known issues with wireless security, these attacks seem to use well known SQL injection techniques. In a way I really hope I'm wrong, and that some new kind of advanced SQL injection attack was involved, but I think we'd see more bigger victims if that were the case. I also find it fascinating that a single individual is at the crux of multiple cases, and used to be a Secret Service informant. I hope more information is revealed about the "hacking platform" that may refer to systems referenced in the Washington Post article back in April.

Finally, does this mean we have two major retailers in violation of breach disclosure laws?

As is almost always the case, preventing these attacks wouldn't necessarily have required rocket science or millions of dollars in specialized security tools.

Wired also has a good article.

–Rich