• Welcome, Guest. Please login.
 
April 03, 2020, 04:08:28 pm

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - David Warner

1
Hi Folks,

I have been tinkering around with SQLitening, what a great project. Well done to Fred and Paul!

My aim was to set up a database containing encrypted compressed data, which I could search easily without having to de-encrypt the underlying tables. Using something like the following SQL statement for example...

SELECT Encrypted_Col
FROM   My_Table
WHERE  Encrypted_Col = 'Encrypted_Value';

I couldn't see a way to do this with the SQLitening functions as they stand (maybe I missed something). I found that by exposing one of the underlying auxiliary functions and by using the Base64 encoding routines written by Semen Matusovski, I could achieve what I wanted. I think it would be great to have this facility available by default, what do you reckon?

Well, here is the source code for discussion/comments.

Cheers,

David

'-------------------------------------------------------------------------------
'- Program Name : SL_Encrypt.bas                                               -
'- Author       : David Warner                                                 -
'- Date         : 24/10/2008                                                   -
'- Compiler     : PB/CC 5.0                                                    -
'- Purpose      : To test the insertion, querying and retrieval of compressed  -
'-                and encrypted data values from a SQLite 3 database using     -
'-                the SQLitening PowerBasic functions.                         -
'-------------------------------------------------------------------------------
'-                                                                             -
'- Changes required to expose the encryption/compression functionality...      -
'-                                                                             -
'- 1) Edit SQLitening.bas                                                      -
'-                                                                             -
'-   Replace...                                                                -
'-                                                                             -
'-       Function irCallAuxRutsA (byval rlAction as Long, _                    -
'-                                rsText as String, _                          -
'-                                wsText as String) as Long                    -
'-                                                                             -
'-   with...                                                                   -
'-                                                                             -
'-       FUNCTION irCallAuxRutsA ALIAS "irCallAuxRutsA" _                      -
'-                                     (BYVAL rlAction AS LONG, _              -
'-                                      rsText AS STRING, _                    -
'-                                      wsText AS STRING) EXPORT AS LONG       -
'-                                                                             -
'- 2) Edit SQLitening.inc                                                      -
'-                                                                             -
'-   Add the following line...                                                 -
'-                                                                             -
'-       DECLARE FUNCTION irCallAuxRutsA _                                     -
'-                        LIB "SQLitening.Dll" ALIAS "irCallAuxRutsA" _        -
'-                        (BYVAL rlAction AS LONG, _                           -
'-                         rsText AS STRING, _                                 -
'-                         wsText AS STRING) AS LONG                           -
'-                                                                             -
'- 3) Compile SQLitening.bas to re-create SQLitening.dll                       -
'-                                                                             -
'-------------------------------------------------------------------------------

#COMPILE EXE
#DIM ALL

#INCLUDE "..\SQLitening_106\Inc\SQLitening.Inc"
#INCLUDE "base64.inc"

MACRO CONST = MACRO

' Set up 'valid action' control constants
' for irCallAuxRutsA encryption and
' compression function...
CONST lSetCryptKey = 1&            ' 1 = Set crypt key (Empty Text1 will
                                   '     remove key, use for security reasons).
CONST lEncrypt = 2&                ' 2 = Encrypt
CONST lDecrypt = 3&                ' 3 = Decrypt
CONST lCompress = 4&               ' 4 = Compress
CONST lUncompress = 5&             ' 5 = Uncompress
CONST lCompress_and_Encrypt = 6&   ' 6 = Compress and Encrypt
CONST lDecrypt_and_Uncompress = 7& ' 7 = Decrypt and Uncompress

