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.
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
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?
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.
#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
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?
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. :)
Quote from: TechSupport on April 18, 2017, 11:05:26 PM
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.
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.
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
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.
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.
#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
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
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 (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).
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))
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