Prevent SQL injection by hardening code
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in August 2006
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
 |
| Read more about security in SQL Server: |
|
|
|
|
|
|
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation