Wednesday 30 March 2011

How to protect code written in Stored Procedure or User Defined Function?


Introduction
We have always been wondering on how to protect/secure our t-sql code written in Stored Procedures and UDF in shared hosting environment from other people who might tweak our work. Here, I would discuss a few options I know to serve purpose.
As per my knowledge, MS SQL Server doesn’t help much with it. The possible alternatives which could be tried are –
  1.        Making Use of WITH ENCRYPTION option

The normal way of creating a stored procedure is -

CREATE PROCEDURE dbo.SimpleStoredProc
AS
BEGIN
    SELECT 'Some t-sql statements'
END

Creating the stored procedure using the WITH EXCRYPTION option is -

CREATE PROCEDURE dbo.EncryptedStoredProc
WITH ENCRYPTION
AS
BEGIN
    SELECT 'Some t-sql statements'
END

However, please make sure that the code of the SP is backed up as a separate script file for future references.

Now, when we try to use the following command to get the details of the SP -
EXEC sp_helptext 'dbo.EncryptedStoredProc'

We get the following error message –
The object comments have been encrypted.

And when we try to open this encrypted SP using SSMS, we get the following error message –

Microsoft SQL-DMO
Error 20585: [SQL-DMO]
/******
    Encrypted object is not transferable,
    and script can not be generated.
******/

Pros
a.       Once encrypted, it is not possible to decrypt using SQL Server commands.

Cons
a.       We as developers will always have to keep a copy of the SP/UDF as a script for our reference or future updates.
b.      One way I know using which this approach could be defeated is by running SQL Profiler while executing the stored procedure.
c.       Another way that users might use to get at your encrypted code is by using readily available code (if any) that allows you to break SQL Server's relatively trivial encryption algorithm. 
2.       Stop creating Stored Procedures and Functions

The very first question which comes to our mind is – if we stop creating them, then what is the alternative. We might use an of the below stated technique –
a.       Make use of Parameterized queries directly in our source code. As the code is deployed in the form of an assembly, it becomes hard to directly get into our query logic. However, de-compilation is always possible and to tackle it we can always obfuscate our assemblies. To learn more about obfuscation please refer http://msdn.microsoft.com/en-us/magazine/cc164058.aspx
b.      Secondly, we can make use of sql-clr functions wherever possible. This would again help us in hiding our logic from the preying eyes. Please refer http://msdn.microsoft.com/en-us/library/ms345136(v=sql.90).aspx for more details.

Recommendation
One should not make use of WITH ENCRYPTION option unless it’s the last option and have a thorough knowledge of its consequences after implementation. However, I would strongly recommend NOT TO USE it.

Tuesday 29 March 2011

Search MSDN or TechNet on Bing

Today, I found an interesting blog on CRM team blog which could be helpful to anyone who is wokring on Microsoft technologies. So I thought to share the same content overhere. Please visit the below link to have filtered search for Dynamics CRM 2011 SDK and implementation guide.

http://blogs.msdn.com/b/crm/archive/2011/03/22/find-sdk-and-implementation-guide-content-on-msdn-or-technet.aspx

Non CRM people can refer the below example.

How can I make new filtered search favorites?

These search filter favorites work because Bing indexes the MSDN and TechNet content using search tags embedded in the metadata of the document. Each page has several Search.MSCategory meta tags that describe the hierarchy that the page appears in the MSDN or TechNet table of contents. Once you identify the page that represents the true parent node for the portion of the MSDN table of contents, you can create a similar filtered search favorite. The tricky part is identifying that true parent node.
 
