Subversion Repositories Applications.papyrus

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, "_");

			//console.debug("Using Google Gears database " + this.dbName);

		}

		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);

			}

		}));

	}

});

}