SQLite Client Server

[Richard Kelly]

Designing a SQLite Client Server. Go along with me while it evolves.

It all starts with a windows socket class that will underpin the client and server classes. Here is the socket class. I can't test everything at this point and the small script below is as far as I've gotten so far. I'll want an authentication handshake between the client and server and that will be next.

You can copy the attachment and add the script in a console session or use FB Firefly and put the script in a button object (be sure console option is turned on). The client and server classes will 'hide' most of the socket details.

Code Select Expand


Dim oSocket         as cCTSocket
Dim sLocalHostName  as String
Dim szHostName      as ZString * 260
Dim szIPAddress     as ZString * 30
Dim SockAddress     as SOCKADDR_IN
Dim hSocket         as SOCKET
Dim sSend           as String
Dim sResponse       as String
Dim sHeaders        as String
Dim sBody           as String
Dim sWANIP          as String
Dim iIndex          as Long

    oSocket.PreferredAddress(sLocalHostName,SockAddress)
    oSocket.IPToString(SockAddress.sin_addr.s_addr,szIPAddress) 
    Print "Local Host Name=" + sLocalHostName
    Print "Preferred Local IP=" + szIPAddress
       
' Get WAN IP and FQDN

    szHostName = "checkip.dyndns.org"
    oSocket.IPFromHostName(szHostName,szIPAddress)
    sSend = "GET /" + " HTTP/1.1" + Chr(13) + Chr(10) + "Host: " + szHostName + Chr(13) + Chr(10) + Chr(13) + Chr(10)
   
    Print "Connecting to: " + szHostName + " (" + szIPAddress + ")"
   
    oSocket.TCPConnect(hSocket,szIPAddress,8245,30000)
    oSocket.SendAndReceiveSocket (hSocket,1024,sSend,sResponse,20)
    oSocket.Disconnect(hSocket)
   
    iIndex = InStr(sResponse,Chr(13) + Chr(10))
    sHeaders = Left(sResponse,iIndex - 1)
    sBody = Right(sResponse,Len(sResponse) - iIndex - 1)
    iIndex = InStr(UCase(sBody),"CURRENT IP ADDRESS: ")

    Print "Response..." + Chr(13) + Chr(10)
    Print "Header..." + Chr(13) + Chr(10) + Chr(13) + Chr(10) + sHeaders + Chr(13) + Chr(10)
    Print "Body..." + Chr(13) + Chr(10) + Chr(13) + Chr(10) + sBody + Chr(13) + Chr(10)
   
    sWANIP = Mid(sBody,iIndex + 20)
    iIndex = InStr(sWanIP,"<")
    szIPAddress  = Mid(sWANIP,1,Len(sWANIP) - iIndex - 1)
    oSocket.HostNameFromIP(szIPAddress,szHostName)
   
    Print "WAN IP=" + szIPAddress + ",FQDN=" + szHostName


[Richard Kelly]

Staying with proven algorithms such as AES/SHA-256 and keeping things relatively simple while providing a reasonable level of secure communications between the client and server without the requirement for SSL/certificates, I've come up with the following:

1. Encryptions keys are generated using two 32 bit prime values, multiplied together to yield a 64 bit result (ULONGINT). A list of, say, hundreds of 32 bit primes will be included for an indirect, indexed selection. With each connection, the server transmits the four index values to use for the duration of the connection, two for the client, and the two the server will use. 200 values has 1,600,000,000 possible combinations of 4 values. There is one common, shared encryption key used only for the passing of the 4 indexed session values as well as server UDP responses.

2. Use windows BCryptGenRandom function to generate random values

3. Use windows BCryptHashData (SHA-256) for hashing

4. Use windows BCryptOpenAlgorithmProvider for AES-256 encryption/decryption

5. All communication between the server and client is encrypted, client encrypts with the server key and server encrypts with the client key

6. Handshake

a. Client connects and receives the server session index list
b. Client generates a random 64 bit value
c. Client encrypts the random 64 bit value with client session key
d. Client hashes the clear random 64 bit value
e. Client encrypts the hash using the server key
f. Client combines the encrypted random 64 bit value and the encrypted hash and sends to server for authentication
g. Server responds, if authentication is successful with steps b-f and client can also authenticate the server
h. Session request/response until completed initiated by client

Any real weaknesses?

[Richard Kelly]

Here is the crypto class for the project.

Notes:

