Principles for Building Secure Database Applications in Action

What I am talking about in this post might be well known to many people(too simple, sometimes naive?), but often most basic things make a difference. OK, get down to business. Thumbs rules for DB security might be:

  • Define your security boundary(or attack surface)
  • All input is evil! Evaluate them with whitelist
  • Don't store blank password, even hard-coded in the source
  • Put DB in a dedicated server and access it with accounts with least privilege
  • Put connection string in registry and read it out from code
  • Use stored procedure
  • The attacker is told nothing
  • Save your resources
  • Specify least assembly permission requirements with attributes

FxCop is obviously a “must-have” for .NET developer, but we have to eliminate complaints one by one. Instead of remembering all “bad behavior” in various tutorials, why not make them our built-in features towards great developers? (if you are still developers, why not much better? ) Let us put most significant principles into simple sample lines of code. Pay special attention to highlighted words.

// <THIS IS UPDATED ON 2/5/2009 PER FEEDBACKS>  

using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using System.Security.Principal;

using System.Security.Permissions;

using System.Text.RegularExpressions;

using System.Threading;

using System.Web;

using Microsoft.Win32;

namespace Sample

{

public class SecureDBAppSample

{

[SqlClientPermissionAttribute(SecurityAction.PermitOnly,

AllowBlankPassword = false)] // (1) Blank password is never allowed

[RegistryPermissionAttribute(SecurityAction.PermitOnly,

Read = @"HKEY_LOCAL_MACHINE\SOFTWARE\MyApp")] // (2) Can read only one specific registry key

static string GetName(string Id)

{

string Status = "Name Unknown";

try

{

// (3) Check for valid shipping ID with white list

// 4-10 digist only, anything else is bad. In most production environment,

// inputs check should be done in attack boundary instead. Of course we can check

// it here for defensive programming efforts

Regex r = new Regex(@"^\d{4,10}$");

if (!r.Match(Id).Success)

{

throw new Exception("Invalid ID");

}

// (8) Shut down connection--even on failure.

using (SqlConnection sqlConn = new SqlConnection(ConnectionString))

{

//Add shipping ID parameter.

// (4) Use a store procedure to hide the application business logic

// in case the code is compromised

string str = "sp_GetName";

// (8) Release resources--even on failure.

using (SqlCommand cmd = new SqlCommand(str, sqlConn))

{

cmd.CommandType = CommandType.StoredProcedure;

// (5) Use parameters, instead of string concatentation to build the query

// (6) Force the input to be 64 bits integer

cmd.Parameters.Add("@ID", Convert.ToInt64(Id));

cmd.Connection.Open();

Status = cmd.ExecuteScalar().ToString();

}

}

}

catch (Exception e)

{

// TODO: For better debugging purpose, we need log the exception with

// something like Logger.Log(e);

// (7) On error, the attacker is told nothing

if (HttpContext.Current.Request.UserHostAddress == "127.0.0.1")

{

Status = e.ToString();

}

else

{

Status = "Error Processing Request";

}

}

return Status;

}

//Get connection string.

internal static string ConnectionString

{

get

{

// (9) Store connection string in registry key intead of xml files

return (string)Registry

.LocalMachine

.OpenSubKey(@"SOFTWARE\MyApp\")

.GetValue("ConnectionString");

}

}

}

}

The data in registry key is the connection string.

Data Source=MyDb008; // (10) DB is on remote server.

                         // Compromised web service does not lead to SQL data access automatically

Integrated Security=SSPI;// (11) Use Windows authentication

Initial Catalog=client

In stead of storing plain text, we can encrypt above connetion string. Keep in mind that I don’t say that they are necessarily the best choice at all times, but many times they are. 

Reference: Write Secure Code