SQL Server 2005 – Hacking password Encryption

Script Date: 12/18/2007 18:18:36 ******/
IF EXISTS (SELECT * FROM sys.objects

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles