Provide minimum level of security to connection strings/passwords

Question

Some info:

-Small application using a SQLite encrypted database

-Using Dotfuscator Community Edition I still can take a look inside of the code (even with methods's names obfuscated)

sQliteConnection.SetPassword("Password=ThisIsMySuperSecretPassword");

-I don´t need a high level of protection about the code itself (the logic), but I need to protect the database password

If I use some concatenation like

PartA = "ThisIs"
PartB= "MySuper"
PartC= "SecretPassword"
Password = PartA + PartB + PartC

With the 4 lines not together on the code, maybe I can have a minimun of protection

Now I can to go a step further with something like this:

PartA = "Tehtibs5Iasu"
PartB= "M4ytS6uhpae3r5"
PartC= "S5efc2rhe8taP3ags5sgw4ogr5d6"

So I have one valid character and one invalid character only to confuse the eventual lurker. I will use a For loop to "decrypt" each part

How much secure can be a solution like this?

Is there other tricks to protect a password inside the code?

Answer 1

Is there other tricks to protect a password inside the code?
Yes
(If by 'tricks' you mean alternate means to supply passwords and use connection strings).

Note that you cannot keep something secret on someone's PC. But with a SQLite DB in particular, you can make it fairly difficult to snatch the connection string...by not storing a connection string. The demo DB for this will use the password: ''. That is, it will use a binary password.

A Class with all Shared methods is used to manage the connection:

Imports System.Data.SQLite
Imports System.IO
Imports System.Drawing.Imaging

Friend Class SQLiteConn

    ' ToDo: obscure the name to hide the purpose ?
    Friend Shared PasswordImg As Image
    ' full path to the dbfile
    Friend Shared DBFile As String

    ' cant create one
    Private Sub New()

    End Sub

    ' this is to avoid exposing any method which returns the PW Byte()
    Friend Shared Sub SetPassword(dbcon As SQLiteConnection)
        If PasswordImg Is Nothing Then
            Throw New ArgumentNullException("PasswordImg")
        End If
        dbcon.SetPassword(ImageToByteArray(PasswordImg))
    End Sub

    Friend Shared Sub ChangePassword(newPW As Image)
        ' not tested, I think this is correct 
        Using dbCon As New SQLiteConnection(GetLiteConnStr())
            dbCon.SetPassword(ImageToByteArray(newPW))
            ' to do: add error handling,
            ' only set pwImg on success
            PasswordImg = newPW
        End Using
    End Sub

    ' the connection string is not persisted
    ' to make it harder to copy the byte[] ... it only
    ' exists momentarliy
    Friend Shared Function GetLiteConnStr() As String
        Dim cb As New SQLiteConnectionStringBuilder

        If String.IsNullOrEmpty(DBFile) Then
            Throw New ArgumentNullException("DbFile")
        End If

        cb.DataSource = DBFile
        cb.HexPassword = ImageToByteArray(PasswordImg)
        cb.Pooling = True
        ' add other needed options

        Return cb.ConnectionString
    End Function

    Private Shared Function ImageToByteArray(img As Image) As Byte()
        Dim tmpData As Byte()
        Using ms As New MemoryStream()
            img.Save(ms, ImageFormat.Png)
            tmpData = ms.ToArray
        End Using
        Return tmpData
    End Function
End Class

SQLite allows binary passwords which this implements.

• The source for the PW in this case is a small orange ball image. This results in a password of 694 bytes
• Naturally, it cant be any orange ball, but that orange ball
• The image is embedded into the app, so it would have to be extracted before it could be used - if they know how and know thats what needs to be done. Any error extracting it will invalidate it as the PW.
• You can change the password by changing a few pixels in that image or using a different image.
• The resulting byte array is not stored anywhere so it exists only momentarily as part of the connection string. Same for the actual connection string.
• It doesn't have to be an image. You could use Guid.ToByteArray() or the result of a Crypto Hash of something. Both of those would be a bit easier to reproduce outside your code.
• Rather than a ConnectionString, it could be modified to return a New SQLiteConnection, but I dont see any advantage in that.
• A (useless, unused) connection string in app settings might still serve as a red herring for someone looking for the normal approach.

Usage

First, set the password image somewhere (remember, everything is static/Shared):

' only place the PW source is revealed in code
SQLiteConn.PasswordImg = My.Resources.ballorange
' change path for AppData or whatever as needed
SQLiteConn.DBFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                                      "SQLite dbs", "CryptoTest.db")

Note that each time you reference something like My.Resources.ballorange a brand new image object is created. You only need to set the PasswordImg once ever. If you were to change the code to accept the image as an argument, you run the risk of leaking resources as a side effect of creating connections.

Create DB

Dim tmpConnStr = String.Format("Data Source='{0}';Version=3;{1}", SQLiteConn.DBFile, "")

Dim sql = <sql>CREATE TABLE CryptoTest (
                    Id             INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
                    Name           TEXT,
                    Value          INTEGER);</sql>.Value

Using dbCon As New SQLiteConnection(tmpConnStr)
    ' tell helper to set PW, encrypt file
    SQLiteConn.SetPassword(dbCon)
    dbCon.Open()
    Using cmd As New SQLiteCommand(sql, dbCon)
        cmd.ExecuteNonQuery()
    End Using
End Using

I would avoid creating the DB on the client machine. If they are supplied with an empty but encrypted DB file as part of installation, it reduces the ease of patching your code to never encrypt it in the first place. Just distribute a blank like any other data file, installing it to the desired path.

Not many SQLite browser tools support encryption though, so you may need the above to apply that encryption.

Write To DB

Using dbcon As New SQLiteConnection(SQLiteConn.GetLiteConnStr())
    dbcon.Open()

    Using cmd As New SQLiteCommand("INSERT INTO CryptoTest (name, value) VALUES ('foo', 7)", dbcon)
        cmd.ExecuteNonQuery()
    End Using
End Using

Read DB

Using dbCon As New SQLiteConnection(SQLiteConn.GetLiteConnStr())
    Using cmd As New SQLiteCommand("SELECT * FROM CryptoTest", dbCon)
        dbCon.Open()

        dtSample = New DataTable()
        dtSample.Load(cmd.ExecuteReader())
    End Using
End Using

dgv1.DataSource = dtSample

It works fine:

It is not fool proof, they can use ILSpy or other decompiler to see what you are doing, but that will always be the case with a NET app. However, thats just the first hurdle - they also need to work out how to get that runtime data.

When you issue updates/bug fixes you could also change the password by using a different image, changing a few pixels, or altering the methods slightly (convert the byte array to a Base64 string for example).

It is certainly better then security by obscurity (concatenating strings) and may suffice for what you are trying to do.