Crypt database

[StungStang]

Hi to all, i've created a database...now i would like crypt the data...how i can crypt a sql database?

There is a sql function to do it? Or i've to do with autoit? what method do you recommend?

Hi!

Edited February 22, 2011 by StungStang

[Sapient]

Hi to all, i've created a database...now i would like crypt the data...how i can crypt a sql database?

There is a sql function to do it? Or i've to do with autoit? what method do you recommend?

Hi!

All common databases are "SQL databases."

You could be a bit more specific.

[jchd]

Hi StungStang,

You can use System.Data.SLite to run an encrypted SQLite database. This work has recently been taken over by the SQLite.org team.

Lookup there to read about features (encryption) and more.

In short, the freely downloadable .dll is 100% compatible with the original SQLite3.dll which AutoIt installs for you when you #include <SQLite.dll.au3>.

When you install System.Data.SLite DLL in your scriptdir folder, remove #include <SQLite.dll.au3> from your script.

I haven't used it myself but I know it works very well.

Should you --or anyone else-- have any problem using it in AutoIt just drop me a note and I'll look at the issue.

[StungStang]

I want simply crypt the data for example :

;   Radio name  |Streaming Link    | WebSite Link       | Genre       |
;   ----------------------------------------------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        |

I want crypt radio name value, streaming value, ecc... and decrypt the text in my application...like autoit _stringencrypt function...

There is a native function to do that? what method do you recommend?

Hi!

[wolf9228]

I want simply crypt the data for example :

;   Radio name  |Streaming Link    | WebSite Link       | Genre       |
;   ----------------------------------------------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        |

I want crypt radio name value, streaming value, ecc... and decrypt the text in my application...like autoit _stringencrypt function...

There is a native function to do that? what method do you recommend?

Hi!

expandcollapse popup

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Crypt.au3>
#Include <Array.au3>
FileDelete("Database.db")
Dim $aResult, $iRows, $iColumns , $Id = 0
_Crypt_Startup()
$hKey=_Crypt_DeriveKey("SomePassword",$CALG_RC4)


;   Radio name  |Streaming Link    | WebSite Link       | Genre       |
;   ----------------------------------------------------|-------------|
;   MyRadio     |mms://myradio.asx |www.myradio.com     |Rock         |
;   Myprefradio |mms://mypref.asx  |www.mypref.net      |Pop          |
;   MyExample   |http.//link.mp3   |www.myexample.com   |Dance        |

_SQLite_Startup ()
_SQLite_Open("Database.db")
_SQLite_Exec (-1, "CREATE TABLE iTABLE (Radio_name,Streaming_Link,WebSite_Link,Genre,Id);")

$Id += 1
_SQLite_Exec (-1, "INSERT INTO iTABLE (Radio_name,Streaming_Link,WebSite_Link,Genre,Id) VALUES (" & _
"'" & Enc("MyRadio") & "','" & Enc("mms://myradio.asx") & "','" & Enc("www.myradio.com") & _
"','" & Enc("Rock") & "' , '" & $Id & "');")

$Id += 1
_SQLite_Exec (-1, "INSERT INTO iTABLE (Radio_name,Streaming_Link,WebSite_Link,Genre,Id) VALUES (" & _
"'" & Enc("Myprefradio") & "','" & Enc("mms://mypref.asx") & "','" & Enc("www.mypref.net") & _
"','" & Enc("Pop") & "' , '" & $Id & "');")

$Id += 1
_SQLite_Exec (-1, "INSERT INTO iTABLE (Radio_name,Streaming_Link,WebSite_Link,Genre,Id) VALUES (" & _
"'" & Enc("MyExample") & "','" & Enc("http.//link.mp3") & "','" & Enc("www.myexample.com") & _
"','" & Enc("Dance") & "' , '" & $Id & "');")

$iRval = _SQLite_GetTable2d (-1, "SELECT * FROM iTABLE;", $aResult, $iRows, $iColumns)
_ArrayDisplay($aResult, "Encrypt aResult")

For $j = 1 To UBound($aResult) - 1
For $i = 0 To UBound($aResult,2) - 2
$aResult[$j][$i] = BinaryToString(_Crypt_DecryptData( $aResult[$j][$i],$hKey,$CALG_USERKEY))
Next
Next
_ArrayDisplay($aResult, "Decrypt aResult")

Func Enc($Text)
Return _Crypt_EncryptData($Text,$hKey,$CALG_USERKEY)
EndFunc

[jchd]

This is going to be much slower than using system.data.sqlite while making the AutoIt code more complex.

[wolf9228]

This is going to be much slower than using system.data.sqlite while making the AutoIt code more complex.

There are no complications, except in your mind

Windows encrypted functions at the high level security

Can be used with confidence and reassurance

Can not perform data encryption in a short time

