--- /dev/null
+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);
+ }
+ }));
+ }
+});
+
+}
projects / eow / blobdiff