By Splunk February 04, 2010
Unless you have had your head in the sand, SQL Injections have made a fierce comeback to the top of the threat vector charts this year. According to the WHID (Web Hacking Incidents Database), SQL injection is still king of the attack vectors, accounting for 19 percent of attacks, followed by authentication abuse (11 percent), content spoofing (10 percent), DDoS/brute force (10 percent), configuration/admin error (8 percent), cross-site scripting (8 percent), cross-site request forgery (5 percent), DNS highjacking (5 percent), and worms (3 percent).
Reflect on the recent increase in compliance legislation requiring businesses to provide dynamic data access to customers for banking, healthcare, or the influx simple purchases on the web, and the concern may be scarier for all of us. Recently, Dark Reading reported on the number of companies who have been compromised through SQL Injection attacks.
What is SQL Injection, and How Does it Work?
If you don’t know what this is, and just learned what SQL is, I recommend going to OWASP.org and reading up a little. It is a great resource, and the mass amount of security professionals dedicated to the Open Web Application Security Project deserve a big shout out.
Lets start with how SQL Injection actually works. SQL Injection occurs when an attacker is able to insert a series of SQL statements into a ‘query’ by manipulating a data input, usually a form for users to update their account information. Some common relational database management systems that use SQL are: Oracle, MSSQL Server, DB2, Sybase, Informix, MS Access, Ingres, and so on, with the most popular being MSSQL of those.
Whether you are a potential attacker, auditor, researcher or an application developer, you may go through the same steps to exploit or find exploitable code:
- Input Validation
- Information Gathering
- 1=1 attacks
- Data extraction
- OS interaction
- OS Cmd Prompt
- Expand influence
More information available at OWASP (Victor Chapela, OWASP, “Advanced Topics on SQL Injection Protection”)
Splunk and SQL Injections
Splunk approaches this attack a little differently because of our ability to make all IT data security-relevant. Within the Splunk index, organizations will collect logs, custom application logs, traps, configurations, stack traces, scripted outputs, auth data and metrics for analysis. Splunk can help in applying security/audit logic in various detective controls to aggregate IT data to one place, make simple sense of the data, apply relationship logic into what might appear to be a standard operational issue. This logic can give you a “report”, alert, dashboard, e-mail, run a script to gather more information, or simply create a news feed of the ongoing event to send to a ticketing system, incident software or other system. Not just designed for tier 1 troubleshooting, Splunk can help incident handlers and analysts backtrack events by digging into logs across geographies, datacenters, applications and technologies. Incident handlers, auditors, security professionals can then persist the same logic in a “search” to identify the next occurrence, proactively defending sensitive assets.
How Does Splunk Do This?
Well, for this case, lets use the Splunk Security application, Enterprise Security Suite (ESS). ESS enables simple searching to illuminate information in the muddy, challenging environment of security and operational data accessed by more people than just security androids and SysAdmins. I use it to help organize security data into categorical security areas: Access Protection, Endpoint Protection, Network Protection, Incident Response, and Governance.
You Already Have the Answers: In Your IT Data
The same Splunk rules still apply, you have to put your data in, to get good information out, so we need a few key pieces of data to find a SQL Injection – some more damning than others – to identify what starts out as an operational issue, but turns into a security investigation.
IDS Logs and Events
Though there are many methods of subterfuge to avoid IDS/IPS detection of the 1=1 statement, getting a look at the application data in a purported attack via a Snort/Cisco/Juniper alert, is very helpful as part of a correlated event. SQL injection may include of logic, depending on the input validation, a ‘;’ may help; seeing JOIN or UNION statements may also be indicators of misuse.
Packet Capture
Always good, certainly looking at application data in the packet with SQL statements is going to be helpful. Thing is, often times, database replication, linked databases, etc. are all capable of using HTTP as the transport protocol, so be advised- this could be a lot of data, and it may be legitimate. Alerting on these events in Splunk would let you execute a script to trigger TCPdump or something based on an event, if the Splunk instance is enabled with tcpdump.
Vulnerability Assessment Tools
Nessus events, or other audit tools, can help qualify the actual threat of the injection language based on the type of systems you are protecting. MSSQL statements are more forgiving than say, Informix for example, and if you are a UNIX shop, MSSQL attacks do not pose a risk, though this may mean some interrogative work.
Anti-virus
This is handy should malicious code be dropped, downloaded and/or propagated via SQL language over HTTP, FTP, SSH or other file transfer protocols. When an event turns off AV, or a failure occurs after a noticed injection, there should be concern as to the sanctity of the system it failed on.
Host Data
Perhaps the application server and database servers have file integrity monitoring, maybe a scripted output of binaries such as top, psstat, or in windows, netstat and ipconfig? Looking at a new listening service you didn’t install, may be after the fact, but at least identifiable with Splunk. If you happen to have something like OSSEC installed, or another kernel monitoring software, perfect. An example SQL Injection provided by OSSEC, looks like this:
200.96.104.241 - - [12/Sep/2009:09:44:28 -0300] "GET /modules.php?name=Downloads&d_op=modifydownloadrequest&%20lid=-1%20UNION%20SELECT%200,username,user_id,user_password,name,%20user_email,user_level,0,0%20FROM%20nuke_users HTTP/1.1" 200 9918 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
note the UNION statement followed by %20SELECT%200. This is the beginning of pulling all the successful users from PHPnuke site, and getting table data on all of their usernames, IDs, passwords, and e-mails. If you aren’t careful about your web application accounts all being the same, you could be the subject of more than a twitter DDoS.
Application Logs
Web server logs, depending on verbosity, this may be a good starting point to determine the source/destination centered around IP traffic, as well as success/failure and some explanation of why. Nothing special about these logs, we use them for operations and security. Several types of HTTP status errors can be helpful in determining what is going on – for instance, a 403 or a 401 error on a customer facing application.
User Audit Log
Finding failed and successful authentication attempts on a local system is extremely helpful- especially around the time something changed in our environment. Make sure to note, actual injection code may be executed on the application tier or webhost, rather than the database server itself. Detective controls should apply to the entire transaction architecture.
MSSQL Error Logs
Error logs provide some good information for Splunking account access, and database errors especially after isolating the application error occurring in the transaction model. To add the error log on a forwarder as an input:
CHARSET = UTF16-LE
NO_BINARY_CHECK = true
(Make sure to do this on the indexer as well)
The most valuable source of information is actual data across the wire. You are using tcpdump, or wireshark, monitoring the SQL server (if you don’t, you should at least have the capability should there be a threat). If extended procedures like “xp_cmdshell” are being executed they may actually be logged when invoked. Xp_cmdshell enables a virtual cmd shell within an SQL statement. Maybe you have Windows event logging, and registry settings baselined, and a profile of your SQL server’s persisted connections base lined so you can apply a diff to them frequently, uncovering new network connections, and services alike. Similarly, user logon/logoff events to the operating system and application, both successful and denied, are handy and can indicate if a system account seems to persist failure a-la brute force attack. We may also see a large amount of auth attempts to SQL server, or the database through the windows Application log as well.
Determining Contextual Relevance
The same information has different context depending on who is looking at it, what it is used for, and how it relates to other data, what we call contextual relevance. In Security, almost every technology is interrelated in either debunking a threat, or validating it. We use Splunk to help solve Operations availability problems, Information Security problems, Compliance requirements, both on the fly and proactively, bringing these problems to light with alerting and notification.
Any of these events may show up, though logging may be turned off/system clock reset given a system may have been compromised with a Splunk forwarder. The stopping of event data in logs is also telltale, so be sure to no alerts=immediate concern in Boolean search, and persist a search that alerts when no events appear.
Input validation is really the first way to stop the problem in development, but stored procedures can be modified to add a regular expression for a credit card number, an SSN, or other fields. Preventing the additive SQL command in the input space of a form, stops the problem ahead of time, so does fuzzing before code is live, but secure coding is another chapter.
Some of the information available in Splunk will also allow the operations personnel or administrators to go back and audit configuration files, to see if DBerrors are being thrown to the user’s screen enabling enumeration/info gathering. Even if the code cannot be pulled from production, enumerating poor SQL input validation can be more difficult if error reporting is turned off.
For instance, is your config file configured to throw exceptions on that server? Have a look in Splunk. Take HTTP status errors for example a 403 Forbidden error status code, this may be something that gives valuable information to a potential attacker. 403 errors may be something you don’t want to show folks, unless in a development environment, so this may suggest turning off error responses if you see them in Splunk. At the same time, this generates an event beneficial to the application owner, but maybe doesn’t need to be broadcast. Users should see 404 errors when error notification is disabled, rather than a 403 error. When in doubt, look in Splunk! Maybe a quick search for all 403 errors may let you know there is an example of a potential SQL Injection occurring.
Once you find a 403 occurrence, or a bunch of failed account logins to a database, or an application server unavailable (503), use Splunk to span a 15-minute window by dragging the search range to the 15 minutes around the error. Then, look for all events during that time, on the host that may have hosted the error event. Perhaps a system has been compromised, and credentialed access may allow downloading/uploading?
Look for failed and successful authentications to the same system. Look for escalated account privileges. Splunk allows you to run a “System Profiler” as well to look at things like the change in listening services and ports on the Network, and things like infections or the state of anti-virus from the Endpoint Protection dashboard. When you find the behavior in all of the layers of events that correlate the event, create a search to populate a summary index as a “notable event” appearing in the Security Posture dashboard, and create an alert for it. Next time, Splunk users and Incident Handlers alike will know without re-creating the on the fly search we just did.
Splunk Helps Make the Seemingly Unrelated – Related
What does all this mean? Simply this- simple users can apply complex concepts from experts in the field, to search, detect, alert and report on security threats like SQL Injections, using Splunk. ESS allows a wide variety of users to look at the same information and derive different conclusions based on contextual relevance. Most real security risks, don’t flash “SQL INJECTION ATTACK” in your dashboard, you need to understand your environment and what pieces work together. Given the increased frequency of Web application threats, specifically SQL Injection, identifying the threat itself depends on multiple layers of security as well as a way to simply search through all of those layers simultaneously. Found an IP of a suspected system? Splunk it. Found an error for authentication to a back-end system? Splunk it. Can’t find the relationship between events in a given period of time? Use Splunk to make the seemingly unrelated, correlated.
For more information on how to monitor your MSSQL-driven application using Splunk, drop us a line: info@splunk.com
Microsoft has provided their own suggestions as well. http://msdn.microsoft.com/en-us/library/ms998271.aspx