As well as Save data Requires a time period

[StungStang]

@jchd

I dont have understand nothing of this system.data.sqlite ...=P

@wolf9228

This metod seems to be too much slow...i can also ise _stringencrypt...it do the same work =P

No native function in SQL or much faster soluction?

Hi!

[jchd]

@wolf9228

You clearly don't understand what you're talking about. Encrypting every column of every row individually _will_ be _very_ slow since it will require C * P calls to underlying encryption routine (whatever "fast" it is) and will definitely preclude the user to use any table scan, forcing the use of index which must then be unencrypting. That defeats the purpose of encryption in the first place!

An actual encryption system designed for a database will encrypt database pages individually and operate transparently. That's why I suggested using system.data.sqlite.

@StungStang

It's very simple: just copy the system.data.sqlite dll in place of the usual sqlite3.dll and give the path of the new dll on sqlite_startup.Read up details on how to specify encryption key (sorry I don't have much time to dig into this atm).

Edited February 24, 2011 by jchd

[StungStang]

Im here...again!

I see your library, but there aren't any help!...i see the wiki but is empty! How you can see here.

There is a soluction do to that with the native sqlite?

More help will be very usefull

Hi!

[jchd]

Follow this link to read about features. It's a drop-in replacement for the native sqlite3.dll file, but includes reliable, fast and transparent encryption as well.

As I previously said, this version is being taken over by the SQLite dev team. This is relatively new and no new version has appeared since the "transfer" to SQLite.org team but rest assured they are working hard to release a next version on par with the classical sqlite release.

The native sqlite3.dll does not offer any encryption feature, but the system.data.sqlite version does (and it's free). Try it and don't hesitate to PM me if you have any problem about this.

[StungStang]

@jchd

I've downloaded the dll...i see the help file, but i dont have found a soluction for encryption...

For example i've this script :

expandcollapse popup

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>

Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg
$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$Create = GUICtrlCreateButton("Create", 8, 432, 75, 25)
$Add = GUICtrlCreateButton("Add", 88, 432, 75, 25)
$Delete = GUICtrlCreateButton("Delete", 168, 432, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Continent|State", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)
$State = GUICtrlCreateCombo("England", 224, 8, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $WS_HSCROLL, $WS_VSCROLL))
GUICtrlSetData (-1,"California")
GUISetState(@SW_SHOW)

_SQLite_Startup("C:\System.Data.SQLite.dll")
 _SQLite_Open(@ScriptDir & "\Database.db")
If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Create
            $DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)
           _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,Site,Genre,Continent,State);")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 1','http://bbc.co.uk/radio/listen/live/r1.ram','http://www.bbc.co.uk/radio1/','Top 40/Dance','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 2','http://bbc.co.uk/radio/listen/live/r2.ram','http://www.bbc.co.uk/radio1/','Adult Contemporary','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 3','http://bbc.co.uk/radio/listen/live/r3.ram','http://www.bbc.co.uk/radio1/','Classical','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('Absolute Radio','http://aacplus-vr-32.smgradio.com:80/listen.pls','http://www.absoluteradio.co.uk/','Hot Adult Contemporary','Europe','England');")
           _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('KLOS','http://citadelcc-klos-fm.wm.llnwd.net/citadelcc_KLOS_FM','http://www.955klos.com/','Classic Rock','America','California');")
       Case $Add
          ;Do nothing for this moment, it's just a try script
        Case $Delete
             $Element = _GUICtrlListView_GetItemTextArray($ListView,0)
            For $x = 1 To $Element[0]
                $Info = $Element[$x]
            Next
            _SQLite_Exec (-1, "DELETE FROM Radio WHERE Name = '" & $Info[1] & "'")
        Case $State
             $Selected = GUICtrlRead ($State)
            _Display_ListView($Selected)
        Case $Search
            $Valore = GUICtrlRead ($Input1)
            _Search($Valore)
    EndSwitch
WEnd

Func _Display_ListView($Value) ;Ok FIXED!
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $aStations, $iRows, $iColumns
_SQLite_GetTable2d(-1, "Select Name,Streaming,Site,Genre,Continent,State FROM Radio WHERE State = '" & $Value &"'",$aStations,$iRows,$iColumns)
For $i = 1 to $iRows
$Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] & "|" & $aStations[$i][4] & "|" & $aStations[$i][5]
    ConsoleWrite($Line)
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
_GUICtrlListView_EndUpdate($ListView)
EndFunc

Func _Search ($Value)
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Radio WHERE Name LIKE '" & $Value & "%';", $aResult, $iRows, $iColumns)
For $i = 1 to $iRows
$Line = $aResult[$i][0] & "|" & $aResult[$i][1] & "|" & $aResult[$i][2] & "|" & $aResult[$i][3] & "|" & $aResult[$i][4] & "|" & $aResult[$i][5]
    ConsoleWrite($Line)
    GuiCtrlCreateListViewItem($Line,$ListView)
