diff options
Diffstat (limited to 'includes/js/dojox/_sql')
| -rw-r--r-- | includes/js/dojox/_sql/LICENSE | 9 | ||||
| -rw-r--r-- | includes/js/dojox/_sql/_crypto.js | 443 | ||||
| -rw-r--r-- | includes/js/dojox/_sql/common.js | 538 | ||||
| -rw-r--r-- | includes/js/dojox/_sql/demos/customers/customers.html | 292 |
4 files changed, 1282 insertions, 0 deletions
diff --git a/includes/js/dojox/_sql/LICENSE b/includes/js/dojox/_sql/LICENSE new file mode 100644 index 0000000..5c277ec --- /dev/null +++ b/includes/js/dojox/_sql/LICENSE @@ -0,0 +1,9 @@ +License Disclaimer: + +All contents of this directory are Copyright (c) the Dojo Foundation, with the +following exceptions: +------------------------------------------------------------------------------- + +_crypto.js - internally uses AES algorithm + * AES algorithm copyright Chris Veness (CLA signed and permission given to use code under BSD license) + Taken from http://www.movable-type.co.uk/scripts/aes.html diff --git a/includes/js/dojox/_sql/_crypto.js b/includes/js/dojox/_sql/_crypto.js new file mode 100644 index 0000000..e8a9214 --- /dev/null +++ b/includes/js/dojox/_sql/_crypto.js @@ -0,0 +1,443 @@ +if(!dojo._hasResource["dojox._sql._crypto"]){ //_hasResource checks added by build. Do not use _hasResource directly in your code. +dojo._hasResource["dojox._sql._crypto"] = true; +// Taken from http://www.movable-type.co.uk/scripts/aes.html by +// Chris Veness (CLA signed); adapted for Dojo and Google Gears Worker Pool +// by Brad Neuberg, bkn3@columbia.edu + +dojo.provide("dojox._sql._crypto"); + +dojo.mixin(dojox._sql._crypto,{ + // _POOL_SIZE: + // Size of worker pool to create to help with crypto + _POOL_SIZE: 100, + + encrypt: function(plaintext, password, callback){ + // summary: + // Use Corrected Block TEA to encrypt plaintext using password + // (note plaintext & password must be strings not string objects). + // Results will be returned to the 'callback' asychronously. + this._initWorkerPool(); + + var msg ={plaintext: plaintext, password: password}; + msg = dojo.toJson(msg); + msg = "encr:" + String(msg); + + this._assignWork(msg, callback); + }, + + decrypt: function(ciphertext, password, callback){ + // summary: + // Use Corrected Block TEA to decrypt ciphertext using password + // (note ciphertext & password must be strings not string objects). + // Results will be returned to the 'callback' asychronously. + this._initWorkerPool(); + + var msg ={ciphertext: ciphertext, password: password}; + msg = dojo.toJson(msg); + msg = "decr:" + String(msg); + + this._assignWork(msg, callback); + }, + + _initWorkerPool: function(){ + // bugs in Google Gears prevents us from dynamically creating + // and destroying workers as we need them -- the worker + // pool functionality stops working after a number of crypto + // cycles (probably related to a memory leak in Google Gears). + // this is too bad, since it results in much simpler code. + + // instead, we have to create a pool of workers and reuse them. we + // keep a stack of 'unemployed' Worker IDs that are currently not working. + // if a work request comes in, we pop off the 'unemployed' stack + // and put them to work, storing them in an 'employed' hashtable, + // keyed by their Worker ID with the value being the callback function + // that wants the result. when an employed worker is done, we get + // a message in our 'manager' which adds this worker back to the + // unemployed stack and routes the result to the callback that + // wanted it. if all the workers were employed in the past but + // more work needed to be done (i.e. it's a tight labor pool ;) + // then the work messages are pushed onto + // a 'handleMessage' queue as an object tuple{msg: msg, callback: callback} + + if(!this._manager){ + try{ + this._manager = google.gears.factory.create("beta.workerpool", "1.0"); + this._unemployed = []; + this._employed ={}; + this._handleMessage = []; + + var self = this; + this._manager.onmessage = function(msg, sender){ + // get the callback necessary to serve this result + var callback = self._employed["_" + sender]; + + // make this worker unemployed + self._employed["_" + sender] = undefined; + self._unemployed.push("_" + sender); + + // see if we need to assign new work + // that was queued up needing to be done + if(self._handleMessage.length){ + var handleMe = self._handleMessage.shift(); + self._assignWork(handleMe.msg, handleMe.callback); + } + + // return results + callback(msg); + } + + var workerInit = "function _workerInit(){" + + "gearsWorkerPool.onmessage = " + + String(this._workerHandler) + + ";" + + "}"; + + var code = workerInit + " _workerInit();"; + + // create our worker pool + for(var i = 0; i < this._POOL_SIZE; i++){ + this._unemployed.push("_" + this._manager.createWorker(code)); + } + }catch(exp){ + throw exp.message||exp; + } + } + }, + + _assignWork: function(msg, callback){ + // can we immediately assign this work? + if(!this._handleMessage.length && this._unemployed.length){ + // get an unemployed worker + var workerID = this._unemployed.shift().substring(1); // remove _ + + // list this worker as employed + this._employed["_" + workerID] = callback; + + // do the worke + this._manager.sendMessage(msg, workerID); + }else{ + // we have to queue it up + this._handleMessage ={msg: msg, callback: callback}; + } + }, + + _workerHandler: function(msg, sender){ + + /* Begin AES Implementation */ + + /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ + + // Sbox is pre-computed multiplicative inverse in GF(2^8) used in SubBytes and KeyExpansion [§5.1.1] + var Sbox = [0x63,0x7c,0x77,0x7b,0xf2,0x6b,0x6f,0xc5,0x30,0x01,0x67,0x2b,0xfe,0xd7,0xab,0x76, + 0xca,0x82,0xc9,0x7d,0xfa,0x59,0x47,0xf0,0xad,0xd4,0xa2,0xaf,0x9c,0xa4,0x72,0xc0, + 0xb7,0xfd,0x93,0x26,0x36,0x3f,0xf7,0xcc,0x34,0xa5,0xe5,0xf1,0x71,0xd8,0x31,0x15, + 0x04,0xc7,0x23,0xc3,0x18,0x96,0x05,0x9a,0x07,0x12,0x80,0xe2,0xeb,0x27,0xb2,0x75, + 0x09,0x83,0x2c,0x1a,0x1b,0x6e,0x5a,0xa0,0x52,0x3b,0xd6,0xb3,0x29,0xe3,0x2f,0x84, + 0x53,0xd1,0x00,0xed,0x20,0xfc,0xb1,0x5b,0x6a,0xcb,0xbe,0x39,0x4a,0x4c,0x58,0xcf, + 0xd0,0xef,0xaa,0xfb,0x43,0x4d,0x33,0x85,0x45,0xf9,0x02,0x7f,0x50,0x3c,0x9f,0xa8, + 0x51,0xa3,0x40,0x8f,0x92,0x9d,0x38,0xf5,0xbc,0xb6,0xda,0x21,0x10,0xff,0xf3,0xd2, + 0xcd,0x0c,0x13,0xec,0x5f,0x97,0x44,0x17,0xc4,0xa7,0x7e,0x3d,0x64,0x5d,0x19,0x73, + 0x60,0x81,0x4f,0xdc,0x22,0x2a,0x90,0x88,0x46,0xee,0xb8,0x14,0xde,0x5e,0x0b,0xdb, + 0xe0,0x32,0x3a,0x0a,0x49,0x06,0x24,0x5c,0xc2,0xd3,0xac,0x62,0x91,0x95,0xe4,0x79, + 0xe7,0xc8,0x37,0x6d,0x8d,0xd5,0x4e,0xa9,0x6c,0x56,0xf4,0xea,0x65,0x7a,0xae,0x08, + 0xba,0x78,0x25,0x2e,0x1c,0xa6,0xb4,0xc6,0xe8,0xdd,0x74,0x1f,0x4b,0xbd,0x8b,0x8a, + 0x70,0x3e,0xb5,0x66,0x48,0x03,0xf6,0x0e,0x61,0x35,0x57,0xb9,0x86,0xc1,0x1d,0x9e, + 0xe1,0xf8,0x98,0x11,0x69,0xd9,0x8e,0x94,0x9b,0x1e,0x87,0xe9,0xce,0x55,0x28,0xdf, + 0x8c,0xa1,0x89,0x0d,0xbf,0xe6,0x42,0x68,0x41,0x99,0x2d,0x0f,0xb0,0x54,0xbb,0x16]; + + // Rcon is Round Constant used for the Key Expansion [1st col is 2^(r-1) in GF(2^8)] [§5.2] + var Rcon = [ [0x00, 0x00, 0x00, 0x00], + [0x01, 0x00, 0x00, 0x00], + [0x02, 0x00, 0x00, 0x00], + [0x04, 0x00, 0x00, 0x00], + [0x08, 0x00, 0x00, 0x00], + [0x10, 0x00, 0x00, 0x00], + [0x20, 0x00, 0x00, 0x00], + [0x40, 0x00, 0x00, 0x00], + [0x80, 0x00, 0x00, 0x00], + [0x1b, 0x00, 0x00, 0x00], + [0x36, 0x00, 0x00, 0x00] ]; + + /* + * AES Cipher function: encrypt 'input' with Rijndael algorithm + * + * takes byte-array 'input' (16 bytes) + * 2D byte-array key schedule 'w' (Nr+1 x Nb bytes) + * + * applies Nr rounds (10/12/14) using key schedule w for 'add round key' stage + * + * returns byte-array encrypted value (16 bytes) + */ + function Cipher(input, w) { // main Cipher function [§5.1] + var Nb = 4; // block size (in words): no of columns in state (fixed at 4 for AES) + var Nr = w.length/Nb - 1; // no of rounds: 10/12/14 for 128/192/256-bit keys + + var state = [[],[],[],[]]; // initialise 4xNb byte-array 'state' with input [§3.4] + for (var i=0; i<4*Nb; i++) state[i%4][Math.floor(i/4)] = input[i]; + + state = AddRoundKey(state, w, 0, Nb); + + for (var round=1; round<Nr; round++) { + state = SubBytes(state, Nb); + state = ShiftRows(state, Nb); + state = MixColumns(state, Nb); + state = AddRoundKey(state, w, round, Nb); + } + + state = SubBytes(state, Nb); + state = ShiftRows(state, Nb); + state = AddRoundKey(state, w, Nr, Nb); + + var output = new Array(4*Nb); // convert state to 1-d array before returning [§3.4] + for (var i=0; i<4*Nb; i++) output[i] = state[i%4][Math.floor(i/4)]; + return output; + } + + + function SubBytes(s, Nb) { // apply SBox to state S [§5.1.1] + for (var r=0; r<4; r++) { + for (var c=0; c<Nb; c++) s[r][c] = Sbox[s[r][c]]; + } + return s; + } + + + function ShiftRows(s, Nb) { // shift row r of state S left by r bytes [§5.1.2] + var t = new Array(4); + for (var r=1; r<4; r++) { + for (var c=0; c<4; c++) t[c] = s[r][(c+r)%Nb]; // shift into temp copy + for (var c=0; c<4; c++) s[r][c] = t[c]; // and copy back + } // note that this will work for Nb=4,5,6, but not 7,8 (always 4 for AES): + return s; // see fp.gladman.plus.com/cryptography_technology/rijndael/aes.spec.311.pdf + } + + + function MixColumns(s, Nb) { // combine bytes of each col of state S [§5.1.3] + for (var c=0; c<4; c++) { + var a = new Array(4); // 'a' is a copy of the current column from 's' + var b = new Array(4); // 'b' is a•{02} in GF(2^8) + for (var i=0; i<4; i++) { + a[i] = s[i][c]; + b[i] = s[i][c]&0x80 ? s[i][c]<<1 ^ 0x011b : s[i][c]<<1; + } + // a[n] ^ b[n] is a•{03} in GF(2^8) + s[0][c] = b[0] ^ a[1] ^ b[1] ^ a[2] ^ a[3]; // 2*a0 + 3*a1 + a2 + a3 + s[1][c] = a[0] ^ b[1] ^ a[2] ^ b[2] ^ a[3]; // a0 * 2*a1 + 3*a2 + a3 + s[2][c] = a[0] ^ a[1] ^ b[2] ^ a[3] ^ b[3]; // a0 + a1 + 2*a2 + 3*a3 + s[3][c] = a[0] ^ b[0] ^ a[1] ^ a[2] ^ b[3]; // 3*a0 + a1 + a2 + 2*a3 + } + return s; + } + + + function AddRoundKey(state, w, rnd, Nb) { // xor Round Key into state S [§5.1.4] + for (var r=0; r<4; r++) { + for (var c=0; c<Nb; c++) state[r][c] ^= w[rnd*4+c][r]; + } + return state; + } + + + function KeyExpansion(key) { // generate Key Schedule (byte-array Nr+1 x Nb) from Key [§5.2] + var Nb = 4; // block size (in words): no of columns in state (fixed at 4 for AES) + var Nk = key.length/4 // key length (in words): 4/6/8 for 128/192/256-bit keys + var Nr = Nk + 6; // no of rounds: 10/12/14 for 128/192/256-bit keys + + var w = new Array(Nb*(Nr+1)); + var temp = new Array(4); + + for (var i=0; i<Nk; i++) { + var r = [key[4*i], key[4*i+1], key[4*i+2], key[4*i+3]]; + w[i] = r; + } + + for (var i=Nk; i<(Nb*(Nr+1)); i++) { + w[i] = new Array(4); + for (var t=0; t<4; t++) temp[t] = w[i-1][t]; + if (i % Nk == 0) { + temp = SubWord(RotWord(temp)); + for (var t=0; t<4; t++) temp[t] ^= Rcon[i/Nk][t]; + } else if (Nk > 6 && i%Nk == 4) { + temp = SubWord(temp); + } + for (var t=0; t<4; t++) w[i][t] = w[i-Nk][t] ^ temp[t]; + } + + return w; + } + + function SubWord(w) { // apply SBox to 4-byte word w + for (var i=0; i<4; i++) w[i] = Sbox[w[i]]; + return w; + } + + function RotWord(w) { // rotate 4-byte word w left by one byte + w[4] = w[0]; + for (var i=0; i<4; i++) w[i] = w[i+1]; + return w; + } + + /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ + + /* + * Use AES to encrypt 'plaintext' with 'password' using 'nBits' key, in 'Counter' mode of operation + * - see http://csrc.nist.gov/publications/nistpubs/800-38a/sp800-38a.pdf + * for each block + * - outputblock = cipher(counter, key) + * - cipherblock = plaintext xor outputblock + */ + function AESEncryptCtr(plaintext, password, nBits) { + if (!(nBits==128 || nBits==192 || nBits==256)) return ''; // standard allows 128/192/256 bit keys + + // for this example script, generate the key by applying Cipher to 1st 16/24/32 chars of password; + // for real-world applications, a more secure approach would be to hash the password e.g. with SHA-1 + var nBytes = nBits/8; // no bytes in key + var pwBytes = new Array(nBytes); + for (var i=0; i<nBytes; i++) pwBytes[i] = password.charCodeAt(i) & 0xff; + + var key = Cipher(pwBytes, KeyExpansion(pwBytes)); + + key = key.concat(key.slice(0, nBytes-16)); // key is now 16/24/32 bytes long + + // initialise counter block (NIST SP800-38A §B.2): millisecond time-stamp for nonce in 1st 8 bytes, + // block counter in 2nd 8 bytes + var blockSize = 16; // block size fixed at 16 bytes / 128 bits (Nb=4) for AES + var counterBlock = new Array(blockSize); // block size fixed at 16 bytes / 128 bits (Nb=4) for AES + var nonce = (new Date()).getTime(); // milliseconds since 1-Jan-1970 + + // encode nonce in two stages to cater for JavaScript 32-bit limit on bitwise ops + for (var i=0; i<4; i++) counterBlock[i] = (nonce >>> i*8) & 0xff; + for (var i=0; i<4; i++) counterBlock[i+4] = (nonce/0x100000000 >>> i*8) & 0xff; + + // generate key schedule - an expansion of the key into distinct Key Rounds for each round + var keySchedule = KeyExpansion(key); + + var blockCount = Math.ceil(plaintext.length/blockSize); + var ciphertext = new Array(blockCount); // ciphertext as array of strings + + for (var b=0; b<blockCount; b++) { + // set counter (block #) in last 8 bytes of counter block (leaving nonce in 1st 8 bytes) + // again done in two stages for 32-bit ops + for (var c=0; c<4; c++) counterBlock[15-c] = (b >>> c*8) & 0xff; + for (var c=0; c<4; c++) counterBlock[15-c-4] = (b/0x100000000 >>> c*8) + + var cipherCntr = Cipher(counterBlock, keySchedule); // -- encrypt counter block -- + + // calculate length of final block: + var blockLength = b<blockCount-1 ? blockSize : (plaintext.length-1)%blockSize+1; + + var ct = ''; + for (var i=0; i<blockLength; i++) { // -- xor plaintext with ciphered counter byte-by-byte -- + var plaintextByte = plaintext.charCodeAt(b*blockSize+i); + var cipherByte = plaintextByte ^ cipherCntr[i]; + ct += String.fromCharCode(cipherByte); + } + // ct is now ciphertext for this block + + ciphertext[b] = escCtrlChars(ct); // escape troublesome characters in ciphertext + } + + // convert the nonce to a string to go on the front of the ciphertext + var ctrTxt = ''; + for (var i=0; i<8; i++) ctrTxt += String.fromCharCode(counterBlock[i]); + ctrTxt = escCtrlChars(ctrTxt); + + // use '-' to separate blocks, use Array.join to concatenate arrays of strings for efficiency + return ctrTxt + '-' + ciphertext.join('-'); + } + + + /* + * Use AES to decrypt 'ciphertext' with 'password' using 'nBits' key, in Counter mode of operation + * + * for each block + * - outputblock = cipher(counter, key) + * - cipherblock = plaintext xor outputblock + */ + function AESDecryptCtr(ciphertext, password, nBits) { + if (!(nBits==128 || nBits==192 || nBits==256)) return ''; // standard allows 128/192/256 bit keys + + var nBytes = nBits/8; // no bytes in key + var pwBytes = new Array(nBytes); + for (var i=0; i<nBytes; i++) pwBytes[i] = password.charCodeAt(i) & 0xff; + var pwKeySchedule = KeyExpansion(pwBytes); + var key = Cipher(pwBytes, pwKeySchedule); + key = key.concat(key.slice(0, nBytes-16)); // key is now 16/24/32 bytes long + + var keySchedule = KeyExpansion(key); + + ciphertext = ciphertext.split('-'); // split ciphertext into array of block-length strings + + // recover nonce from 1st element of ciphertext + var blockSize = 16; // block size fixed at 16 bytes / 128 bits (Nb=4) for AES + var counterBlock = new Array(blockSize); + var ctrTxt = unescCtrlChars(ciphertext[0]); + for (var i=0; i<8; i++) counterBlock[i] = ctrTxt.charCodeAt(i); + + var plaintext = new Array(ciphertext.length-1); + + for (var b=1; b<ciphertext.length; b++) { + // set counter (block #) in last 8 bytes of counter block (leaving nonce in 1st 8 bytes) + for (var c=0; c<4; c++) counterBlock[15-c] = ((b-1) >>> c*8) & 0xff; + for (var c=0; c<4; c++) counterBlock[15-c-4] = ((b/0x100000000-1) >>> c*8) & 0xff; + + var cipherCntr = Cipher(counterBlock, keySchedule); // encrypt counter block + + ciphertext[b] = unescCtrlChars(ciphertext[b]); + + var pt = ''; + for (var i=0; i<ciphertext[b].length; i++) { + // -- xor plaintext with ciphered counter byte-by-byte -- + var ciphertextByte = ciphertext[b].charCodeAt(i); + var plaintextByte = ciphertextByte ^ cipherCntr[i]; + pt += String.fromCharCode(plaintextByte); + } + // pt is now plaintext for this block + + plaintext[b-1] = pt; // b-1 'cos no initial nonce block in plaintext + } + + return plaintext.join(''); + } + + /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ + + function escCtrlChars(str) { // escape control chars which might cause problems handling ciphertext + return str.replace(/[\0\t\n\v\f\r\xa0!-]/g, function(c) { return '!' + c.charCodeAt(0) + '!'; }); + } // \xa0 to cater for bug in Firefox; include '-' to leave it free for use as a block marker + + function unescCtrlChars(str) { // unescape potentially problematic control characters + return str.replace(/!\d\d?\d?!/g, function(c) { return String.fromCharCode(c.slice(1,-1)); }); + } + + /* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */ + + function encrypt(plaintext, password){ + return AESEncryptCtr(plaintext, password, 256); + } + + function decrypt(ciphertext, password){ + return AESDecryptCtr(ciphertext, password, 256); + } + + /* End AES Implementation */ + + var cmd = msg.substr(0,4); + var arg = msg.substr(5); + if(cmd == "encr"){ + arg = eval("(" + arg + ")"); + var plaintext = arg.plaintext; + var password = arg.password; + var results = encrypt(plaintext, password); + gearsWorkerPool.sendMessage(String(results), sender); + }else if(cmd == "decr"){ + arg = eval("(" + arg + ")"); + var ciphertext = arg.ciphertext; + var password = arg.password; + var results = decrypt(ciphertext, password); + gearsWorkerPool.sendMessage(String(results), sender); + } + } +}); + +} diff --git a/includes/js/dojox/_sql/common.js b/includes/js/dojox/_sql/common.js new file mode 100644 index 0000000..00f4893 --- /dev/null +++ b/includes/js/dojox/_sql/common.js @@ -0,0 +1,538 @@ +if(!dojo._hasResource["dojox._sql.common"]){ //_hasResource checks added by build. Do not use _hasResource directly in your code. +dojo._hasResource["dojox._sql.common"] = true; +dojo.provide("dojox._sql.common"); + +dojo.require("dojox._sql._crypto"); + +// summary: +// Executes a SQL expression. +// description: +// There are four ways to call this: +// 1) Straight SQL: dojox.sql("SELECT * FROM FOOBAR"); +// 2) SQL with parameters: dojox.sql("INSERT INTO FOOBAR VALUES (?)", someParam) +// 3) Encrypting particular values: +// dojox.sql("INSERT INTO FOOBAR VALUES (ENCRYPT(?))", someParam, "somePassword", callback) +// 4) Decrypting particular values: +// dojox.sql("SELECT DECRYPT(SOMECOL1), DECRYPT(SOMECOL2) FROM +// FOOBAR WHERE SOMECOL3 = ?", someParam, +// "somePassword", callback) +// +// For encryption and decryption the last two values should be the the password for +// encryption/decryption, and the callback function that gets the result set. +// +// Note: We only support ENCRYPT(?) statements, and +// and DECRYPT(*) statements for now -- you can not have a literal string +// inside of these, such as ENCRYPT('foobar') +// +// Note: If you have multiple columns to encrypt and decrypt, you can use the following +// convenience form to not have to type ENCRYPT(?)/DECRYPT(*) many times: +// +// dojox.sql("INSERT INTO FOOBAR VALUES (ENCRYPT(?, ?, ?))", +// someParam1, someParam2, someParam3, +// "somePassword", callback) +// +// dojox.sql("SELECT DECRYPT(SOMECOL1, SOMECOL2) FROM +// FOOBAR WHERE SOMECOL3 = ?", someParam, +// "somePassword", callback) +dojox.sql = new Function("return dojox.sql._exec(arguments);"); + +dojo.mixin(dojox.sql, { + dbName: null, + + // summary: + // If true, then we print out any SQL that is executed + // to the debug window + debug: (dojo.exists("dojox.sql.debug")?dojox.sql.debug:false), + + open: function(dbName){ + if(this._dbOpen && (!dbName || dbName == this.dbName)){ + return; + } + + if(!this.dbName){ + this.dbName = "dot_store_" + + window.location.href.replace(/[^0-9A-Za-z_]/g, "_"); + // database names in Gears are limited to 64 characters long + if(this.dbName.length > 63){ + this.dbName = this.dbName.substring(0, 63); + } + } + + if(!dbName){ + dbName = this.dbName; + } + + try{ + this._initDb(); + this.db.open(dbName); + this._dbOpen = true; + }catch(exp){ + throw exp.message||exp; + } + }, + + close: function(dbName){ + // on Internet Explorer, Google Gears throws an exception + // "Object not a collection", when we try to close the + // database -- just don't close it on this platform + // since we are running into a Gears bug; the Gears team + // said it's ok to not close a database connection + if(dojo.isIE){ return; } + + if(!this._dbOpen && (!dbName || dbName == this.dbName)){ + return; + } + + if(!dbName){ + dbName = this.dbName; + } + + try{ + this.db.close(dbName); + this._dbOpen = false; + }catch(exp){ + throw exp.message||exp; + } + }, + + _exec: function(params){ + try{ + // get the Gears Database object + this._initDb(); + + // see if we need to open the db; if programmer + // manually called dojox.sql.open() let them handle + // it; otherwise we open and close automatically on + // each SQL execution + if(!this._dbOpen){ + this.open(); + this._autoClose = true; + } + + // determine our parameters + var sql = null; + var callback = null; + var password = null; + + var args = dojo._toArray(params); + + sql = args.splice(0, 1)[0]; + + // does this SQL statement use the ENCRYPT or DECRYPT + // keywords? if so, extract our callback and crypto + // password + if(this._needsEncrypt(sql) || this._needsDecrypt(sql)){ + callback = args.splice(args.length - 1, 1)[0]; + password = args.splice(args.length - 1, 1)[0]; + } + + // 'args' now just has the SQL parameters + + // print out debug SQL output if the developer wants that + if(this.debug){ + this._printDebugSQL(sql, args); + } + + // handle SQL that needs encryption/decryption differently + // do we have an ENCRYPT SQL statement? if so, handle that first + if(this._needsEncrypt(sql)){ + var crypto = new dojox.sql._SQLCrypto("encrypt", sql, + password, args, + callback); + return; // encrypted results will arrive asynchronously + }else if(this._needsDecrypt(sql)){ // otherwise we have a DECRYPT statement + var crypto = new dojox.sql._SQLCrypto("decrypt", sql, + password, args, + callback); + return; // decrypted results will arrive asynchronously + } + + // execute the SQL and get the results + var rs = this.db.execute(sql, args); + + // Gears ResultSet object's are ugly -- normalize + // these into something JavaScript programmers know + // how to work with, basically an array of + // JavaScript objects where each property name is + // simply the field name for a column of data + rs = this._normalizeResults(rs); + + if(this._autoClose){ + this.close(); + } + + return rs; + }catch(exp){ + exp = exp.message||exp; + + console.debug("SQL Exception: " + exp); + + if(this._autoClose){ + try{ + this.close(); + }catch(e){ + console.debug("Error closing database: " + + e.message||e); + } + } + + throw exp; + } + }, + + _initDb: function(){ + if(!this.db){ + try{ + this.db = google.gears.factory.create('beta.database', '1.0'); + }catch(exp){ + dojo.setObject("google.gears.denied", true); + dojox.off.onFrameworkEvent("coreOperationFailed"); + throw "Google Gears must be allowed to run"; + } + } + }, + + _printDebugSQL: function(sql, args){ + var msg = "dojox.sql(\"" + sql + "\""; + for(var i = 0; i < args.length; i++){ + if(typeof args[i] == "string"){ + msg += ", \"" + args[i] + "\""; + }else{ + msg += ", " + args[i]; + } + } + msg += ")"; + + console.debug(msg); + }, + + _normalizeResults: function(rs){ + var results = []; + if(!rs){ return []; } + + while(rs.isValidRow()){ + var row = {}; + + for(var i = 0; i < rs.fieldCount(); i++){ + var fieldName = rs.fieldName(i); + var fieldValue = rs.field(i); + row[fieldName] = fieldValue; + } + + results.push(row); + + rs.next(); + } + + rs.close(); + + return results; + }, + + _needsEncrypt: function(sql){ + return /encrypt\([^\)]*\)/i.test(sql); + }, + + _needsDecrypt: function(sql){ + return /decrypt\([^\)]*\)/i.test(sql); + } +}); + +// summary: +// A private class encapsulating any cryptography that must be done +// on a SQL statement. We instantiate this class and have it hold +// it's state so that we can potentially have several encryption +// operations happening at the same time by different SQL statements. +dojo.declare("dojox.sql._SQLCrypto", null, { + constructor: function(action, sql, password, args, callback){ + if(action == "encrypt"){ + this._execEncryptSQL(sql, password, args, callback); + }else{ + this._execDecryptSQL(sql, password, args, callback); + } + }, + + _execEncryptSQL: function(sql, password, args, callback){ + // strip the ENCRYPT/DECRYPT keywords from the SQL + var strippedSQL = this._stripCryptoSQL(sql); + + // determine what arguments need encryption + var encryptColumns = this._flagEncryptedArgs(sql, args); + + // asynchronously encrypt each argument that needs it + var self = this; + this._encrypt(strippedSQL, password, args, encryptColumns, function(finalArgs){ + // execute the SQL + var error = false; + var resultSet = []; + var exp = null; + try{ + resultSet = dojox.sql.db.execute(strippedSQL, finalArgs); + }catch(execError){ + error = true; + exp = execError.message||execError; + } + + // was there an error during SQL execution? + if(exp != null){ + if(dojox.sql._autoClose){ + try{ dojox.sql.close(); }catch(e){} + } + + callback(null, true, exp.toString()); + return; + } + + // normalize SQL results into a JavaScript object + // we can work with + resultSet = dojox.sql._normalizeResults(resultSet); + + if(dojox.sql._autoClose){ + dojox.sql.close(); + } + + // are any decryptions necessary on the result set? + if(dojox.sql._needsDecrypt(sql)){ + // determine which of the result set columns needs decryption + var needsDecrypt = self._determineDecryptedColumns(sql); + + // now decrypt columns asynchronously + // decrypt columns that need it + self._decrypt(resultSet, needsDecrypt, password, function(finalResultSet){ + callback(finalResultSet, false, null); + }); + }else{ + callback(resultSet, false, null); + } + }); + }, + + _execDecryptSQL: function(sql, password, args, callback){ + // strip the ENCRYPT/DECRYPT keywords from the SQL + var strippedSQL = this._stripCryptoSQL(sql); + + // determine which columns needs decryption; this either + // returns the value *, which means all result set columns will + // be decrypted, or it will return the column names that need + // decryption set on a hashtable so we can quickly test a given + // column name; the key is the column name that needs + // decryption and the value is 'true' (i.e. needsDecrypt["someColumn"] + // would return 'true' if it needs decryption, and would be 'undefined' + // or false otherwise) + var needsDecrypt = this._determineDecryptedColumns(sql); + + // execute the SQL + var error = false; + var resultSet = []; + var exp = null; + try{ + resultSet = dojox.sql.db.execute(strippedSQL, args); + }catch(execError){ + error = true; + exp = execError.message||execError; + } + + // was there an error during SQL execution? + if(exp != null){ + if(dojox.sql._autoClose){ + try{ dojox.sql.close(); }catch(e){} + } + + callback(resultSet, true, exp.toString()); + return; + } + + // normalize SQL results into a JavaScript object + // we can work with + resultSet = dojox.sql._normalizeResults(resultSet); + + if(dojox.sql._autoClose){ + dojox.sql.close(); + } + + // decrypt columns that need it + this._decrypt(resultSet, needsDecrypt, password, function(finalResultSet){ + callback(finalResultSet, false, null); + }); + }, + + _encrypt: function(sql, password, args, encryptColumns, callback){ + //console.debug("_encrypt, sql="+sql+", password="+password+", encryptColumns="+encryptColumns+", args="+args); + + this._totalCrypto = 0; + this._finishedCrypto = 0; + this._finishedSpawningCrypto = false; + this._finalArgs = args; + + for(var i = 0; i < args.length; i++){ + if(encryptColumns[i]){ + // we have an encrypt() keyword -- get just the value inside + // the encrypt() parantheses -- for now this must be a ? + var sqlParam = args[i]; + var paramIndex = i; + + // update the total number of encryptions we know must be done asynchronously + this._totalCrypto++; + + // FIXME: This currently uses DES as a proof-of-concept since the + // DES code used is quite fast and was easy to work with. Modify dojox.sql + // to be able to specify a different encryption provider through a + // a SQL-like syntax, such as dojox.sql("SET ENCRYPTION BLOWFISH"), + // and modify the dojox.crypto.Blowfish code to be able to work using + // a Google Gears Worker Pool + + // do the actual encryption now, asychronously on a Gears worker thread + dojox._sql._crypto.encrypt(sqlParam, password, dojo.hitch(this, function(results){ + // set the new encrypted value + this._finalArgs[paramIndex] = results; + this._finishedCrypto++; + // are we done with all encryption? + if(this._finishedCrypto >= this._totalCrypto + && this._finishedSpawningCrypto){ + callback(this._finalArgs); + } + })); + } + } + + this._finishedSpawningCrypto = true; + }, + + _decrypt: function(resultSet, needsDecrypt, password, callback){ + //console.debug("decrypt, resultSet="+resultSet+", needsDecrypt="+needsDecrypt+", password="+password); + + this._totalCrypto = 0; + this._finishedCrypto = 0; + this._finishedSpawningCrypto = false; + this._finalResultSet = resultSet; + + for(var i = 0; i < resultSet.length; i++){ + var row = resultSet[i]; + + // go through each of the column names in row, + // seeing if they need decryption + for(var columnName in row){ + if(needsDecrypt == "*" || needsDecrypt[columnName]){ + this._totalCrypto++; + var columnValue = row[columnName]; + + // forming a closure here can cause issues, with values not cleanly + // saved on Firefox/Mac OS X for some of the values above that + // are needed in the callback below; call a subroutine that will form + // a closure inside of itself instead + this._decryptSingleColumn(columnName, columnValue, password, i, + function(finalResultSet){ + callback(finalResultSet); + }); + } + } + } + + this._finishedSpawningCrypto = true; + }, + + _stripCryptoSQL: function(sql){ + // replace all DECRYPT(*) occurrences with a * + sql = sql.replace(/DECRYPT\(\*\)/ig, "*"); + + // match any ENCRYPT(?, ?, ?, etc) occurrences, + // then replace with just the question marks in the + // middle + var matches = sql.match(/ENCRYPT\([^\)]*\)/ig); + if(matches != null){ + for(var i = 0; i < matches.length; i++){ + var encryptStatement = matches[i]; + var encryptValue = encryptStatement.match(/ENCRYPT\(([^\)]*)\)/i)[1]; + sql = sql.replace(encryptStatement, encryptValue); + } + } + + // match any DECRYPT(COL1, COL2, etc) occurrences, + // then replace with just the column names + // in the middle + matches = sql.match(/DECRYPT\([^\)]*\)/ig); + if(matches != null){ + for(var i = 0; i < matches.length; i++){ + var decryptStatement = matches[i]; + var decryptValue = decryptStatement.match(/DECRYPT\(([^\)]*)\)/i)[1]; + sql = sql.replace(decryptStatement, decryptValue); + } + } + + return sql; + }, + + _flagEncryptedArgs: function(sql, args){ + // capture literal strings that have question marks in them, + // and also capture question marks that stand alone + var tester = new RegExp(/([\"][^\"]*\?[^\"]*[\"])|([\'][^\']*\?[^\']*[\'])|(\?)/ig); + var matches; + var currentParam = 0; + var results = []; + while((matches = tester.exec(sql)) != null){ + var currentMatch = RegExp.lastMatch+""; + + // are we a literal string? then ignore it + if(/^[\"\']/.test(currentMatch)){ + continue; + } + + // do we have an encrypt keyword to our left? + var needsEncrypt = false; + if(/ENCRYPT\([^\)]*$/i.test(RegExp.leftContext)){ + needsEncrypt = true; + } + + // set the encrypted flag + results[currentParam] = needsEncrypt; + + currentParam++; + } + + return results; + }, + + _determineDecryptedColumns: function(sql){ + var results = {}; + + if(/DECRYPT\(\*\)/i.test(sql)){ + results = "*"; + }else{ + var tester = /DECRYPT\((?:\s*\w*\s*\,?)*\)/ig; + var matches; + while(matches = tester.exec(sql)){ + var lastMatch = new String(RegExp.lastMatch); + var columnNames = lastMatch.replace(/DECRYPT\(/i, ""); + columnNames = columnNames.replace(/\)/, ""); + columnNames = columnNames.split(/\s*,\s*/); + dojo.forEach(columnNames, function(column){ + if(/\s*\w* AS (\w*)/i.test(column)){ + column = column.match(/\s*\w* AS (\w*)/i)[1]; + } + results[column] = true; + }); + } + } + + return results; + }, + + _decryptSingleColumn: function(columnName, columnValue, password, currentRowIndex, + callback){ + //console.debug("decryptSingleColumn, columnName="+columnName+", columnValue="+columnValue+", currentRowIndex="+currentRowIndex) + dojox._sql._crypto.decrypt(columnValue, password, dojo.hitch(this, function(results){ + // set the new decrypted value + this._finalResultSet[currentRowIndex][columnName] = results; + this._finishedCrypto++; + + // are we done with all encryption? + if(this._finishedCrypto >= this._totalCrypto + && this._finishedSpawningCrypto){ + //console.debug("done with all decrypts"); + callback(this._finalResultSet); + } + })); + } +}); + +} diff --git a/includes/js/dojox/_sql/demos/customers/customers.html b/includes/js/dojox/_sql/demos/customers/customers.html new file mode 100644 index 0000000..a4c0c03 --- /dev/null +++ b/includes/js/dojox/_sql/demos/customers/customers.html @@ -0,0 +1,292 @@ +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> + +<html> + <head> + <script type="text/javascript" + src="../../../../dojo/dojo.js" djConfig="isDebug: false"></script> + <script type="text/javascript" src="../../../../dojox/off/offline.js"></script> + + <style type="text/css"> + body{ + padding: 2em; + } + + #dataTable{ + margin-top: 2em; + } + + button{ + margin-left: 1em; + } + + th, tr, td{ + text-align: left; + } + + table{ + text-align: center; + clear: both; + } + + #cryptoContainer{ + float: left; + width: 60%; + } + + #numRowsContainer{ + float: right; + width: 40%; + } + + #numRowsContainer input{ + margin-left: 1.5em; + width: 5em; + } + + .table-columns{ + font-weight: bold; + } + </style> + + <script> + dojo.require("dojox.sql"); + + dojo.connect(window, "onload", function(){ + // draw our customer table on the screen + createTable(); + + // create our customer table in the database + dojox.sql("DROP TABLE IF EXISTS CUSTOMERS"); + dojox.sql("CREATE TABLE CUSTOMERS (" + + "last_name TEXT, " + + "first_name TEXT, " + + "social_security TEXT" + + ")" + ); + }); + + function createTable(){ + // get number of rows to create + var NUM_ROWS = document.getElementById("numRows").value; + if(!NUM_ROWS){ + alert("Please enter the number of " + + "customer rows the table should have"); + return; + } + + var table = document.getElementById("dataTable"); + if(table){ + table.parentNode.removeChild(table); + } + + table = document.createElement("table"); + table.setAttribute("id", "dataTable"); + table.setAttribute("border", 1); + + // if we don't use IE's craptacular proprietary table methods + // we get strange display glitches + var tr = (dojo.isIE) ? table.insertRow() : document.createElement("tr"); + tr.className = "table-columns"; + var th = (dojo.isIE) ? tr.insertCell() : document.createElement("th"); + th.appendChild(document.createTextNode("Last Name")); + if(!dojo.isIE){ + tr.appendChild(th); + } + th = (dojo.isIE) ? tr.insertCell() : document.createElement("th"); + th.appendChild(document.createTextNode("First Name")); + if(!dojo.isIE){ + tr.appendChild(th); + } + th = (dojo.isIE) ? tr.insertCell() : document.createElement("th"); + th.appendChild(document.createTextNode("Social Security")); + if(!dojo.isIE){ + tr.appendChild(th); + + table.appendChild(tr); + } + + for(var i = 1; i <= NUM_ROWS; i++){ + tr = (dojo.isIE) ? table.insertRow() : document.createElement("tr"); + tr.className = "data-item"; + + var elem = (dojo.isIE) ? tr.insertCell() : document.createElement("td"); + elem.className = "last-name"; + var lastName = "Doe" + i; + elem.appendChild(document.createTextNode(lastName)); + if(!dojo.isIE){ + tr.appendChild(elem); + } + + elem = (dojo.isIE) ? tr.insertCell() : document.createElement("td"); + elem.className = "first-name"; + var firstName = "John" + i; + elem.appendChild(document.createTextNode(firstName)); + if(!dojo.isIE){ + tr.appendChild(elem); + } + + elem = elem = (dojo.isIE) ? tr.insertCell() : document.createElement("td"); + elem.className = "social-security"; + var ss = 513121500 + i; + ss = new String(ss); + ss = ss.slice(0, 3) + "-" + ss.slice(3, 5) + "-" + ss.slice(5); + elem.appendChild(document.createTextNode(ss)); + if(!dojo.isIE){ + tr.appendChild(elem); + + table.appendChild(tr); + } + } + + document.body.appendChild(table); + + // reset button state + dojo.byId("encrypt").disabled = false; + dojo.byId("decrypt").disabled = true; + } + + function readTable(){ + var data = []; + var rows = dojo.query(".data-item"); + dojo.forEach(rows, function(row){ + var td = row.getElementsByTagName("td"); + + var lastName = td[0].childNodes[0].nodeValue; + var firstName = td[1].childNodes[0].nodeValue; + var ssNumber = td[2].childNodes[0].nodeValue; + + data.push({lastName: lastName, firstName: firstName, ssNumber: ssNumber, + toString: function(){ + return "{lastName: " + lastName + + ", firstName: " + firstName + + ", ssNumber: " + ssNumber + + "}"; + }}); + }); + + return data; + } + + function setData(data){ + var rows = document.getElementsByTagName("tr"); + for(var i = 1; i < rows.length; i++){ + var customer = data[i - 1]; + var td = rows[i].getElementsByTagName("td"); + td[2].childNodes[0].nodeValue = customer.social_security; + } + } + + function encrypt(){ + // disable our buttons + dojo.byId("encrypt").disabled = true; + dojo.byId("decrypt").disabled = true; + + var data = readTable(); + + var password = document.getElementById("password").value; + + // delete any old data + dojox.sql("DELETE FROM CUSTOMERS"); + + // insert new data + insertCustomers(data, 0, password); + } + + function insertCustomers(data, i, password){ + var nextIndex = i + 1; + + if(i >= data.length){ + var savedRows = dojox.sql("SELECT * FROM CUSTOMERS"); + setData(savedRows); + return; + } + dojox.sql("INSERT INTO CUSTOMERS VALUES (?, ?, ENCRYPT(?))", + data[i].lastName, data[i].firstName, + data[i].ssNumber, + password, + function(results, error, errorMsg){ + // enable our buttons + dojo.byId("encrypt").disabled = true; + dojo.byId("decrypt").disabled = false; + + if(error == true){ + alert(errorMsg); + return; + } + + insertCustomers(data, nextIndex, password); + } + ); + } + + function decrypt(){ + // disable our buttons + dojo.byId("encrypt").disabled = true; + dojo.byId("decrypt").disabled = true; + + var password = document.getElementById("password").value; + + dojox.sql("SELECT last_name, first_name, DECRYPT(social_security) FROM CUSTOMERS", + password, + function(results, error, errorMsg){ + // enable our buttons + dojo.byId("encrypt").disabled = false; + dojo.byId("decrypt").disabled = true; + + if(error == true){ + alert(errorMsg); + return; + } + + setData(results); + } + ); + } + </script> + </head> + + <body> + <h1>Dojo SQL Cryptography</h1> + + <h2>Instructions</h2> + + <p>This demo shows Dojo Offline's SQL encryption technologies. In the table below, we have a + sample SQL table that has three columns of data: a last name, a first name, and + a social security number. We don't want to store the social security numbers + in the clear, just in case they are downloaded for offline use to a laptop and the + laptop is stolen.</p> + + <p>To use this demo, enter a password and press the ENCRYPT button to see the Social Security column encrypt. Enter + the same password and press DECRYPT to see it decrypt. If you enter an incorrect password and + press DECRYPT, the Social Security column will remain encrypted and only show gibberish.</p> + + <p>Under the covers we use 256-bit AES encryption and your password to derive the crypto key; we use + a facility in Google Gears to do the cryptography in such a way that the browser does not lock up + during processing. Dojo Offline ties this cryptography into Dojo SQL, providing convenient ENCRYPT() + and DECRYPT() SQL keywords you can use to easily have this functionality in your + own offline applications. To learn how you can use this feature + <a href="http://docs.google.com/View?docid=dhkhksk4_8gdp9gr#crypto" target="_blank">see here</a>.</p> + + <div id="cryptoContainer"> + <label for="password"> + Password: + </label> + + <input type="input" name="password" id="password" value="sample_password"> + + <button id="encrypt" onclick="window.setTimeout(encrypt, 1)">Encrypt</button> + + <button id="decrypt" onclick="window.setTimeout(decrypt, 1)" disabled="true">Decrypt</button> + </div> + + <div id="numRowsContainer"> + <label for="numRows"> + Number of Customer Rows in Table: + </label> + + <input id="numRows" type="input" value="30"> + + <button onclick="createTable()">Update</button> + </div> + </body> +</html>
\ No newline at end of file |