Unfortunately for Visual FoxPro developers, Microsoft has never felt compelled to provide built-in encryption or secure tables in FoxPro. .DBF files haven't changed all that much over the years, except to provide new data types and inclusion in a DBC.

So how do FoxPro developers protect data if you can't create encrypted databases? If you absolutely must protect your data so someone with Notepad can't break in and look at text fields, you should use a different database platform, such as Microsoft SQL Server 2000 (Personal Edition, or the full version). SQL Server protects data from prying eyes, and you can secure it using passwords authenticated by Windows.

But, SQL Server may not be the answer in every case. You may be distributing a small application over the Web that contains some information you don't want the customer to be able to see easily. In this case, size and usability may be the big concern, and requiring SQL Server to run just isn't an option.

Cryptography

The Microsoft Base Cryptographic Provider distributed with CryptoAPI versions 1.0 and 2.0 may be the answer for you. It's a general-purpose provider that supports digital signatures and data encryption. This provider is currently included with Microsoft Windows XP/2000/98/NT/95. It's also included with Microsoft Internet Explorer 3.0 and later.

The Microsoft FoxPro team was really thinking when it included the FFC _crypt.vcx in Visual FoxPro 7. _crypt.vcx uses the CryptoAPI to provide easy-to-use functions for encrypting and decrypting data. You can use this class in your application to provide cryptographic services.

When you provide cryptographic services, you make some concessions. You'll have to give up VFP's blazing fast query speed. Encrypting and decrypting data requires a lot of time. Doing it on-the-fly in the middle of a query is a bad idea.

So, encrypting data before you store it, and after you retrieve it, are the best options. But how do you store encrypted data in a FoxPro table?

The correct answer is: however you can. Here's a quick sample application to demonstrate one way you can use the cryptographic services provided in _crypt.vcx:

*** A sample program to build a table and encrypt it.

Public goApp
goApp = Createobject("myApp")
goApp.Run()


DEFINE CLASS myApp AS Session

   ** You might choose a more complicated key than this
   cKey = "VFP7Rocks!"
   oCrypto = .f.

PROCEDURE Init
   This.oCrypto = Newobject("_cryptapi", Home(1) + ;
      "ffc\_crypt.vcx")
   ** The default provider in Windows 2000 is
   ** different than the default provider in
   ** Windows XP. Your data may encrypt/decrypt
   ** differently depending on the provider, so make
   ** sure you specify which provider you want to
   ** use. I'm using the Windows 2000 provider.
   this.oCrypto.cProviderName = ;
      "Microsoft Base Cryptographic Provider v1.0"
Endproc

Function Run

   =this.BuildTable("original")
   =this.EncryptTable("original", "encrypted", this.cKey)
   =this.DecryptTable("encrypted", "decrypted", this.cKey)

   =this.BrowseTable("original", "encrypted", "decrypted")
Endfunc

** Build a simple table
Function BuildTable( tcTableName )
   Create Table (tcTableName) Free (name C(20))
   Insert Into (tcTableName) Values ("Green Clovers")
   Insert Into (tcTableName) Values ("Blue Diamonds")
   Insert Into (tcTableName) Values ("Orange Stars")
   Insert Into (tcTableName) Values ("Pink Hearts")
   Insert Into (tcTableName) Values ("Purple Horseshoes")
   Insert Into (tcTableName) Values ("Yellow Moons")
Endfunc

** Encrypt each record and stuff into a new table
Function EncryptTable( tcDecryptedTable, tcEncryptedTable, tcKey )
   Create Table (tcEncryptedTable) Free (data C(254))
   Select (tcDecryptedTable)
   Local lcCipher, luResult
   Scan
      lcCipher = ""
      lcText = Trim(&tcDecryptedTable..name)
      luResult = ;
this.oCrypto.encryptSessionBlockString(lcText, tcKey, @lcCipher )
      Insert Into (tcEncryptedTable) Values (lcCipher)
   EndScan
Endfunc

** Decrypt each record and stuff into a new table
Function DecryptTable( tcEncryptedTable,
                                  tcDecryptedTable,
                                  tcKey )
   Create Table (tcDecryptedTable) Free (data C(20))
   Select (tcEncryptedTable)
   Local lcCipher, luResult
   Scan
      lcText = ""
      lcCipher = Trim(&tcEncryptedTable..data)
      luResult = ;
this.oCrypto.decryptSessionBlockString(lcCipher, tcKey, @lcText)
      Insert Into (tcDecryptedTable) Values (lcText)
   EndScan
EndFunc

** Browse each of the three tables so you can compare
** the data
Function BrowseTable( tcTable1, tcTable2, tcTable3 )
   Select (tcTable1)
   Browse Nowait
   Select (tcTable2)
   Browse Nowait
   Select (tcTable3)
   Browse Nowait
Endfunc

ENDDEFINE

The code creates a simple table containing descriptions of each of the possible marshmallows in Lucky Charms cereal, encrypts them, and then decrypts them using a specified key.

This is a simple example of how you can use field-level encryption in a table to protect your data.

It's important to note the caveats. After you encrypt a field, you're limited (by performance) to searching for only exact matches. In addition, when you search, you should encrypt your search criteria using the same key and search using the cipher:

Locate For MyTable.name == ";qŽÁëG9Ñ­oÿ* -° "

Don't try to search for a record by decrypting the data as you search, for example:

Locate for Decrypt(MyTable.Name, key) = "Orange Stars"

You'll regret it if you try to search this way, because FoxPro will be required to decrypt every record until it finds a match!

Key management

Your encryption algorithm is only as secure as your keys. You can use the most powerful ten billion bit encryption algorithm in the world to encrypt your data if you're so inclined, but as soon as the key you use to encrypt your data is made public, your data might as well be posted on the Internet.

There isn't a simple answer to how to protect your keys. One approach is to make sure your key is never stored in a complete phrase like my sample code does. Instead, make it a combination of words and letters, each stored in different properties of your application:

...
   lcFoobar = "WFJ@##"
   lcGoof = "$@%93093"
...
   luResult = encryptSessionBlockString( ;
      lcText, lcFoobar + "XJ-EF" + lcGoof, @lcCipher )

Using a system like this will make it more difficult for someone to hack your application and extract the string you use as your key.

This article is an excerpt from Daryl's "Security Fundamentals for VFP Developers" in FoxPro Advisor magazine. Pro-level subscribers can read the article online at http://Advisor.com/doc/10205.