Edit a private record

I don’t know if many of you will remember me, but I was invited to be a guest-blogger here on Land of Zoho Creator last fall. After one post, I managed to disappear off the face of the Earth for a while, but hopefully I’m back to regular blogging and will be able to post a bit more frequently.

A while back, Pete shared a great tip for generating a direct link to edit a specific record in a Zoho Creator. This was a great tip, as this is a feature sadly lacking in Zoho Creator by default. The only drawback with this is that it only works for records that are publicly accessible.

Unfortunately for people storing sensitive information, such as e-mail addresses, it is not usually a good idea to make the records public. In particular, I’m thinking about an application that stores member, subscriber, or client information. Since this information can’t be made public, there’s no way for the user to be able to update his or her own information when an address, e-mail address, or other information changes.

However, if you’re willing to do a little bit of Deluge scripting, there is a way to get around this progromatically. All you need is the ability to have one unique field for each record (such as an e-mail address), or a combination of fields (such as a username and phone number/address).

Once your main “add a record” form has been created, create another duplicate form to use for updating records. This new form should have all of the same fields (at least, the ones you want users to be able to edit). Although you could put this functionality directly into the main form, your users won’t be able to save the record, or you might end up with duplicate data.

You’ll want to make your identifier field(s) the first field that will be filled out in the “update” form, and you may want to add a “Note” field to explain to people how this will work.

Add a “On Add -> Validate” script to your application. You can do this from from the Script page. Click on the “Validate” link under “On Add.” Drag a “Fetch Records” code block into the code section of the page and click the “edit” button to customize the code.

Select the form that holds the data you will be checking against (the primary form that holds the original data).

Enter a variable name to use for the record you select (this can be anything you want).

Finally, specify the criteria you will be using to select the record that matches the information the user has entered into the field(s) in your form (The “Criteria field” tab contains the field names for the record you are checking, and the “Input field” tab contains the field names for the currently open form). Set the criteria to select records where the criteria field(s) matches the input field(s). This can get a little confusing, since the field names will be the same, so pay attention to what you are doing. Be sure that the field(s) you are checking cannot possibly contain any duplicate values, or you may find that people are updating records other than their own.

After you’ve fetched the record containing the unique value(s) that your user has entered, you can use a series of “Update record” code blocks to update the fetched record with the new information that the user has submitted.

To make things easier for your user, you may want to pre-load their information after they have entered the unique value(s) to identify themselves. To do this, add an “On User Input” code block to the field that contains the unique value (You may want to do this for each field that contains user information if you don’t have a single unique field for each user).

Drag a “For each record” code block into this section, and use the same criteria to specify the matching record. Inside this code block, use a series of “Set variable” code blocks to set the input fields to the values in the matching record.

This way, your user will be able to see the values that were previously entered. It has the added benefit of ensuring that any fields the user doesn’t change don’t get replaced with “empty” data.

That’s all there is to it. It does take a little bit of coding to work, but it can be well worth it if you need to allow users to change information in a recordset that they don’t otherwise have access to.

This kind of functionality can be useful for updating general information, but can be particularly useful for people who use Zoho Creator to handle registrations for classes or events. Users will be happy knowing that they can change their minds later and update their information whenever they need to.

Incidentally, this same type of functionality can be used with the “show/hide” field codes. If you want to make certain fields in your form only available to “members,” you can use the “For each record” code block to validate the information they enter into one field to determine which fields are available for editing.

I hope you find this tip useful.

SSLick Feature Addition, Much Anticipated

After many desperate, impassioned complaints, I see on the ZC blog that there is now SSL Support in Zoho Creator.

Yep, more proof of Zoho’s remarkable commitment to and support of the customers.

Encrypting Zoho Creator Data

Goal:

Encrypt and decrypt data in a Zoho Creator application.

Ideas:

We have two example applications this time:

• The first one demonstrates how you might encrypt, store, and decrypt U.S. Social Security and credit card numbers in a ZC application. It’s a barebones web page just to give you an idea of practical usage. You’ll only be able to successfully decrypt the records encrypted with your passphrase, the others will display as garbled text. I made a quick screencast of me putting it through its paces:

  

 

