70-340 Implementing Security for Applications with Microsoft Visual C# .NET Online Class

Demo Question 10.

You are an application developer for EliteCertify.com. You are modifying an application that was developed by another developer. The application was developed by using the Microsoft .NET Framework. The application accepts user input into a variable named userinput and saves information in a Microsoft SQL Server database. The application uses the following code to construct a SQL query string sqlquery sqlquery = "SELECT FROM Table1 WHERE ColumnA = '" + userinput = " ' " ; You need to improve the security of the application to reduce the likelihood of SQL injection attacks from user input. Which three actions should you perform for all user input? (Each correct answer presents part of the solution. Choose three)

A. Remove double hyphens (--) from the input.
B. Encode the input by using the HttpUtility.HTMLEncode method.
C. Decode the input by using the HttpUtility.HTMLDecode method.
D. Limit the length to the exact size of the SQL Server column where the input will be stored.
E. Replace single quotation marks (') with two single quotation marks (").
F. Convert all characters to Unicode.


Display Answer

Answer: A, D, E

Explanation: Preventing SQL Injection AttacksIf you design your scripts and applications with care, SQL injection attacks can be avoided most of the time. There are a number of things that we as developers can do to reduce our site's susceptibility to attack. Here's a list (in no particular order) of our options: Limit User Access The default system account (sa) for SQL server 2000 should never be used because of its unrestricted nature. You should always setup specific accounts for specific purposes. For example, if you run a database that lets users of your site view and order products, then you should set up a user called webUser_public that has SELECT rights on the products table, and INSERT rights only on the orders table. If you don't make use of extended stored procedures, or have unused triggers, stored procedures, user-defined functions, etc, then remove them, or move them to an isolated server. Most extremely damaging SQL injection attacks attempt to make use of several extended stored procedures such as xp_cmdshell and xp_grantlogin, so by removing them, you're theoretically blocking the attack before it can occur. Escape Quotes As we've seen from the examples discussed above, the majority of injection attacks require the user of single quotes to terminate an expression. By using a simple replace function and converting all single quotes to two single quotes, you're greatly reducing the chance of an injection attack succeeding. Using ASP, it's a simple matter of creating a generic replace function that will handle the single quotes automatically, like this: <% function stripQuotes(strWords) stripQuotes = replace(strWords, "'", "''") end function %> Now if we use the stripQuotes function in conjunction with our first query for example, then it would go from this: select count(*) from users where userName='john' and userPass='' or 1=1 --' ...to this: select count(*) from users where userName='john'' and userPass=''' or 1=1 --' This, in effect, stops the injection attack from taking place, because the clause for the WHERE query now requires both the userName and userPass fields to be valid. Remove Culprit Characters/Character Sequences As we've seen in this article, certain characters and character sequences such as , --, select, insert and xp_ can be used to perform an SQL injection attack. By removing these characters and character sequences from user input before we build a query, we can help reduce the chance of an injection attack even further. As with the single quote solution, we just need a basic function to handle all of this for us: <% function killChars(strWords) dim badChars dim newChars badChars = array("select", "drop", ";" ", "--", "insert", "delete", "xp_") newChars = strWords for i = 0 to uBound(badChars) newChars = replace(newChars, badChars(i), "") next killChars = newChars end function %> Using stripQuotes in combination with killChars greatly removes the chance of any SQL injection attack from succeeding. So if we had the query: select prodName from products where id=1; xp_cmdshell 'format c:/q/yes '; drop database my DB; -- and ran it through stripQuotes and then killChars, it would end up looking like this: prodName from products where id=1 cmdshell ''format c: /q /yes '' database myDB ...which is basically useless, and will return no records from the query. Limit the Length of User Input It's no good having a text box on a form that can accept 50 characters if the field you'll compare it against can only accept 10. By keeping all text boxes and form fields as short as possible, you're taking away the number of characters that can be used to formulate an SQL injection attack. If you're accepting a querystring value for a product ID or the like, always use a function to check if the value is actually numeric, such as the IsNumeric() function for ASP. If the value isn't numeric, then either raise an error or redirect the user to another page where they can choose a product. Also, always try to post your forms with the method attribute set to POST, so clued-up users don't get any ideas --- they might if they saw your form variables tacked onto the end of the URL. SQL Server 2000 SP3 Security Features and Best Practices: Security Best Practices Checklist Administrator Checklist Developer ChecklistIn addition to all of the items above, the following should be considered best practices for developers. Software Vendor ChecklistIn addition to all of the items above, the following security development practices have proven useful in increasing the quality and security of code in various development environments.