Insert dynamic parameters to sqlite database statements

Question

I want to insert parameters dynamically to statements at sqlite. I now write as follows:

 tx.executeSql('SELECT Data from Table Where something = "'+ anyvariable+ '"',[],successFn, errorCB);

But I guess there is a better (cleaner) method to do it.. Any ideas?

• Sana Joseph: No I said I want a better method. the one with "?",
• rlemon: Why in the world would you be using js to execute SQL statements... use prepared statements on the server to ensure proper sanitization.
• ThiefMaster: @rlemon: It might be WebSQL or server-side JS which is just wrongfully tagged with jquery

Answer 1

I would create an object to model my dynamic parameters using the following interfaces:

{ //reduced format
 clause: string,
 params: an array of parameters if more than one is used
}
{ //simple entry format
 clause: string
 param: the parameter if only one is used
}
{ //or format
 or: an array of dynamic parameter interfaces to be joined as part of an or clause
}
{ //and format
 and: an array of dynamic parameter interfaces to be joined as part of an and clause
}

From here I could use the following functions:

function reduce(param) {
 var clause = [], params = [], nest, nestType;
 if (param.clause !== undefined && param.params) {
 //already in reduced format 
 return param;
 }
 if (param.clause !== undefined && param.param !== undefined) {
 //convert simple format to reduced
 return { clause: param.clause, params: [param.param] };
 }
 if (param.clause) {
 //special case (clause without additional parameter)
 //parameters without clause would be done by using empty string
 return { clause: param.clause, params: [] };
 }
 //convert nested forms (and and or)
 if (param.and) {
 nest = param.and;
 nestType = ' AND ';
 } else if (param.or) {
 nest = param.or;
 nestType = ' OR ';
 } else {
 throw new Error('Invalid dynamic parameter found');
 }
 nest.forEach(function (p) {
 p = reduce(p);
 clause.push(p.clause);
 params.push.apply(params, p.params);
 });
 return {
 clause: '(' + clause.join(nestType) + ')',
 params: params
 };
}
function executeDynamicSql(tx, base, dynamicparameter, onSuccess, onError) {
 var reduction = reduce(dynamicparameter);
 tx.executeSql(base + reduction.clause, reduction.params, onSuccess, onError);
}

And call it (in your test case) like so:

executeDynamicSql(tx, 'SELECT Data from Table Where ', {
 clause: 'something = ?',
 param: anyvariable
}, successFn, errorCB);

What is nice about this function is that it can handle a far more complex where clause such as:

var dynamicwhere = {
 or: [
 {clause: 'something = ?', param: somethingid},
 {clause: 'somethingelse = ?', param: somethingelse},
 {clause: 'somedate between ? and ?', params: [d1, d2]},
 {
 and: [
 {clause: 'amt > ?', param: min},
 {clause: 'amt < ?', param: max},
 ]
 }
 ]
 };
executeDynamicSql(tx, 'SELECT Data from Table Where ', dynamicwhere, successFn, errorCB);

Ultimately executing:

SELECT Data from Table Where (something = ?
 or somethingelse = ?
 or somedate between ? and ?
 or (amt > ? and amt < ?))

With the in order parameter list:

[somethingid, somethingelse, d1, d2, min, max]

You could do something more fancy using a regex replace or whatever, but I don't think it should be necessary.