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.


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



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


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

        static string GetName(string Id)


            string Status = "Name Unknown";



                // (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));


                        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 == "")


                    Status = e.ToString();




                    Status = "Error Processing Request";



            return Status;



        //Get connection string.

        internal static string ConnectionString




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

                return (string)Registry








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