git.pond.sub.org Git - eow/blob - static/dojo-release-1.1.1/dojox/_sql/common.js

   1 if(!dojo._hasResource["dojox._sql.common"]){ //_hasResource checks added by build. Do not use _hasResource directly in your code.
   2 dojo._hasResource["dojox._sql.common"] = true;
   3 dojo.provide("dojox._sql.common");
   4
   5 dojo.require("dojox._sql._crypto");
   6
   7 // summary:
   8 //      Executes a SQL expression.
   9 // description:
  10 //      There are four ways to call this:
  11 //      1) Straight SQL: dojox.sql("SELECT * FROM FOOBAR");
  12 //      2) SQL with parameters: dojox.sql("INSERT INTO FOOBAR VALUES (?)", someParam)
  13 //      3) Encrypting particular values:
  14 //                      dojox.sql("INSERT INTO FOOBAR VALUES (ENCRYPT(?))", someParam, "somePassword", callback)
  15 //      4) Decrypting particular values:
  16 //                      dojox.sql("SELECT DECRYPT(SOMECOL1), DECRYPT(SOMECOL2) FROM
  17 //                                      FOOBAR WHERE SOMECOL3 = ?", someParam,
  18 //                                      "somePassword", callback)
  19 //
  20 //      For encryption and decryption the last two values should be the the password for
  21 //      encryption/decryption, and the callback function that gets the result set.
  22 //
  23 //      Note: We only support ENCRYPT(?) statements, and
  24 //      and DECRYPT(*) statements for now -- you can not have a literal string
  25 //      inside of these, such as ENCRYPT('foobar')
  26 //
  27 //      Note: If you have multiple columns to encrypt and decrypt, you can use the following
  28 //      convenience form to not have to type ENCRYPT(?)/DECRYPT(*) many times:
  29 //
  30 //      dojox.sql("INSERT INTO FOOBAR VALUES (ENCRYPT(?, ?, ?))",
  31 //                                      someParam1, someParam2, someParam3,
  32 //                                      "somePassword", callback)
  33 //
  34 //      dojox.sql("SELECT DECRYPT(SOMECOL1, SOMECOL2) FROM
  35 //                                      FOOBAR WHERE SOMECOL3 = ?", someParam,
  36 //                                      "somePassword", callback)
  37 dojox.sql = new Function("return dojox.sql._exec(arguments);");
  38
  39 dojo.mixin(dojox.sql, {
  40         dbName: null,
  41
  42         // summary:
  43         //      If true, then we print out any SQL that is executed
  44         //      to the debug window
  45         debug: (dojo.exists("dojox.sql.debug")?dojox.sql.debug:false),
  46
  47         open: function(dbName){
  48                 if(this._dbOpen && (!dbName || dbName == this.dbName)){
  49                         return;
  50                 }
  51
  52                 if(!this.dbName){
  53                         this.dbName = "dot_store_"
  54                                 + window.location.href.replace(/[^0-9A-Za-z_]/g, "_");
  55                         // database names in Gears are limited to 64 characters long
  56                         if(this.dbName.length > 63){
  57                           this.dbName = this.dbName.substring(0, 63);
  58                         }
  59                 }
  60
  61                 if(!dbName){
  62                         dbName = this.dbName;
  63                 }
  64
  65                 try{
  66                         this._initDb();
  67                         this.db.open(dbName);
  68                         this._dbOpen = true;
  69                 }catch(exp){
  70                         throw exp.message||exp;
  71                 }
  72         },
  73
  74         close: function(dbName){
  75                 // on Internet Explorer, Google Gears throws an exception
  76                 // "Object not a collection", when we try to close the
  77                 // database -- just don't close it on this platform
  78                 // since we are running into a Gears bug; the Gears team
  79                 // said it's ok to not close a database connection
  80                 if(dojo.isIE){ return; }
  81
  82                 if(!this._dbOpen && (!dbName || dbName == this.dbName)){
  83                         return;
  84                 }
  85
  86                 if(!dbName){
  87                         dbName = this.dbName;
  88                 }
  89
  90                 try{
  91                         this.db.close(dbName);
  92                         this._dbOpen = false;
  93                 }catch(exp){
  94                         throw exp.message||exp;
  95                 }
  96         },
  97
  98         _exec: function(params){
  99                 try{
 100                         // get the Gears Database object
 101                         this._initDb();
 102
 103                         // see if we need to open the db; if programmer
 104                         // manually called dojox.sql.open() let them handle
 105                         // it; otherwise we open and close automatically on
 106                         // each SQL execution
 107                         if(!this._dbOpen){
 108                                 this.open();
 109                                 this._autoClose = true;
 110                         }
 111
 112                         // determine our parameters
 113                         var sql = null;
 114                         var callback = null;
 115                         var password = null;
 116
 117                         var args = dojo._toArray(params);
 118
 119                         sql = args.splice(0, 1)[0];
 120
 121                         // does this SQL statement use the ENCRYPT or DECRYPT
 122                         // keywords? if so, extract our callback and crypto
 123                         // password
 124                         if(this._needsEncrypt(sql) || this._needsDecrypt(sql)){
 125                                 callback = args.splice(args.length - 1, 1)[0];
 126                                 password = args.splice(args.length - 1, 1)[0];
 127                         }
 128
 129                         // 'args' now just has the SQL parameters
 130
 131                         // print out debug SQL output if the developer wants that
 132                         if(this.debug){
 133                                 this._printDebugSQL(sql, args);
 134                         }
 135
 136                         // handle SQL that needs encryption/decryption differently
 137                         // do we have an ENCRYPT SQL statement? if so, handle that first
 138                         if(this._needsEncrypt(sql)){
 139                                 var crypto = new dojox.sql._SQLCrypto("encrypt", sql,
 140                                                                                                         password, args,
 141                                                                                                         callback);
 142                                 return; // encrypted results will arrive asynchronously
 143                         }else if(this._needsDecrypt(sql)){ // otherwise we have a DECRYPT statement
 144                                 var crypto = new dojox.sql._SQLCrypto("decrypt", sql,
 145                                                                                                         password, args,
 146                                                                                                         callback);
 147                                 return; // decrypted results will arrive asynchronously
 148                         }
 149
 150                         // execute the SQL and get the results
 151                         var rs = this.db.execute(sql, args);
 152
 153                         // Gears ResultSet object's are ugly -- normalize
 154                         // these into something JavaScript programmers know
 155                         // how to work with, basically an array of
 156                         // JavaScript objects where each property name is
 157                         // simply the field name for a column of data
 158                         rs = this._normalizeResults(rs);
 159
 160                         if(this._autoClose){
 161                                 this.close();
 162                         }
 163
 164                         return rs;
 165                 }catch(exp){
 166                         exp = exp.message||exp;
 167
 168                         console.debug("SQL Exception: " + exp);
 169
 170                         if(this._autoClose){
 171                                 try{
 172                                         this.close();
 173                                 }catch(e){
 174                                         console.debug("Error closing database: "
 175                                                                         + e.message||e);
 176                                 }
 177                         }
 178
 179                         throw exp;
 180                 }
 181         },
 182
 183         _initDb: function(){
 184                 if(!this.db){
 185                         try{
 186                                 this.db = google.gears.factory.create('beta.database', '1.0');
 187                         }catch(exp){
 188                                 dojo.setObject("google.gears.denied", true);
 189                                 dojox.off.onFrameworkEvent("coreOperationFailed");
 190                                 throw "Google Gears must be allowed to run";
 191                         }
 192                 }
 193         },
 194
 195         _printDebugSQL: function(sql, args){
 196                 var msg = "dojox.sql(\"" + sql + "\"";
 197                 for(var i = 0; i < args.length; i++){
 198                         if(typeof args[i] == "string"){
 199                                 msg += ", \"" + args[i] + "\"";
 200                         }else{
 201                                 msg += ", " + args[i];
 202                         }
 203                 }
 204                 msg += ")";
 205
 206                 console.debug(msg);
 207         },
 208
 209         _normalizeResults: function(rs){
 210                 var results = [];
 211                 if(!rs){ return []; }
 212
 213                 while(rs.isValidRow()){
 214                         var row = {};
 215
 216                         for(var i = 0; i < rs.fieldCount(); i++){
 217                                 var fieldName = rs.fieldName(i);
 218                                 var fieldValue = rs.field(i);
 219                                 row[fieldName] = fieldValue;
 220                         }
 221
 222                         results.push(row);
 223
 224                         rs.next();
 225                 }
 226
 227                 rs.close();
 228
 229                 return results;
 230         },
 231
 232         _needsEncrypt: function(sql){
 233                 return /encrypt\([^\)]*\)/i.test(sql);
 234         },
 235
 236         _needsDecrypt: function(sql){
 237                 return /decrypt\([^\)]*\)/i.test(sql);
 238         }
 239 });
 240
 241 // summary:
 242 //      A private class encapsulating any cryptography that must be done
 243 //      on a SQL statement. We instantiate this class and have it hold
 244 //      it's state so that we can potentially have several encryption
 245 //      operations happening at the same time by different SQL statements.
 246 dojo.declare("dojox.sql._SQLCrypto", null, {
 247         constructor: function(action, sql, password, args, callback){
 248                 if(action == "encrypt"){
 249                         this._execEncryptSQL(sql, password, args, callback);
 250                 }else{
 251                         this._execDecryptSQL(sql, password, args, callback);
 252                 }
 253         },
 254
 255         _execEncryptSQL: function(sql, password, args, callback){
 256                 // strip the ENCRYPT/DECRYPT keywords from the SQL
 257                 var strippedSQL = this._stripCryptoSQL(sql);
 258
 259                 // determine what arguments need encryption
 260                 var encryptColumns = this._flagEncryptedArgs(sql, args);
 261
 262                 // asynchronously encrypt each argument that needs it
 263                 var self = this;
 264                 this._encrypt(strippedSQL, password, args, encryptColumns, function(finalArgs){
 265                         // execute the SQL
 266                         var error = false;
 267                         var resultSet = [];
 268                         var exp = null;
 269                         try{
 270                                 resultSet = dojox.sql.db.execute(strippedSQL, finalArgs);
 271                         }catch(execError){
 272                                 error = true;
 273                                 exp = execError.message||execError;
 274                         }
 275
 276                         // was there an error during SQL execution?
 277                         if(exp != null){
 278                                 if(dojox.sql._autoClose){
 279                                         try{ dojox.sql.close(); }catch(e){}
 280                                 }
 281
 282                                 callback(null, true, exp.toString());
 283                                 return;
 284                         }
 285
 286                         // normalize SQL results into a JavaScript object
 287                         // we can work with
 288                         resultSet = dojox.sql._normalizeResults(resultSet);
 289
 290                         if(dojox.sql._autoClose){
 291                                 dojox.sql.close();
 292                         }
 293
 294                         // are any decryptions necessary on the result set?
 295                         if(dojox.sql._needsDecrypt(sql)){
 296                                 // determine which of the result set columns needs decryption
 297                                 var needsDecrypt = self._determineDecryptedColumns(sql);
 298
 299                                 // now decrypt columns asynchronously
 300                                 // decrypt columns that need it
 301                                 self._decrypt(resultSet, needsDecrypt, password, function(finalResultSet){
 302                                         callback(finalResultSet, false, null);
 303                                 });
 304                         }else{
 305                                 callback(resultSet, false, null);
 306                         }
 307                 });
 308         },
 309
 310         _execDecryptSQL: function(sql, password, args, callback){
 311                 // strip the ENCRYPT/DECRYPT keywords from the SQL
 312                 var strippedSQL = this._stripCryptoSQL(sql);
 313
 314                 // determine which columns needs decryption; this either
 315                 // returns the value *, which means all result set columns will
 316                 // be decrypted, or it will return the column names that need
 317                 // decryption set on a hashtable so we can quickly test a given
 318                 // column name; the key is the column name that needs
 319                 // decryption and the value is 'true' (i.e. needsDecrypt["someColumn"]
 320                 // would return 'true' if it needs decryption, and would be 'undefined'
 321                 // or false otherwise)
 322                 var needsDecrypt = this._determineDecryptedColumns(sql);
 323
 324                 // execute the SQL
 325                 var error = false;
 326                 var resultSet = [];
 327                 var exp = null;
 328                 try{
 329                         resultSet = dojox.sql.db.execute(strippedSQL, args);
 330                 }catch(execError){
 331                         error = true;
 332                         exp = execError.message||execError;
 333                 }
 334
 335                 // was there an error during SQL execution?
 336                 if(exp != null){
 337                         if(dojox.sql._autoClose){
 338                                 try{ dojox.sql.close(); }catch(e){}
 339                         }
 340
 341                         callback(resultSet, true, exp.toString());
 342                         return;
 343                 }
 344
 345                 // normalize SQL results into a JavaScript object
 346                 // we can work with
 347                 resultSet = dojox.sql._normalizeResults(resultSet);
 348
 349                 if(dojox.sql._autoClose){
 350                         dojox.sql.close();
 351                 }
 352
 353                 // decrypt columns that need it
 354                 this._decrypt(resultSet, needsDecrypt, password, function(finalResultSet){
 355                         callback(finalResultSet, false, null);
 356                 });
 357         },
 358
 359         _encrypt: function(sql, password, args, encryptColumns, callback){
 360                 //console.debug("_encrypt, sql="+sql+", password="+password+", encryptColumns="+encryptColumns+", args="+args);
 361
 362                 this._totalCrypto = 0;
 363                 this._finishedCrypto = 0;
 364                 this._finishedSpawningCrypto = false;
 365                 this._finalArgs = args;
 366
 367                 for(var i = 0; i < args.length; i++){
 368                         if(encryptColumns[i]){
 369                                 // we have an encrypt() keyword -- get just the value inside
 370                                 // the encrypt() parantheses -- for now this must be a ?
 371                                 var sqlParam = args[i];
 372                                 var paramIndex = i;
 373
 374                                 // update the total number of encryptions we know must be done asynchronously
 375                                 this._totalCrypto++;
 376
 377                                 // FIXME: This currently uses DES as a proof-of-concept since the
 378                                 // DES code used is quite fast and was easy to work with. Modify dojox.sql
 379                                 // to be able to specify a different encryption provider through a
 380                                 // a SQL-like syntax, such as dojox.sql("SET ENCRYPTION BLOWFISH"),
 381                                 // and modify the dojox.crypto.Blowfish code to be able to work using
 382                                 // a Google Gears Worker Pool
 383
 384                                 // do the actual encryption now, asychronously on a Gears worker thread
 385                                 dojox._sql._crypto.encrypt(sqlParam, password, dojo.hitch(this, function(results){
 386                                         // set the new encrypted value
 387                                         this._finalArgs[paramIndex] = results;
 388                                         this._finishedCrypto++;
 389                                         // are we done with all encryption?
 390                                         if(this._finishedCrypto >= this._totalCrypto
 391                                                 && this._finishedSpawningCrypto){
 392                                                 callback(this._finalArgs);
 393                                         }
 394                                 }));
 395                         }
 396                 }
 397
 398                 this._finishedSpawningCrypto = true;
 399         },
 400
 401         _decrypt: function(resultSet, needsDecrypt, password, callback){
 402                 //console.debug("decrypt, resultSet="+resultSet+", needsDecrypt="+needsDecrypt+", password="+password);
 403
 404                 this._totalCrypto = 0;
 405                 this._finishedCrypto = 0;
 406                 this._finishedSpawningCrypto = false;
 407                 this._finalResultSet = resultSet;
 408
 409                 for(var i = 0; i < resultSet.length; i++){
 410                         var row = resultSet[i];
 411
 412                         // go through each of the column names in row,
 413                         // seeing if they need decryption
 414                         for(var columnName in row){
 415                                 if(needsDecrypt == "*" || needsDecrypt[columnName]){
 416                                         this._totalCrypto++;
 417                                         var columnValue = row[columnName];
 418
 419                                         // forming a closure here can cause issues, with values not cleanly
 420                                         // saved on Firefox/Mac OS X for some of the values above that
 421                                         // are needed in the callback below; call a subroutine that will form
 422                                         // a closure inside of itself instead
 423                                         this._decryptSingleColumn(columnName, columnValue, password, i,
 424                                                                                                 function(finalResultSet){
 425                                                 callback(finalResultSet);
 426                                         });
 427                                 }
 428                         }
 429                 }
 430
 431                 this._finishedSpawningCrypto = true;
 432         },
 433
 434         _stripCryptoSQL: function(sql){
 435                 // replace all DECRYPT(*) occurrences with a *
 436                 sql = sql.replace(/DECRYPT\(\*\)/ig, "*");
 437
 438                 // match any ENCRYPT(?, ?, ?, etc) occurrences,
 439                 // then replace with just the question marks in the
 440                 // middle
 441                 var matches = sql.match(/ENCRYPT\([^\)]*\)/ig);
 442                 if(matches != null){
 443                         for(var i = 0; i < matches.length; i++){
 444                                 var encryptStatement = matches[i];
 445                                 var encryptValue = encryptStatement.match(/ENCRYPT\(([^\)]*)\)/i)[1];
 446                                 sql = sql.replace(encryptStatement, encryptValue);
 447                         }
 448                 }
 449
 450                 // match any DECRYPT(COL1, COL2, etc) occurrences,
 451                 // then replace with just the column names
 452                 // in the middle
 453                 matches = sql.match(/DECRYPT\([^\)]*\)/ig);
 454                 if(matches != null){
 455                         for(var i = 0; i < matches.length; i++){
 456                                 var decryptStatement = matches[i];
 457                                 var decryptValue = decryptStatement.match(/DECRYPT\(([^\)]*)\)/i)[1];
 458                                 sql = sql.replace(decryptStatement, decryptValue);
 459                         }
 460                 }
 461
 462                 return sql;
 463         },
 464
 465         _flagEncryptedArgs: function(sql, args){
 466                 // capture literal strings that have question marks in them,
 467                 // and also capture question marks that stand alone
 468                 var tester = new RegExp(/([\"][^\"]*\?[^\"]*[\"])|([\'][^\']*\?[^\']*[\'])|(\?)/ig);
 469                 var matches;
 470                 var currentParam = 0;
 471                 var results = [];
 472                 while((matches = tester.exec(sql)) != null){
 473                         var currentMatch = RegExp.lastMatch+"";
 474
 475                         // are we a literal string? then ignore it
 476                         if(/^[\"\']/.test(currentMatch)){
 477                                 continue;
 478                         }
 479
 480                         // do we have an encrypt keyword to our left?
 481                         var needsEncrypt = false;
 482                         if(/ENCRYPT\([^\)]*$/i.test(RegExp.leftContext)){
 483                                 needsEncrypt = true;
 484                         }
 485
 486                         // set the encrypted flag
 487                         results[currentParam] = needsEncrypt;
 488
 489                         currentParam++;
 490                 }
 491
 492                 return results;
 493         },
 494
 495         _determineDecryptedColumns: function(sql){
 496                 var results = {};
 497
 498                 if(/DECRYPT\(\*\)/i.test(sql)){
 499                         results = "*";
 500                 }else{
 501                         var tester = /DECRYPT\((?:\s*\w*\s*\,?)*\)/ig;
 502                         var matches;
 503                         while(matches = tester.exec(sql)){
 504                                 var lastMatch = new String(RegExp.lastMatch);
 505                                 var columnNames = lastMatch.replace(/DECRYPT\(/i, "");
 506                                 columnNames = columnNames.replace(/\)/, "");
 507                                 columnNames = columnNames.split(/\s*,\s*/);
 508                                 dojo.forEach(columnNames, function(column){
 509                                         if(/\s*\w* AS (\w*)/i.test(column)){
 510                                                 column = column.match(/\s*\w* AS (\w*)/i)[1];
 511                                         }
 512                                         results[column] = true;
 513                                 });
 514                         }
 515                 }
 516
 517                 return results;
 518         },
 519
 520         _decryptSingleColumn: function(columnName, columnValue, password, currentRowIndex,
 521                                                                                         callback){
 522                 //console.debug("decryptSingleColumn, columnName="+columnName+", columnValue="+columnValue+", currentRowIndex="+currentRowIndex)
 523                 dojox._sql._crypto.decrypt(columnValue, password, dojo.hitch(this, function(results){
 524                         // set the new decrypted value
 525                         this._finalResultSet[currentRowIndex][columnName] = results;
 526                         this._finishedCrypto++;
 527
 528                         // are we done with all encryption?
 529                         if(this._finishedCrypto >= this._totalCrypto
 530                                 && this._finishedSpawningCrypto){
 531                                 //console.debug("done with all decrypts");
 532                                 callback(this._finalResultSet);
 533                         }
 534                 }));
 535         }
 536 });
 537
 538 }