Problem with XorC Byte function

[carpking]

Hi, I am using a XorC Byte function as part of an encryption macro. The problem is that the function is resulting in a Line feed or New line character in the encrypted string which is sent to a text file and when decrypted causes problems. eg...

serial = 1741833591
 
insertdatastring = sdait,hs8it,rf88i,s88fi,ni888,888cg,8u888,888l8,8t888
 
encoded = XorC(insertdatastring, serial)
 
Function XorC(ByVal sData As String, ByVal sKey As String) As String
    Dim l As Long, i As Long, byIn() As Byte, byOut() As Byte, byKey() As Byte
    Dim bEncOrDec As Boolean
    'confirm valid string and key input:
    If Len(sData) = 0 Or Len(sKey) = 0 Then XorC = "Invalid argument(s) used": Exit Function
    'check whether running encryption or decryption (flagged by presence of "xxx" at start of sData):
    If Left$(sData, 3) = "xxx" Then
        bEncOrDec = False   'decryption
        sData = Mid$(sData, 4)
    Else
        bEncOrDec = True   'encryption
    End If
    'assign strings to byte arrays (unicode)
    byIn = sData
    byOut = sData
    byKey = sKey
    l = LBound(byKey)
    For i = LBound(byIn) To UBound(byIn) - 1 Step 2
        byOut(i) = ((byIn(i) + Not bEncOrDec) Xor byKey(l)) - bEncOrDec 'avoid Chr$(0) by using bEncOrDec flag
        l = l + 2
        If l > UBound(byKey) Then l = LBound(byKey)  'ensure stay within bounds of Key
    Next i
    XorC = byOut
    If bEncOrDec Then XorC = "xxx" & XorC  'add "xxx" onto encrypted text
End Function

Once the function has been run, you will see that the encrypted string contains a line feed or new line character.

Can anyone explain why and how to ensure that it doesnt occur for a variable serial as it is read from the PC harddrive ?

 

[sijpie]

You are working with bytes and so your code will now and again produce a CHR10 or CHR13 code (line feed or carriage return). This will show up if you work with the result as a string. It should still decode OK.

 

[carpking]

Hi sijpie, you are correct about the CHR10 or CHR13. However I am reading the encrypted info out of the text file, line by line and so the decryption fails. Is there a way to ensure that the code will never produce the CHR10 or CRH13 code?

Perhaps you can explain which combination of characters and numerics will result in Chr10 and Chr13, then i can run a test for these combinations and write some sort of exclusion rule.

Thanks for your help!

 

[sijpie]

I suggest you do your read in binary, then it doesn't matter what the characters are.

If I understand it correctly, you encode whatever and store it in a "text"file. Then you read the file again as a string to decode it.

Why are you worried as to how it displays? Why would you need to send it to the screen or to a printer?

 

[carpking]

let me explain, the client has requested a macro that will take the contents of cells in a input spreadsheet, row by row and store them in a text file in an encrypted format, to be decrypted line by line and placed into another report worksheet. So my macro reads a row on the spread sheet, builds an array, inserts the number 8 to ensure each array item has the same length and then sends it together with the serial to the XorC function for encryption. The encrypted line is then written to the text file and then loops.

So during decryption, each line is taken out of the text file and decrypted and sent to the new spreadsheet, thus an encrypted string that spans more than one line is fragmented and and becomes problematic.

Basically it all works fine except for cases which result in CHR10 or CRH13, so all i need to do is find a way to avoid these.

How would i convert the insertdatastring to binary? Would the XorC function still work with the binary data?
once decrypted, how would i convert the binary data back to a comma delimited string ?

 

[Richard Schollar]

My word that function brings a memory back from the dim & distant past!

Your best bet (which is a variant of sijpie suggested) would be to write fixed length strings to the text file. The size of the fixed length string is determined by the maximum length of all cells contained within one row in your sheet that you need to encrypt. You the pad any other concatenated rows out to the same length using eg space characters. This means that all of your rows that you write to the text file will be the same length.

