[ALOY-535] Need support for prepared statements

GitHub Issue	n/a
Type	Improvement
Priority	High
Status	Closed
Resolution	Fixed
Resolution Date	2013-02-25T22:19:45.000+0000
Affected Version/s	Alloy 1.0.3
Fix Version/s	Alloy 1.1.0, 2013 Sprint 05
Components	Runtime
Labels	api
Reporter	Thomas Wilkinson
Assignee	Tony Lukasavage
Created	2013-02-25T15:06:25.000+0000
Updated	2013-04-09T19:33:25.000+0000

Description

update (2/25/2013)

The new syntax will be, allowing for the existing syntax as well as allowing for a prepared statement to be defined with an object.

// plain query
collection.fetch({
    query: 'select * from some_table where column1 = "somevalue"'
});

// prepared statement
collection.fetch({
    query: {
        statement: 'select * from some_table where column1 = ?',
        params: [values]
    }
});

original

Currently there is no support for prepared statements. The only way to make an SQL statement with parameters is to have code insert the parameters. This is a security risk, a bad practice, and reflects poorly on Alloy models as a whole. Currently I have to: collection.fetch({query: "select * from some_table where column1 = '" + value + "'"); Ideally I would like to: collection.fetch({query: "select * from some_table where column1 = ?" + params: [value]}); Assuming this uses the Ti.Database.execute() in the background, this should be a small change as the execute() method already supports this.

Comments

Tony Lukasavage 2013-02-25

The proposed syntax doesn't make much sense, in that it's using inline concatenation of arguments, making it look like some kind of string:

   collection.fetch({
       query: "select * from some_table where column1 = ?" + params: [value]
   });

I'm assuming you meant something like this:

   collection.fetch({
       query: "select * from some_table where column1 = ?",
       params: [value]
   });

I like the idea of keeping the query and args contained in a single property of the fetch object though, to keep them clearly coupled as fetch can have other arguments:

   collection.fetch({
       query: {
           statement: "select * from some_table where column1 = ?",
           params: [values]
       }
   });

Thomas Wilkinson 2013-02-25

You're correct in your assumption, I intended a comma instead of a plus sign. However, I do like your proposal better. Thank you.
Federico Casali 2013-04-09

Sample code available in https://github.com/appcelerator/alloy/tree/1_1_X/test/apps/models/sql_queries Verified as fixed. Alloy: 1.1.0-cr CLI version: 3.1.0-cr TiSDK: 3.1.0.v20130408154547 Closing.

JSON Source