Next
_GUICtrlListView_EndUpdate($ListView)
EndFunc

How i can crypt the radio station data? You know the function (or a command??) to do that?

Thanks for your help

Edited March 4, 2011 by StungStang

[ProgAndy]

@StungStang: Here you are.

-use Open to open the db

-then encrpypt it using ReKey

-now encrypted is set up

next time using the db:

-open using Open

-use Key to specify key

[-use ReKey to chage key / remove key)

Func _SQLite_Key($hDB, $bKey)
    ; ProgAndy
    If __SQLite_hChk($hDB, $SQLITE_ERROR) = $SQLITE_ERROR Then Return SetError(-1, 0, $SQLITE_ERROR)
    $bKey = Binary($bKey)
    Local $iLen = BinaryLen($bKey)
    Local $tKey = DllStructCreate("byte[" & $iLen & "]")
    DllStructSetData($tKey, 1, $bKey)
    Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_key", "ptr", $hDB, "ptr", DllStructGetPtr($tKey), "int", $iLen)
    If @error Then Return SetError(-1, 0, $SQLITE_ERROR)
    Return $avRval[0]
EndFunc

Func _SQLite_ReKey($hDB, $bKey)
    ; ProgAndy
    If __SQLite_hChk($hDB, $SQLITE_ERROR) = $SQLITE_ERROR Then Return SetError(-1, 0, $SQLITE_ERROR)
    $bKey = Binary($bKey)
    Local $iLen = BinaryLen($bKey)
    Local $tKey = DllStructCreate("byte[" & $iLen & "]")
    DllStructSetData($tKey, 1, $bKey)
    Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_rekey", "ptr", $hDB, "ptr", DllStructGetPtr($tKey), "int", $iLen)
    If @error Then Return SetError(-1, 0, $SQLITE_ERROR)
    Return $avRval[0]
EndFunc

[StungStang]

I've found this code :

#include <SQLite.au3>   

_SQLite_Startup ("System.Data.SQLite.dll")
ConsoleWrite(_SQLite_LibVersion() & @LF)
_SQLite_Open("testcrypt.db")
_SQLite_Exec(-1, "pragma key = 'Radu is happy!';create table if not exists test (id integer, val text);" & _
                "insert into test values (1, 'abc');")
_SQLite_Exec(-1, "pragma key = 'Radu is happy!';")
Local $row
_SQLite_QuerySingleRow(-1, "select * from test;", $row)
ConsoleWrite($row[1] & @LF)
_SQLite_Close()
_SQLite_Shutdown()

He just use pragma key = or pragma rekey= ...

What it's better to use? In term of speed?

Hi!

[SmOke_N]

Personally:

