• Welcome to SQLitening Support Forum. Please login.
 
January 17, 2022, 09:56:26 PM

News:

Welcome to the SQLitening support forums!


Saving Files in a database

Started by Joe Byrne, March 08, 2014, 12:16:07 AM

Previous topic - Next topic

Joe Byrne

March 08, 2014, 12:16:07 AM Last Edit: March 08, 2014, 02:19:17 AM by Joe Byrne
Before I get too far into this aspect of my current project, I'd like to know if:


(a) Is it possible to store a complete file (both text and binary/executable) in an SQLite database?
(b) If so, can anyone shed some light on how to save and retrieve the file?


Followup.  Ok, using the slGetFile routine, I am getting ASCII/Text files just fine, but my executable/binary files are returning just "MZ".  It seems like the slGetFile routine should work, but the docs aren't real clear when it comes to binary files.

TIA,
--Joe

cj

March 08, 2014, 03:57:37 AM #1 Last Edit: March 08, 2014, 07:45:46 AM by cj
slGetFile and slPutFile are for binary files.
If using MSGBOX/PRINT the text is truncated when a null is encountered

#INCLUDE "\sql\inc\sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL rsFileName, rsFileData,rsModChars AS STRING
  rsFileName = "JOE"
  rsFileData = CHR$(0,"ABC",0)
  rsModChars = "C"  'create if file does not exists (local)
  KILL rsFileName:ERRCLEAR
  slPutFile rsFileName, rsFileData, rsModChars
  slGetFile rsFileName, rsFileData, rsModChars
  ? MID$(rsFileData,2),,"Length" + STR$(LEN(rsFileData))
END FUNCTION

                                     

cj

March 08, 2014, 04:20:43 AM #2 Last Edit: March 08, 2014, 07:44:37 AM by cj
This demonstrates saving a file and executing it.

Note: Files can also be read/written as blobs within a database
and executed using this technique.


#INCLUDE "\sql\inc\sqlitening.inc"
#INCLUDE "win32api.inc"

FUNCTION PBMAIN () AS LONG
  OldFileName$ = "c:\windows\write.exe"
  NewFileName$ = "newfile.exe"
  slGetFile OldFileName$, rsFileData$
  slPutFile NewFilename$, rsFileData$,"CT" 'create/truncate
  REM ? USING$("Length #,",LEN(rsFileData$))
  ShellExecute (0, "OPEN", NewFileName$ + $NUL, BYVAL 0, CURDIR$, %SW_SHOWNORMAL)
END FUNCTION



Saving notepad/write as blob and displaying



#DIM ALL
#COMPILE EXE "\sql\bin\blob"
#INCLUDE "\sql\inc\sqlitening.inc"
#INCLUDE "win32api.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sData AS STRING, rowid AS LONG
  slOpen "test.db3","C"
  slexe "Drop Table if exists T1"
  slexe "Create Table if not exists T1(F1)"
  'put notepad.exe in record 1
  slGetFile "c:\windows\notepad.exe", sData$
  slExeBind("Insert into T1 values(?)",slBuildBindDat(sData))
  'put write.exe in record 2
  slGetFile "c:\windows\write.exe",sData
  slExeBind("Insert into T1 values(?)",slBuildBindDat(sData))

  ShowBlob 1  'notepad
  ShowBlob 2  'write/wordpad

END FUNCTION

FUNCTION ShowBlob(RowId AS LONG) AS LONG
  LOCAL sTempFile AS STRING
  sTempFile = "temp" + FORMAT$(RowID) + ".exe"
  slSel "select * from t1 where rowid =" + FORMAT$(RowID)
  slGetRow
  slPutfile sTempFile,slf(1),"CST" 'create/shared/truncate
  ShellExecute %NULL, "OPEN", sTempFile + $NUL, BYVAL %NULL, CURDIR$, %SW_SHOWNORMAL
  SLEEP 500
  KILL sTempFile
END FUNCTION








                                                                                         

Joe Byrne

cj,


Thanks man!  I really appreciate it.  I'll give it a shot tonight when I get back to coding!

cj