To create a filtered search favorite:
1. Navigate to MSDN or TechNet and identify a topic area you are interested in. In this example, let’s say I want to focus on the Silverlight documentation.
2. Examine the Table of contents. This works best using the “Classic” view.
3. Locate a topic below the main table of contents node. I’ll choose the XAML topic.
4. If you right click on the topic and use the context menu to choose View Source, in the area where you might expect to find meta tags you will see this:
<!-- Looking for metadata? Use the robot view instead http://currentsite/currenturl(robot).aspx -->
5. Following those instructions, open this page: http://msdn.microsoft.com/en-us/library/cc189054(VS.95)(robot).aspx
6. Now right click on the page and use the context menu to choose View Source.
7. Now you will see lots of meta tags. Look for the ones with the name value of Search.MSCategory
For this page you should find the following:
<<meta name="Search.MSCategory" content="ms310241" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="aa139615" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="cc838813" xmlns="http://www.w3.org/1999/xhtml" />
<meta name="Search.MSCategory" content="ff630996" xmlns="http://www.w3.org/1999/xhtml" />
Each of these represents the true parent in the hierarchy of the ‘anchor’ pages in the table of contents. At the top of the XAML overview page in the classic view you can see this visually:
MSDN > MSDN Library>.NET Development > Silverlight > XAML
But these anchor pages in the table of contents don’t necessary represent the true parent of the hierarchy that is indexed. To search for only Silverlight documentation we are interested in the value of the content attribute in the second meta tag from the bottom: cc838813.
If you open this page directly, (using http://msdn.microsoft.com/en-us/library/cc838813.aspx) you will see a strange page with the title @No Title, but this strange page is actually the root table of contents node according to the meta tags.
8. Now that you have identified the ‘true’ parent, With that page number you can create a search filter:
[Search Terms] meta:Search.MSCategory("cc838813") site:msdn.microsoft.com
9. Go to http://www.bing.com and type the search filter into the search field. Execute the search and save the resulting page as a favorite.

You have now created a search favorite for the Silverlight documentation.

Tuesday 15 March 2011

Table Valued parameters in SQL Server 2008



Introduction
The most awaited feature has arrived - "Table-Valued parameters in SQL Server 2008". Here, I would discuss on how to use this new feature with .net.

Problem Statement
Before to the introduction of table-valued parameters in SQL Server 2008, the options for passing data from multiple rows of a DataTable to a stored procedure or a parameterized SQL command were limited. Some of the possible options for a developer until now were:
1. Make multiple parameters, with one parameter representing one column of the DataTable
2. Create XML string & then parse it in the SP
3. Create a delimiter separated string & then parse it in the SP

Also, the above stated options had to be repeated for each row....hhuuuuuuuuhh.......just imagine the round trips to the server for large DataTables or the complex logic to be implemented for parsing the parameters.

Solution with Table-Valued parameter
1. Creating Table-Valued Parameter Types
CREATE TYPE dbo.TableType AS TABLE
( Col1 int, Col2 nvarchar(50),Coln ... )


2. Create Stored Procedure with a table valued parameter
CREATE PROCEDURE dbo.StoredProc
(@TableType dbo.TableType READONLY)


NOTE : The READONLY keyword is required for declaring a table-valued parameter.

3. Use the parameter as under -
For Insert
INSERT INTO dbo.Tbl (Col1,Col2)
SELECT paramTbl.Col1, paramTbl.Col2
FROM @TableType AS paramTbl;


For Update
UPDATE dbo.Tbl
SET Tbl.Col2 = paramTbl.Col2
FROM dbo.Tbl
INNER JOIN @TableType AS paramTbl
ON dbo.Tbl.Col1 = paramTbl.Col1;


For Delete
DELETE FROM dbo.Tbl
FROM dbo.Tbl
INNER JOIN @TableType AS paramTbl
ON dbo.Tbl.Col1 = paramTbl.Col1;


4. Calling from .net Code

// Assumes connection is an open SqlConnection object.
using (connection)
{// Create a DataTable with the modified rows.
DataTable addedValues = ValuesDataTable.GetChanges(DataRowState.Added);
 // Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand("SP_Name", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@TableType ", addedValues );
tvpParam.SqlDbType = SqlDbType.Structured;
 // Execute the command.
insertCommand.ExecuteNonQuery();
}


Limitations of Table-Valued parameter
1. User-defined functions do not support Table valued parameters.
2. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.
3. SQL Server does not maintain statistics on table-valued parameters.
4. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you 
cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must 
insert the data into a temporary table or into a table variable.
5. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.
6. If you are using Entity Framework 1.0, DBType enum does not support Structured type.
As a work around, create a SQLParameter & then typecast it into DbParameter.

SqlParameter param = new SqlParameter("@TableType", addedValues );
param.SqlDbType = SqlDbType.Structured;
command.Parameters.Add(param as DbParameter);