Re: Web SQL and SQL

WebSQL is dead.  See
http://lists.w3.org/Archives/Public/public-webapps/2010OctDec/0451.html.

- Kyle

On Tue, May 7, 2013 at 8:20 PM, Dai Rees <drees@microsoft.com> wrote:

> 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.
>
>
>
>