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

Comments (2)
  1. Anonymous says:
    1. Regex check ID不需要,用parameter传递不会有注入风险,这个逻辑不应该在DAL出现

    2. Exception handling太土。。。throw出去,callstack在这里就hide掉了怎么debug??

    3. C#有一个语法叫做using

    4. C#防了这么多,结果password明文在注册表里。。。。。。。。。。。。。。。。。。。。雷,既然用了SQL Server, 就不要用password!

  2. MSDN Archive says:

    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.

Comments are closed.

Skip to main content