TrwsFbFw

1sQliteConnection.SetPassword("Password=ThisIsMySuperSecretPassword");
2	
3PartA = "ThisIs"
4PartB= "MySuper"
5PartC= "SecretPassword"
6Password = PartA + PartB + PartC
7	
8PartA = "Tehtibs5Iasu"
9PartB= "M4ytS6uhpae3r5"
10PartC= "S5efc2rhe8taP3ags5sgw4ogr5d6"
11	
12Imports System.Data.SQLite
13Imports System.IO
14Imports System.Drawing.Imaging
15
16Friend Class SQLiteConn
17
18    ' ToDo: obscure the name to hide the purpose ?
19    Friend Shared PasswordImg As Image
20    ' full path to the dbfile
21    Friend Shared DBFile As String
22
23    ' cant create one
24    Private Sub New()
25
26    End Sub
27
28    ' this is to avoid exposing any method which returns the PW Byte()
29    Friend Shared Sub SetPassword(dbcon As SQLiteConnection)
30        If PasswordImg Is Nothing Then
31            Throw New ArgumentNullException("PasswordImg")
32        End If
33        dbcon.SetPassword(ImageToByteArray(PasswordImg))
34    End Sub
35
36    Friend Shared Sub ChangePassword(newPW As Image)
37        ' not tested, I think this is correct 
38        Using dbCon As New SQLiteConnection(GetLiteConnStr())
39            dbCon.SetPassword(ImageToByteArray(newPW))
40            ' to do: add error handling,
41            ' only set pwImg on success
42            PasswordImg = newPW
43        End Using
44    End Sub
45
46    ' the connection string is not persisted
47    ' to make it harder to copy the byte[] ... it only
48    ' exists momentarliy
49    Friend Shared Function GetLiteConnStr() As String
50        Dim cb As New SQLiteConnectionStringBuilder
51
52        If String.IsNullOrEmpty(DBFile) Then
53            Throw New ArgumentNullException("DbFile")
54        End If
55
56        cb.DataSource = DBFile
57        cb.HexPassword = ImageToByteArray(PasswordImg)
58        cb.Pooling = True
59        ' add other needed options
60
61        Return cb.ConnectionString
62    End Function
63
64    Private Shared Function ImageToByteArray(img As Image) As Byte()
65        Dim tmpData As Byte()
66        Using ms As New MemoryStream()
67            img.Save(ms, ImageFormat.Png)
68            tmpData = ms.ToArray
69        End Using
70        Return tmpData
71    End Function
72End Class
73	
74' only place the PW source is revealed in code
75SQLiteConn.PasswordImg = My.Resources.ballorange
76' change path for AppData or whatever as needed
77SQLiteConn.DBFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
78                                      "SQLite dbs", "CryptoTest.db")
79	
80Dim tmpConnStr = String.Format("Data Source='{0}';Version=3;{1}", SQLiteConn.DBFile, "")
81
82Dim sql = <sql>CREATE TABLE CryptoTest (
83                    Id             INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
84                    Name           TEXT,
85                    Value          INTEGER);</sql>.Value
86
87Using dbCon As New SQLiteConnection(tmpConnStr)
88    ' tell helper to set PW, encrypt file
89    SQLiteConn.SetPassword(dbCon)
90    dbCon.Open()
91    Using cmd As New SQLiteCommand(sql, dbCon)
92        cmd.ExecuteNonQuery()
93    End Using
94End Using
95	
96Using dbcon As New SQLiteConnection(SQLiteConn.GetLiteConnStr(My.Resources.ballorange))
97    dbcon.Open()
98
99    Using cmd As New SQLiteCommand("INSERT INTO CryptoTest (name, value) VALUES ('foo', 7)", dbcon)
100        cmd.ExecuteNonQuery()
101    End Using
102End Using
103	
104Using dbCon As New SQLiteConnection(SQLiteConn.GetLiteConnStr())
105    Using cmd As New SQLiteCommand("SELECT * FROM CryptoTest", dbCon)
106        dbCon.Open()
107
108        dtSample = New DataTable()
109        dtSample.Load(cmd.ExecuteReader())
110    End Using
111End Using
112
113dgv1.DataSource = dtSample