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.
