Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / Visual Basic
Article

Decrypting Data in Crystal Reports

Rate me:
Please Sign up or sign in to vote.
4.79/5 (24 votes)
27 Dec 2003CPOL6 min read 167.4K   2.1K   33   19
How to decrypt data for Crystal Reports using formulae to automate your managed code with User Function Libraries.

Introduction

I should stress at this point that this article is in no way intended as a Crystal Reports tutorial, a lesson in Interop, Security or Cryptography, it is however a very specific outline on how to write custom functions using managed code or re-use investments made in existing managed code libraries, to help facilitate the decryption of data in a SQL Server database for use in Crystal Reports. I will however briefly state some things that will be painfully obvious to experienced Crystal users, so bare with me if that's you.

What are User Function Libraries?

User Function Libraries (UFLs) are User Defined Functions which are made accessible (via a COM interface) to programmers, using the formula editor of Crystal Reports. Programmers are capable of writing Crystal Reports without ever having to open the formula editor, however those who have used the reporting capabilities of Crystal Reports will have come to know this tool with some appreciation for its power and usefulness. Put simply, the formula editor furnishes us with programmatic control at the field level, the ability to customize formatting, derive calculation, and places familiar programming constructs such as decision, iteration and evaluation at our fingertips. By the way, you can write code targeted for the formula editor in two syntaxes, namely Crystal and Basic (most VB programmers will probably go straight for Basic for obvious reasons).

Exhibit 1.0

Image 1