FUNCTION PBMAIN () AS LONG

    ' String to hold SQL commands to be passed to SQLitening
    DIM sSQL AS STRING

    ' Encryption and compression function
    ' return status and data variables
    DIM lCompEncrypStatus AS LONG            ' Compress & encrypt func retval
    DIM sCompressedEncryptedData AS STRING   ' Compressed and encrypted data

    DIM lDecrypUncompStatus AS LONG          ' Decrypt & uncompress func retval
    DIM sDecryptedUncompressedData AS STRING ' Decrypted and uncompressed data

    ' Base64 encoding and decoding functions
    ' return status and data variables
    DIM lEncodeStatus AS LONG                ' Encoding function return value
    DIM sEncodedData AS STRING               ' Base64 encoded binary data

    DIM lDecodeStatus AS LONG                ' Decoding function return value
    DIM sDecodedData AS STRING               ' Decoded binary data

    ' 'Record Set' control variable to be passed
    ' to SQLitening functions
    DIM lRecSet1 AS LONG
    lRecSet1 = 1

    ' open existing or create new SQLite3 database
    slopen "SL_Encrypt.db3", "C"

    ' remove previously used table if necessary
    slexe "DROP TABLE IF EXISTS [Encrypted_Table];"

    ' create a new table with a single data column
    slexe "CREATE TABLE [Encrypted_Table] " _
                     & "([Encrypted_Column] NVARCHAR2(50));"

    ' Put an index on the data column
    slexe "CREATE INDEX [Encrypted_Column_IDX] " _
                      & "ON [Encrypted_Table] ([Encrypted_Column]);"

    ' Insert a row into our newly created table containing
    ' a Base64 encoded, compressed and encrypted value

    ' Set crypt key for subsequent encryption/decryption
    slSetProcessMods "KJustForTesting12"

    ' Compress and encrypt a string literal value
    lCompEncrypStatus = irCallAuxRutsA(lCompress_and_Encrypt, _
                                       "Row 1 Data", _
                                       sCompressedEncryptedData)

    ' Base 64 encode the compressed, encrypted string
    lEncodeStatus = encode_base64(sCompressedEncryptedData, _
                                  19, _
                                  sEncodedData)

    ' Insert the encoded, compressed and encrypted value
    sSQL = "INSERT INTO [Encrypted_Table] VALUES('" & sEncodedData & "');"
    slexe sSQL

    ' Select the previously inserted encoded value from the the database
    sSQL = "SELECT rowid, " _
         & "[Encrypted_Column] " _
         & "from [Encrypted_Table] " _
         & "WHERE [Encrypted_Column] = '" & sEncodedData & "';"

    slsel (sSQL, lRecSet1)

    ' Process the result set one row at a time (only one row expected)
    DO WHILE slGetRow (lRecSet1)

        ' Retrieve the encoded string from the recordset
        sEncodedData = slFN("Encrypted_Column", lRecSet1)

        ' Display the coded, encrypted and compressed data
        ? "Encoded data retreived from database... "
        ?
        ? "     '" & sEncodedData & "'"
        ?

        ' Base64 decode the compressed, encrypted string
        lDecodeStatus = decode_base64(STRPTR(sEncodedData), sDecodedData)

        ' Decrypt and uncompress the string
        lDecrypUncompStatus = irCallAuxRutsA(lDecrypt_and_Uncompress, _
                                             sDecodedData, _
                                             sDecryptedUncompressedData)

        ' Display the decoded, decrypted and uncompressed data
        ? "After decoding, decryption and uncompression becomes..."
        ?
        ? "     '" & sDecryptedUncompressedData & "'"

   LOOP

   slCloseSet (lRecSet1)

   WAITKEY$

END FUNCTION


and the required include file...

