REALSQL Server Blog

I just published this post on my personal blog. It is true also for the REALSQLServer.

The latest issue of FacFan (issue 67, http://www.macfan.nl) has a review of REALSQLServer RC1. Anyone understand German Dutch (thanks Andre) and can translate it for me?

We discovered a bug in R1 that prevents you to INSERT large sql statement into the server, large means sql strings bigger than 10/20KB (I am sorry I don’t remember exactly). To be really precise, the bug isn’t in the refuse from the server to execute big sql statements but in the way it is handled because instead of just returns an error message, the connection is dropped (well, to be really more precise here, the connection is compromised, not dropped).

We introduce this hard limit in sql statements for two reasons, the first one is for security because we don’t want to give the opportunity to bad programmers to send multi gigabytes sql statements to the server, the second reason is because a big sql statement isn’t the right way to “talk” with the server. (For big sql statement I mean a single string bigger than 10/20KB).

After this bad news, there are two good news now … the first one is that this bug has already been fixed and the second one is that there is a workaround and the workaround is the right way to send big INSERT statement to the server.

So, the right way to proceed is to use a DatabaseRecord class, for example:

Dim rec As New DatabaseRecord
rec.BlobColumn("image") = myJPEGFile
rec.IntegerColumn("id") = 512
rec.Column("comment") = "This is my test comment"
db.InsertRecord("myTable", rec)
db.Commit

Using the above code you’ll obtain a lot of advangates, for example no need to escape strings, no need to encode BLOB data and at the end the most important reason is that the statement can be sent in chunks to the server.

There are two bugs in the UPDATE SCHEDULE command, one is just in the documentation (missing commands and quotes) so the right documented syntax should be:

UPDATE SCHEDULE schedName SET DAYS=’days’, HOURS=hours, MINUTES=minutes, WEEKS=weeks, TYPE=’type’, OPTIONS=’options’, ENABLED=enabled

but even with this fix there is a bug in R1 that prevents it to works correctly.

As a workaround you can use this command (it will continue to work fine even with the fix I am writing for R2):

UPDATE SCHEDULE schedName SET scheddays=’days’, schedhours=hours, schedminutes=minutes, schedweeks=weeks, schedtype=’type’, schedoptions=’options’, schedenabled=enabled

Please note that you aren’t forced to set all the fields, but just the ones you need to update, so for example is perfectly legal to use a command like:

UPDATE SCHEDULE schedName SET scheddays=’days’, schedhours=hours

SHA-AES Security Method
This post provides a description of the SHA-AES method so as to reassure users of its security (and allow them to identify any defects). The SHA-AES security method is the most secure and the fastest security method provided by the REALSQLServer. It employs standard algorithms published and certified by the US Government.

Cryptographic Algorithms

It is foolish to attempt to devise and trust new cryptographic algorithms for particular products because the strength of an algorithm is usually derived solely from its reputed uncrackability, and this property can only be built up over time. For this reason, the REALSQLServer network protocol only uses well-known and trusted cryptographic algorithms. Here are the algorithms the protocol employs:

SHA-1: This is NIST’s secure hash function. It reads a block of bytes (of any length) and produces a cryptographically strong 160-bit (20 byte) hash of the block. In this page, the SHA-1 of data block X is written as H(X).

AES-CBC: This is NIST’s AES (Advanced Encryption Standard) algorithm. On January 2nd, 1997, NIST made a call for algorithms to replace the aging DES (Data Encryption Standard) algorithm. Out of fifteen AES candidate algorithms, Rijndael was selected on October 2nd, 2002. This algorithm is considered more secure than the Triple-DES algorithm. REALSQLServer uses the algorithm with a 128/192/256 bit key and in Cipher Block Chaining Mode (CBC). REALSQLServer’s implementation comes from Dr Brian Gladman. The algorithm is reputedly uncracked. In this page, the AES-CBC encryption of data block X with key K is written as AESCBC(X,K).

These algorithms are combined in various ways to create the protocol.

Step By Step: The Protocol Conversation

In the following, A;B means the data block A followed by the data block B.

The protocol is based on the client and server sharing knowledge based to a secret password P that is specific to the server. The password consists of a sequence of one or more characters taken from the set of decimal digits and upper and lower case letters. The password is represented as ASCII bytes for processing.

The server’s configuration file contains H(H(P)). This is expressed in hexadecimal following the SHA-AES: method keyword. Because the server only stores H(H(P)), it is not possible to determine the password of a virtual server from its configuration file entry. Connections proceed as follows and the conversation is aborted at each stage if any check fails.

Client sends random X: If the client is happy with the use of the SHA-AES security method, it generates a 20-byte random number X and sends AESCBC(X;H(X),H(H(P))) to the server.

Server checks X: The server uses H(H(P)) to decrypt the message from the client and retrieve X and H(X). It then calculates H(X) from X and compares it to the H(X) sent by the client. If the two match, then this proves that the message was encrypted by H(H(P)) and this proves that the client possesses H(H(P)) which is enough authentication for the server to proceed to the next step.

Server sends random Y: Now it’s the server’s turn to generate a random number. The server generates a 20-byte random number Y and sends AESCBC(Y;H(Y),H(H(P))) to the client. The server guarantees to never generate Y=X.

Client checks Y: The client uses H(H(P)) to decrypt the message from the server and retrieve Y and H(Y). If Y is equal to the random number X previously sent, the client assumes that it is talking to a spoof server that is conducting a replay attack and it terminates the conversation. The client then calculates H(Y) from Y and compares it to the H(Y) sent by the server. If the two match, then this proves that the message was encrypted by H(H(P)) and this proves that the server possesses H(H(P)) which is enough authentication for the client to proceed to the next step.

Calculation of session key S: The client and server then each independently calculate 160-bit session key S as S=H(H(H(P));X;Y). The session key includes the two random numbers which ensure that each session key is likely to be unique, along with the secret information H(H(P)) shared by the client and the server. The secret information is a good backup in case the client and server’s random number generators are predictable for some reason. All this information is hashed to yield the session key so that if someone somehow uses the streamed data to determine the session key, they still cannot determine H(H(P)). The session key is not transmitted by either the client or the server.

Client sends H(P);PADx12: To prove that it knows P, the client sends the server AESCBC(H(P);PADx12,S). PADx12 refers to 12 bytes of padding, this is necessary since the AES algorithm encrypts in blocks of 16 bytes. The server decrypts H(P) using the session key. It then calculates H(H(P)) from the H(P) just sent and compares the calculated H(H(P)) with the H(H(P)) provided by the configuration file. If the two match, then this proves that the client knows H(P) (and therefore almost certainly P). This is the final piece of authentication required by the server and an important piece because by providing H(P), the client proves that it did not merely get a look at H(H(P)) in the configuration file. This step of the protocol prevents those who see H(H(P)) in the configuration file from connecting. To connect, you must know H(P) and hence effectively P.

AES-CBC encryption: Once the exchanges described above have taken place, the remainder of the TCP connection is encrypted using AES-CBC encryption with first 128 bits of the session key S.

Protocol Properties

The protocol has the following properties:

Password P is never transmitted: The password P is never transmitted through the network, not even in encrypted form. Only the hash of the password is transmitted and even that is heavily AES-CBC encrypted first. Furthermore, the protocol does not transmit H(H(P)) in plain or encrypted form, and it does not transmit H(P) in plaintext.

Config file does not reveal password: The configuration file does not contain the password. It only contains H(H(P)).

Reading config file does not allow access: The configuration file contains H(H(P)) only. But H(P) is required to connect. This means that read-access to the server’s configuration file does not allow access to the server.

Reading config file enables eavesdropping: If an intruder can read the configuration file AND can sniff packets, then the intruder can decrypt any conversation with the server. If the intruder can decrypt a conversation, it can determine H(P) and thereafter connect to the server himself at will. Therefore it is important either to keep the server configuration file secret or to ensure that no one can read the packets of connections to the server.

Reading config file enables spoofing: If you can read the configuration file and can divert packets headed for the server, it might be possible to create a spoof server. Therefore if packet diversion is a possibility at your site, it is important to keep the configuration file secret.

Server crypto-silent until it sees H(H(P)): The protocol is designed so that the server does not provide any crypto-related information to the client until the client sends it H(H(P)).

Two-way authentication with zero leakage: In addition to the server initially authenticating the client by requiring evidence of knowledge of H(H(P)), the client authenticates the server by requiring evidence of knowledge of H(H(P)). Neither proof conveys H(H(P)) to the other party, so the failing party does not obtain any information from the failed transaction.

H(P) transmission protected: The client only hands over H(P) after the client has authenticated the server and has set up a session key with which to encrypt H(P).

Protocol Design Notes

The following design notes may be of interest:

Two levels of hash needed: If the server stored H(P) in its configuration file instead of H(H(P)), then the client would have to send P to prove that it knows P. This would involve transmitting P (admittedly encrypted) which seemed like a bad idea. The current protocol does not ever transmit P, either encrypted or unencrypted. By eliminating P itself from all storage and communication, the protocol reduces the chance of a human getting their hands on P.

Dual contribution to session key: Both the client and the server contribute to the randomness of the session key. This is important because if the random component of the session key were generated entirely by one party, the other party might worry that the first party might be compromising the security of the connection by using a random key that wasn’t sufficiently random.

Playback attacks eliminated: The protocol eliminates playback attacks by either party because both sides generate part of the session key and the communication can only be understood by an entity that possesses H(H(P)).

That’s all

I am quite sure that few people really understand how much effort we put into the server about security and encryption. The server can communicate with the clients using the AES 128/192/256 protocol. The code used in the server is similar to the code used in the latest WinZip application.

Yes OK, but what is AES?

As the culmination of a four-year competitive process, NIST (the National Institute of Standards and Technology) has selected the AES (Advanced Encryption Standard), the successor to the venerable DES (Data Encryption Standard). The competition was an open one, with public participation and comment solicited at each step of the process. The AES, formerly known as Rijndael, was chosen from a field of five finalists.
The AES selection was made on the basis of several characteristics:

• security
• unclassified
• publicly disclosed
• available royalty-free, worldwide
• capable of handling a block size of at least 128 bits
• at a minimum, capable of handling key sizes of 128, 192, and 256 bits
• computational efficiency and memory requirements on a variety of software and hardware, including smart cards
  flexibility, simplicity and ease of implementation
• The AES will be the government’s designated encryption cipher. The expectation is that the AES will suffice to protect sensitive (unclassified) government information until at least the next century.

REALSQLServer uses AES with CBC mode (Cipher Block Chaining). This mode requires an Initialization Vector (IV) that is the same size as the block size. Use of a randomly generated IV prevents generation of identical ciphertext from packets which have identical data that spans the first block of the cipher algorithm’s block size. The IV is XOR’d with the first plaintext block before it is encrypted. Then for successive blocks, the previous ciphertext block is XOR’d with the current plaintext, before it is encrypted.

REALSQLServer’s implementation comes from Dr Brian Gladman and the algorithm is reputedly uncracked.
In a next post I’ll write much more details about how AES is used into the server…

I think that one of the most undervaluated feature of the REALSQLServer is its powerful scheduling subsystem. You can think at it as a fully featured cron manager that lives inside the server. With the scheduling system you can schedule backups, SQL commands and even shell scripts. Unfortunaly the admin doesn’t yet exposes all of its power so I’ll try to fill the gap with this blog entry.

Actually there are 8 custom commands related to the schedule:

• CREATE SCHEDULE schedName DAYS d HOURS h MINUTES m WEEKS w TYPE BACKUP|SQL|SHELL OPTIONS opt ENABLED 1|0
• DROP SCHEDULE schedName
• ATTACH SCHEDULE schedName TO DATABASE dbName
• DETACH SCHEDULE schedName FROM DATABASE dbName
• SHOW SCHEDULE schedName
• SHOW SCHEDULES FOR DATABASE dbName
• SHOW SCHEDULES
• UPDATE SCHEDULE schedName SET …

The right way to proceed should be first to create a schedule and then attach it to the various databases.

So for example if you want to VACUUM a database at a fixed time then you could create a schedule like this:
CREATE SCHEDULE vacuumDB DAYS 0 HOURS 23 MINUTES 0 WEEKS 1 TYPE SQL OPTIONS VACUUM ENABLED 1 (it means execute the vacuumDB schedule every Sunday at 11:00 PM of every week).

Please note that I plan to post a full ScheduleManager written in RealBasic in the near feature.

When I first started developing SQLiteServer my goal was that it should be able to be installed also on embedded devices (linux based), this is the main reason why the server has a so low memory footprint compared to any other sql database solution. The REALSQLServer is written entirely in ANSI C and the core executable size is just about 500KB.

In order to be able to run correctly the server needs about 2/3MB of RAM when it startups and each new connection requires about 100KB RAM (well, it is a lot less but I am exploring the worse scenario).

Each connection reads a CHUNK_SIZE setting (fixed to 512KB in R1 but it will be fully customizable in the next R2 version) that defines the maximum size of each block sent from the server to the client. So for example if a client requires a 1GB cursor (it is like a RealBasic RecordSet) then the server will send the cursor in chunks each of 512KB in size. If we explore the worse scenario we can also try to find out a formula that gives us an estimated memory requirement based on N, where N is the maximum number of simultaneous connection to the server. A possible formula could be:

MemRequirement: 5MB + ((512KB + 100KB) * N) + (256KB * N) = 5MB + 868KB*N
Following this formula if you want to be able to satisfy 100 concurrent connection than you should have about 95MB of memory dedicated to the server.

Real memory requirement should be lower (especially on Intel based processor) but the formula should be fine for the worse scenario.

On May 5, 2005 REAL Software, Inc. acquired SQLite based database technology developed by SQLabs. This technology includes SQLiteServer. After about one year of development SQLiteServer becomes REALSQLServer and we continue to be the main developers behind it.

The new REALSQLServer R1 was announced on July 25, 2006 by REAL Software.
The new server is blazing fast and it contains a lot of hidden features, with this blog I’ll try to expose the majority of them and I’ll try also to explain the most difficults and interesting topics.

During the next weeks I’ll post some projects related to the REALSQLServer and anyone who want to share his own projects is welcomed to send me code, tips, announces and so on…