I use rekey to create a password for the db initially ( or change the password once it's open already ).

I use key to open the db with the password.

Edited March 4, 2011 by SmOke_N

[jchd]

I couldn't reply as fast as you guys.

I've something quite similar to what has been proposed but I've a doubt about the portability of the AutoIt *_key and *_rekey functions: if you supply a string you lose portability of the encrypted DB. Only if you feed an UTF-8 version of the string can you be guaranteed that the password will be the same across platforms and be independant of the locale used.

Reason: Binary($somestring) will convert the string to ANSI then return the binary of the ANSI data. To ensure portability, you need to convert the string to UTF-8 and pass the struct buffer to the *key functions.

I coded this in a bit of a hurry and I don't have time to test it right now, sorry.

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Key
; Description ...: Specify the cryptographic key to use for a given encrypted Database (if you use system.data.sqlite.dll)
; Syntax.........: _SQLite_Key($hDB, $kKey)
; Parameters ....: $hDB - An Open Database, Use -1 to use Last Opened Database
;               $kKey - key to be used, passed as a string or a binary variant
; Return values .: On Success - Returns $SQLITE_OK
;               @error Value(s):    -1 - SQLite Reported an Error (Check @extended Value)
;               1 - Library misuse (sqlite DLL not loaded or wrong DB handle)
;               2 - Error while converting key string to UTF-8
;               3 - Error Calling SQLite API 'sqlite3_key'
;               @extended Value(s): Can be compared against $SQLITE_* Constants
; Author ........: jchd
; Remarks .......: this call won't work with the standard version of sqlite3.dll which doesn't support encryption.
;               Alternative libraries like system.data.sqlite _do_ support encryption and will work with this function.
;               This function needs to be invoked right after an _SQLite_Open call and before any other call to _SQLite_* functions.
; ===============================================================================================================================
Func _SQLite_Key($hDB, $kKey)
    If Not $g_hDll_SQLite Then Return SetError(1, $SQLITE_MISUSE, -1)
    If __SQLite_hChk($hDB, 2) Then Return SetError(1, $SQLITE_MISUSE, -1)
    Local $iKeyLen, $tKey
    If IsBinary($kKey) Then
        $iKeyLen = BinaryLen($kKey)
        $tKey = DllStructCreate("byte[" & $iKeyLen & "]")
        DllStructSetData($tKey, 1, $kKey)
    Else
        $tKey = __SQLite_StringToUtf8Struct(String($kKey))
        If @error Then Return SetError(2, @error, -1)
        $iKeyLen = DllStructGetSize($tKey) - 1  ; don't count the terminating \0
    EndIf
ConsoleWrite("dll called with key = >" & DllStructGetData($tKey, 1) & "< and length = " & $iKeyLen & @LF)
    Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_key", "ptr", $hDB, "ptr", DllStructGetPtr($tKey), "int", $iKeyLen)
    If @error Then Return SetError(1, @error, 0) ; Dllcall error
    If $avRval[0] <> $SQLITE_OK Then SetError(3, $avRval[0], -1)
    Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Key

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_ReKey
; Description ...: Specify a new cryptographic key for a given Database (if you use system.data.sqlite.dll)
; Syntax.........: _SQLite_ReKey($hDB [, $kKey])
; Parameters ....: $hDB - An Open Database, Use -1 to use Last Opened Database
;               $kKey - Optional, new key to be used, passed as a string or a binary variant
;                       By default, the database will be "encrypted" with a null key, that is it will be unencrypted,
;                       making it useable by the standard version of sqlite3.dll
; Return values .: On Success - Returns $SQLITE_OK
;               @error Value(s):    -1 - SQLite Reported an Error (Check @extended Value)
;               1 - Library misuse (sqlite DLL not loaded or wrong DB handle)
;               2 - Error while converting key string to UTF-8
;               3 - Error Calling SQLite API 'sqlite3_rekey'
;               @extended Value(s): Can be compared against $SQLITE_* Constants
; Author ........: jchd
; Remarks .......: this call won't work with the standard version of sqlite3.dll which doesn't support encryption.
;               Alternative libraries like system.data.sqlite _do_ support encryption and will work with this function.
;               As a safety measure you should perform an integrity check before changing the encryption key!!!
; ===============================================================================================================================
Func _SQLite_ReKey($hDB, $kNewKey = Default)
    If Not $g_hDll_SQLite Then Return SetError(1, $SQLITE_MISUSE, -1)
    If __SQLite_hChk($hDB, 2) Then Return SetError(1, $SQLITE_MISUSE, -1)
    Local $iKeyLen, $tKey, $pPtr
    If Not IsKeyword($kNewKey) Then
        If IsBinary($kNewKey) Then
            $iKeyLen = BinaryLen($kNewKey)
            $tKey = DllStructCreate("byte[" & $iKeyLen & "]")
            DllStructSetData($tKey, 1, $kNewKey)
        Else
            $tKey = __SQLite_StringToUtf8Struct(String($kNewKey))
            If @error Then Return SetError(2, @error, -1)
            $iKeyLen = DllStructGetSize($tKey) - 1  ; don't count the terminating \0
        EndIf
        $pPtr = DllStructGetPtr($tKey)
    Else
        $pPtr = 0
        $iKeyLen = 0
    EndIf
    Local $avRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_rekey", "ptr", $hDB, "ptr", $pPtr, "int", $iKeyLen)
    If @error Then Return SetError(1, @error, 0) ; Dllcall error
    If $avRval[0] <> $SQLITE_OK Then SetError(3, $avRval[0], -1)
    Return($SQLITE_OK)
EndFunc   ;==>_SQLite_ReKey

EDIT: code changed to tested working version

Edited March 6, 2011 by jchd

[ProgAndy]

@jchd: The binary type is universal, so you can do everything with it. If you want to use the UTF-8 representation, supply StringToBinary("Your Key", 4) as parameter, or you can pass binary data. Your version is easier for beginners to understand, though. Still, you forgot to add the $hDB-Parameter to your DLLCall.

Edited March 4, 2011 by ProgAndy

[SmOke_N]

Ya'll went all fancy-fide.

I've always just used pragma rekey = or pragma key =.

Has been used widely across many systems, but now ya'll got me scratching my head wondering if it's safe or not lol.

[ProgAndy]

Why safe or not? I think in SQLite should work with UTF-8 by default.

Edit: I think this is at least true in AutoIt.

Edit2: You can also create an own encryption layer for SQLite and register it vie sqlite3_vfs_register http://www.sqlite.org/c3ref/vfs.html

Edited March 4, 2011 by ProgAndy

[SmOke_N]

Why safe or not? I think in SQLite should work with UTF-8 by default.

That was totally my assumption when doing it originally.

However, now you'll have me testing in different environments to be sure.