'-------------------------------------------------------------------------------
'- Program Name : Base64.inc                                                   -
'- Author       : Semen Matusovski                                             -
'- Date         : 07/07/2001 01:15 am                                          -
'- Purpose      : To Base64 encode/decode binary data                          -
'- URL          : http://www.powerbasic.com/support/pb...ad.php?t=23062        -
'-                                                                             -
'- referenced by...                                                            -
'-                                                                             -
'- URL     : http://www.powerbasic.com/support/pbforums/showthread.php?t=24839 -
'- topic   : A Program for Posting and Reading Posted Binary Files             -
'- name    : Bob Scott, member                                                 -
'- date    : Feb 20th, 2006, 02:14 PM                                          -
'-                                                                             -
'- example calls :                                                             -
'-                                                                             -
'- encoding...                                                                 -
'-                                                                             -
'-      local stext as string, outbuf as string                                -
'-      encode_base64(stext, 19, outbuf)                                       -
'-                                                                             -
'- decoding...                                                                 -
'-                                                                             -
'-      local stext as string, outbuf as string,                               -
'-      decode_base64(strptr(stext), outbuf)                                   -
'-                                                                             -
'-------------------------------------------------------------------------------

   ' outbuf is not necessary to allocate
   ' $crlf after each ngroups per 3 input bytes

   FUNCTION encode_base64(inbuf AS STRING, BYVAL ngroups AS LONG, _
                                         outbuf AS STRING) AS LONG
      #REGISTER NONE

      outbuf = SPACE$(4 * FIX((LEN(inbuf) + 2) / 3) + _
                   2 * FIX(LEN(inbuf) / (3 * ngroups)))

      ! push ebx

      ! lea ebx, encode_base64_trt

      ! mov edi, outbuf
      ! mov edi, [edi]                    ' strptr(outbuf)

      ! mov esi, inbuf
      ! mov esi, [esi]                    ' strptr(inbuf)

      ! mov edx, esi
      ! sub edx, 4
      ! mov edx, [edx]                    ' len(inbuf)

      ! mov ecx, ngroups

encode_base64_lb1:
      ! cmp edx, 0
      ! jle encode_base64_lb4

      ! mov al, [esi]
      ! shr al, 2
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! mov al, [esi + 1]
      ! mov ah, [esi]
      ! shr ax, 4
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! cmp edx, 1
      ! jne encode_base64_lb2

      ! mov al, 61                        ' add ==
      ! mov [edi], al
      ! inc edi
      ! mov [edi], al
      ! inc edi
      ! jmp encode_base64_lb4

encode_base64_lb2:
      ! mov al, [esi + 2]
      ! mov ah, [esi + 1]
      ! shr ax, 6
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! cmp edx, 2
      ! jne encode_base64_lb3

      ! mov al, 61                        ' add =
      ! mov [edi], al
      ! inc edi
      ! jmp encode_base64_lb4

encode_base64_lb3:
      ! mov al, [esi + 2]
      ! and al, &h3f
      ! xlatb
      ! mov [edi], al
      ! inc edi

      ! add esi, 3
      ! sub edx, 3

      ! dec ecx
      ! cmp ecx, 0
      ! jne encode_base64_lb1

      ! mov al, 13                         ' add $crlf
      ! mov [edi], al
      ! mov al, 10
      ! mov [edi + 1], al
      ! add edi, 2
      ! mov ecx, ngroups
      ! jmp encode_base64_lb1

encode_base64_lb4:
      ! pop ebx

      EXIT FUNCTION

encode_base64_trt:
      ! db  65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77
      ! db  78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90
      ! db  97, 98, 99,100,101,102,103,104,105,106,107,108,109
      ! db 110,111,112,113,114,115,116,117,118,119,120,121,122
      ! db  48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 43, 47
   END FUNCTION

'-------------------------------------------------------------------------------

   ' outbuf is not necessary to allocate
   ' function = offset in inbuf for last not recognized character
   FUNCTION decode_base64(BYVAL inbuf AS DWORD, _
                     outbuf AS STRING) AS LONG
      #REGISTER NONE
      DIM i AS LONG

      ! mov edi, 0
      ! mov ah, 0

decode_base64_lb1:
      ! push ebx
      ! lea ebx, i
      ! mov esi, inbuf
      ! push esi
      ! mov cl, 0

decode_base64_lb2:
      ! mov al, [esi]

      ! cmp al, 13                         ' pass $crlf
      ! je decode_base64_lb15
      ! cmp al, 10
      ! je decode_base64_lb15

      ! cmp al, 65                         ' a .. z
      ! jl decode_base64_lb3
      ! cmp al, 90
      ! jg decode_base64_lb3
      ! sub al, 65
      ! jmp decode_base64_lb8

