Security in ADO.NET

This article gives a sound idea how to write secure code for ADO.NET. Data Access Layer (DAL)  is a common and very curtail  for your application. Its very important know some of the basic security points while writing ADO.NET program.

1. One of the key point of security is "never ever trust on user inputs". You must validate the user's data properly before process. The hacker always tries to crash your application through malicious inputs (especially dynamic SQL statements). As a developer you must take care of all vulnerable inputs pass through SQL statements for example lets says you are trying to search customer details by taking the customer name as input and you are build a dynamic SQL to fetch the details from SQL Server, if you do not validate the user's input and directly  process can cause a heavy damage to your application assume the user (smart user) pass the customer name as "1;DROP TABLE Cust". The code snippet will be as below:

1. 
   string strQuery = "SELECT * from Cust WHERE custName="+txtCustName.Text;
   SqlCommand cmd = new SqlCommand( strQuery, conn);
   conn.Open();
   SqlDataReader myReader = cmd.ExecuteReader();
   myReader.Close();
   conn.Close();
   
   The solution to the above problem is validate such vulnerable before execute the query. 
2. The next point is parameterize store procedures. This is a convenient way to safeguard your application against SQL injection attacks, make sure your stored procedures or methods  accept only  values not the SQL statements  and also recommend to validate the user inputs as explained in above point before execute.
   
   
3. Use Regex to validate user input for a particular format (pattern)  the other way it helps quickly parse large  amount of text to find specific character patterns, also help to edit or replace or delete text substring. For example  to validate the input value should have 5 character alphanumeric string.
   
   public void CheckString(string inputValue)
   {
   Regex rg = new Regex("^[A-Za-z0-9]{5}$");
   return rg.IsMatch(inputValue)
   }
   
   
4. One of the way a  hacker can reach your database or  data source  through system generated exception. The most keep point for everyone is do not display complete   system exception information to the user, display only required exception information  to client, suggest to implement exception wrapping or replace to display custom exception by hiding the actual  database exception. To know more about exception management click here.
   
   
5. The other key point is never ever try to connect to database through user name and password in plain text it is a serious vulnerable i.e if the  user name and password is a part of your source code that can be exploited by disassemble the IL code. This is the big plus point for the hacker to play with your application .When connecting to Microsoft SQL Server it is  highly recommended to use Integrated Security, which uses the identity of the current active user rather than passing a user name and password. Do not forget to set  Persist Security Info to true or yes this allow security sensitive information including the user name  and password  to be obtained from the connection after the connection has been opened.

These are the some of the basic security points every body should keep in mind  while working with ADO.NET or database.