1. By allowing manipulation of the encryption Initialization Vector (IV), you can use the same base key with billions of different IV's.
2. The encryption/decryption routines were written they way they were so I can add encrypt/decrypt functions later on for files.
3. Standard Windows BCrypt api's were used. I think Vista or later is required. I open the handles once in class constructor and release in deconstructor.
4. I'll probably add Base24 later on - I got some hazy ideas about how and when to use B24 encoding later on.
5. Nothing from Jose's includes....yet...more to come later. He has some nice stuff I can leverage.

You can copy the attachment and add the script in a console session or use FB Firefly and put the script in a button object (be sure console option is turned on).

The script is modeled on the outline of the client/server authentication mentioned in the previous posting.

Let me know of any issues with either this class or the socket class.

Code Select Expand


#Include Once "string.bi"

Dim oCrypto as cCTSQLLiteCrypto
Dim iIndex1        as Long
Dim iIndex2        as Long
Dim sSessionKey    as String
Dim sSessionHex    as String
Dim sHash          as String
Dim sHashHex       as String
Dim sPlainText     as String
Dim sCipherText    as String
Dim sCipherHex     as String

' Client side

Dim oCryptoClient       as cCTSQLLiteCrypto
Dim iClientClientIndex1 as Long
Dim iClientClientIndex2 as Long
Dim iClientServerIndex1 as Long
Dim iClientServerIndex2 as Long
Dim sClientClientKey    as String
Dim sClientServerKey    as String
Dim sServerResponse     as String
Dim sClientDecrypt      as String
Dim sClientEncrypt      as String
Dim sClientHex          as String
Dim sClientRandom       as String
Dim sClientHash         as String
Dim sClientServerMessage      as String
Dim sClientServerAuth         as String

' Server side

Dim oCryptoServer       as cCTSQLLiteCrypto
Dim iServerClientIndex1 as Long
Dim iServerClientIndex2 as Long
Dim iServerServerIndex1 as Long
Dim iServerServerIndex2 as Long
Dim sServerClientKey    as String
Dim sServerServerKey    as String
Dim sClientResponse     as String
Dim sServerIndices      as String
Dim sServerClientRandom as String
Dim sServerClientHash   as String
Dim sServerDecrypt      as String
Dim sServerHex          as String
Dim sServerHash         as String


   Print "Client connects..."
   Print "Server randomly selects session crypto indices and sends to client..."
   
   sServerClientKey = oCryptoServer.SessionKey(iServerClientIndex1,iServerClientIndex2)
   sServerServerKey = oCryptoServer.SessionKey(iServerServerIndex1,iServerServerIndex2)
   
   sServerIndices = Format(iServerClientIndex1,"0000") + Format(iServerClientIndex2,"0000") _
                  + Format(iServerServerIndex1,"0000") + Format(iServerServerIndex2,"0000")
                 
   Print "Server clear connection response=" + sServerIndices
                 
' Encrypt response with shared key and respond

   oCryptoServer.EncryptText(sServerIndices,sServerResponse,5,6)
   
   Print "Client receives the response with session indices..."
   oCryptoClient.Bin2Hex(sServerResponse,sClientHex)
   Print "Encrypted server response=" + sClientHex
   oCryptoClient.DecryptText(sServerResponse,sClientDecrypt,5,6)
   Print "Decrypted server response=" + sClientDecrypt

' Save Encryption indices from server
   
   iClientClientIndex1 = Val(Left(sClientDecrypt,4))
   iClientClientIndex2 = Val(Mid(sClientDecrypt,5,4))
   iClientServerIndex1  = Val(Mid(sClientDecrypt,9,4))
   iClientServerIndex2  = Val(Mid(sClientDecrypt,13,4))
   
' Client generates a random 64 bit value and then hashes it

   oCryptoClient.RandomString(sClientRandom,8)
   oCryptoClient.HashString(sClientRandom,sClientHash)
   sClientServerAuth = sClientRandom + sClientHash
   
   oCryptoClient.Bin2Hex(sClientServerAuth,sClientHex)
   Print "Client Clear Auth Message=" + sClientHex
   
' Client encrypts random value with client session key

   oCryptoClient.EncryptText(sClientRandom,sClientServerAuth,iClientClientIndex1,iClientClientIndex2)
   
' Client encrypts hash with server key

   oCryptoClient.EncryptText(sClientHash,sClientEncrypt,iClientServerIndex1,iClientServerIndex2)

' This is the encrypted client authentication request message sent to the server

   sClientResponse = sClientServerAuth + sClientEncrypt

   oCryptoClient.Bin2Hex(sClientResponse,sClientHex)
   Print "Client Encrypted Auth Message=" + sClientHex
   
