Encryption of a string

kbmMW Professional and Enterprise edition contains multiple cipher and hash algorithms. This short blog post shows how to use them to encrypt a string or binary data.

kbmMW bundles AES/Rijndael, Twofish, Blowfish, Misty, RC2, RC4, RC5, RC6, Tea, Serpent, Mars, IDEA, ICE, DES, 3DES, CAST128 and CAST256 encryption algorithms.

The currently most widely used algorithm around is AES so lets use that to encrypt a string.

function EncryptValue(const AValue:string; 
  const AKey:string):string;
var
   aes:TkbmMWCipherAES;
begin
   aes:=TkbmMWCipherAES.Create(nil);
   try
     aes.InitString(AKey,TkbmMWHashSHA256);
     Result:=aes.EncryptString(AValue);
   finally
    aes.Free;
 end;
end;

It is as simple as that.

However there are many options one can choose for how the encryption shall take place, amongst others, how the string key should be converted to something that can be used for encryption, and because all encryption algorithms works on blocks of bytes, how UTF16 (unicode) strings should be converted to bytes upon encryption.

The InitString method defineds what key to use (in this case a Unicode string that is automatically converted to UTF8 before use, and that is then hashed using the SHA256 hashing algorithm, which is the defacto standard way to prepare keys for general AES encryption.

However to make things harder to break, you could use a different hashing algorithm, because most encryption breakers assume that your key is hashed using SHA256. Just make sure to use another hashing method that also outputs 256 bit keys and that is considered a strong hash.

However a better way to confuse encryption breaking, is to add a salt to your key.

The salt is a bit of information that is only known by you and the other end. It can be a machine ID, a pin code or something else that both sides knows about.

So if your key would be THIS IS MY WEAK KEY, you could salt it which would add some technical stuff to the start of it, making it more difficult to break, because now two things needs to be known to be able to guess your key.

A salt is typically an application and installation specific thing, so one instance of an installed server application will have its own unique salt, that all its clients also needs to know about. You could even give each client his own personal salt, as long as you are able to manage that on the server.

A stupid example could be to salt with the external IP address a client is approaching the server with.

Then when the user logs in via one client, the data will be encrypted differently to when the client logs in via another client.

kbmMW supports adding salt to a key via the InitString/InitBytes methods.

Kim/C4D

User defined functions and kbmMemSQL

[updated with additional standard functions]

As you may know, the widely used kbmMemTable also supports querying and more using a SQL92 like syntax via the component TkbmMemSQL.

This article explains how to create UDF (user defined functions) that can be used as part of the SQL statement. Although UDF has been supported for a long time, it has been somewhat expanded in the upcoming release, why this article do reference features not available in the current kbmMemTable v. 7.76.

Standard UDF functions

 

kbmMemTable already comes with a fairly extensive set of standard UDF functions which can be used as part of your query, but you can expand and enable and disable which UDF functions should be available for your purpose.

Include kbmSQLStdFunc in your projects uses clause to get access to all the standard UDF functions, which includes the following, grouped by their logical category names:

  • MATH.TRIG
    • SIN(x)
    • COS(x)
    • TAN(x)
    • LOG(x)
    • LOG2(x)
    • EXP(x)
  • MATH
    • TRUNC(x) Returns integer part
    • FRAC(x) Returns fractional part
    • MOD(x,y) Return remainder after integer division
    • DIV(x,y) Integer division
    • SQRT(x) Square root
    • SQR(x) Square. Same as x*x
    • ROOT(x,y) Calculate y’th root of x.
    • MIN(x1..,xn) Return minimum argument value
    • MAX(x1..,xn) Return maximum argument value
    • AVG(x1..xn) Return average value of all arguments
    • SUM(x1..,xn) Return sum of all arguments
    • ABS(x) Return absolute (Non negative) value of x
    • POW(x,y) Return x in the power of y
  • STRING
    • UPPER(x) Return uppercase value
    • LOWER(x) Return lowercase value
    • TRIM(x) Return value trimmed for leading and trailing spaces
    • MID(x,y,z) Return z characters from x starting at y (first character is 1)
    • LEFT(x,y) Return y leftmost characters of x
    • RIGHT(x,y) Return y rightmost characters of x
    • LENGTH(x) Return the length in characters of x
    • LEFTPAD(x,y,z) Pad left of x with the character y until the complete result has a length of z
    • RIGHTPAD(x,y,z) Pad right of x with the character y until the complete result has a length of z
    • CHR(x) Convert the integer value x to a unicode character
    • POS(x,y) Return the position in y, where the substring x is to be found. 0 is returned if x is not in y
    • REPLACE(x,y,z,v,w) Search x for value y. When found, replace y with z. If v (optional) is true, then all occurrences are replaced. If w (optional) is true, then search is case insensitive
    • SPLIT(x,y,OUT z) Search x for the sub string y. When found return the leading part as result or null if nothing found. If z is provided (optional) then the trailing part is returned in the variable given by z. *NEW IN 7.77*
  • DATETIME
    • NOW Returns current date and time as a Delphi TDateTime type floating point value.
    • DATE(x) Returns the date part of x which is a Delphi TDateTime type floating point value, as an integer. Same as TRUNC(somedate)
    • TIME(x) Returns the time part of x which is a Delphi TDateTime type floating point value as a float. Same as FRAC(somedate)
    • YEAR(x) Returns the year as an integer (2017) of the Delphi TDateTime type floating point value.
    • MONTH(x) Returns the month as an integer (1 = Jan, 12=Dec) of the Delphi TDateTime type floating point value.
    • DAY(x) Returns the day of month as an integer (1..31) of the Delphi TDateTime type floating point value.
    • HOURS(x) Returns the hour value as an integer (0..23) of the Delphi TDateTIme type floating point value.
    • MINUTES(x) Returns the minutes part as an integer (0..59) of the Delphi TDateTime type floating point value.
    • SECONDS(x) Returns the seconds part as an integer (0..59) of the Delphi TDateTime type floating point value.
    • DATESTRING(x) Returns the date of a Delphi TDateTime type floating point value as a formatted string according to the FormatSettings defined on the TkbmMemSQL component.
    • TIMESTRING(x) Returns the time of a Delphi TDateTime type floating point value as a formatted string according to the FormatSettings defined on the TkbmMemSQL component.
  • CAST
    • CASTTODATETIME(x) Casts x to a Delphi TDateTime floating point value. If x is a string, it will be parsed according to the FormatSettings.
    • CASTTOSTRING(x) Casts x to a string. If x is a TDateTime value, it will be converted to a date/time string value according to the FormatSettings.
    • CASTTONUMBER(x) Casts x to a number. If x is a string, it will be converted to a floating point value according to the FormatSettings.
  • CONDITIONAL
    • IF(x,y,z,v) Depending on x, either y, z or v (optional) will be returned. If x is true, then y will be returned. If x is false, then z will be returned and if x is null then v alternative z will be returned if v is not specified.
    • ISNULL(x) Return true or false depending on if x is null.
  • CONVERSION
    • DATATYPE(x,OUT y, OUT z) Parses the SQL style datatype (eg. VARCHAR(30)) given in x, and returns the Delphi TFieldType best matching as an integer or NULL if the given SQL datatype is invalid. If y is provided (optional), the size of the declaration (eg 30) is returned in the referenced variable. If z is provided (optional), the precision of the declaration (eg 0) is returned in the referenced variable. *NEW IN 7.77*

 

Please take notice to the functions category names. They can be used to enable or disable whole groups of functions. E.g.

kbmSQLFunctionRegistrations.DisableGroup(‘MATH.TRIG’) would result in all the trigonometrical functions being unavailable for SQL expressions.

Obviously there is also an EnableGroup function. All registered functions are default enabled.

If you want to only disable a certain function, you can use:

kbmSQLFunctionRegistrations.DisableFunction(‘MONTH’)

Now the MONTH function will not be available for SQL expressions.

Creating a new custom UDF function

It is pretty easy to create a custom UDF function. Basically you will need to create a globally accessible function, and register it to kbmMemSQL.

Typically an UDF takes zero or more arguments and until and including v. 7.76 returns one single value. The UDF function is usually called many times depending on the actual SQL statement.

Let us look at how the SIN standard function is implemented.

function SQLSin(const AOperation:TkbmSQLCustomOperation; const ASituation:TkbmSQLFunctionSituation; const AArgs:TkbmSQLNodes; var AResult:variant):boolean;
begin
 kbmSQLCheckArgs(AArgs,1);
 case ASituation of
   fsWidth:
     AResult:=0;
   fsExecute:
     begin
       AResult:=AArgs.Node[0].Execute;
       if not VarIsNull(AResult) then
          AResult:=Sin(AResult);
     end;
   fsDataType:
     AResult:=ftFloat;
 end;
 Result:=true;
end;

The SIN UDF function takes 4 arguments:

AOperation is the current operation the function is being called from (it can be a select, insert etc. operation). This argument can usually be ignored, unless you want to get to the FormatSettings which is available as a property in the operation instance.

ASituation which indicates which situation the UDF is being called in. It can be one of fsWidth, fsDataType or fsExecute. The UDF will be called during compilation of the SQL statement to determined what type of data it will return, and what it expects the width of the returned data to be. And it will finally be called a number of times to execute the actual function at SQL execution time.

AArgs provides the arguments for the UDF function. Your function may require a minimum number of arguments, and to check for that it is recommended to use the kbmSQLCheckArgs function as shown in the example. The SIN function only expects one argument.

AResult is the parameter that should receive the outcome of the function. It must adhere to the data type which the UDF function provided at compilation time.

The SIN function is registered to kbmMemSQL by calling RegisterFunction, typically in the units Initialization section. E.g:

initialization
   kbmSQLFunctionRegistrations.RegisterFunction(‘MATH.TRIG’,’SIN’,@SQLSin);

Check the kbmSQLStdFunc.pas unit to see the how other standard functions has been implemented and registered.

The SIN function can for example be used like this:

SELECT SIN(field1) AS “Sineof”, field1 FROM sometable

But it can also be part of the condition and any other place in the SQL statement, where an expression can be given.

But what if you want to split a value into multiple values using an UDF function, how is that done? For example an UDF splitting a string in two parts.

SPLIT(x) Lets say we want to split x into two parts, exactly where there is a colon (:), but how will we get 2 strings back when we can only return one?

From kbmMemTable v. 7.77 it is possible (optionally) to use a sligltly different syntax in the SQL statement when calling UDF functions.

SELECT SPLIT(field1, OUT $var1) AS “Left”, $var1 as “Right” FROM sometable

The trick is to use the new OUT syntax. Now we will let the split function return all the data left of the colon (:), or if no colon is found all the data in x, and send the remaining data back as the contents of a variable called var1.

function SQLSplit(const AOperation:TkbmSQLCustomOperation; const ASituation:TkbmSQLFunctionSituation; const AArgs:TkbmSQLNodes; var AResult:variant):boolean;
var
  v:variant;
  s:string;
  i:integer;
begin
 kbmSQLCheckArgs(AArgs,2);
 case ASituation of
   fsWidth:
     begin
       AResult:=AArgs[0].Width;
     end;
   fsExecute:
     begin
       v:=AArgs[0].Execute;
       if VarIsNull(v) then
         Result:=Null
       else
       begin
         s:=v; 
         i:=pos(':',s);
         if i<0 then
            AResult:=s
         else
         begin
           AResult:=copy(s,1,i-1);
           kbmSQLSetVariableValue(AArgs[1],copy(s,i+1,length(s)));
         end;
       end;
     end;
   fsDataType:
     begin
       kbmSQLSetVariableMetaData(AArgs[1],ftString,AArgs[0].Width);
       AResult:=ftString;
     end;
 end;
 Result:=true;
end;

And register it:

initialization
   kbmSQLFunctionRegistrations.RegisterFunction(‘MYFUNCS’,’SPLIT’,@SQLSplit);

You can have as many OUT variables as you need, but remember that the function MUST return one value in the old fashioned way.

Also notice that the variable names must start with $, and that the variable values are not available until the UDF function has been called. Usually it’s thus recommended to call the UDF function as early as possible in your SQL statement, so the variables are available for the remainder of the current statement processing.

The variables are automatically cleared before attempting to process another record.

If you create some cool, can’t live without, UDF functions you think ought to be part of the standard SQL function library, ping us.

If you like this blog post or other posts on our blog, please spread the word and let others know about them!

Packaging files securely using TkbmMWLookupStreamStorage

Chinese blog by Xalion about how to use the kbmMW virtual filesystem called TkbmMWStreamStorage and its descendant TkbmMWLookupStreamStorage in combination with TkbmMWCrypt to pack multiple files/streams into a single searchable and encrypted file.

http://www.cnblogs.com/xalion/p/7397363.html

English Google translation: Translate

kbmMemTable SQL new features coming

In the upcoming release of kbmMemTable, a number of improvements of the optional SQL parser is included.

Now the following syntax is also supported (see examples further down):

  • CASE … WHEN … THEN … ELSE… END
  • SELECT … INTO… FROM….
  • CREATE TABLE
  • CREATE INDEX
  • DROP INDEX
  • ALTER TABLE… ADD COLUMN…
  • ALTER TABLE… DROP COLUMN…
  • ALTER TABLE… MODIFY COLUMN…
  • LIST TABLES
  • LIST INDEXES ON …
  • DESCRIBE TABLE …
  • DESCRIBE INDEX …

Further multiple statements separated by semicolon is now also supported. All will be run in order.

The DESCRIBE functions will return SQL 2003 style metadata descriptions.

In addition kbmMemTable SQL now supports simple inner joins.

<TEASER>

Many of these new features will be utilized by next version of kbmMW, which supports extensive SQL rewriting for major databases. Hence you will be able to write most SQL in the standard kbmMemTable SQL way which is SQL 92 compatible, and kbmMW will optionally automatically rewrite it to match the actual database, making supporting different databases a breeze. More about that in another blogpost.

</TEASER>

Examples:

SELECT fld1, fld2,
CASE
WHEN fld2<100 THEN ‘LOW’
WHEN fld2>=100 AND fld2<200 THEN ‘MEDIUM’
ELSE ‘HIGH’
END AS FLD3 INTO table7
FROM Table1

SELECT fld1, fld2,
CASE fld2
WHEN 10 THEN 99999
WHEN 20 THEN 22222
ELSE -1
END
FROM Table1

CREATE TABLE table9 (
id VARCHAR(40),
fld1 VARCHAR(50),
fld2 VARCHAR(5) NOT NULL,
fld3 BLOB NOT NULL,
fld4 INTEGER,
fld5 FLOAT,
fld6 TIMESTAMP,
PRIMARY KEY (id))

DROP TABLE table3

CREATE INDEX idx5 ON table1 (fld2 DESC)

DROP INDEX idx5 ON table1

CREATE TABLE (id INT, fld7 CLOB, PRIMARY KEY (id) )

CREATE TABLE (
id INT PRIMARY KEY,
fld2 VARCHAR(10),
fld7 CLOB ) ; CREATE INDEX idx5 (fld2 DESC) ; DROP INDEX idx5

ALTER TABLE table1 ADD COLUMN NewField VARCHAR(30)

ALTER TABLE table1 DROP COLUMN Fld2

ALTER TABLE table1 ALTER COLUMN Fld1 VARCHAR(30)

ALTER TABLE table3 MODIFY COLUMN Fld8 VARCHAR(30)

ALTER TABLE table3 MODIFY COLUMN Fld8 INTEGER

ALTER TABLE table1 ADD NewField VARCHAR(30)

ALTER TABLE table1 DROP Fld2

ALTER TABLE table1 ALTER Fld1 VARCHAR(30)

ALTER TABLE table3 MODIFY Fld8 VARCHAR(30)

ALTER TABLE table3 MODIFY Fld8 INTEGER

LIST TABLES

LIST INDEXES ON TABLE table1

LIST INDEXES ON table1

LIST INDEXES FOR TABLE table1

LIST INDEXES FOR table1

LIST INDEXES table1

DESC TABLE table1

DESCRIBE TABLE table1

DESCRIBE INDEX iDescend ON table1

DESCRIBE INDEX iDescend FOR table1

DESCRIBE INDEX iDescend ON TABLE table1

NB: If you like our blog posts, feel free to share then with your social network!

ANN: kbmMemTable v. 7.76.00 Standard and Professional Edition released!

We are happy to announce the latest and greatest release of our memory
table.

Whats new in 7.76.00 July 25 2017

  • Fixed bug related to ADT fields.
  • Added support for SQL DDL statements:
    CREATE TABLE…
    CREATE INDEX…
    DROP TABLE xxx
    DROP INDEX xxx
    ALTER TABLE xxx DROP COLUMN xxx,
    ALTER TABLE xxx ADD COLUMN xxx
    ALTER TABLE xxx MODIFY COLUMN xxxxxxxxx
  • Added naive (non optimized) support for SQL joins.
  • Improved to allow LIMIT or TOP or OFFSET at start of
    SELECT stattement in addition to at the end.
  • Split Calculate and Evaluate statements up into
    CompileCalculate, ExecuteCalculate
    and CompileEvaluate and ExecuteEvaluate to allow
    reusing compiled expressions.
    Calculate and Evaluate still exists for one shot runs.
  • Standard Edition is released with source to holders of an active
    kbmMemTable Service and Update subscription (SAU).

Professional Edition is released with source and additional performance
enhancement features to holders of an active kbmMW Pro/Ent Service and
Update subscription (SAU).

A free CodeGear Edition can be found bundled with kbmMW CodeGear Edition
for specific Delphi versions.

kbmMemTable supports the following development environments:

  • RAD Studio Delphi/C++ XE2
  • RAD Studio Delphi/C++ XE3
  • RAD Studio Delphi/C++ XE4
  • RAD Studio Delphi/C++ XE5
  • RAD Studio Delphi/C++ XE6
  • RAD Studio Delphi/C++ XE7
  • RAD Studio Delphi/C++ XE8
  • RAD Studio Delphi/C++ 10 Seattle
  • RAD Studio Delphi/C++ 10.1 Berlin
  • RAD Studio Delphi/C++ 10.2 Tokyo
  • Lazarus 1.2.4 with FPC 2.6.4

And can be used on mobile, Linux, OSX and Windows 32/64 bit platforms.

kbmMemTable is the premier high performance, high functionality in
memory dataset for Delphi and C++Builder with kbmMemTable Professional
topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then
you can immediately visit https://portal.components4developers.com to
download the latest kbmMemTable release.

If not, please visit our shop at http://www.components4developers.com
and extend your SAU with another 12 months.

Im only half as wise as I were yesterday…

I had to tell my day job employer last week, that he should expect me to be only half as wise from this Wednesday and in all future.

Obviously that got some attention. The potential issues with employing a previously “full wit” but now halfwit, who expect no change in the salary, the potential issues with the planned applications to be made… do we now requite an additional halfwit to solve the same amount of work, or can the current halfwit work double shifts to make up for the loss of wisdom?

Actually the day was less than perfect.. and the night worse. It was one of those days that you just want to forget about as soon as possible.

So what is it all about?

Well… I’m nearing 50 years old, and all my years, I have had my good looks (according to myself), my healthy teeth s, and all of my wisdom that my 4 wisdom teeth s provide me with.

Then on one Wednesday I visited my dentist, and in absolutely no time, my wisdom was half ed. He pulled my two top dear and beloved wisdom teeth s, that has followed me for better and worse, without asking for much!

That was the beginning of a bad day.

We have a saying here in Denmark… bad things happens in streams. So obviously more had to happen.

Back at the office again, with a whole days of planned coding work in front of me, a bottle of coke zero ready, and the keyboard just waiting with excitement for my gentle touch, I decided to start with rescuing an ailing gaming laptop which is used for some specialized programming, but which motherboard is starting to make problems and refusing to start unless retried many times. I wanted to virtualize it using VMWare to my main development machine and then move it to some NAS drives.

Logging in, Microsoft told me that there were some updates for me, and VMWare also wanted to do some minor updates. Since my computers mostly run 24×7, and only fairly rarely are rebooted, I decided… ok.. let the update happen.

So reboot and update…. and reboot again….. and waiting… and waiting… and waiting…. while my blood pressure began to rise alarmingly for someone who couldn’t control his lips and who was drooling like a newborn baby. due to the anesthetic, but being a patriot, of course in the  Danish national colors… mostly red… and some white.

On top of it all, I of some reason has become allergic in the middle of everything… so my nose was playing catch with my mouth… about which one could produce most uncontrolled and unwanted fluids.

So sniffing, cursing and damning everything Microsoft, I sat there and waited and waited… until at some point I decided… it should have booted by now. But alas only a spinning wheel, which indicates that it is working on something. I know what it was working on… to make my day worse!

Ok… annoying… but relax maestro… you are the champ…. the computer champ. You can easily fix this little issue. That thought was the first sign of my lost wisdom.

Reboot… ….. ….. …….. ok.. that didn’t work.

Reset while rebooting…… Ahh… there is the nice advanced startup menu…. it has all sorts of tools to make my life easier and my computer work.

Lets first let it attempt to repair my startup problems.

Reboot….. Checking disk….. 1 hour later… done checking disk. No problems…. MS: “I think I have fixed your startup problem…. please try to reboot”… so… Reboot……waiting… waiting… waiting…. …. …. Argh… still not working.

Ok.. reboot twice with reset in between to access the advanced startup menu again. Next step… Start in safe mode…..Reboot…. Waiting… waiting… waiting………:ARRRGHHH.

And so my night continued. Then enabled boot log files to see what was causing the problem. Then spend countless of hours analyzing the situation. I didn’t really want to reinstall Windows from scratch again… it would take days to get all reinstalled again. Remember I have some 10-12 development environments and versions installed on it.

In late evening I started to panic… Ok… Ill go to the nearest open hypermarket and buy a new computer. Browsing what was available… and decided not to. If to buy something I like to buy something substantially better… not just more of the same… and basically… doing that would absolutely hurt my computer champ pride.

Then it was late dinner time, and it was my turn to put the plates on the table and serve the food made by my wife. Obviously one shouldn’t give such an amount of responsibility to someone like me, that was influenced in more than one way.

That resulted in loss of china. Dropped on the tile floor.

What I did get more wise about, was that mood do not improve in such situations. I learned that the hard way.

After dinner and cleaning up and trying to forget about it all for just a moment, back on death row with my stubborn computer.

Going thru all well meaning guides about the topic on the internet, and waiting… wai…wa… w…. zzzzzz…. F…. is it still not working!!!!

I gave up. I decided to reinstall Windows by issuing a fresh install, but keeping my so called “personal files”. I was worrying about the not so personal files more!

Whoaa… it has installed… let it …. Reboot!…… and waiting….waiting…. F.. F… SAKE!

It STILL DO NOT get past the spinning wheel!

To wrap the story up, I ended ripping the SSD drive from an external casing, and installing it into the computer, reinstalling a completely fresh Windows, hoping that tools like Laplink PCmover would make my life less miserable afterwards by moving applications over from my 2TB half full disk…to my….ehh…. that is gonna be a tight fit…. 256GB  SSD drive.

Darned… it was not going to be able to work the night (well now it was morning) over. I need a bigger disk to restore to.

And that is just about where I am right now. A 4TB disk richer, but probably around 10 years older and with only half my wisdom left.

So dear Components4Developer friends, dear Danvægt friends and dear family… please bear with me!

🙂