Blog Archives
Custom DAL Class SQL ORM ASP .NET
(common.DataObject may be of your choosing or may simply replace with dynamic)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Data.SqlClient;
using AIS.Common; //this is a common namespace I use in examples throughout my site
using System.Reflection;
using System.Dynamic;
//TODO: consider returning ienumerable in sp return values for lazy eval vs .tolist immediate eval
namespace AIS.DAL.AppName
{
public static class StoredProcedures
{
public delegate void ErrorHandler(Exception ex);
/// <summary>
/// If no custom error handling is bound to this event, exceptions will be thrown back up to the calling function.
/// If custom handling is bound to this event, ensure it does not perform a redirect or kill the thread unless you intend to abort the procedural
/// steps following the method/function call which threw the error.
/// </summary>
public static event ErrorHandler HandleError;
#region Unique Procedures
public static List<Common.DataObject> LoadUserSessions_All(dynamic o)
{
return ExecuteRead("an_get_db_fn1", o);
}
public static List<Common.DataObject> LoadUserSessionsDetails_LiveStream(dynamic o)
{
return ExecuteRead("an_get_db_fn2", o);
}
public static List<Common.DataObject> LoadUserSessionsDetails_Live(dynamic o)
{
return ExecuteRead("an_get_db_fn3", o);
}
public static int LogChat()
{
return ExecuteScalar("an_get_db_fn4", null);
}
public static int LogError()
{
return ExecuteScalar("an_get_db_fn5", null);
}
#endregion
//TODO: consider hiding from external assemblies which would require strong mappings above
#region Execution Logic
public static List<Common.DataObject> ExecuteRead(string procedurename, dynamic param)
{
try
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
sds.SelectCommand = procedurename;
if (param != null)
{
foreach (PropertyInfo pi in param.GetType().GetProperties())
{
object pval = pi.GetValue(param, null);
if (pval != null)
{
sds.SelectParameters.Add(pi.Name, pval.ToString());
}
}
}
List<Common.DataObject> results = new List<Common.DataObject>();
//sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<dynamic>(o, ref results));
sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<Common.DataObject>(o, ref results));
return results;
}
catch (Exception ex)
{
HandleError_Condensed(ex);
return null;
}
}
public static void Load_AddResult<t>(Common.DataObject o, ref List<t> results)
{
try
{
t r = (t)Activator.CreateInstance(typeof(t));
foreach (PropertyInfo pi in typeof(t).GetProperties())
{
object v = o[pi.Name].ToString();
Type pt = Type.GetType(pi.PropertyType.FullName);
//try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
//catch (Exception ex) { HandleError_Condensed(ex); }
o.Add(pi.Name, Convert.ChangeType(v, pt));
}
results.Add(r);
}
catch (Exception ex)
{
HandleError_Condensed(ex);
}
}
//public static void Load_AddResult<t>(dynamic o, ref List<t> results)
//{
// try
// {
// t r = (t)Activator.CreateInstance(typeof(t));
// foreach (PropertyInfo pi in typeof(t).GetProperties())
// {
// object v = o[pi.Name].ToString();
// Type pt = Type.GetType(pi.PropertyType.FullName);
// try { pi.SetValue(r, Convert.ChangeType(v, pt), null); }
// catch (Exception ex) { HandleError_Condensed(ex); }
// }
// results.Add(r);
// }
// catch (Exception ex)
// {
// HandleError_Condensed(ex);
// }
//}
public static void ExecuteNonScalar(string procedurename, dynamic param)
{
try
{
ExecuteScalar(procedurename, param);
}
catch (Exception ex)
{
HandleError_Condensed(ex);
}
}
public static int ExecuteScalar(string procedurename, dynamic param)
{
try
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = ConfigValues.TrainingPortalConnectionString;
sds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
sds.UpdateCommand = procedurename;
if (param != null)
{
foreach (PropertyInfo pi in param.GetType().GetProperties())
{
object pval = pi.GetValue(param, null);
if (pval != null)
{
sds.SelectParameters.Add(pi.Name, pval.ToString());
}
}
}
return sds.Update();
}
catch (Exception ex)
{
HandleError_Condensed(ex);
return 1; //1 signifies error in tsql
}
}
#endregion
private static void HandleError_Condensed(Exception ex)
{
if (HandleError != null) { HandleError(ex); } else { throw new Exception(ex.Message, ex); }
}
}
}
How to Create secure relational databases in SQL
The first step to a secure DB, is a well structured DB and the application layers that access it.
Take the following example of a table containing factories and the robots they produce.
| FACTORYNAME | ROBOTNAME |
| FACTORY1 | Ron |
| FACTORY1 | John |
| FACTORY2 | Bob |
| FACOTRY2 | Bill |
This serves a good purpose by itself, and allows for easy manipulation and viewing of the stored data, but this table could very quickly grow out of hand. What if you needed to store the parts that are required to make each robot? Or if some factories share the types of robots that they can create?
First, lets separate the data into two separate tables of factories and robots:
“SP_GET_FACTORIES”
| FACTORYID | FACTORYNAME |
| 1 | FACTORY1 |
| 2 | FACTORY2 |
“SP_GET_ROBOTS”
| ROBOTID | ROBOTNAME |
| 1 | Ron |
| 2 | John |
| 3 | Bob |
| 4 | Bill |
In order to group these two together. we now require a mapping table.
“FactoryRobotMapping”
| FACTORYID | ROBOTID |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
To see this data in a single set, like your original table, you would run a select statement with a join. Some programmers put these statements with joins directly in their code as plain text while others keep them in the database.
Relatively newer technologies, such as Microsoft Linq or Ruby have their own syntax for performing these joins and queries which is ultimately translated to plain sql behind the scenes.
Unfortunately, this often leads to a common misconception that because you are using Linq, Ruby or another data access layer that your work ends there.
Consider the following scenario:
App A->retrieves a join of factories and robots
App B->also retrieves the same join
To make this simpler, you create a new project (this would be your data access layer) and add a new method called “GetRobotsInFactories” which uses Linq, plain sql, ROR or some other technology to run the select with the join.
So now you have:
App A->DAL->GetRobotsInFactories
App B->DAL->GetRobotsInFactories
For future changes, simply modify GetRobotsInFactories, upload the DLL and that’s it! Or is it?
What if a new business change now requires you to only get robots in factories after specific manufacture dates?
You could just add an optional parameter to GetRobotsInFactories, but now you have to code in conditional logic to your function checking if the parameter is null, and forming a different query based on this. If there are 10 more parameters like this, your function could now very quickly become much more troublesome to support, especially when certain combinations of parameters may be required.
This creates a weaker structure, and therefore opens the door to security vulnerabilities. The approach on separating the DAL into a new project and wrapping everything in here is great, but you must be very careful on how your DAL accesses the DB.
What’s the alternative?
Views are joins
For starters, creating views in the database for your joins will save you from having to constantly type out select statements with joins and improve your productivity in database debugging greatly.
An example of such join for our above is:
“VW_JOIN_FACTORIES_ROBOTS”
select * from factories inner join robots on factories.factoryid = robots.robotid
This now also allows you to perform specific select statements against the view, which will look very similar to your first table you created before segregating the data.
Cool, now what?
You might consider plugging in a simple “select * from VW_JOIN_FACTORIES_ROBOTS” into that GetRobotsInFactories function, but not done yet.
Stored Procedures offer security
Many developers understand the minimal amount of SQL required to power their app, or have heard of or created and modified views as well as stored procedures, but often are not aware of the security benefits.
Stored procedures encapsulate the data you need to retrieve. This allows you to control not only the tables being modified, but the way they are modified, dependencies, constraints, parameters and even database permissions on who can execute or modify which stored procedures and ultimately obfuscate your underlying data from would be attackers or meddlers.
Consider the following stored procedure:
“SP_GET_ROBOTS_IN_FACTORIES”
USE [RobotCo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIED ON GO CREATE PROCEDURE [dbo].[RobotCo] (@MfgDate datetime) AS BEGIN SET NOCOUNT ON; select * from VW_JOIN_FACTORIES_ROBOTS where datetime between @MdfDate and getdate() END
You can now change your “GetRobotsInFactories” function to call “SP_GET_ROBOTS_IN_FACTORIES” and simply pass in date as a parameter to the SP. Future changes will only require you to add parameters to the SP and then simply update the DAL/dbml or definition if you’re using linq/ror/etc.
Locking it down
All structures are already in place! You’re next steps are to make sure the applications have their own SQL user accounts with only access to execute the specified stored procedures.
Without direct access to the view or the tables, exploitation of the app will prevent attackers from retrieving or manipulating the data directly.
Hopefully this offered some insight on database level security, as well as understanding some of the benefits of view and SP utilization for better overall software architecture.
References
Microsoft Linq, http://msdn.microsoft.com/en-us/netframework/aa904594
Ruby on Rails, http://rubyonrails.org/
Out of Office Reply 