| Farhan Uddin's profileFarhan Uddin's spacePhotosBlogNetwork | Help |
|
|
June 22 Using OLE Automation to Create a User Defined SQL Function that Validates Regular Expressions.In my previous post I explained on how to create a CLR Enabled SQL User Defined Function to Validate Regular Expressions. However that is not the only option to use regular expression in SQL Server. You can also use OLE Automation procedures to create a user defined function that will do the same job for you. I’ll now show you how to create this function.
Step1: Enable OLE Automation in SQL Server (Not required if you are using SQL Server 2000).
OLE Automation Procedures are disabled by default in SQL Server 2005. To enable OLE Automation procedures in SQL Server 2005 execute the statements below in query analyzer.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
For more information on configuring OLE automation procedures. Plesae visit http://technet.microsoft.com/en-us/library/ms191188.aspx.
Step2: Create the function that uses OLE Automation Procedures to Create the Instance of Required Object.
We will be using the Regex object of VBScript. For more info on VBScripts Regex object please visit http://msdn.microsoft.com/en-us/library/ms974570.aspx. Execute the code below in query analyzer to create the function.
Create Function dbo.RegX ( @Input varchar(8000), @Pattern varchar(255) ) Returns int AS Begin
Declare @ObjRegx Int Declare @Result Int Declare @Match Int
Set @Match = 0
-- Creating object RegExp. -- sp_OACreate is used to create the automation object. Exec @Result = sp_OACreate 'VBScript.RegExp', @ObjRegx OUT If (@Result <> 0) Begin Return Null End
-- Setting up objects pattern property. -- sp_OASetProperty is called to set the property of the object Exec @Result = sp_OASetProperty @ObjRegx, 'Pattern', @pattern If (@Result <> 0) Begin Return Null End
-- Begin: Setting up objets IgnoreCase property to true. Exec @Result = sp_OASetProperty @ObjRegx, 'IgnoreCase', 1 If (@Result <> 0) begin Return Null End
-- [Test] method is called on the object to validate the expression. -- sp_OAMethod is used to call the method on object. Exec @Result = sp_OAMethod @ObjRegx, 'Test', @Match OUT, @Input If (@Result <> 0) Begin Return Null End
-- Clean up. sp_OADestroy is used to destroy the automation object. Exec @Result = sp_OADestroy @ObjRegx Return @Match End
Once the function is created successfully you can test it using the following example. Select dbo.RegX ('Farhan@Hotmail.com', '\w+([-+.'']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*')
Note: To learn more on automation procedures please visit http://msdn.microsoft.com/en-us/library/aa172803(SQL.80).aspx.
Creating CLR SQL User Defined Function to Validate Regular Expressions.This article will explain how to validate your values on the basis of a regular expression in a user defined SQL function. SQL server does not provide any function for this purpose but we can write a CLR enabled function to fulfill our purpose.
Step1: Write code in .Net
The following code will create a .Net SQL function that will validate values on the basis of a regular expression. Create a new Class library project with the name ClrFunctions. Add a new class (Functions) to the project and copy / paste the code below to the class file.
using System; using System.Collections.Generic; using System.Text; using System.Text.RegularExpressions; using System.IO; using System.Data.SqlClient; using Microsoft.SqlServer.Server; using System.Data.SqlTypes;
namespace ClrFunctions { public partial class Functions { /// <summary> /// Validates a value based on expression pattern. /// </summary> /// <param name="value">input to be checked</param> /// <param name="pattern">regular expression</param> /// <returns></returns> [SqlFunction(Name="RegEx")] public static SqlBoolean RegEx(SqlString value, SqlString pattern) { Regex ObjRegx = new Regex((string) pattern); Match ObjMatch = ObjRegx.Match((string) value); return (SqlBoolean)ObjMatch.Success; } } }
Compile your projec in release mode. You will now notice that there will be a DLL in your Project\bin\Release folder.
Step2: Using the Function in SQL Server
Once you have compiled your dll do the following to import your dll into SQL Server.
2.1 Enable CLR.
SQL server is not configured to use CLR code by default. To enable CLR write the following code in SQL query analyzer and press F5 (execute). sp_Configure 'CLR Enabled', 1 Reconfigure With Override
2.2 Write Code to Import your DLL.
Create Assebmly ClrFunctions From 'E:\MyProjects\Dll\ClrFunctions.dll' -- Note: Change the path according to the loacation of your DLL.
2.3 Write Code to Create the User Defined Function.
Create Function dbo.RegEx( @Input As nvarchar(4000), @Expression As nvarchar(1000) ) Returns Bit As External Name ClrFunctions.[ClrFunctions.Functions].RegEx
Step4: Test Your Function
Once you are successful in configuring you DLL. Use the following statement to test your function. The example below will test the email format supplied. Select dbo.RegEx('Farhan@Hotmail.com', '\w+([-+.'']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*')
Step5: Unloading DLL from SQL Server
You may want to unload the .Net DLL and function that you created in SQL Server. In this case do the following.
Drop Function dbo.RegEx Drop Assembly ClrFunctions
Note: For more information on creating CLR enabled user defined functions please see the follwing URL’s.
June 07 Talking about Using HTTP Handler to Generate CAPTCHA (Part - III).
Before reading this please make sure that you have read the following.
Implement HTTPHandler to Intercept Requests.
To create a custom Hypertext Transfer Protocol (HTTP) handler, you create a class that implements the IHttpHandler interface to create a synchronous handler or the IHttp-AsyncHandler to create an asynchronous handler. Both handler interfaces require you to implement the IsReusable property and the ProcessRequest method. The IsReusable property specifies whether the IHttpHandlerFactory object (the object that actually calls the appropriate handler) can place your handlers in a pool and reuse them to increase performance or whether it must create new instances every time the handler is needed. The ProcessRequest method is responsible for actually processing the individual HTTP requests.
The following is the implementation of our handler class. ProcessRequest method is executed as soon as any *.jpg request is encountered on the page but in our case we will restrict it to execute only when it has the request for captcha.jpg (explained later).
public class ImageHandler : IHttpHandler { public ImageHandler() {} #region IHttpHandler Members public bool IsReusable { get { return false; } }
public void ProcessRequest(HttpContext context) { string code = Helper.DeCrypt(context.Request["vCode"].ToString()); context.Response.ContentType = "image/jpeg"; Helper.DrawTextImage(context, code); } #endregion }
As you can see the ProcessReqeust method is fairly simple. First it receives the code from query string and then decrypts it then content type is set for the response and finally the decrypted code is send to the DrawTextImage in the Helper class which will do the rest.
Configure IIS to pass requests for the required image types to ASP.NET.
For performance reasons, IIS only passes requests for specific file types to ASP.NET.For example, IIS passes requests for .aspx, .axd, and .axd to the Aspnet_Isapi.dll file that performs the ASP.NET processing. For all other file types, including .htm, .jpg,and .gif, ASP.NET simply passes the file from the file system directly to the client browser. Therefore, to handle image requests using ASP.NET, you must configure an IIS application mapping from the image file extension you need to the Aspnet_Isapi.dll file. To configure this, follow these steps:
1. Open the IIS Manager. 2. View the Web site or virtual folder properties. 3. Click the Directory tab, and then click Configuration. 4. On the Mappings tab of the Application Configuration dialog box, click Add. 5. In the Add/Edit Application Extension Mapping dialog box, add an association for the image file extension to the aspnet_isapi.dll file, in our case .jpg. You can copy the executable path from the .aspx file. Deselect the Check That File Exists check box. Then click OK.
Configure ASP.NET to process requests for files with the required file extensions
After you configure IIS to forward image requests to ASP.NET, you must configure a handler for the file extension in your Web.config file. For each file extension or file name you want to register, create an <add> element in the <configuration><system. web><httpHandlers> section of your Web.config file, as the following example demonstrates:
<system.web> <httpHandlers> <add verb="*" path="*.jpg" type="ImageHandler"/> </httpHandlers> </system.web>
In this example, ASP.NET handles requests for files ending in .jpg by forwarding them to the ImageHandler class. For this to work properly, the ImageHandler assembly must be available in the application’s Bin folder or the source code must be in the App_Code folder.
In our case I will use the path=”Captcha.jpg” as I don’t want to forward other requests for images but Captcha.jpg.
Implement the Code on an Asp Form.
This section deals with the implementation of the CAPTCHA code on the page. For the simplicity reasons I will stick to the basics.
<!—HTML section of the codeà
<form id="form1" runat="server"> <div> <asp:Label ID="LblMsg" runat="server" style="color:Red"></asp:Label><br /> <asp:Image ID="ImgCaptcha" ImageUrl="" runat="server" /><br /> <asp:TextBox ID="TxtCode" runat="server"></asp:TextBox><br /> <asp:Button ID="BtnSubmit" Text=" Verify " runat="server" OnClick="BtnSubmit_Click" /> </div> </form>
// Code behind
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DrawCaptcha(); } }
private void DrawCaptcha() { Session["vCode"] = Helper.GetRandomizeString(5); // Generating text of length 5 ImgCaptcha.ImageUrl = "/Captcha.jpg?vCode=" + Server.UrlEncode(Helper.EnCrypt(Session["vCode"].ToString())); }
protected void BtnSubmit_Click(object sender, EventArgs e) { if (String.Compare(Session["vCode"].ToString(), TxtCode.Text) != 0) { LblMsg.Text = "Please enter a correct verification code."; DrawCaptcha(); } }
As you can see that we are dynamically assigning the value to ImageUrl propertly so when the page is rendered it will have a string something like “/Captcha.jpg?vCode=%3AXJRBCDESSFT” (Encrypted code). The request for this image is automatically forwared to our ImageHandler class and CAPTCHA is generated at our page.
Note: There a other ways to generate captcha for example using an .ashx handler (this will save you from configuration in IIS and web.config) which also implements IHttpHandler but my purpose was to explain the request forwading using HTTP handler. Talking about Using HTTP Handler to Generate CAPTCHA (Part-II).
Before reading this please make sure that you have read the following.
Write code to dynamically generate the image.
In our case we will be writing code to generate the image with some random text. The following code can be replaced with any CAPTCHA generation code. I’m adding a helper class which has image generation code. You can add this class to you application.
using System.Drawing; using System.Drawing.Text; using System.Drawing.Imaging; using System.Drawing.Drawing2D; using System.Text; using System.Security.Cryptography;
public class Helper { public Helper() {}
#region Encryption / Decryption
public static string EnCrypt(string data) { MemoryStream Ms = new MemoryStream(); Rfc2898DeriveBytes PasswordKey; SymmetricAlgorithm Algo; CryptoStream CrStream;
try { string Password = "Password"; string SaltString = "Salt";
Algo = new RijndaelManaged(); PasswordKey = new Rfc2898DeriveBytes(Password, System.Text.Encoding.ASCII.GetBytes(SaltString)); Algo.Key = PasswordKey.GetBytes(Algo.KeySize / 8); Algo.IV = PasswordKey.GetBytes(Algo.BlockSize / 8);
ICryptoTransform EnCryptor = Algo.CreateEncryptor(); Ms = new MemoryStream(); CrStream = new CryptoStream(Ms, EnCryptor, CryptoStreamMode.Write); byte[] Data = System.Text.Encoding.ASCII.GetBytes(data); CrStream.Write(Data, 0, Data.Length); CrStream.Close();
return Convert.ToBase64String(Ms.ToArray()); } finally { if (Ms != null) { Ms.Close(); Ms.Dispose(); } }
}
public static string DeCrypt(string data) { MemoryStream Ms = new MemoryStream(Convert.FromBase64String(data)); Rfc2898DeriveBytes PasswordKey; SymmetricAlgorithm Algo; CryptoStream CrStream;
try { string Password = "Password"; string SaltString = "Salt";
Algo = new RijndaelManaged(); PasswordKey = new Rfc2898DeriveBytes(Password, System.Text.Encoding.ASCII.GetBytes(SaltString)); Algo.Key = PasswordKey.GetBytes(Algo.KeySize / 8); Algo.IV = PasswordKey.GetBytes(Algo.BlockSize / 8);
ICryptoTransform Decryptor = Algo.CreateDecryptor(); CrStream = new CryptoStream(Ms, Decryptor, CryptoStreamMode.Read); StreamReader Sr = new StreamReader(CrStream); return Sr.ReadToEnd();
} finally { if (Ms != null) { Ms.Close(); Ms.Dispose(); } } }
#endregion
#region Captcha Generation /// <summary> /// Draws captcha on 25 X 161 Image /// </summary> public static void DrawTextImage(System.Web.HttpContext context, string code) { Rectangle Rect = new Rectangle(0, 0, 161, 25); Bitmap Bmp = new Bitmap(161, 25); Graphics Gr = Graphics.FromImage(Bmp); // Creating Background for image Brush HatchBr = new HatchBrush(HatchStyle.OutlinedDiamond, Color.Black, Color.White); Gr.FillRectangle(HatchBr, Rect); Random R = new Random(); Char[] Data = code.ToCharArray(); try { int x = 10; // Used to add some spaces b/w characters. int Angle = 0; for (int i = 0; i < Data.Length; i++) { Angle = R.Next(-15, 15); Bitmap Img = GetRotatedImage(Data[i].ToString(), Angle); Gr.DrawImage(Img, x, 3); x += (Img.Width + i + 3); Img.Dispose(); } Bmp.Save(context.Response.OutputStream, ImageFormat.Jpeg); } finally { Bmp.Dispose(); Gr.Dispose(); HatchBr.Dispose(); } }
/// <summary> /// Rotates the input string on an angle specified on 25 X 25 image. /// </summary> private static Bitmap GetRotatedImage(string r, int angle) { int FontSize = 13; string FontName = "Arial"; Bitmap Bmp = new Bitmap(25, 25); Bmp.MakeTransparent(); Graphics Gr = Graphics.FromImage(Bmp); System.Drawing.Font Fn = new System.Drawing.Font(FontName, FontSize, FontStyle.Regular); Matrix M = new Matrix();
try { M.Rotate(angle); Gr.Transform = M; Gr.DrawString(r, Fn, Brushes.Black, 2, 2); } finally { Fn.Dispose(); M.Dispose(); Gr.Dispose(); } return Bmp; }
/// <summary> /// Gives you randomized string of specified length /// </summary> public static string GetRandomizeString(int length) { string Code = string.Empty; Random R = new Random(); int Num = 1; for (int i = 1; i <= length; i++) { switch (R.Next(1, 3)) { case 1: Num = R.Next(65, 91); break; case 2: Num = R.Next(49, 58); break; } Code += Convert.ToChar(Num).ToString(); } return Code; }
#endregion
} }
At this point you might be wondering why I’ve added the encryption / decryption routines. Well it is explained later in the article (Using HTTP Hander to Generate CAPTHCHA (Part - III)) Using HTTP Handler to Generate CAPTCHA (Part-I).
Each image in an HTML page requires a separate browser request and a separate response from your Web server. By default, IIS does not pass requests for images to ASP.NET. Instead, IIS simply reads the image file from the file system and sends it directly to the Web browser.
In some cases, you might want to handle requests for images in ASP.NET instead. For example, generating CAPTCHA image dynamically or you might want to dynamically create thumbnails in a photo album application.
To generate images dynamically you can configure ASP.NET to receive requests for images and generate the images dynamically for each request. To configure ASP.NET to receive requests for images, follow these steps:
1. Write code to dynamically generate the images. 2. Implement HTTPHandler to intercept requests. 3. Configure IIS to pass requests for the required image types to ASP.NET. 4. Configure ASP.NET to process requests for files with the required file extensions. 5. Implement the code on an .aspx page.
I’ll now explain the steps. Due to the length of the article I had to divide it into 3 parts. For more info on this article please see the following...
Using HTTP Handler to Generate CAPTCHA (Part-II).
Using HTTP Handler to Generate CAPTCHA (Part-III). |
|
|