He didn't say users were going to write their own SQL.
You're one size fits all approach still isn't the right fit for everyone. I have a desktop application that downloads public data from the internet and stores it in a local database for analysis (mostly pre-developed reports but also has an ad-hoc feature). It does not make sense for me to have a central server for this because the app is free, the central service would be too expensive being that the app is free and the range and size of data plus the frequency of load would by intensive as the user base expands. However, it's perfect for SQL Express or SQL Compact running locally. Let's assume SQL Compact. They can now cache their data offline and use it as needed. It is leaps and bounds faster running off of this than relying on delivery of large amounts of return data over HTTP. Security wise, what are they going to do... decompile my app, tinker with the dynamic SQL generation methods and wreak havoc on their own SQL Express/Compact instance? So what if they do, it's a local database with non sensitive data in it. Have at it.
Now, that said, I'm not saying using a middle tier HTTP service is bad by any stretch of the imagination. On the contrary, it's a great idea in a lot of cases. Just not 100% of them.
In which case use something like SQLlite in your app directly. No need to force the user to install a full blown DB management software on their system if all you want is an app-local DB.