Monday, April 29, 2013

Querying QuickBase

QuickBase is a nice tool for quickly creating data-centric web applications without having to write any code. It basically allows you to define table similar to the way you would a database, but also give you roles, permissions, reports, forms, emails, etc all in one place. If you are familiar with Custom Lists in SharePoint you will see how this is very similar.

Once you have a table created in your application you may want to show that data in another web application. You can do that use a POST or GET request. For our purposes we will be doing a GET request since it doesn't require any coding and is easiest to play with.

Here are the API docs. Of particular interest are the following:
  • api_authenticate-- you will need this to get the authToken if you are not logged in
  • gen_results_table -- does all the heavy lifting
  • do_query -- use this to create your own custom query instead of a existing view
  The example below shows how to return all rows in the table that match the criteria I pass it using a GET request. You can also customize the query.

According to the sample here, you can embed the QuickBase on your page by doing something like the following:

<html><head> <script lang="javascript" src=https://yourcompanyhere.quickbase.com/db/yourdbidhere?a=API_GenResultsTable&qid=1&jht=1></script> <style> td.m { font-family:verdana; font-size:70%; } td.hd { font-family:verdana; font-size:70%; font-weight:bold; color:white;} </style> </head> <body> <h3>QuickBase.com content below:</h3> <script lang="javascript">qdbWrite();</script> </body></html>

Finding the parameters we need using the UI

While this is pretty easy to do the stuff above, you need to know what to put for the placeholders in red. First thing I recommend is log into www.quickbase.com using your favorite browser. Click on the tab for the application you want to access. Next click on one of the reports. Now take note of the url. It should map pretty closely to the following:

https://yourcompanynamehere.quickbase.com/db/yourdbidhere?a=q&qid=1

The host will be your host. The stuff after db/ and before ?a= will be your dbid. The qid variable is the id (integer) of the report you want to use. In this example, the report id is 1. Now, just use those same values in the url for the javascript src attribute (replacing the items in red with the values you see in the url to the report).

POTENTIAL MAJOR SECURITY ISSUE:
In the above example, we will not be getting the authToken (QuickBase calls it a ticket) and instead assume that you are already logged into QuickBase.com. However, if you are trying to display the QuickBase.com on your own web page and you will be using a functional account for QuickBase.com instead of each user that comes to your website having a QuickBase.com login also you will need to get the authToken programmatically.  Read this discussion on how you might do this. The short answer is you COULD (but SHOULD NOT) pass the username and password via the url in the browser's address bar, because this is dangerous because even HTTPS does not hide urls stored in browser history. Thankfully, the url is enrypted from everyone except the browser and server computers. The url will be on the QuickBase.com log files, but they already have access to your data so it should not be an issue.

So, I suggest making a HTTP POST request ON THE SERVER-SIDE (not client-side such as JavaScript) and using SSL to protect the functional username and password of the account that will be accessing QuickBase.com. Please note, JavaScript is accessible to anyone that cares to read it, so it is not a good way to do the HTTP POST. I recommend doing this on the serverside and passing it to your page. Keep in mind the authToken (ticket) in in the url for the JavaScript so, end users could get the content just by going to going to the url and doing exactly what we are doing here. This not a huge issue since they already have access to the page you are displaying the data. To minimize how long someone can use the url, you may want to make the ticket expire after 1 hour. 

1 comment:

Teo T said...

Very interesting post! I work for a new social blogging site called glipho.com, and was just wondering if you would be interested in sharing your posts there with us? It wouldn't affect your blog at all, and I know there are many programmers and web developers within our community who would love to read through your work here. Let me know what you think!

All the best,

Teo