' Server receives the client authentication message

   sServerClientRandom = Left(sClientResponse,16)
   sServerClientHash = Mid(sClientResponse,17,48)
   
' Server decrypts random value with client key

   oCryptoServer.DecryptText(sServerClientRandom,sServerDecrypt,iServerClientIndex1,iServerClientIndex2)
   
   oCryptoServer.Bin2Hex(sServerDecrypt,sServerHex)
   Print "Client Random value received=" + sServerHex
   sServerClientRandom = sServerDecrypt
   
' Server decrypts hash value with server key

   oCryptoServer.DecryptText(sServerClientHash,sServerDecrypt,iServerServerIndex1,iServerServerIndex2)
   
   oCryptoServer.Bin2Hex(sServerDecrypt,sServerHex)
   Print "Client hash value received=" + sServerHex
   sServerClientHash = sServerDecrypt
   
' Server hashes the random value and compares

   oCryptoServer.HashString(sServerClientRandom,sServerHash)

   oCryptoServer.Bin2Hex(sServerHash,sServerHex)   
   Print "Server hash check=" + sServerHex
   
   If sServerClientHash = sServerHash Then
   
      Print "Client authenticated..."
     
   Else
   
      Print "Client authentication failed..."
     
   End If


[Richard Kelly]

Time to start thinking about the SQLite class. Since I want it to include a connection pool for reusable sqlite connection handles for maximum concurrency, here are my thoughts on a connection pool class.

1. Database file names (i.e. mydb.db) are registered with the class and assigned an id provided to the client by the server
2. Pool tracks sqlite connection handles by the database id
3. Pool contains the database id, sqlite connection handle, and the connection status (free or in use)
4. Functions
   a. Get a connection from the pool
   b. Return a connection to the pool
   c. Add a connection to the pool
   d. Remove a connection(s) from the pool
   e. Enumerate the pool
   f. Shutdown the pool (shuts down all functions except pool enumeration and returning a connection to the pool)

The pool object itself will be gated by a mutex to support a multithreading server where each thread will have it's own sqlite connection handle.

I'll be out of town on and off for the next few weeks. Be patient grasshoppers.

Thoughts?

[Paul Squires]

Looking forward to this! An updated sqlitening type of product is needed especially with no PB 64 bit in sight.
Make sure to keep everything simple. It will be easier for folks like me to jump in and help.

[Richard Kelly]

Looking forward to this! An updated sqlitening type of product is needed especially with no PB 64 bit in sight.
Make sure to keep everything simple. It will be easier for folks like me to jump in and help.

It has to simple for me to remember in later years how it all works...:-)

I'm planning on using the sqlite3_exec() convenience wrapper which will really cut down on development time. The server will have options to emulate stored procedures like MS SQL Server has by stashing SQL code in a special table and the ability to replace named token placeholders. Responses will be constructed like variable length records from my mainframe days. #rows, #columns,length, value (repeated however many times it takes). I'll always return exactly what SQLite gives me.

[Paul Squires]

I found that the networking part was the "easiest"(?) part of the library but then again I wasn't using compression or encryption. The sending and receiving of easy EXE queries is pretty simply because you only need to acknowledge the client that the call was successful. The last part that I was working on was the more involved queries that return 10's or 1000's of results. I know that in sqlitening the code is somewhat complex in that area because it involves a pretty detailed custom return format and the creation of temporary local data files to store results as the client traverses through it. I think the idea of using xml, json or bson is a great approach for simplicity of sending and receiving data and also being able to have a standard industry accepted way of reading and manipulating the data. My vote would be for json because it is easier and less weight than xml. Because at times you will be transferring blobs of data then I would tend to gravitate towards bson.

[Richard Kelly]

I understand your perspective. Looking ahead, I was planning on the most compact form for the results transmission. I was also going to look at compressing it with zlib then encrypting before transmission. AES encryption would add, at most 16 bytes. The client class can reconstruct things and emit any format, such as bson, json, xml, or, a just a regular FB array. I was also planning on taking another look at the previous classes to see if using Jose's Unicode string class would be beneficial. Sockets don't care what they send and receive, it's just a byte stream.

Rick

[Richard Kelly]

Before I take some time to chase some rainbow and brown trout on the fly, I thought I would outline what the end game looks like. At this point, the classes I've shared are just part of the foundation.

Client Class

