DES Encryption/Decryption Function

,

There is a great article on SQLServerCentral on an Extended Stored Procedure http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp and this XP will undoubtedly perform better than my Function. However I had a need to encrypt a column in a shared hosted environment where I was not allowed to install XPs. Just copy script and paste into Query Analyzer

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_PwdCrypt]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fn_PwdCrypt]

GO

 

CREATE FUNCTION dbo.fn_PwdCrypt(@ptSource varchar(255), @ptPassword varchar(255))

 

RETURNS varbinary(64)

BEGIN

 

            DECLARE @tdest                                 varchar(255),

                                    @lteller                         int,

                                    @lPasswTeller   int,

                                    @cnt                            int,

                                    @len                             int,

                                    @tFinal                         varbinary(64)

 

            SELECT @tFinal = Convert(varbinary(64),'')

            SELECT @len = Len(@ptSource)

            SELECT @lteller = 1

            SELECT @tdest = @ptSource

            SELECT @lPasswTeller = 0

 

            WHILE @lteller <= @len

            BEGIN

            SELECT @lPasswTeller = @lPasswTeller - 1

            If @lPasswTeller < 1 

                                    SELECT @lPasswTeller = Len(@ptPassword)

 

                        SELECT @tfinal = @tfinal + Convert(varbinary(64),Stuff(Substring(@tdest, @lteller, 1),1,Len( Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ) )

                        SELECT @lteller = @lteller + 1

            END

            RETURN @tfinal

 

 

 

END

 

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

 

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_PwdDeCrypt]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fn_PwdDeCrypt]

GO

 

CREATE FUNCTION dbo.fn_PwdDeCrypt(@ptSource varchar(255), @ptPassword varchar(255))

 

RETURNS varchar(255)

BEGIN

 

            DECLARE @tdest                                 varchar(255),

                                    @lteller                         int,

                                    @lPasswTeller   int,

                                    @cnt                            int,

                                    @len                            int,

                                    @tFinal                         varchar(255)

 

            SELECT @tFinal = ''

            SELECT @len = Len(@ptSource)

            SELECT @lteller = 1

            SELECT @tdest = @ptSource

            SELECT @lPasswTeller = 0

 

            WHILE @lteller <= @len

            BEGIN

            SELECT @lPasswTeller = @lPasswTeller - 1

            If @lPasswTeller < 1 

                                    SELECT @lPasswTeller = Len(@ptPassword)

 

                        SELECT @tfinal = @tfinal + Left(Stuff(substring(@tdest, @lteller, 1),1,Len( Char(Ascii(substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1)))), Char(Ascii(Substring(@ptSource, @lteller, 1)) ^ Ascii(Substring(@ptPassword, @lPasswTeller, 1))) ), 1)

                        SELECT @lteller = @lteller+1

            END

            RETURN @tfinal

 

END

 

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

 

--Test it here

create table #temp

            ( pwd varbinary(64))

 

insert into #temp (pwd)

select dbo.fn_PwdCrypt ('MyPassword','secret')

 

select pwd from #temp

select dbo.fn_PwdDeCrypt (pwd,'secret') from #temp

drop table #temp

Rate

Share

Share

Rate