The consequence of the above would be that you can then read the text file using binary access every 100 characters (or whatever your maximum row size was). That way you always know you have read an entire Excel row equivalent (then it doesn't matter if the string contains lineFeeds part way thru).

Does that make sense?

 

[carpking]

Hi Richard, thankyou for your help. Let me see if i understand ...

I am already checking my array from which i make my string, for the maximum no of characters and fleshing out the rest with "8's" to ensure a uniform length. Now i need to compare the lines that need to be encrypted and find a maximum length for the input string and flesh out those strings that are shorter.

I utilise the function as normal and if a combination of the serial and string results in a CHR10 or CRH13 then the string which is exported to the text file will contain a line break. This however will not be of consequence since the code will read the text file using binary access utilising the maximum length of the input string and not each physical line seperately. ...is that correct.

It definately sounds like the solution, my only question is ... assuming that the maximum length of the input string is 1000 how do i achieve the binary access of the text file 1000 chars at a time (ensuring that the second iteration of the loop starts at char 1001 and ends at 2000 etc etc ) and would i need to write the data to the text file in a special way ... my current code reading from the text file is

Const PTH = "C:\Documents and Settings\shaunb\Desktop\enquiries2.txt"
ff = FreeFile
strtext = Space(FileLen(PTH))
Open PTH For Binary Access Read As #ff
  Get #ff, , strtext
Close #ff
origarray = Split(strtext, vbCrLf)
serial = CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber
For line = 0 To (UBound(origarray) - 1)
    teststring = Application.Substitute(origarray(line), Chr(34), "")
decoded = XorC(teststring, serial)

and here is my current code for writing to the text file ...

Set ExpRng = Selection
NumCols = ExpRng.Columns.count
NumRows = ExpRng.Rows.count
filename = "C:\Documents and Settings\shaunb\Desktop\enquiries2.txt"
Open filename For Output As #1
For r = 1 To NumRows
For c = 1 To NumCols
data = ExpRng.Cells(r, c).Value
If IsNumeric(data) Then data = Val(data)
If IsEmpty(ExpRng.Cells(r, c)) Then data = ""
If c <> NumCols + 1 Then
'Write #1, data;
If r > 0 And c = 1 Then
insertdatastring = ""
End If
    If insertdatastring = "" Then
    insertdatastring = insertdatastring & data
    Else
    insertdatastring = insertdatastring & "," & data
    End If
    
    End If
Next c
myArray = Split(insertdatastring, ",")
Call encode
For i = LBound(Arr2) To UBound(Arr2)
'check for even array element
If i Mod 2 = 0 Then
reverse = StrReverse(Arr2(i))
Arr2(i) = reverse
End If
Next i
insertdatastring = Join(Arr2, ",")
serial = CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber

encoded = XorC(insertdatastring, serial)
Write #1, encoded
Next r
Close #1

Please could you explain any code changes that you recommend

Thanks again

 

[sijpie]

In your file read code you have

origarray = Split(strtext, vbCrLf)

this looks for the Char(10) or Char(13) characters in your string and splits them there into bits.

You need to write a piece of code that feeds origarray strtext split in fixed length pieces.

 

[carpking]

Hi Sijpie,

I understand ... can you suggest any code that will do the trick. Remember though that each iteration of the loop needs to take the previous loop into account. So assuming that the input string length is 100 chars, the first loop needs to extract chars 1 to 100 from the text file, loop 2 chars 101 to 200 etc etc.

thanks for your help!!!

 

[Richard Schollar]

Well, if you use this piece of code which I showed you a couple of months ago:

Sub test()
Dim strtext As String
Dim varArray As Variant
Dim i As Long
Dim ff As Integer
Const PTH = "C:\Documents and Settings\Richard\My Documents\Readme.txt"
ff = FreeFile
strtext = Space(FileLen(PTH))
Open PTH For Binary Access Read As #ff
  Get #ff, , strtext
Close #ff
varArray = Split(strtext, vbCrLf)
For i = 0 To UBound(varArray)
    Debug.Print varArray(i)
Next
End Sub

Rather than plonking the output into an array, just iterate thru the string variable extracting one row's worth of characters at a time:

Sub test()
Dim strtext As String
Dim varArray As Variant
Dim i As Long
Dim ff As Integer
Const PTH = "C:\Documents and Settings\Richard\My Documents\Readme.txt"
ff = FreeFile
strtext = Space(FileLen(PTH))
Open PTH For Binary Access Read As #ff
  Get #ff, , strtext
Close #ff
For i = 1 To Len(strText) Step 100  'modify the step to the size of your row!
    'do something with the contents of strText:
    strTemp = Mid(strText,i,100)   'now you have one row
    strTemp = XorC(strTemp, "Whatever your key is")
    'now maybe write strTemp, suitably split up in chunks to your sheet
    'move on to next bit of strText:
   Next
End Sub