Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Need help getting encoding function to work

OK, I have two functions in SQL.   Once function encodes text into a binary data type and the other decodes the data.   This all seems to work great except for when the text has an "R" on the end.   Here is the code for both functions.

CREATE FUNCTION [dbo].[DecryptHN] (@InputString  varchar(11))  
RETURNS varchar (12)
 AS  
BEGIN

declare @ctr tinyint
declare @master varchar(32)
declare @TempKey varchar(255)
declare @Result varchar(32)
declare @FinalResult varchar(12)
declare @Tchar int
declare @Mchar int
declare @strlen int


select @strlen =  LEN(RTRIM(@InputString))

set @TempKey = 'cktm091795.'

set @ctr = 1
set @Result = ''

WHILE @ctr <= @strlen
   BEGIN
   set @Mchar = ASCII(SUBSTRING(@InputString, @ctr, 1))
   set @Tchar = ASCII(SUBSTRING(@TempKey, @ctr, 1))
   set @Result = @Result + cast(char(@Mchar ^ @Tchar)as varchar(32))
   SELECT @ctr = @ctr + 1
   if @ctr > @strlen
     BREAK
 
   END
   Set @FinalResult=LEFT(@Result,3)+'-'+RTRIM(SUBSTRING(@Result,4,7))+SUBSTRING(@Result,11,1)
Return (@FinalResult)

END

and the other function.


CREATE FUNCTION [dbo].[EncryptFullHN] (@InputString  char(11))  
RETURNS binary (11)
 
BEGIN

declare @ctr tinyint
declare @master varchar(32)
declare @TempKey varchar(255)
declare @Result varchar(32)
declare @Tchar int
declare @Mchar int
declare @BinResult binary(11)

set @TempKey = 'cktm091795.'

set @ctr = 1
set @Result = ''
WHILE @ctr <= datalength(@InputString)
   BEGIN
     set @Mchar = ASCII(SUBSTRING(@InputString, @ctr, 1))
     set @Tchar = ASCII(SUBSTRING(@TempKey, @ctr, 1))
     set @Result = @Result + cast(char(@Mchar ^ @Tchar)as varchar(32))
     SET @ctr = @ctr + 1
END

set @BinResult = CAST (@Result AS binary(11) )

Return (@BinResult)


END


To test this you can run this SQL.

select dbo.encryptfullhn('11602105R')

it should return 0x525A425D0208010219157C
but it returns    0x525A425D020801026B150E

What am I missing here?



0
pamsauto
Asked:
pamsauto
  • 3
  • 3
1 Solution
 
imitchieCommented:
There is no difference, because your input string is not 11 chars, the end becomes undefined.
Try

select dbo.decrypthn( 0x525A425D020801026B150E )
select dbo.decrypthn( dbo.encryptfullhn( '11602105R  ' ) )

you will see that the result is correct
0
 
pamsautoAuthor Commented:
It does give results in that test that would seem to be ok,but the issue is I have a table of encrypted codes, and I encrypt the text and then use that in a select statement, so I need the results to be exact of course.   The function is used like this.

select * from IndexList where Encryptedcode=dbo.encryptfullhn('11602105R')

0
 
imitchieCommented:
How about

select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pamsautoAuthor Commented:
Yeah, except that the table has 8 million rows in it and it would take quite a while to run that query.........   What do you mean the end becomes undefined in your first answer?   I have to be honest, I don't undersatnd how the encoding works, and if I did I could probably figure this out.
0
 
imitchieCommented:
Okay, the problem is that there could be MORE than one EncryptedCode that will decrypt to a particular string, i.e.

select dbo.decrypthn( 0x525A425D0208010219157C )
select dbo.decrypthn( 0x525A425D020801026B150E )

both return '116-02105R'
due to this line in decrypt:
   Set @FinalResult=LEFT(@Result,3)+'-'+RTRIM(SUBSTRING(@Result,4,7))+SUBSTRING(@Result,11,1)

If the string contains SPACES in the raw decrypted result between positions 5-10, those spaces disappear.  To get 0x525A425D0208010219157C, use

select dbo.encryptfullhn('11602105  R')

So in fact,

select * from IndexList where Encryptedcode=dbo.encryptfullhn('11602105R')

CORRECTLY returns no results, because 0x525A425D0208010219157C is not encrypted from that string
0
 
pamsautoAuthor Commented:
Excellent - I can sure handle it from there!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now