Encryption, digital signatures

For a few weeks NextDB has had support for encryption via the CYPHER function, and sending email through the SENDEMAIL function. However, we didn't have support for providing application specific encryption keys, nor for digital signatures. Well, I spent today prototyping support for both of these two things. The CYPHER function now accepts a second argument, which is a 16-character string (128 bit privtate key). Digital signatures are accomplished through a new function called MD (Message Digest). A DECYPHER function allows you to decrypt whatever you encrypt. This creates a "round trip" model for your data, a lot like we do for our SURIDs. But you can put whatever applicaiton specific payloads you like inside the encrypted messages.

the impotus for this was being able to send out "confirmation" emails from NextQuery expressions, and not allow the content of the URL in the email to be tampered. The following is a NextQuery expression that I am using with a "5-star reviews" widget that I am developing. When the user posts a review, this query sends an email to the poster of the review, including a link to click to confirm (for sake of example, we are assuming the Reviews Widget has been places on 'mysite.com')

NAME=sendReviewConfirmationEmail;
ROW review FROM REVIEW;
WHERE(SURID pk){
review.PK = ${pk}
}
RETRIEVE SENDEMAIL(
'please confirm your review',
'click this link to confirm your review\n' ||
'http://mysite.com/reviewConfirm?'||CYPHER(review.title||':'||review.author||':'||MD(review.title||':'||review.author),'0123456789abcdef'),
review.authoremail,
'reviewAdmin@mysite.com'

)
into email.status;

The SENDEMAIL function accepts the subject, body, to, and from parameters. The interesting thing here is the third parameter, the body of the email, which includes a URL. Part of the URL (the part after the question mark) consists of a cyphered and digitally signed concatenation of the review's title, and the author's screenname.

Where things get really mindbending, is that the content of mysite.com/reviewConfirm is actually an HTML page with JavaScript embeded. The embedded JS can actually get access to parameters passed to the page, and thereby fulfil functions typcially handled on the serverside, even though they will run right there in the user's browser.

When the "reviewConfirm" JavaScript executes, it uses NextDB's JavaScript API to execute a query. Said query will use the DECYPHER function to decypher the URL's query parameter, and use the MD function to check the digital signature to be sure the confirmation email content has not been tampered. Said query returns a SURID with FOR UPDATE permission which allows the JS to change the value of the 'status' column of the row to 'approved'.

But what about replay attacks? That's the beauty of this technique. Because the CYPHER function's secret key can be different for each query, and because the content of the encrypted message can be used to confirm the presence of a row in the database, you can't "replay" a cyphered structure from your database against someone else's database.