Web SQL and SQL

While researching the feasibility of using Web SQL for an internal project, I was taken aback by the use of raw SQL strings - I thought as an industry we had moved past that horrid hack :)

My understanding is that Web SQL presents a simplified means of storing and retrieving relational data in the client for offline storage, and it isn't as though they need OLAP cubes, so allowing freeform SQL seems a bit dangerous - and leaves the spectre of implementation incompatibilities (like present-day SQL) free to stalk again.

The main thing that concerns me is the risk of SQL injection - many of us are veterans of VBScript and PHP code that is wide-open (e.g. "SELECT * FROM accounts WHERE username = $_POST['user']") so I'm surprised the current specification gives us a simple hand-waving dismissal: "Authors are strongly recommended to make use of the ? placeholder feature of the executeSql() method, and to never construct SQL statements on the fly.".

I'd like to propose that the executeSql method be completely removed and replaced with individual functions that can be used to work with relational data in a safe, efficient manner. Please excuse the bias visible from my email address domain, but I think our Linq library is a good approach to follow, for example:

db.from( "tableName" ).where( "c", function(value) { return value > 5; } ).orderBy( "c").select("a", "b", "d");

....is safer than letting developers, who span a huge gamut of competence, play with fire, for example:

var c = prompt("which column?");
tx.executeSql("SELECT a, b, d FROM tableName WHERE " + c + " > 5 ORDER BY " + c);

Joins and other complex queries can be done:

db.from("tableFoo").join("tableBar", "a", "g").select("tableFoo.a", "tableBar.g");

Seeming as aggregate operations (e.g. SUM, AVG, etc) are known to the implementation they can also be exposed directly:

db.from("tableFoo").where("c", function(value) { return value > 5; }).sum();

This approach can be extended to replace the other core SQL statements, e.g..:

db.update("tableName").where( "c", function(value) { return value == 5; } ).select("a", "b", "d").set( 5, 7, 13 );
db.insert("tableName").select("a", "b", "d").set( 5, 7, 13 ); 
db.delete("tableName").where( "c", function(value) { return value < 5; });

This approach has the advantage of providing syntax checking when the script is interpreted by the browser (rather than waiting for the SQL string to be executed first, which might never happen), making it impossible to perform SQL-injection attacks. This proposed API requires no introduction of new ECMAScript language features either (though not to be confused with the Linq language extensions to C# and VB.NET). It also eliminates SQL's counter-intuitive syntax which puts the SELECT projection before the sources, predicates and  joins - something that led to no end of confusion when I was starting-off with SQL.

Received on Wednesday, 8 May 2013 17:50:15 UTC