decode_base64_lb3:
      ! cmp al, 97                         ' a .. z
      ! jl decode_base64_lb4
      ! cmp al, 122
      ! jg decode_base64_lb4
      ! sub al, 71
      ! jmp decode_base64_lb8

decode_base64_lb4:
      ! cmp al, 48                         ' 0 .. 9
      ! jl decode_base64_lb5
      ! cmp al, 57
      ! jg decode_base64_lb5
      ! add al, 4
      ! jmp decode_base64_lb8

decode_base64_lb5:
      ! cmp al, 43                         ' +
      ! jne decode_base64_lb6
      ! mov al, 62
      ! jmp decode_base64_lb8

decode_base64_lb6:
      ! cmp al, 47                         ' /
      ! jne decode_base64_lb7
      ! mov al, 63
      ! jmp decode_base64_lb8

decode_base64_lb7:
      ! cmp al, 61                         ' =
      ! jne decode_base64_lb18
      ! mov al, 255

decode_base64_lb8:
      ! inc cl
      ! cmp cl, 1
      ! jne decode_base64_lb9
      ! mov [ebx], al
      ! jmp decode_base64_lb15

decode_base64_lb9:
      ! cmp cl, 2
      ! jne decode_base64_lb10
      ! mov [ebx + 1], al
      ! jmp decode_base64_lb15

decode_base64_lb10:
      ! cmp cl, 3
      ! jne decode_base64_lb11
      ! mov [ebx + 2], al
      ! jmp decode_base64_lb15

decode_base64_lb11:
      ! mov [ebx + 3], al

      ! mov cl, [ebx]
      ! shl cl, 2
      ! mov ch, [ebx + 1]
      ! shr ch, 4
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb12
      ! mov [edi], cl

decode_base64_lb12:
      ! mov cl, [ebx + 2]
      ! cmp cl, 255
      ! je  decode_base64_lb17

      ! mov cl, [ebx + 1]
      ! shl cl, 4
      ! mov ch, [ebx + 2]
      ! shr ch, 2
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb13
      ! mov [edi + 1], cl

decode_base64_lb13:
      ! mov cl, [ebx + 3]
      ! cmp cl, 255
      ! je  decode_base64_lb16

      ! mov cl, [ebx + 2]
      ! shl cl, 6
      ! mov ch, [ebx + 3]
      ! or  cl, ch

      ! cmp ah, 0
      ! je decode_base64_lb14
      ! mov [edi + 2], cl

decode_base64_lb14:
      ! add edi, 3
      ! mov cl, 0

decode_base64_lb15:
      ! inc esi
      ! jmp decode_base64_lb2


decode_base64_lb16:
      ! inc edi

decode_base64_lb17:
      ! inc edi

decode_base64_lb18:
      ! mov ecx, esi
      ! pop esi
      ! sub ecx, esi
      ! mov function, ecx
      ! pop ebx

      ! cmp ah, 0
      ! jne decode_base64_lb19
      ! mov ah, 1

      ! mov i, edi

      outbuf = SPACE$(i)
      ! mov edi, outbuf
      ! mov edi, [edi]
      ! jmp decode_base64_lb1

decode_base64_lb19:
      EXIT FUNCTION
   END FUNCTION
'-------------------------------------------------------------------------------


2
Hi folks,

As there are no posts in this section yet, I thought I would be the first with something (hopefully) useful!

I was referred to this free online SQL resource last year by the Alpha Software company who produce the Alpha 5 database system so I believe it is perfectly legitimate. There are four SQL books available for download at http://www.computer-books.us/sql.php and to quote from the email I received about one of them...

"Teach Yourself SQL in 21 Days.

The fourth edition (which you can find at Amazon.com) lists for $44.95. However the second edition can be downloaded for free and is a wonderful resource for those of you who are interested in using MySQL, SQL Server and other popular SQL backend databases"
http://www.computer-books.us/sql_0003.php

I actually bought the second edition of this book about ten years ago and thought it was pretty good.

I hope you find something useful and interesting there.

Best Regards

David