Creating spreadsheets in Google Docs... mission possible?

Exporting data as spreadsheets into Google Docs sounds easy and quite feasible. However, it also has a few gotcha's. After struggling for a day, I've decided to share my notes on approaching this problem.

Creating the spreadsheet
My initial requirement for my application is to export my data into a brand new spreadsheet where the user will have ownership of it. A quick stroll through the Spreadsheet API led me to a section describing the need to upload a spreadsheet beforehand or create one manually. This was definitely against my requirements.

However, buried inside the Google Documents List Data API is a mechanism for creating empty documents or from a template (I'll come to this in a minute). Voila!

Changing the term attribute to http://schemas.google.com/docs/2007#spreadsheet surely does the trick if I want a completely blank spreadsheet.

Feeding data into the spreadsheet
This is where it becomes more tricky -- as it turns out, there isn't any convenient way to use the list approach (something I preferred using as opposed to the cell approach) to populate an empty spreadsheet without first telling Google Docs the schema that your list would use. One bit of observation to make is that the list schema is derived from the header row in your spreadsheet, therefore if the A1 cell stores "sample text" as its value, every subsequent row will have the "A" column described as <gsx:sampletext>. In my case, I opted to use a pre-built template from which to create my spreadsheet (see above about copying documents); however, there might be also a workaround involving batch cell update.

Some random tips
Here are some random bits that I found useful during this exercise:
- Always pay attention to the response object when posting new items (be it documents, worksheets, list entries, etc.) to Google Docs. The response object will provide you with some very useful information (stored inside the link element of the feed/entry) about what are the next logical APIs to call.
- The _worksheetId_ for the default worksheet in a newly created spreadsheet is... yes, "default". Should you need to add a row to it, you can simply call POST https://spreadsheets.google.com/feeds/list/[spreadsheet key]/default/private/full.


Edit: I went back to experiment with alternative ways of setting the header row of the default worksheet. As expected, one can set the header row by fetching the cells there and updating them with whatever contents they need to have.

Chrome and Privacy Mode

A while back I was playing around with Google Gears and Incognito mode. The test was simple: I store some data in Gears while in Incognito and try to read it while in Normal mode.

The goal
The goal of this test is quite simple: prove that privacy related data (yes, with Gears gaining momentum a lot of apps like Google Docs, Google Reader, Google Calendar, MySpace, etc. are beginning to utilize Gears' SQLite engine to cache information) that was obtained in Incognito mode will persist in Normal mode and vice-versa.

The code
Load the following code in Incognito mode:

var db = google.gears.factory.create('beta.database');
db.open('privacy-test');

db.execute('drop table if exists secret');
db.execute('create table secret (message text)');
db.execute('insert into secret values (?)', "secret message");

Load the following code in Normal mode:

var db = google.gears.factory.create('beta.database');
db.open('privacy-test');

db.execute('create table if not exists secret (message text)');
var rs = db.execute('select message from secret');
if(rs.isValidRow()) {
 alert(unescape(rs.field(0)));
}

The result
Data indeed persisted. I've posted the code for Incognito mode here and for Normal mode here.

Conclusion
Browsers' attempt to provide additional privacy to users is noble; however, this is valid only for data that the browser controls directly (e.g. cookies, browser cache). As Web applications are becoming richer in features, third party technologies like Google Gears, Flash, and Silverlight will soon need to start playing the privacy game as well if browsers are to fully allow privacy modes to users.

Fun with error logs

It's sometimes impressive what you can find on the Web these days. I had an error in PHP that I wanted to troubleshoot and stumbled on this.

Time for step 2...

Google Maps API: Tips and Tricks

Google Maps API is awesome! There is no doubt about it. The Javascript functionality allows developers to do anything and everything when it comes to geo positioning. As I've incorporated the maps API into the gt project (release coming soon), I ran into a few issues that require some tricks in order to work. Here is the list:

1. Never use addOverlay before you call setCenter. If you do, you will experience the "Cannot call 'getProjection' on null" error which results from Google Maps trying to place a marker on a map that does not exist.


2. Attach to events in a separate function. In one of the map API examples I found the nice functionality to pop a message box when the user clicks on a marker. This is done through the GEvent.addListener call. The problem comes when addListener and addOverlay are called within the same function. This causes Google Maps to always display the same pop-up at the same location regardless of which marker has been clicked.

More tips and tricks to come at a later point. :)

Google Gears and Information Disclosure

While reading some of the new stuff around Google Gears, I noticed a blog article by the Gears Team regarding the integration of Google Gears with Google Docs (or Office as I like to call it). It is quite interesting to see how Gears will impact the service offered by Docs. However, it also brings to question the confidentiality of the cached information.

Take for instance a simple user-based web application. The application consists of the logon page and some important user information. When Gears is deployed in this scenario, the caching can be directed via one of two ways:

• Dynamically generate Gears manifest to reflect the user's session ID. This is usually done when the web application uses the session ID as part of the URL. Because Gears caches resources based on URL, it is important to update the manifest when the URL for a to-be-cached resource has changed.


• Look for a specific cookie. The latest version so far (0.3.13.0) allows developers to direct the serving of a page by demanding that a specific cookie is present before the serving can occur. This is done by using the requiredCookie attribute in the Gears manifest.

What is the problem with these 2 scenarios?
Because both cases expose the problem of caching using random IDs, retrieving cached data back from Gears relies on either keeping browser history or making session lifetime longer. While the first approach is a concern only at the client, extending the lifetime of a session is generally considered a bad security practice. Additionally, it is not quite clear whether/how Gears allows developers to clear data from the local cache. As a result, anybody with local access to the browser may retrieve data that is otherwise protected by a web application.


Why this matters?
As a final note, combine all of this with a popular web application of choice and public machine such as a library/university computer or a simple Internet kiosk. I believe you'll soon get the idea.

Google Gears updated

Google has released a new version of Google Gears. Among the new additions are the HttpRequest, much like the AJAX HttpRequest, and Timer classes. At first glance it seems like Google is definitely looking forward to expand the plug-in into a full development platform. However, until then, here is a little teaser as to what can be done with their workerPool.createWorkerFromUrl and timer.setInterval APIs. :)