This class handles all the communication with the server. The socket and crypto classes live here. "Finding" the server and passing requests and receiving results is the main purpose. The server will be either local on the same subnet or remote on the other side of a router. Local servers will be "found" via UDP broadcasting or you'll have to provide name or IP of a remote server with port forwarding rules in the router. This is the "base" client class which returns results in array and "raw" format. I'll code up a thin wrapper for JSON/BSON result format and you can use this to wrap the class to better suit your application. My app wrapper for this class will return array of application specific records with whatever additional calculations needed. The client class will periodically poll the server to maintain a connection status and handle timeouts/retries - all those nasty tcp details. There is also some "feature" negotiation with the server that is supported.

Server Class

This class communicates with the SQLite database. SQLite DLL, socket, crypto, and connection pool classes live here. There will be a few features to assist. Two I have in mind are a table of named prebuilt SQL code snippets with named parameter substitutions and an in-memory scratch pad database. I'm also thinking about having a named locking feature not directly tied to SQLite but just a normal special file that I can FLOCK to help apps with multiple clients address event synchronizations. You'll still need some GUI kind of server program that works with the server class (specifically a message loop for the server class to SENDMESSAGE with) and I'll code up a working template. The server class will also handle backups, both on request and timed. If time permits, I think it would good to add FTP and secure Email as well.


Supporting these two classes will be multiple foundation classes. Two have been presented. I'm working on the specs for the connection pool class and a database pool class for the server and a shared class that builds and parses results into a common internal format. There might some more as I work through all the design details.

Add to this your thoughts and we can discuss getting other things "baked" in.

[Richard Kelly]

Here is the server connection pool class for the project.

The test script attempts to simulate a multithreaded server. I thought throwing a bunch of threads at the connection pool would give it a good workout. Most of the time on my computer the pool size held at 1, occasionally I would see one new connection added which supports the idea of a shared connection pool for SQLite calls. Feel free to experiment with different loading levels.

You can copy the attachment and add the script in a console session or use FB Firefly and put the script in a button object (be sure console option is turned on).

Let me know of any issues with this class.

Code Select Expand



#Include Once "string.bi"
#Include Once "fbthread.bi"


Dim Shared oPool as cCTConnectionPool

Dim iPoolID                     as Long
Dim iPoolSize                   as Long
Dim iTotalCurrentConnections    as Long
Dim iTotalActiveConnections     as Long
Dim nAverageRequestMilliseconds as Double
Dim arPool()                    as POOL_CONNECTION

' Create one pool connection for threads to request

Print "Adding pool connection,DatabaseID=1,ConnectionID=1"
Print ""

oPool.AddPoolConnection(1,1)
oPool.ReleasePoolConnection(1,1)

Print "Starting 1000 threads..."
Print ""

For iPoolID = 2 To 1001

ThreadDetach(ThreadCreate(@thread,iPoolID))
Sleep 10,1

Next

' Give threads some time to complete

Sleep 25,1

Print "Checking pool statistics..."
Print ""

oPool.PoolConnectionStatistics (iPoolSize,iTotalCurrentConnections,iTotalActiveConnections,nAverageRequestMilliseconds)

Print "Pool size=" + Format(iPoolSize)
Print "Total Current Connections=" + Format(iTotalCurrentConnections)
Print "Total Active Connections=" + Format(iTotalActiveConnections)
Print "Average connection in use time=" + Format(nAverageRequestMilliseconds) + " milliseconds"
Print ""
Print "On server shutdown, the following connections need to be closed..."
Print ""

oPool.PoolConnectionDetail(arPool())

For iPoolID = 0 To UBound(arPool)

' For demo only, we won't check for the connection still in use, the server will have to allow for connections still in use during shutdown

Print "Close DatabaseID=" + Format(arPool(iPoolID).DatabaseID) + ",ConnectionID=" + Format(arPool(iPoolID).ConnectionID)
oPool.RemovePoolConnection(arPool(iPoolID).DatabaseID,arPool(iPoolID).ConnectionID)

Next

Sub thread( ByVal userdata as Any Ptr )

Dim as Long iConnectionID

' If pool connection request is false then simulate adding a new database connection

If oPool.RequestPoolConnection(1,iConnectionID) = False Then
   iConnectionID = CLng(userdata)
   oPool.AddPoolConnection(1,iConnectionID)
   
End If

' Simulate some database request

Sleep 10,1

' Release the connection back to the pool

oPool.ReleasePoolConnection(1,iConnectionID)

End Sub


[Richard Kelly]

Before diving in to the heart of the server, I took a bit of thought to work through how the server is designed. Look at the attachment - comments welcomed.