March 08, 2014, 08:10:59 PM #4 Last Edit: March 09, 2014, 04:52:10 AM by cj
Save compressed and encrypted "blob data" into a database.
Each record has 2-columns, column1 unique key, column2 = data.
NOTE: $NUL replaced with $ReplaceNulWithThis   Optional to view blobs in a MSGBOX.

#DIM ALL
#COMPILE EXE "\sql\bin\blob2"     'blob2.bas
#INCLUDE "\sql\inc\sqlitening.inc"
#INCLUDE "win32api.inc"
$EncryptKey = "ABCDABCDABCDABCD" '16-bytes in this example, 24/32 can be used.
$CompressEncrypt   = "CN"
$DecryptUncompress = "DU"
$ReplaceNulWithThis = "<null character>"

FUNCTION PBMAIN () AS LONG
  LOCAL sResult, sKey, sData, sEverything AS STRING, x AS LONG
  slSetProcessMods "K" + $EncryptKey 'set encryption key
  slOpen "test.db3","C"
  slexe "Drop Table if exists BlobFile"
  slexe "Create Table if not exists BlobFile(BlobKey,BlobField)"
  slexe "Create UNIQUE index if not exists BlobIndex ON BlobFile(BlobKey)"

  FOR x = 1 TO 5                               'write key and blob column
    sKey = "Key"+FORMAT$(x)
    sData = "Blob data" + STR$(x) + $NUL + $CR
    PutBlob sKey, sData
  NEXT

  FOR x = 1 TO 5
    sKey = "Key" + FORMAT$(x)                  'get key and blob column
    sResult = GetKeyAndBlob(sKey)
    sEverything = sEverything + sResult        'combine records for display
  NEXT
  REPLACE $NUL WITH $ReplaceNulWithThis IN sEverything
  MSGBOX sEverything,,"Key/Blob"

  RESET sEverything
  FOR x = 1 TO 5
    sResult = GetOnlyBlob("Key" + FORMAT$(x))  'only return blob
    sEverything = sEverything + sResult
  NEXT
  REPLACE $NUL WITH $ReplaceNulWithThis IN sEverything
  MSGBOX sEverything,,"Blob only"
END FUNCTION

' 3  blob helper functions

FUNCTION PutBlob(sKey AS STRING, sData AS STRING) AS LONG
  slExeBind "Insert into BlobFile values(?,?)",_
     slBuildBindDat(skey,"T") & _              'write key text only
     slBuildBindDat(sData,$CompressEncrypt)    'write blob with compress/encrypt
END FUNCTION

FUNCTION GetKeyAndBlob(sKey AS STRING) AS STRING
  slSel "select * from BlobFile where BlobKey = '" +  sKey + "'"
  slGetRow
  FUNCTION = slfx(1)                    + _  'key
               " "                      + _  'space
               slFX(2,$DecryptUncompress)    'blob data
END FUNCTION

FUNCTION GetOnlyBlob(sKey AS STRING) AS STRING
  slSel "select BlobField from BlobFile where BlobKey = '" + sKey + "'"
  slGetRow
  FUNCTION = slFX(1,$DecryptUncompress)
END FUNCTION


#DIM ALL
#COMPILE EXE "\sql\bin\blob2"     'blob2.bas
#INCLUDE "\sql\inc\sqlitening.inc"
#INCLUDE "win32api.inc"
$EncryptKey = "ABCDABCDABCDABCD"
$CompressEncrypt   = "CN"
$DecryptUncompress = "DU"
$ReplaceNulWithThis = "<null character>"

FUNCTION PBMAIN () AS LONG
  LOCAL sResult, sKey, sData, sEverything AS STRING, x AS LONG
  slSetProcessMods "K" + $EncryptKey 'set encryption key
  slOpen "test.db3","C"
  slexe "Drop Table if exists BlobFile"
  slexe "Create Table if not exists BlobFile(BlobKey,BlobField)"
  slexe "Create UNIQUE index if not exists BlobIndex ON BlobFile(BlobKey)"

  FOR x = 1 TO 5                               'write key and blob column
    sKey = "Key"+FORMAT$(x)
    sData = "Blob data" + STR$(x) + $NUL + $CR
    PutBlob sKey, sData
  NEXT

  FOR x = 1 TO 5
    sKey = "Key" + FORMAT$(x)                  'get key and blob column
    sResult = GetKeyAndBlob(sKey)
    sEverything = sEverything + sResult        'combine records for display
  NEXT
  REPLACE $NUL WITH $ReplaceNulWithThis IN sEverything
  MSGBOX sEverything,,"Key/Blob"

  RESET sEverything
  FOR x = 1 TO 5
    sResult = GetOnlyBlob("Key" + FORMAT$(x))  'only return blob
    sEverything = sEverything + sResult
  NEXT
  REPLACE $NUL WITH $ReplaceNulWithThis IN sEverything
  MSGBOX sEverything,,"Blob only"