Using Google Gears to detect XSS

As cross-site scripting can be exploited in many ways, I've experienced in my past the need to place an HTTP resource on a remote host and reference it from the web application under test. When such resource is used for capturing data from the web application under test, one must have access to the remote host's HTTP logs. What if that's not possible?

One of the first things that I looked at in Google Gears was its ability to cache HTTP resources (except for streaming resources, but that's a whole different story). As a resource is cached, Gears records the last time this resource has been updated from the Internet. However, the part interested me more was in obtaining the last time when the browser attempted to display this resource. Unfortunately, Gears does not provide APIs; therefore a work around must be used.


To address my need of knowing that my captured data has gone to my intended resource, I've taken the concept of using different images for 1) the resource itself, 2) failed attempt to capture data, and 3) successful attempt to capture data. The following manifest represents the role of each image:

{
  "betaManifestVersion": 1,
  "version": "v0.1",
  "entries": [
      { "url": "imageTest.html"},
      { "url": "green.png"},
      { "url": "green.png?capturedData=", "src": "blue.png"},
      { "url": "green.png", "ignoreQuery": true, "src": "red.png"}
    ]
}

Next, to ensure that all data is properly cached into Google Gears, the browser is navigated to a page that kicks off the process. Finally, once Gears and the browser is properly set, the attacker's web site simply goes "offline". Note that the browser's cache might interfere with this process; therefore it's a good idea to clear the browser's cache once the attacker's site has gone offline.

My current belief is that this approach (along with checking the resource's URL in the browser window) is a good mechanism for determining if a) a page is vulnerable to XSS and b) an attacker can steal data from that page. However, I'm yet to try it out on a project. :)


A working demo of this can be found here.

Building security tools on top of Google Gears

Fitting into the role of a penetration tester from time to time, I sometimes encounter the trouble of not being able to use a remote web server to deploy certain attacks, or simply to verify the existence of a vulnerability. As a result, lately I've been spending quite a bit of time toward the crazy idea of using Google Gears as the platform for tool development, mainly to address my needs when I need a remote host. The logic of Gears is quite simple. If a resource is available online, download it, and let the user see it. If the resource is not available (mainly due to network connectivity), Gears will fetch the last available copy of the resource from its cache.

Because Gears provides local caching capability and a built-in database engine, one can assume that tool development won't be an impossible task. For instance, auditing a remote server's logs to verify a cross-site scripting vulnerability can be approached by two different ways:
- A check against Gears' last access time on the desired object may reveal whether the attack was successful.
- A record in Gears' database may also indicate whether a request for a cached object is successful.

Anyhow, there are still quite a bit of research to be done before a solid conclusion regarding its use and feasibility can be made.

Google Maps Test

The intention of this post is to test and play with the Google Maps API. Although doable through the use of an iframe pointing to maps.google.com, the map below is constructed by Google's APIs.

Gearing up with Google

Google has released its Google Gears project to the public. At first glance it seems like a pretty cool and interesting tool to play around with. It improves performance by limiting redundant traffic, allows offline browsing... pardon, searching, etc. On the other side, it's interesting to see how they manage to handle the local caching of data from a security stand point. From an attacker's perspective, Google Gears could become a sweet tool to actually steal not only users' identities (i.e. identity fraud), but also steal their online identities by abusing common web vulnerabilities such as XSS to potentially retrieve personal data that has been previously cached locally by Google Gears. It's definitely something to look into at some point.

Entering the world of Google Apps

Last week I began exploring the applications that Google makes available to its users. If you like the services offered by Google, you'd really get a kick of Google Apps. I think at this point Google's efforts to introduce the web office into Web 2.0 have really paid off. r-n-d.org is an example of how different Google applications (calendar, mail, documents, blogs) can be fit together into a single domain and allow its users to do their work without the worries of installing software. As Roussi said about 2 yrs ago, Google's vision is to allow the user to be independent of their computers -- just grab a browser and go to work. Of course, this will have some security implications, but it also will be very useful for open community projects and small businesses.

As final words, I'd like to share a video that Ady sent a few weeks ago. Enjoy!