We've already shown how the handshake is going to work. The next design point is the Database Collection class and the session negotiations between the client and server.

Rick

[Richard Kelly]

Attached is the SQLite class that will support the server database collection class. You need to download the SQLite3.dll from https://www.sqlite.org/download.html and save it in the same directory as your EXE. Not sure if the class is complete and future changes might be necessary as the server class evolves.

You can copy the attachment and add the script in a console session or use FB Firefly and put the script in a button object (be sure console option is turned on).

Code Select Expand


Dim oSQLite                  as cCTSQLite
Dim sStartUpErrorDescription as String
Dim hDbc                     as DWORD
Dim iErrorCode               as Long
Dim zDatabaseName            as ZString * 260
Dim szSQL                    as ZString * 260
Dim arResults()              as String
Dim iCols                    as Long
Dim iRows                    as Long
Dim sErrorDescription        as String
Dim lResult                  as BOOLEAN
Dim iIndex                   as Long
Dim iRowIndex                as Long
Dim sResultRow               as String

   If oSQLite.StartupStatus (sStartUpErrorDescription) = False Then
   
      Print "Server startup failed. " + sStartUpErrorDescription
      Exit Function
     
   End If

   Print "SQLite Library version=" + oSQLite.Version
     
   zDatabaseName = ":memory:"
   If oSQLite.OpenDatabase(zDatabaseName,hDbc,iErrorCode,cCTSQLiteClass.SQLITE_OPEN_READWRITE + cCTSQLiteClass.SQLITE_OPEN_CREATE) = False Then
   
      Print "Open of " + zDatabaseName + " failed. Code=" + Str(iErrorCode)
      Exit Function
   End If
     
   Print "Open of " + zDatabaseName + " was successful,Database Version=" + Str(oSQLite.DatabaseVersion(hDbc))

' Create a table
   
   szSQL = "CREATE TABLE POSTAL (POSTALID INTEGER PRIMARY KEY," _
         + "CTRYCD TEXT," _
         + "POSTALCD TEXT," _
         + "CITYNAME TEXT," _
         + "STATE TEXT," _
         + "DATELASTMAINT TEXT DEFAULT (DATETIME('NOW')))"
   
   
   lResult = oSQLite.SQLExec(hDbc,szSQL,arResults(),iCols,iRows,iErrorCode,sErrorDescription)
   
   If lResult = False Then
   
      Print "Table create failed,error=" + sErrorDescription
      Print "Extended error=" + oSQLite.SQLExtendedErrorDescription(hDbc,iErrorCode)
      oSQLite.CloseDatabase(hDbc,iErrorCode)
      Exit Function
     
   End If

   Print "Table create successful..."   
   
' Insert some rows

   szSQL = "INSERT INTO POSTAL (POSTALID,CTRYCD,POSTALCD,CITYNAME,STATE) VALUES (1,'USA','00501','Holtsville','NY');" _
           "INSERT INTO POSTAL (POSTALID,CTRYCD,POSTALCD,CITYNAME,STATE) VALUES (2,'USA','00544','Holtsville','NY')"
           
   lResult = oSQLite.SQLExec(hDbc,szSQL,arResults(),iCols,iRows,iErrorCode,sErrorDescription)
   
   If lResult = False Then
   
      Print "Insert rows failed,error=" + sErrorDescription
      oSQLite.CloseDatabase(hDbc,iErrorCode)
      Exit Function
     
   End If
   
   Print "Row insert successful..."
   
   szSQL = "select * from POSTAL"
   
   lResult = oSQLite.SQLExec(hDbc,szSQL,arResults(),iCols,iRows,iErrorCode,sErrorDescription)

   If lResult = False Then
   
      Print "Select rows failed,error=" + sErrorDescription
      oSQLite.CloseDatabase(hDbc,iErrorCode)
      Exit Function
     
   End If

   Print ""
   
   For iIndex = 0 To UBound(arResults) Step iCols   

       sResultRow = ""
       
       For iRowIndex = 0 To iCols - 1
       
          sResultRow = sResultRow + " " + arResults(iIndex + iRowIndex)
       
       Next
       
       Print sResultRow
       
   Next       

   Print ""
   
   Print "Close of " + zDatabaseName + "=" + Str(oSQLite.CloseDatabase(hDbc,iErrorCode))


[James Klutho]

Richard  -  Thanks for your cCTSQLite.inc code.  Nicely done.   I thought I would spend some time again with Freebasic and start updating an old program of mind and port it to Freebasic 64 bit.

Jim