Articles

Current Articles | Categories | Search | Syndication

By Jonathan Kehayias @ Monday, July 21, 2008 7:05 PM :: 78 Views :: 0 Comments

I have seen more than a few posts on the various forums I play on, where the original poster has attempted to catalog the System.Web assembly into SQL as UNSAFE:

CREATE ASSEMBLY SystemWeb 
FROM 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.web.dll'
WITH PERMISSION_SET = UNSAFE

There are a number of problems with this decision aside from the fact that in a number of cases it just won't work.  First, this is not an approved assembly, so loading it into SQL can open you up to support problems.  Second, there is a lot of code in this assembly that you can't and won't ever use in SQL Server.  I have seen a number of posts for Out of Memory exceptions while trying to create the assembly, problems with the assembly failing to load after a server reboot, as well as other various issues.  If you actually do create this assembly here is the output from the CREATE Assembly call:

Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.directoryservices.protocols, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.web.regularexpressions, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.serviceprocess, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.configuration.install, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

This alone should be motivation enough to not load this assembly just for this singular class, but it hasn't proven to be yet, since there are no other options except to load this assembly to consume this class, or are there?

So far I have created the two most commonly used methods from the HttpUtility Class as SAFE CLR User-Defined Functions, to provide answers to posts where I recommended that the logic be abstracted into a SAFE implementation and was asked how someone might do that. The way to do this applies to any class in the .NET core framework, and only requires a minimal amount of programming knowledge in the .NET languages. 

One of my favorite tools in .NET is Lutz Roeder's Reflector.  This is a very powerful tool that allows you to open any CLR Assembly and view the source code behind classes and their methods and properties.  Using it to look at the HtmlEncode and HtmlDecode methods shows that they both implement .NET unsafe memory pointers.  These are never going to work in SQL Server, but all is not lost.  You can replace the pointer based loops with standard for loops and have similar performance with an identical result.  You can find the code for both the HtmlEncode and HtmlDecode methods in the examples area:

Replacing Unsafe System.Web HttpUtility.HtmlDecode with SAFE SQL CLR User-Defined Function
Replacing Unsafe System.Web HttpUtility.HtmlEncode with SAFE SQL CLR User-Defined Function

The above functions were written and tested in a .NET Winforms application that had 3 text boxes on a single form.  The first text box was a code input box to be either encoded or decoded.  The second box was used to display the encoded/decoded text from the System.Web class, and the third box was used to display the encoded/decoded text from my class.  Then the two were compared in CLR to determine if they were identical.  In 20+ test cases they all passed as being equal.  If there happens to be an issue from my code, please let me know, and I will happily look at it. 

Hopefully this not only shows you how to safely perform these common tasks, but also provides you the methodologies to build SQL SAFE implementations of important classes and methods that you might have otherwise cataloged as UNSAFE.  If you happen to have another example of a method in .NET that requires you to catalog a assembly in the Base Class Library as UNSAFE in SQL, please leave a comment as I am interested to see if I can reproduce it in a SAFE SQL Implemenation.

Posted @ Monday, July 21, 2008 7:05 PM by Jonathan Kehayias
Previous Page | Next Page
Comments
Currently, there are no comments. Be the first to post one!
You must be logged in to post a comment. You can login here

Survey

Which of the following CLR objects are you currently using in SQL Server?




Submit Survey  View Results

Links


Copyright 2007 by SQLCLR.net Terms Of Use Privacy Statement
Website graphics provided by Matt Green Designs
Page generated in 0.2343705 seconds.

All information and example code on this site is provided 'as-is', without any express or implied warranty. In no event will the authors be held liable for any damages arising from its use.

This site is in no way affiliated with Microsoft. Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.