What the above exhibit demonstrates is that we are using a User Function Library and that the formula editor, is for our purposes, a very useful code editor which comes flush with a standard templated set of functions, Additional Functions (that's where UFLs live) and a full set of templated operators ready to use out of the box. Here I have created a formula field which uses the the database field fldTestEncrypted (from the table tblTestEncrypt) as a parameter to the UFL rijDecryptorReconstruct. Once having created this formula with this short trivial line of code, I am able to place it in the report (probably in the Details section of the report) and for every row that the report prints, this field will be passed to a method which will decrypt the data in the database and display the plain text value for the field, in a readable format as displayed in Exhibit 2.0 and 3.0.

Exhibit 2.0 (Data Decrypted in the Crystal Report Viewer)

Image 2

What do rows 1 & 2 from the report look like in the database? See below.

Exhibit 3.0 (Encrypted Data in SQL Server)

Image 3

UFLs are Dynamic Link Libraries that expose one or more functions which are part of a COM automation server. When you install Crystal Reports for .NET, the file U2lcom.dll is installed into Crystal Decisions\1.0\Bin\ folder of your installation. U2lcom.dll is a UFL that is able to make use of COM DLL files that have been custom authored for a multitude of purposes. The caveats here are that these automation servers must be built with a COM capable language (not .NET languages that can be used to build DLL files registered for interop - according to Crystal Decisions) and you must name your DLL according to a standard naming convention defined by Crystal. We will see how to do this shortly.

Taking Advantage of System.Security.Cryptography

Because we want to leverage the FCL Cryptography libraries to encrypt our data, we are going to need to decrypt the same data with managed code. For the purpose of this article, I have chosen to create an assembly using C# and the RijndaelManaged cryptography library to both encrypt and decrypt my data.

Listing 1.0 (Managed Code Encrypts and Decrypts)

C#
using System; 
using System.Security.Cryptography; 
using System.Text; using System.IO;


namespace Dotnetstudios 
{ 
    public class CrystalEncryption 
    { 
      private static MemoryStream memStream = new MemoryStream(); 
      private string b64key = "06W9tLEMdwxKv1C6XMoel7ibhix9dPpwJqdZ+7EuMMU="; 
      private string b64iv = "dIOh4fhx5iL6JzGw6b2B1yp56jxShQqwo2U2G3GqI3s="; 
      private ASCIIEncoding textConverter = new ASCIIEncoding(); 
      private RijndaelManaged myRijndael = new RijndaelManaged();
      public CrystalEncryption(){}  


      public string CrystalEncrypt(string encData) 
      {  
        try 
        {        
            byte[] rijKey = Convert.FromBase64String(b64key);  
            byte[] rijIv = Convert.FromBase64String(b64iv);  
            myRijndael.BlockSize = 256;  
            myRijndael.KeySize = 256;  
            ICryptoTransform encryptor = 
                     myRijndael.CreateEncryptor(rijKey, rijIv);  
            MemoryStream msEncrypt = new MemoryStream();  
            CryptoStream csEncrypt = new CryptoStream(msEncrypt, 
                     encryptor, CryptoStreamMode.Write);  
            byte[] toEncrypt;  
            toEncrypt = textConverter.GetBytes(encData);  
            csEncrypt.Write(toEncrypt, 0, toEncrypt.Length);  
            csEncrypt.FlushFinalBlock();  
            byte[] encrypted;  
            encrypted = msEncrypt.ToArray();  
            string cipherText = Convert.ToBase64String(encrypted, 
                     0 , encrypted.Length);  
            return cipherText; 
        } 
        catch(Exception ex) 
        { 
                     throw ex; 
        } 
      } 
      public string CrystalDecrypt(string decData) 
      { 
        try 
        { 
            byte[] rijKey = Convert.FromBase64String(b64key); 
            byte[] rijIv = Convert.FromBase64String(b64iv); 
            byte[] encrypted; 
            encrypted = Convert.FromBase64String(decData); 
            myRijndael.BlockSize = 256; 
            myRijndael.KeySize = 256; 
            ICryptoTransform decryptor = 
              myRijndael.CreateDecryptor(rijKey, rijIv); 
            MemoryStream msDecrypt = new MemoryStream(encrypted); 
            CryptoStream csDecrypt = new CryptoStream(msDecrypt, 
              decryptor, CryptoStreamMode.Read); 
            byte[] fromEncrypt; 
            fromEncrypt = new byte[encrypted.Length]; 
            csDecrypt.Read(fromEncrypt, 0, fromEncrypt.Length); 
            string roundtrip = textConverter.GetString(fromEncrypt); 
            return roundtrip; 
        } 
        catch(Exception ex) 
        { 
            return ex.Message;  
        } 
      } 
    } 
}

Why Interop?

Very simply, the class above is not the actual UFL, (remember UFLs can only be authored in COM capable environments excluding .NET assemblies registered for Interop). Therefore what is required here is an Automation Server built in say VB or C++ (perhaps even Delphi - don't quote me on that). We are going to use the COM Automation Server to create a UFL that is a wrapper for our Managed Code Libraries that will perform the encryption and decryption. If you are using Visual Studio .NET, then all you need to do to register the managed code assembly is set the Register for COM Interop attribute in the project's property sheet and build the project.

Exhibit 4.0

Image 4

If you are not using VS.NET, then the Regasm utility tool will do the job just fine. You can find it at C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322 in a typical install.

Building the UFL

I am going to limit the example here to Visual Basic, as C++ is not my best suit. First create a Visual Basic ActiveX DLL project and add the functions that you want to be exposed by the UFL and any private ones if necessary. All of the functions you want to be available in the Crystal Environment need to be declared as PUBLIC and must return one of the following data types only: Integer, Long, Single, Double, Currency, Date, Boolean and String. You cannot use the Variant type.

Change the ActiveX DLL project name to CRUFLxxx where xxx can be any three characters and the first character being only a letter. CRUFLa87 and CRUFLrij will both work fine. The first part of the project name must be CRUFL in order for the U2lcom.dll to be able to understand the PUBLIC functions defined in your UFL.

Exhibit 5.0 (Visual Studio 6 IDE)

Image 5

Listing 2.0

VB
Public Function Reconstruct(data As String) As String 
  Dim reco
  Set reco = CreateObject("Dotnetstudios.CrystalEncryption") 
  Reconstruct = reco.CrystalDecrypt(data) 
End Function

The code above is using late binding to create an instance of our managed assemblies class. The string argument used in the CreateObject function must be fully qualified i.e. namespace.namespace.namespace.Class Name.

Finally, build the ActiveX DLL, place it in the same directory as U2lcom.dll and then register your DLL using Regsvr32.exe. If your not familiar with Regsver32.exe then either use the command window or the Run dialog in Windows to execute a command like this: regsvr32.exe "C:\Program Files\Common Files\Crystal Decisions\1.0\Bin\CRUFLrij.dll". This will register the ActiveX DLL with Windows.

Using your UFL in Crystal Reports

All that's left to do now is create a formula field that calls the UFL in your Crystal Report. Start by creating a report and then using the Field Explorer, create a New... formula field. Under the Additional Functions node in the Functions tree in the formula editor, select your UFL and the appropriate function, and select the relevant database field to pass as argument.

Exhibit 6.0 (Creating a new formula)

Image 6

Exhibit 7.0 (Writing the formula)

Image 7

Test the formula, save it and then run the report and what you should see is the encrypted data decrypted and displayed as plain text as per exhibit 2.0.

Final Word

Clearly, questions of securing your Crystal Reports should be considered, otherwise encrypting the data would seem pointless if you are going to expose the data to any prying eyes via the report. As I stated earlier, this article does not set out to discuss securing Managed Code. But I strongly advise you make sure your reports are secure, otherwise your data will be in jeopardy.

And finally I like to thank Professor Ido Millet for his help and guidance.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Australia Australia
Simon Segal resides in Melbourne Australia, is a certified MCAD, MCSD, MCDBA, MCSE, MCST BizTalk Specialist and has been working in the Software Development industry for some 10 years now. His key area of interest are distributed systems / SOA built with Microsoft technologies.

Comments and Discussions

 
QuestionMemory full. Not enough memory for operation. Pin
Member 1161579717-Apr-15 9:47
Member 1161579717-Apr-15 9:47 
AnswerRe: Memory full. Not enough memory for operation. Pin
Member 1161579721-Apr-15 4:14
Member 1161579721-Apr-15 4:14 
QuestionBug in UFL Crystal report/ VS2008 with strings longer than 254 characters Pin
Paperless26-Feb-12 3:28
Paperless26-Feb-12 3:28 
GeneralMemory full. OtherError Not enough memory for operation. Pin
syed imran azmat24-Jun-08 2:02
syed imran azmat24-Jun-08 2:02 
GeneralFind me here.... Pin
Simon Segal20-Jun-08 21:39
Simon Segal20-Jun-08 21:39 
QuestionWhat happening Pin
rickbemis6-Jul-07 5:05
rickbemis6-Jul-07 5:05 
QuestionGetting Error "Not enough memory for the operation" Pin
scshankar11-Jan-07 2:41
scshankar11-Jan-07 2:41 
AnswerRe: Getting Error "Not enough memory for the operation" Pin
Simon Segal13-Jan-07 0:30
Simon Segal13-Jan-07 0:30 
GeneralRe: Getting Error "Not enough memory for the operation" Pin
scshankar15-Jan-07 3:05
scshankar15-Jan-07 3:05 
GeneralRe: Getting Error "Not enough memory for the operation" Pin
Simon Segal16-Jan-07 21:45
Simon Segal16-Jan-07 21:45 
AnswerRe: Getting Error "Not enough memory for the operation" Pin
CVVVV4-Apr-07 21:02
CVVVV4-Apr-07 21:02 
GeneralRe: Getting Error "Not enough memory for the operation" Pin
Vari_khehra2-Feb-10 6:47
Vari_khehra2-Feb-10 6:47 
GeneralRe: Getting Error "Not enough memory for the operation" Pin
zhanga12-Mar-12 9:21
zhanga12-Mar-12 9:21 
QuestionU2lcom.dll not visible in Crystal reports Pin
Dr.Zeus6-Jan-07 6:36
Dr.Zeus6-Jan-07 6:36 
QuestionSite is not working Pin
eatwork18-Sep-06 11:38
eatwork18-Sep-06 11:38 
GeneralDon't need the VB6 Wrapper Pin
Mike Mayer29-Jul-04 16:28
Mike Mayer29-Jul-04 16:28 
GeneralRe: Don't need the VB6 Wrapper Pin
Anonymous20-Apr-05 17:28
Anonymous20-Apr-05 17:28 
GeneralRe: Don't need the VB6 Wrapper Pin
Simon Segal28-May-05 18:43
Simon Segal28-May-05 18:43 
QuestionRe: Don't need the VB6 Wrapper Pin
robrich25-Sep-05 6:56
robrich25-Sep-05 6:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.