Data Access Code

This section identifies the key review points that you should consider when you review your data access code. For more information about the issues raised in this section, see Chapter 14, "Building Secure Data Access."

• Do you prevent SQL injection?

• Do you use Windows authentication?

• Do you secure database connection strings?

• How do you restrict unauthorized code?

• How do you secure sensitive data in the database?

• Do you handle ADO .NET exceptions?

• Do you close database connections?

Do You Prevent SQL Injection?

Check that your code prevents SQL injection attacks by validating input, using least privileged accounts to connect to the database, and using parameterized stored procedures or parameterized SQL commands. For more information, see "SQL Injection" earlier in this chapter.

Do You Use Windows Authentication?

By using Windows authentication, you do not pass credentials across the network to the database server, and your connection strings do not contain user names and passwords. Windows authentication connection strings either use Trusted_Connection='Yes' or Integrated Security='SSPI' as shown in the following examples.

 "server='YourServer'; database='YourDatabase' Trusted_Connection='Yes'" "server='YourServer'; database='YourDatabase' Integrated Security='SSPI'" 

Do You Secure Database Connection Strings?

Review your code for the correct and secure use of database connection strings. These strings should not be hard coded or stored in plaintext in configuration files, particularly if the connection strings include user names and passwords.

Search for the "Connection" string to locate instances of ADO .NET connection objects and review how the ConnectionString property is set.

• Do you encrypt the connection string?

  Check that the code retrieves and then decrypts an encrypted connection string. The code should use DPAPI for encryption to avoid key management issues.

• Do you use a blank password?

  Do not. Check that all SQL accounts have strong passwords.

• Do you use the sa account or other highly privileged accounts?

  Do not use the sa account or any highly privileged account, such as members of sysadmin or db_owner roles. This is a common mistake. Check that you use a least privileged account with restricted permissions in the database.

• Do you use Persist Security Info ?

  Check that the Persist Security Info attribute is not set to true or yes because this allows sensitive information, including the user name and password, to be obtained from the connection after the connection has been opened.

How Do You Restrict Unauthorized Code?

If you have written a data access class library, how do you prevent unauthorized code from accessing your library to access the database? One approach is to use StrongNameIdentityPermission demands to restrict the calling code to only that code that has been signed with specific strong name private keys.

How Do You Secure Sensitive Data in the Database?

If you store sensitive data, such as credit card numbers , in the database, how do you secure the data? You should check that it is encrypted by using a strong symmetric encryption algorithm such as 3DES.

If you use this approach, how do you secure the 3DES encryption key? Your code should use DPAPI to encrypt the 3DES encryption key and store the encrypted key in a restricted location such as the registry.

Do You Handle ADO .NET Exceptions?

Check that all data access code is placed inside try / catch blocks and that the code handles the SqlExceptions , OleDbExceptions or OdbcExceptions , depending on the ADO .NET data provider that you use.

Do You Close Database Connections?

Check that your code is not vulnerable to leaving open database connections if, for example, exceptions occur. Check that the code closes connections inside a finally block or that the connection object is constructed inside a C# using statement as shown below. This automatically ensures that it is closed.

 using ((SqlConnection conn = new SqlConnection(connString))) {   conn.Open();   // Connection will be closed if an exception is generated or if control flow   // leaves the scope of the using statement normally. }