What is it and why should I care?
SQL Injection (SQLi) is an issue that is caused because of poor code/data separation. The general issue is that a developer intends the user input to be interpreted as data, but an attacker can manipulate the input to cause the database to interpret the input as commands.
There have been a lot of devastating attacks recently using SQLi. You don’t have to look very far to see the amount of damage it’s caused, involving great financial, political and reputational impact. You probably won’t have a tough time selling the C-Level exec on the idea that SQLi protection is important.
I’ve already written pretty extensively here on the basic problems with and solutions for SQLi. However, I’ll cover the solutions briefly here for clarity’s sake.
1. Use Parameterized Queries (Note that in Java, parameterized queries = prepared statements)
Parameterized queries are a great solution because they’re fairly simple to learn,are ubiquitous and don’t require your developers to learn another API – it’s just SQL. (One nice additional benefit: it will make your security guy happy)
2. Stored Procedures
Stored procedures are a good option in some environments where you want to have the query management done separately from the code, and are often helpful for performance if properly tuned.
3. Output Encoding/Escaping
Output Escaping is more of a last resort. It can be properly done, but it’s usually more work than the other 2 options, requires more customization, and is more error-prone for most developers. If you go this route, it’s definitely advisable to use a common security framework such as ESAPI so that your controls are consistent across all your applications.
Now that we’ve covered the basic technology solution for SQLi in Java, let’s consider the larger context of eradicating entire classes of vulnerabilities from our codebase as I discussed last week. Let’s look at each specific sub-point I mentioned in that post and consider what you might do regarding SQLi.
1. Understand the problem.
I’d read available documentation on SQLi to make sure I understood the issue as well as I *think* I do. I’d also go read about SQLi for the database platform(s) that is used in my environment. I’d look for any specific weaknesses in implementation that I need to be aware of.
2. Consider all use cases where the issue can occur.
I would think about all the different types of database interactions that occur in my application portfolio. How many languages am I developing in or supporting (Java, C#, Cobol)? What platforms am I developing for (web, services, desktop, mobile, mainframe, html5)? What interaction paradigms are they using (OLAP, OLTP, Warehousing, Batch)?
3. Evaluate solutions.
I would look at the SQLi cheat sheet from OWASP (and any associated weaknesses with the approaches that it espouses). Once I understood the recommended solution(s), I would consider whether said solution(s) would work in the environment in which I’m working. I would focus on using the best solution possible for everyone who can use it, then consider exceptions if it’s not supported by a given interaction paradigm based on business need.
4. Institutionalize the chosen solution.
After deciding on a solution (say parameterized queries), you have to work with development teams to determine the current status of database interactions (are they using dynamic sql now or are they already on parameterized queries?). This step is a good place to (re)train developers on this specific topic from a security perspective, since they’ll be implementing the solution. You then just have to do the work of fixing queries, and re-testing applications to make sure everything still works as expected. Note: In addition to solving SQLi from the “code” perspective, there are also additional steps you can implement here that increase assurance such as lowering the privileges/access of users so they can’t cause certain types of harm even if they were to be malicious.
5. Add technology and processes for verification.
At this point, I would add a few tools in to make sure the technology is being used. I would make sure that there were static and dynamic analysis tools in place to check for SQLi. I would also make sure to code review closely any queries made by the application. I might go so far as to write custom rules for my static toolset that “trusts” my safe output functions to reduce any false positives. Lastly, I would monitor logs for suspicious activity. Using something like AppSensor is a way to automate some of that process.
Hopefully the approach I described makes sense to you. There are certainly additional steps you can take, but this should be a good start. The idea is that you don’t just go and squash a few bugs. The hope is that you do solve the immediate problem, but make it impossible (or as close as you can get) for the problem to resurface.