In some cases, you need to ensure that if a user submits special characters, your application will handle them correctly. As the example attack shows, the single-quote character is of particular interest because it lets an attacker close an open quotation, providing a spot to introduce a new string in your system's code. The solution in this case is to ensure that if a user types a single quote in a string, the new quote will be escaped—instead of treating the character as a typical single quote, the code will treat it as just another character in the string. To escape a single quote, you simply match it with a second single quote. An easy way to do this is to use the Replace() method on a string:
TextBox1.Text.Replace (" ' "," ' ' ")
This command should contain a single quote only in the first set of double quotes and a pair of single-quote marks in the second set of double quotes. This command replaces each instance of a single-quote mark with two single-quote marks, thus escaping them from the SQL execution sequence. You can escape other special characters in a similar fashion.
The accompanying challenge is that you need to place this character-screening command in your code. One option is to add a transform such as the previous command at the points where your application accepts user input—which might mean inserting the command in hundreds of locations in your code. Alternatively, you can execute this screening just before the code calls your SQL Server. Of course, if you haven't centralized your data access, then you might still have to screen at hundreds of locations in a large application's code.
Implementing character screening becomes easy when all your data-access commands go through one set of components, the Data Access Layer Components (DALC). The Microsoft Data Access Application Block is an excellent model for your data-access logic, but plan to customize it for your own situation.
Even if you've been using more than one set of data-access components, you can implement a design that funnels all your database calls through one set of data-access classes. In addition to forcing all your database access logic to go through a custom screening process, you can use the DALC to implement other best practices. For example, you can use the DALC to limit developers to writing code that accesses only stored procedures—not dynamic SQL. One of the primary requirements of SQL injection attacks is the use of dynamic SQL. Stored procedures don't suffer the same risks as dynamic SQL queries, because unlike dynamic queries, which SQL Server interprets on the fly, a stored procedure is partially interpreted in advance. When you submit dynamic SQL, you're submitting a string, which SQL Server then interprets from start to finish. However, a stored procedure works with named variables. Each of these variables holds a place for a value within your stored procedure's code. As a result, SQL Server can isolate the variables from the code and avoid inadvertently executing embedded values. So if a user attempts to submit an embedded command string as one of the parameters in a stored procedure, the stored procedure won't execute the commands.
Ensuring that stored procedures handle all your data access is one of the best methods of protecting your data from SQL injection attacks. Unfortunately, it's not foolproof. If your code uses the EXEC command along with the sp_ExecuteSQL stored procedure, you essentially reopen the security hole because you're permitting the passing of strings, which SQL Server then interprets.
One final way to prevent exposure is to be sure your database doesn't give potential intruders error messages that can help them bypass your default logic. An error message such as Username is correct but password is incorrect provides an intruder with valuable information. A simple Invalid logon message tells users that the credentials are invalid without revealing to an intruder whether the username was valid.
At a more complex level, handling error messages correctly means preventing your system from returning actual SQL Server error-message text. By default, ASP.NET doesn't return such error messages to remote connections. Your application should politely inform users that an error has occurred, but users should never see the original message associated with an invalid query, which might contain the submitted query. Developers working on Web projects sometimes add code that returns a submitted query in the error message so that they can debug why the query didn't work. However, the error message can help a hacker debug how to attack your system.
The solutions I've shown you so far focus on how to design, configure, and implement your application so that it doesn't expose your database to an attack. Now, let's look at solutions you can implement in your database's configuration.
Limiting Database Permissions
The first step toward database security in most developers' minds is protecting the connection string. None of the solutions I've suggested thus far have mentioned the connection string because the connection string isn't part of this attack. The application already has access to your connection string; the injection attack works by manipulating the data that the application sends across that connection to your database. Although you might hide the connection string under several layers of encryption, you still need to presume that the connection string can fall into enemy hands. What have you done to limit damage if that occurs? You need to set up the accounts users employ to access the database with only the permissions they need to access the stored procedures the application uses.
This is where database roles come into play. Many developers would prefer to simply use the sa account for easy access during development, but I hope you've not allowed the production application to have sa permissions on your system. Some developers create a custom account for development, but then grant that account full permissions or a broad set of permissions such as the built-in SQL Server db_datareader and db_datawriter roles. These roles are better than the alternative of using an account with administrator privileges, but in most cases, they provide only limited protection against the ravages of SQL injection.
Prev. page
1
2
[3]
4
next page 