• The second example is about as simple as you can get in Zoho Creator, one form with one field, but on the whole this is more complex than the first example because the embedding web page is designed to help you encrypt and decrypt data for any Zoho Creator form field you care to designate. You need to be pretty familiar with the ZC GUI and configure several parameters to get it working; it’s mainly here to demonstrate the guts of what is going on and to help you build more domain-specific applications of the idea. The screencast shows me filling out the form and doing some encryption:

  

Blabber:

In both example applications, all encryption-related activity is taking place inside web pages that load a modified version of John Walker’s JavaScrypt tools. Take a look around that site if you’re interested in the implementation details.

No Web Server Necessary

You don’t actually need to access the example web pages on the LoZC web server, you can download them and run them from your own machine’s desktop. Walker says:

…to have any degree of security, it is essential that all processing be done on your computer, without involving any transmission or interaction with other sites on the Internet. A Web browser with JavaScript makes this possible, since the programs embedded in these pages run entirely on your own computer and do not transmit anything over the Internet.

So if you’re especially paranoid, you can download the web pages to your own machine, examine the source, load them from there, and be next to certain that no sensitive data is being transmitted during the encryption or decryption process.

The fact is that whether or not you take the extra-paranoid step of making local copies, all the encryption/decryption activity is happening in your own browser. The only data that Zoho Creator or the rest of the internet for that matter will ever see is the encrypted version of your data.

You can also feel confident in the quality of the encryption that is taking place. JavaScrypt uses AES 256-bit keys. AES provides “a cipher approved by NSA for encryption of TOP SECRET information”.

All that being said, JavaScript as a technology has its own inherent security issues so caveat usor.

Finally, local copies of these pages work great but if you’re using Internet Explorer it’s going to give you a security warning before the JavaScript will execute. Once you disable the warning or approve the page for local use, IE should work fine.

Clunkiness

There is no Zoho Creator API (yet) so getting our encrypted data into our ZC applications is clunky. Each time we want to save a record with some encrypted data, we’re locally creating encrypted data, embedding a ZC form in an HTML iframe that is preset to load that encrypted data, and finally asking the user to manually submit the ZC form. Yuck.

I suppose if you really wanted to hide the Zoho Creator form from your user, you might be able to do something with your own copy of the ZC HTML form, a hidden iframe, and polling one of the export format links to make sure the data made it to the ZC database, but that’s really too much of a hack. Even for me.

Once an API does become available there will likely be better ways to facilitate field-level encryption in ZC and in fact it’s not hard to imagine the Zoho Creator team someday providing “native” client-side encryption functionality in a future release of the ZC GUI. Until then, if you need support for encrypted fields you can use an approach like the one described in this post.

Data Bloat

Note that the encryption web page communicates with Zoho Creator forms by sending preset values through the query string. Given Internet Explorer’s 2048 character limit for GET requests and the inherent size increase that comes along with our hex-based encryption, as a general rule you probably shouldn’t try to encrypt any data that is longer than about 780 characters.

Private Applications & Private Links

If the ZC application you’re trying to access with the encryption pages is set to Private, make sure you’re actually logged in to ZC before you try to use them. Otherwise they won’t work because the links required to configure the encryption won’t be accessible to you.

Also note that Private Links, e.g.:

http://creator.zoho.com/username/form/1/dejgODOeBwhvEsE...

won’t work either. ZC does not seem to support sending in preset form field values in the query string with this kind of URL.

Related Links:

JavaScrypt and Gmail, Encrypt This! Firefox Extension

Here’s a ZIP file of all the HTML and JavaScript used in the example pages.

ZC Weirdness (potential bugs in the system):

Form presets sent through the query string don’t seem to work for Drop Down fields (i.e., when field cc_type was defined as a Drop Down, its value was not being set in the Account Information example application, even though the query string was trying to set it. So even though the external web page uses an HTML dropdown, I had to change the ZC application so that Credit Card Type was of type Single Line.

Also there is the Private Links issue mentioned above but possibly that one is by design.