Today, there's a lot of talk about the dangers of SQL injection, but not many people are getting the details of what SQL injection is, why it's a problem, and what you can do to fix it. I hope to remedy that situation by clearly defining SQL injection, describing how it happens, and letting you know how to protect your organization from it.
What Is SQL Injection?
What is SQL injection? It's a vulnerability, or defect, that results from a coding error in an application. SQL injection occurs when an application uses user-provided data to create a SQL statement and submits that statement to Microsoft SQL Server for execution. The defect's impact varies based on the nature of the coding error. Specifically, the impact depends on where the error is in the code, how easy it is to exploit that error, and what access the application has to SQL Server. Theoretically, SQL injection can occur in any type of application, but it's most commonly associated with Web applications because that's the type of application most often hacked.Thus, I'll concentrate on risks that are exposed by Web applications, but the same concerns exist in all applications that access SQL Server.
How SQL Injection Occurs
Let's look at some code that's SQL injectable to see why problems arise. The code that Listing 1 shows dynamically builds a SQL statement by concatenating the SQL command and the LocationID that's being passed in by a user from a browser. Note that whatever data the user passes in for the LocationID will become part of the SQL statement and therefore will be executed by SQL Server. Here are a few examples of what might get passed to the application and executed by SQL Server:
User input: 1
SQL statement: SELECT LocationName FROM Locations WHERE LocationID = 1
Result: A valid SQL statement
User input: Kilroy was here
SQL statement: SELECT LocationName FROM Locations WHERE LocationID = Kilroy was here
Result: Syntax error because the string Kilroy was here couldn't be converted to a numeric value
User input: 1 UNION SELECT Name FROM Sysobjects
SQL statement: SELECT LocationName FROM Locations WHERE LocationID = 1 UNION SELECT Name FROM Sysobjects
Result: A hacker just ran an arbitrary command on SQL Server
As Figure 1 shows, the SQL statement can be exploited because the user's input is passed directly to SQL Server. As you can see, giving users direct access to your SQL Server machine is bad idea.
How to Stop SQL Injection
To stop SQL injection, an application's code must be written correctly. The application must not allow users direct access to SQL Server. Avoiding direct access can most easily be accomplished with parameterized queries. Parameterized queries separate the SQL command from the data that the command needs to execute.
For comparison, let's look at a secure version of our sample SQL statement. Listing 2 shows a parameterized version of the code in Listing 1, and Figure 2 illustrates what happens in this secure scenario. In the secure scenario,the application sends the SQL statement to SQL Server without the user's input. Instead, a parameter—@LocationID (type int)—is used as placeholder for that input.The SQL command and the @ LocationID parameter are passed separately, so that the user-provided data never becomes part of the command that SQL will execute. Thus, if a user attempts to inject a command for the LocationID, that command wouldn't become part of the SQL parameter and SQL injection would be avoided. You would still receive a SQL error, but it would be a simple data-type conversion error and not something that a hacker could exploit.
This parameterized query technique is commonly considered to be "the fix" for SQL injection. If used consistently, parameterized queries will completely stop SQL injection and protect your SQL Server machines. However, having been a developer for a long time, I can tell you that no matter how hard I try, I'm not consistent—and neither are most other developers. We're human, and humans make mistakes.And in this case, a mistake of not using a parameterized query can lead to a SQL injection attack.Thus, it's important to take a layered approach to securing SQL Server against SQL injection.
Prev. page
[1]
2
3
next page 
