Share via

Principles for Building Secure Database Applications in Action

  • Article

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

Comments

  • Anonymous
    February 04, 2009
    Regex check ID不需要，用parameter传递不会有注入风险，这个逻辑不应该在DAL出现 Exception handling太土。。。throw出去，callstack在这里就hide掉了怎么debug?? C#有一个语法叫做using C#防了这么多，结果password明文在注册表里。。。。。。。。。。。。。。。。。。。。雷，既然用了SQL Server, 就不要用password!

  • Anonymous
    February 04, 2009
    Thanks for the great inputs and most of your points are well taken. I will go ahead to update the sample. Although this is so called SAMPLE code for security, we should definitely follow other best practice.