• Welcome to SQLitening Support Forum. Please login.
 
January 27, 2022, 05:10:25 AM

News:

Welcome to the SQLitening support forums!


IsEncrypted or compressed function wanted #1

Started by cj, August 13, 2013, 11:46:35 AM

Previous topic - Next topic

cj

August 13, 2013, 11:46:35 AM Last Edit: August 14, 2013, 06:28:34 PM by cj
Is there a way to examine a recordset to see which columns are encrypted or compressed?

Adding rowid or count(*)  to a SQL statement requires using SLF for that column where encrypted
or compressed columns might use SLFX.   This requires knowing in advance what columns will be
returned in what format and coding IF or CASE logic to handle each case.


cj

August 13, 2013, 12:48:10 PM #1 Last Edit: August 14, 2013, 06:28:56 PM by cj
Trying to come up with a function that returns any recordset without hard-coding IF or CASE statements.

Setup a test program with one encrypted and compressed column.
Need function(s) to determine if the column returned is encrypted and/or compressed.

Dummy proposed functions for now:

FUNCTION IsCN(colNum AS LONG) AS LONG  'Is column compressed and encrypted
END FUNCTION

FUNCTION IsC(colNum AS LONG)  AS LONG  'Is column only compressed
END FUNCTION

FUNCTION IsN(colNum AS LONG)  AS LONG  'Is column only encrypted
END FUNCTION

Thinking  compressed or encrypted columns start with a token.

#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL colnum,columns AS LONG, sResult AS STRING
  slSetProcessMods "K1234567890123456" 'encryption key or error 17
  slOpen "test1.db3","C"               'open or create database
  slexe  "drop table if exists t1"     'get rid of previous table
  slExe  "create table if not exists t1(f1)" 'create table
  slExeBind slBuildInsertOrUpdate("t1","?"),slBuildBindDat("ONE","CN")
  slSel "select rowid, * from t1"
  columns = slGetColumnCount
  DO WHILE slGetRow
    FOR colnum = 1 TO Columns
      IF colnum > 1 THEN
        sResult = sResult + slfx(colnum,"DU") + "," 'opposite "CN
      ELSE
        sResult = sResult + slf(colnum) + "," 'no compress or encrypt
      END IF
    NEXT
    ASC(sResult,LEN(sResult)) = 13  '$CR after each row
  LOOP
  ? sResult
END FUNCTION












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

FUNCTION PBMAIN () AS LONG
  slSetProcessMods "K1234567890123456" 'encryption key or error 17
  slOpen "test1.db3","C"
  slexe  "drop table if exists t1"
  slExe  "create table if not exists t1(f1)
     '-- Insert record ---------------------------------------------
  REDIM sArray(1 TO 1) AS STRING   'column data
  REDIM sInsertMod(1 TO 1) AS STRING  'column modchar C/N
  sArray(1) = "ONE"
  sInsertMod(1) = "CN" 'compress and encrypt
  InsertRecord "t1", sArray(),sInsertMod()
  ? SQL("Select f1 from t1")
END FUNCTION

FUNCTION SQL(SqlStatement AS STRING) AS STRING
LOCAL columns,x AS LONG, s AS STRING
slSel SqlStatement
columns = slGetColumnCount
DO WHILE slGetRow
  FOR x = 1 TO Columns
    IF IsCN(x) THEN        'compressed/encrypted
      s = s + slfx(x,"DU") + "," 'decompress/unencrypt
    ELSEIF IsC(x) THEN    'compressed
      s = s + slfx(x,"D")  + ","  'decompress
    ELSEIF IsN(x) THEN               '
      s = s + slfx(x,"U")   'encrypted
    ELSE
      s = s + slf(x)        'not compress or encrypted
      ? s,, "Not compress or encrypted"
    END IF
  NEXT
  ASC(s,LEN(s)) = 13  '$CR after each row
LOOP
FUNCTION = s
END FUNCTION

FUNCTION IsCN(colNum AS LONG) AS LONG
  FUNCTION = 1  'always return true
END FUNCTION

FUNCTION IsC(colNum AS LONG)  AS LONG
  FUNCTION = 0  'always return false
END FUNCTION

FUNCTION IsN(colNum AS LONG)  AS LONG
  FUNCTION = 0  'always return false
END FUNCTION
'--------------------------------------------------------------
FUNCTION InsertRecord(sTableName AS STRING, sInsertData() AS STRING,sInsertMod() AS STRING) AS LONG
  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING
  NumberOfColumns = UBOUND(sInsertData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sInsertData(COL),sInsertMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind slBuildInsertOrUpdate(sTableName,sPlaceHolders),sBindData
END FUNCTION
'--------------------------------------------------------------
FUNCTION UpdateRecord(sTableName       AS STRING, _
                      sColumnsToUpdate AS STRING, _
                      sUpdateData()    AS STRING,    _
                      sUpdateMod()     AS STRING, _
                      sWhere           AS STRING) AS LONG

  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING

  NumberOfColumns = UBOUND(sUpdateData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sUpdateData(COL),sUpdateMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind(slBuildInsertOrUpdate(sTableName,sPlaceHolders,sColumnsToUpdate,sWhere),sBindData)
END FUNCTION