SQL Server 2005 - Hacking password Encryption

December 28, 2007

Part 1 of this article series illustrated the ENCRYPTION by passphrase and DECRYPTION by passphrase mechanism. Part 2 of this article discusses how to hack/de-cipher the encrypted data encrypted by passphrase.

As we all know, “ENCRYPTION by passphrase” is basically encrypting the data using a password and can be decrypted using the same password. Now, when we forget the password, it is a hectic process to get the data back.

Step 1

Let’s encrypt the data using the ENCRYPTION by passphrase mechanism as shown below.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
--------------------------------------------------------------------------
0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC

Step 2

Now let’s create the procedure that will hack the encrypted data. This procedure uses the “DecryptByPassPhrase” function to decrypt the data and display the password.

USE [Master]
GO
 
/****** Object:  StoredProcedure [dbo].[hack_encryption]    
	Script Date: 12/18/2007 18:18:36 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption]') 
	AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[hack_encryption]
GO
set nocount on
SET CONCAT_NULL_YIELDS_NULL OFF
go
USE [Master]
GO
 
/****** Object:  StoredProcedure [dbo].[hack_encryption]    
	Script Date: 12/18/2007 18:18:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE procedure [dbo].[hack_encryption] @encryptedtext varbinary(max)
as
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int
 
 
set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''
 
while @i<255
begin
   while @j<255
   begin
       while @k<255
       begin
                 while @l<255
                 begin
                   while @m<255
                   begin
                       while @n<=255
                       begin
                       set @password=isnull(char(@i),'') 
		+ isnull(char(@j),'')
		+isnull(char(@k),'')+ isnull(char(@l),'')
		+isnull(char(@m),'') + isnull(char(@n),'')
                       if convert(varchar(100),
		DecryptByPassPhrase(ltrim(rtrim(@password)),
		@encryptedtext)) is not null
                       begin
                       print 'This is the Encrypted text:' 
			+@password
                       set @i=256;set @j=256;set @k=256;set 
			@l=256;set @m=256;set @n=256;
                       print 'The actual data is :' 
		+convert(varchar(100),
		DecryptByPassPhrase(ltrim(rtrim(@password)),
		@encryptedtext)) 
                       end
                       --print 'A'+ltrim(rtrim(@password))+'B'
                       --print convert(varchar(100),
		DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                       set @n=@n+1
                       end
                   set @n=0
                   set @m=@m+1
                   end
          set @m=0
          set @l=@l+1
          end
       set @l=0
       set @k=@k+1
       end
   set @k=0
   set @j=@j+1
   end
set @j=0
set @i=@i+1
end
 
 
GO

Please download code from here.

Step 3

Let’s assume that we forgot the password for the encrypted data style="color: #993300; background: transparent;"“0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC”.

We can retrieve the password and the encrypted data by using the above written procedure as shown below.

use master
go
select getdate() as StartingTime
go
declare @myencryptedtext varbinary(max)
set @myencryptedtext=0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC
print @myencryptedtext
exec hack_encryption @encryptedtext=@myencryptedtext
go
select getdate() as EndingTime
go

Result

StartingTime
-----------------------
2007-12-18 18:24:10.843
 
0x01000000F75D553409C74570F6DDBCADA53FD489DDD52D9277010050565ADF30F244F8CC
This is the Encrypted text:   MAK
The actual data is :123456789
 
EndingTime
-----------------------
2007-12-18 18:26:36.080

Fig 1.0

As you can see from the result [Refer Fig 1.0], it took 2 minutes to retrieve the data and password.

Basically, this procedure iterates through all the possible combinations of ascii characters up to 6 character length to find the password and uses the password to decrypt the data.

Creating a procedure will not help that much when you have the encrypted data on a table. So let us update this procedure as a scalar function as shown below.

Step 1

Create the following procedure as shown.

USE [master]
GO
 
/****** Object:  UserDefinedFunction [dbo].[hack_encryption_password]    Script Date: 12/18/2007 18:36:29 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption_password]')
	AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[hack_encryption_password]
GO
use [Master]
go
 
CREATE function [dbo].[hack_encryption_password] (@encryptedtext varbinary(max))
returns varchar(6)
with execute as caller
as
begin
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int
 
 
set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''
 
while @i<255
begin
    while @j<255
    begin
        while @k<255
        begin
            while @l<255
            begin
                while @m<255
                begin
                    while @n<=255
                    begin
                    set @password=isnull(char(@i),'') + isnull(char(@j),'')
                    +isnull(char(@k),'')+ isnull(char(@l),'')
                    +isnull(char(@m),'') + isnull(char(@n),'')
                    if convert(varchar(100),DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext)) is not null
                    begin
                    --print 'This is the Encrypted text:' +@password
                    set @i=256;set @j=256;set @k=256;set @l=256;set @m=256;set @n=256;
                    --print 'The actual data is :' +convert(varchar(100),
                    DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    end
                    --print 'A'+ltrim(rtrim(@password))+'B'
                    --print convert(varchar(100),DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    set @n=@n+1
                    end
                set @n=0
                set @m=@m+1
                end
            set @m=0
            set @l=@l+1
            end
        set @l=0
        set @k=@k+1
        end
    set @k=0
    set @j=@j+1
    end	
set @j=0
set @i=@i+1
end
 
return @password
END

Please download code from here.

Step 2

Let’s create a table with encrypted data as shown below.

USE [tempdb]
GO
/****** Object:  Table [dbo].[MyTable]    Script Date: 12/18/2007 18:44:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
create table MyTable(id int, encrypteddata varbinary(max))
go
insert into MyTable select 1, EncryptByPassPhrase('Do', '1112228333')
insert into MyTable select 2, EncryptByPassPhrase('Re', '1212223833')
insert into MyTable select 3, EncryptByPassPhrase('Me', '1132223393')
insert into MyTable select 4, EncryptByPassPhrase('Fa', '1114223383')
insert into MyTable select 5, EncryptByPassPhrase('So', '1112523333')
insert into MyTable select 6, EncryptByPassPhrase('La', '1112263373')
insert into MyTable select 7, EncryptByPassPhrase('Si', '1112227338')
go

Step 3

Now let’s query the data using the following transact SQL Statement.

Select * from MyTable

You would see the data as shown below. [Refer Fig 1.1]

1          0x01000000D8ED1498BEA4023D541C6EA9766A6B7B0585FAE91B942C88C23677550C6FD7FA
2          0x01000000F0725A52501A41D125F049011BE87C5C4A42263E7538B837B8278ADEE5FC2678
3          0x01000000C8804D8516B944B0AE35C71F79130DA415CED5CCF58E522692AC749115EEF0D9
4          0x010000007A91A24638C0E0354336AE5682805312CCB0B1E6BBACB6D9E65DC5D9DA73906E
5          0x010000008FB6BDD91C3D1A8C94FAF647DE1F931CEE5104045BD03DE4E809565E74604DF3
6          0x01000000C3A41428A21EDE8D8579AF9C42132678448A9113A31A869276A7631A58A32BE3
7          0x01000000BD829E12D3EAAF96BB66930301BA1D9CD3748946F354301922A03AE49047FE00

Fig 1.1

Step 4

Use the hack_encryption_password function to retrieve all the passwords from the encrypted data from the table MyTable. Execute the following transact SQL statement.

select ID ,master.[dbo].[hack_encryption_password] (encrypteddata) as Password from MyTable

You will see the results as shown below. [Refer Fig 1.2]

1            Do
2            Re
3            Me
4            Fa
5            So
6            La
7            Si

Fig 1.2

The above function can be modified to return the encrypted data as well, as shown below.

Step 1

Create the following function.

USE [master]
GO
 
/****** Object:  UserDefinedFunction [dbo].[hack_encryption_password]    Script Date: 12/18/2007 18:36:29 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[hack_encryption_data]')
	AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[hack_encryption_data]
GO
use [Master]
go
 
CREATE function [dbo].[hack_encryption_data] (@encryptedtext varbinary(max))
returns varchar(8000)
with execute as caller
as
begin
declare @data varchar(8000)
declare @password varchar(6)
declare @i int
declare @j int
declare @k int
declare @l int
declare @m int
declare @n int
 
 
set @i=-1
set @j=-1
set @k=-1
set @l=-1
set @m=-1
set @n=-1
set @password =''
 
while @i<255
begin
    while @j<255
    begin
        while @k<255
        begin
            while @l<255
            begin
                while @m<255
                begin
                    while @n<=255
                    begin
                    set @password=isnull(char(@i),'') + isnull(char(@j),'')+isnull(char(@k),'')
					+ isnull(char(@l),'')+isnull(char(@m),'') + isnull(char(@n),'')
                    if convert(varchar(100),DecryptByPassPhrase(ltrim(rtrim(@password)),
					@encryptedtext)) is not null
                    begin
                    --print 'This is the Encrypted text:' +@password
                    set @i=256;set @j=256;set @k=256;set @l=256;set @m=256;set @n=256;
                    set @data = convert(varchar(100),
					DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    end
                    --print 'A'+ltrim(rtrim(@password))+'B'
                    --print convert(varchar(100),
					DecryptByPassPhrase(ltrim(rtrim(@password)),@encryptedtext))
                    set @n=@n+1
                    end
                set @n=0
                set @m=@m+1
                end
            set @m=0
            set @l=@l+1
            end
        set @l=0
        set @k=@k+1
        end
    set @k=0
    set @j=@j+1
    end
set @j=0
set @i=@i+1
end
 
return @data
END

Please download code from here.

Step 2

Let’s decrypt the data using the function we created as shown below.

select ID ,master.[dbo].[hack_encryption_data] (encrypteddata) as Password from MyTable

The result is shown below. [Figure 1.3]

Fig 1.3

Note:

a. The procedure and the functions can hack only a 6 character length password. There is enough to optimize this procedure.

b. This procedure and function can take lot of CPU time to hack the data and retrieve the password.

Conclusion

As mentioned in the beginning of the article, these are small procedures and functions to hack the encrypted data and retrieve the password and data.

» See All Articles by Columnist MAK

SQL Server 2005 - Hacking password Encryption

Conclusion

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos