Wednesday, February 6th, 2008

Server Side JavaScript Databases Access

Category: Aptana, Gears, JavaScript

Reposted from my personal blog

As soon as I started to play with Aptana Jaxer, I saw an interesting opportunity to port the Google Gears Database API (note the Gears in the logo!)

If I could use the same API for both client and server side database access, then I can be enabled to do things like:

  • Use one API, and have the system do a sync from local to remote databases
  • If the user has JavaScript, use a local database, else do the work remotely
  • Share higher level database libraries and ORMs such as Gears DBLib for use on server side data too

I quickly built a prototype to see if this would all work.

The Jaxer shim of the Gears API was born, and to test it out I took the database example from Gears itself and made it work.

To do so, I only had to make a few changes:

Run code on the server

I changed the main library to run on the server via:

  1. <script type="text/javascript" src="gears_init.js" runat="server"></script>

I wrapped database access in proxy objects, such as:

javascript

  1. function addPhrase(phrase, currTime) {
  2.   getDB().execute('insert into Demo values (?, ?)', [phrase, currTime]);
  3. }
  4. addPhrase.proxy = true;

This now allows me to run the addPhrase code from the browser, and it will be proxied up to the server to actually execute that INSERT statement.

This forced me to separate the server side code from the client side code, which is a better practice anyway, but it does make you think about what goes where. In a pure Gears solution I can put everything in one place since it all runs on the client.

Create the new gears_init.js

A new gears_init.js acts as the shim itself. Instead of doing the typical Gears logic, it implements the Gears database contract. This wasn’t that tough, although there are differences between the Gears way, and the Jaxer.DB way. The main difference is to do with the ResultSet implementation, where Gears goes for a rs.next()/rs.field(1) type model versus the Jaxer.DB rs.rows[x] model.

I actually much prefer Gears DBLib as it hides all of that, and just gives the programmer what he wants… the rows to work on.

oncallback magic

In the current Jaxer beta, I ran into an issue where I wanted the Gears library to just “be there” for any proxy requests.

You have to think about the lifecycle of a Jaxer application, and the documentation tells you what you need to know to work around the issue.

In this case, I wrapped the code in:

javascript

  1. function oncallback() {
  2.   // create the wrapper here
  3. }

This is less than idea, and Aptana is playing with nice scoping which would enable you to just say “hey, load this library once and keep it around for the lifetime of the server | application | session | page”. That will be very nice indeed.

You can do a little bit of this by opening up your jaxer_prefs file and adding the resource for your file:

javascript

  1. // This option sets up an html document that will be loaded
  2. // everytime a callback is processed.  This has to be a local file.
  3. // If not specified, an empty document will be loaded.
  4. // pref("Jaxer.dev.LoadDocForCallback", "resource:///framework/callback.html");

Future…

This is just the beginning. As I mentioned at the beginning, I am interested to see where you can take this to handle clients who do not support JavaScript, and also to deal with synchronization with minimal code (sync from local to remote with exactly the same SQL API).

Posted by Dion Almaer at 8:02 am
7 Comments

+++--
3 rating from 27 votes

7 Comments »

Comments feed TrackBack URI

Great work. I think Jaxer is quite a step in the right direction. Although I see it more used for DOM modification than server-side actions. So use it as for a templating system, by having one template system that runs server and client side, you can have a data.php that retrieves all the data, and a template.php that retrieves a single template (say for a search result) which is then populated by the many rows of data, reducing bandwidth, and providing quite the web 2.0 experience. So with jaxer, if the user does not have js enabled, the [same] template system will just run server side instead. The implementation I am eyeing is: http://code.google.com/p/jsmarty/ although it still needs some work…

Comment by balupton — February 6, 2008

When the Gears team announced that they where using SQLite for persistence, I tried to warn them that it would be an attractive nuisance. I warned them that having a client-side SQL interface would induce some idiot into exposing a server-side SQL interface in the interest of saving a little bit of effort. The obvious problem is that exposing SQL to the net is extremely dangerous because of XSS and other attacks. It puts too much authority into the hands of the attackers.

I never imagined that that idiot would be you.

Comment by crock — February 7, 2008

@crock, that’s the beauty of Jaxer, you can run the SQL interface just server side, it will not be available client side (with the exception of managed hooks whatever, which should be secure if written correctly).

Comment by balupton — February 7, 2008

agree with balupton.

Jaxer exposes the sql interface on the server side, not client-side. The snippet above didn’t include , so maybe that’s where the confusion is for a lot of people.

I suggest ppl try it out first, dig around a bit before scratching the idea all together.

Comment by Liming — February 8, 2008

@balupton and @liming

I think you’ve missed @crock’s point. He’s not saying using SQL with the Jaxer Server is a problem, he’s saying there is a potential security risk in using a mashup between the client side SQL of Google Gears and the server-side SQL of Jaxer.

Unless I’m mistaken, doing a “sync” of the server to client side database would create an opportunity for SQL injection attacks on the local database.

“This now allows me to run the addPhrase code from the browser, and it will be proxied up to the server to actually execute that INSERT statement.” – This seems to indicate that an SQL statement will be executed from the client to the server, creating a potential for an SQL injection attack against the server.

I think the risks could be mitigated (ie, balupton’s managed hooks), but there is an increased potential for a security breach.

I may also have totally misunderstood the situation Dion is describing. Let me know if I have.

Comment by wgyouree — February 8, 2008

I’m not positive but I think you could execute code from a bookmarklet on a webpage implementing this setup and from that bookmarklet code make SQL queries and updates to the server-side database. Something such as “DROP TABLE Demo” comes to mind as a statement that could do some damage. That is, if those proxy methods are exposed to the client’s JavaScript runtime.

Comment by wgyouree — February 8, 2008

SQL is fine in a server, if it is hidden safely behind a tier of some sort. Exposing an SQL interface to the network is inexcusably dangerous. A client service that would make it seem to be a good idea to expose the database to the network will induce good people to do very bad things, as we have seen here. Gears and AIR do this. It is possible to use them well. It is also possible to use them very badly.

Comment by crock — February 8, 2008

Leave a comment

You must be logged in to post a comment.