70-340 Implementing Security for Applications with Microsoft Visual C# .NET Practice Exams

Demo Question 2.

You are an application developer for EliteCertify.com. You are developing a client application that queries a Microsoft SQL Server database. The application uses an unmanaged component to retrieve data from another application, and your application uses that data as part of a SQL query. In the application code, you use a variable named externalobject to refer to the unmanaged component. A variable named calcval contains an integer value that is calculated by your application. A SQLCommand object named sqlcmd is already defined and associated with an open ADO.NET connection to the SQL Server database. The application contains the following code segment. string myquery ; myquery = "INSERT INTO DataStore (Exterbal ID, CalcValue) ' ; myquery + = "Value (" + externalobject. LegacyDatra + ", " ; myquery + = calcval.ToString( ) + ") " ; sql cmd. CommandText = myquery ; sql cmd. ExecuteNonQuery ( ) ; You need to improve the security of this code segment. What should you do?

A. Place the code segment within a try-catch block.
B. In the code segment, ensure that the value of externalobject.LegacyData meets the length and type requirements of the SQL Server table.
C. Validate the externalobject.LegacyData contains only expected data and no additional SQL statements.
D. Copy the contents of externalobject.LegacyData into a string variable, and append the string variable to the SQL statement.


Display Answer

Answer: C

Explanation: It is best for the approach that you must validate and cleanse data before it is used and/or store. Rule number two is: data must be validated as it crosses the boundary between untrusted and trusted environments. By definition, trusted data is data you or an entity you explicitly trust has completecontrol over; untrusted data refers to every else. In short, any data submitted by a user is initially untrusted data. When it comes to SQL statements, all dynamic SQL is bad, and paramterized store procedures must be used. Dynamic SQL can be easily compromised and used for SQL injection attacks. All relational databases-including SQL Server, Oracle, IBM DB2, and MySQL-are susceptible to SQL-injection attacks. You can buy products that protect your system from SQL injection, but for most businesses, the defense against SQL-injection attack must be code-based. The opening for SQL-injection attacks comes primarily through Web applications that combine user input with dynamic SQL to form SQL commands that the application sends to the database. Here are four important steps you can take to protect your Web applications from SQL-injection attacks. In addition to the following tips, the Microsoft Patterns and Practices Library that I highlighted last month provides advice about securing your data-access applications. 4. Principle of Least Privilege The account an application uses to connect to the database should have only the privileges that application requires. The security permissions that an intruder gains from a compromised application define the harm that the intruder can inflict. Applications shouldn't connect as sa or with the Administrator account. Instead, the account should have permissions to access only the database objects it needs. 3. Validate All Input If an input field should contain numeric data, then verify that users enter only numbers. If character data is acceptable, check for unexpected characters. Make sure your application looks for characters such as semicolons, equals signs, double dashes, brackets, and SQL keywords. The .NET Framework provides regular expressions that enable complex pattern matching, a good way to test user input. Limiting the length of accepted user input is also a good idea. Validating your input might seem obvious, but many applications are vulnerable to SQL-injection attacks because intruders can use the openings that Web applications offer. 2. Avoid Dynamic SQL Dynamic SQL is a great tool for performing ad hoc queries, but combining dynamic SQL with user input creates exposure that makes SQL-injection attacks possible. Replacing dynamic SQL with prepared SQL or stored procedures is feasible in most applications. Prepared SQL and stored procedures accept user input as parameter data rather than as SQL commands, thus limiting what an intruder can do. Of course, replacing dynamic SQL with a stored procedure won't help you if you use the user input to build dynamic SQL statements in your stored procedures. In that case, the dynamic SQL that the user input creates will still be corrupted, and your database will still be in danger of SQL-injection attack. 1. Use Double Quotes Replace all the single quotes that your users' input contains with double quotes. This simple precaution will go a long way toward warding off SQL-injection attacks. Single quotes often terminate SQL expressions and give the input more power than is necessary. Replacing the single quotes with double quotes will cause many SQL-injection attacks to fail.