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.
The Layered Approach
Taking a layered approach means implementing multiple practices to avoid a vulnerability or problem.That way, if one of the security practices fails or isn't followed, another practice will mitigate the risk. To thwart SQL injection attacks, you should not only use parameterized queries but also validate input, restrict applications' privileges, use stored procedures, and have a strong sa account password.
Validate input. Since the advent of computers, there's been a common saying among software developers: "Garbage in, garbage out." This saying basically reminds us that if you allow anything into your system (garbage in), you're likely to have an unreliable system (garbage out).This concept can be extended to security in the form of input validation. Input validation means that you use user-provided data only after you've ensured that it's valid. Specifically, you make sure you're getting data of an acceptable type and format.You can validate data in many ways. Some methods are acceptable, whereas some methods are risky.
The acceptable methods include allowing only explicit data and allowing only explicit types of data. Both methods are rooted in the concept of allowing only known good data into SQL Server (i.e., whitelisting). When you allow only explicit data, you're allowing only a known list of values (e.g., specific account types, specific account numbers). When you allow only explicit types of data, you allow only data of a known type (e.g., integers, letters of the English alphabet).
The risky methods include rejecting known bad data and flagging, or escaping, a character (or character sequence) that has a special meaning to SQL Server or the OS shell.When you reject known bad data, you don't allow data that's known to be bad into SQL Server (i.e., blacklisting). However, because it's impossible to know all the bad data ahead of time, using this technique can be risky. When you escape a character that has a special meaning (e.g., a single quote that's inserted when a value is expected), you insert the shell escape character (^) in front of that character to tell the system to treat the item that follows as its literal value and not as a special character. Alternatively, you can replace the special character with a value that doesn't cause a problem. This method is risky because the escaping must be perfect. In addition, it assumes no new hacking techniques will come to light that can circumvent the escaping.
Restrict applications' privileges.Every application runs in a security context.When dealing with Web applications, the Web site is typically configured to connect to SQL Server using a predefined user credential.The application then enforces security through some type of application-defined process.The user credential used by the application to connect to and run SQL commands on SQL Server is of particular concern. Because any SQL injection attack would run in the context of this user credential,it's crucial that the user credential be limited to only those rights that the application needs to function.This concept has been around for years but is frequently not enforced because it requires that developers articulate all needed access as opposed to just asking for sa rights to SQL Server.
Clearly defining the access rights that the application will need to function and not allowing the application to perform any other SQL calls won't prevent SQL injection. However, it will make exploiting the SQL injection significantly more difficult.
Use stored procedures. Many developers believe that using stored procedures is a great fix for SQL injection. I don't completely agree with this notion because stored procedures can include dynamic SQL statements that don't use parameterized queries. However, I do agree that using stored procedures can help in the effort to protect organizations against SQL injection attacks. It helps for several reasons. First, developers tend to use parameterized queries when they create stored procedures because it's easier to write the code when you use such queries. So, developers end up writing more secure code. Second, if the application uses stored procedures to execute SQL commands, it's less likely that the hacker will have access to the SQL command that actually gets executed. This makes exploiting SQL injections much more difficult. Finally, developers have control over what goes into and out of a stored procedure.
Have a strong sa account password. One security measure that developers frequently forget about is setting a strong sa password. The following scenario shows why having a strong sa password is so important.
Assume that the SQL Server machine is configured with a minimal amount of rights for the application user, there's an instance of SQL injection in the Web application, and the sa password is weak. Here's how a hacker can wreak havoc:
- The hacker locates an instance of SQL injection on the Web site.
- The hacker discovers that he can't execute-a SQL command using the typical hacking techniques.
-
The hacker determines that he needs to run the command under the sa account, so he sets out to find out the sa password. The hacker knows he can run a SQL command with an alternate identity if he runs an OPENROWSET command such as
SELECT * FROM OPENROWSET ('SQLOLEDB',';'The hacker comes up with a SQL injection attack that will let him pass in a valid query. He uses sa as the username and guesses the password.'; ' ',' ') - The application errors out, which lets him know that the password was invalid.
- The hacker creates a simple brute-force program to crack the sa password.
- Once the hacker has the sa password, he can continue to execute the SQL injection attack using the OPENROWSET command.
Determining the sa password is much easier when the sa password is weak. For this reason, it's very important that you use a strong sa password.
Cover All the Bases
For some vulnerabilities, there is a simple fix or a single action to protect against them. SQL injection isn't one of those vulnerabilities. Although using parameterized queries will stop SQL injection, you have to use them 100 percent of the time. Using them 99.9 percent of the time means you have at least one Web page in which a hacker can get full access to your data. Because developers are human and humans make mistakes, a layered approach will give you greatest level of safety against SQL injection.