END FUNCTION

FUNCTION PutBlob(sKey AS STRING, sData AS STRING) AS LONG
  slExeBind "Insert into BlobFile values(?,?)",_
     slBuildBindDat(skey,"T") & _              'write key text only
     slBuildBindDat(sData,$CompressEncrypt)    'write blob with compress/encrypt
END FUNCTION

FUNCTION GetKeyAndBlob(sKey AS STRING) AS STRING
  slSel "select * from BlobFile where BlobKey = '" +  sKey + "'"
  slGetRow
  FUNCTION = slfx(1)                    + _  'key
               " "                      + _  'space
               slFX(2,$DecryptUncompress)    'blob data
END FUNCTION

FUNCTION GetOnlyBlob(sKey AS STRING) AS STRING
  slSel "select BlobField from BlobFile where BlobKey = '" + sKey + "'"
  slGetRow
  FUNCTION = slFX(1,$DecryptUncompress)
END FUNCTION


slSetProcessMods(rsModChars String)  'how to set encryption key
ModChars:
Kx = Set the crypt key for subsequent encryption/decryption. x is the key string, it must immediately follow the K. If there is no value following the K then any existing crypt key will be cleared, this is for security reasons. This ModChar MUST be used alone since the letters in the key may be equal to other ModChars. If you have not written your own custom encryption then the passed key must be either 16, 24, or 32 bytes long.
 

cj

March 09, 2014, 04:45:03 AM #5 Last Edit: March 09, 2014, 04:58:29 AM by cj
Example blob using ON CONFLICT REPLACE if the same key is used.

Posting source code using COURIER works so well I will no longer use code blocks.
This way the entire posting can be read and still easily copied and pasted.


#COMPILE EXE "\sql\bin\blob2short"     'blob2short.bas
#INCLUDE "\sql\inc\sqlitening.inc"
%DropTable = 0

FUNCTION PBMAIN () AS LONG
  RANDOMIZE
  LOCAL sKey, sData AS STRING, x AS LONG
  slOpen "test.db3","C"
  IF %DropTable THEN slExe "Drop Table if Exists BlobFile"
  slexe "Create Table If Not Exists BlobFile(CustomerNumber INTEGER PRIMARY KEY ON CONFLICT REPLACE,BlobKey)"
  FOR x = 1 TO 5
    sKey$  = FORMAT$(RND(1,10))
    sData$ = CHR$(0,0,0,"Time is ",TIME$,0,0) 'blob data
    PutBlob sKey$, sData$
  NEXT
  sData = GetAll
  REPLACE $NUL WITH "<nul>" IN sData
  ? sData
END FUNCTION

FUNCTION PutBlob(sKey AS STRING, sData AS STRING) AS LONG
  slExeBind "Insert into BlobFile values(?,?)",_
     slBuildBindDat(skey,"T") & _  'write key text only
     slBuildBindDat(sData)         'write blob
END FUNCTION

FUNCTION GetAll AS STRING
  LOCAL Lastcolumn, COL AS LONG
  LOCAL s AS STRING
  slSel "select * from BlobFile order by CustomerNumber"
  LastColumn = slGetColumnCount
  DO WHILE slGetRow
    FOR COL = 1 TO LastColumn
       s = s + slfx(COL) + "|"
    NEXT
    ASC(s,LEN(s)) = 13
  LOOP
  FUNCTION = s
END FUNCTION

Joe Byrne

CJ,


WOW.  Thank you SO much. Great info.  I really appreciate it.