Never trust user input -- it's a cornerstone of secure coding, right? True, but why is SQL injection still occurring? Isn't everyone validating all user input these days? If it were only that simple! The fact is given the complexities of your applications and the time crunch to release them (not to mention all the code you've already developed) you simply can't expect to prevent all SQL injection weaknesses - at least for a while.
That said, now's a good time to start looking at your code in a new way -- from the perspective of a malicious attacker. Look to see what can be exploited and what can be done differently to prevent SQL injection. You've heard how important it is to validate all user input and use stored procedures or parameterized statements. But how do you know what to look for and what not to do. The possibilities are literally endless depending on your code.
Here are a few examples of bad coding and SQL configuration that can lead to SQL injection:
Executing unreliable parameters within your stored procedures like the following:
CREATE PROCEDURE dbo.doQuery(@query nchar(64))
AS
exec(@query)
RETURN
In this case, the stored procedure simply executes whatever is input, and certainly negates the "We're safe because we use stored procedures" argument. It's also dangerous to perform string concatenation directly within your stored procedures.
Haphazardly using SQL concatenation operators such as concat(), String.Concat(), dbo.concat(), and more to build SQL statements from user input.
Not ensuring user input is correctly formed using validation statements such as the following:
IF RTRIM (name) LIKE '[0-9]'
You can create your own regular expressions for input validation or use a product to assist such as RegexBuddy.
Default stored procedures the database user account has access to that can lead to a dangerous injection such as:
application.asp?user=joe';EXEC master.dbo.xp_cmdshell'cmd.exe dir c:'--
These examples are just the tip of the enormous SQL injection iceberg.
I can conservatively say two-thirds of the applications I test are susceptible to SQL injection attacks in some way. With all we know about the dangers of SQL injection vulnerabilities, secure Web application and SQL coding should be on your radar. Don't worry about rewriting everything immediately. Your apps may not even be vulnerable, but you'll never know until you test them.
Start by scanning your applications to see where they're weak and if any SQL injection holes are present. You can do this relatively easily and inexpensively using an automated tool such as Acunetix Web Vulnerability Scanner, SPI Dynamics' WebInspect -- both of which I've used reliably. You can even use the SQL injector tool that ships with WebInspect to automatically exploit the SQL injection weaknesses you come across. The truth is if you want to fully exploit any SQL injection vulnerabilities to see what the bad guys can see, the only realistic way to do it is to use an automated tool. Otherwise, it's just too cumbersome and time consuming. I cover other automated SQL injection testing tools in my tip Automate SQL injection testing.
Another method for finding SQL injection holes is to use a code analysis tool such as those offered by Ounce Labs, /Fortify Software, and Klocwork.
Finally, you can perform your own code review or hire a third-party expert to
help. This method can be very effective, but it can also be very time consuming and painful. Your odds of overlooking one or more SQL injection vulnerabilities also greatly increases. The act of staring at hundreds if not thousands of lines of code looking for all the possible ways SQL injection can occur is more than my eyes can handle.
So, start thinking about how you're handling input, what a malicious attacker could do to manipulate your code, and then test, test, test. SQL injection holes can crop up anytime and anywhere regardless of what you think is supposed to be happening. There are simply too many variables and too much to lose.
About the author: Kevin Beaver is an independent information security consultant and expert witness with Atlanta-based Principle Logic, LLC. He has more than 18 years of experience in IT and specializes in performing information security assessments revolving around compliance and IT governance. Kevin has written six books, including Hacking For Dummies (Wiley), Hacking Wireless Networks For Dummies, and The Practical Guide to HIPAA Privacy and Security Compliance (Auerbach). He can be reached at kbeaver@principlelogic.com.
