Solved

Need help getting encoding function to work

Posted on 2007-12-04
6
301 Views
Last Modified: 2012-05-05
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
Comment
Question by:pamsauto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20405714
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
 

Author Comment

by:pamsauto
ID: 20405749
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20405854
How about

select * from IndexList where dbo.decrypthn( Encryptedcode ) = '116-02105R'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:pamsauto
ID: 20405927
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20406238
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
 

Author Comment

by:pamsauto
ID: 20406335
Excellent - I can sure handle it from there!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question