Hacking Google Forms

A few months back I pitched the idea of using Google forms for all the forms on the new website. Our current forms were created through a Joomla-specific add-on, and I'm not proficient enough in PHP or SQL to feel comfortable recreating them from scratch. Also, the forms on our current site turned into a pretty huge security risk as they aged, and I like the thought of using Google's servers to house the forms and resulting data. However, on their own, Google forms are pretty limited in their functionality, just dumping data into a Google spreadsheet document (which can be exported, but you still have to regularly log into your Google account to view the data.)

Forms do have the option of sending an email alert whenever someone fills out the form, but the alerting email doesn't include the actual form data, so you're still tied to constantly logging into that account to get the information. Enter: Google Apps Script. With a little knowledge of JavaScript, you can use their library of classes and methods to add functionality to basic Google forms.

I started out just wanting to be able to receive an email when someone submitted a form, with all the responses included. For this I found a really nice tutorial from Amit Agarwal (http://www.labnol.org/internet/google-docs-email-form/20884/). If that's all you need your form to do, great! You got it, dude.

I needed a few extras though. First, some of the forms need to go to multiple people. You can easily do this with a slightly more advanced version of the sendEmail method. (You can find documentation on the MailApp class and various iterations of the sendEmail method here: https://developers.google.com/apps-script/reference/mail/mail-app. Also helpful, their Understanding Events cheat sheet: https://developers.google.com/apps-script/understanding_events)

Essentially, you just need to find this line in the original code:

MailApp.sendEmail(email, subject, message);

and change it to:

MailApp.sendEmail(email, subject, message, {cc: email 
   of person you want to copy});

You can cc multiple people by just separating their email addresses with commas.

One of the librarians, however, wanted users to indicate what department they were affiliated with, and then have a copy of the form results go to the department liaison. This is where things start to get a little complicated, and it's helpful to know a little bit about programming languages. I wrote a simple switch statement (with some help from Babs, of course, my go-to programming guru.)

 var dept = e.values[array location 
    of dropdown].toString();
 var contact = toString("xx");

 switch (dept) {
   case "dropdown value 1": 
     contact = "email address 1";
     break;
   case "dropdown value 2": 
     contact = "email address 2";
     break;
   default:
     contact = "default email address";
 }

The first line of code pulls whatever drop-down value the user selected (the associated Google spreadsheet stores these values as an array. 'e.values' accesses the values in this array. Position [0] of the array is the time-date stamp that gets put in automatically, so your array location is just the exact question number of the drop-down question.)

Your switch statement is then just comparing that value to values that you associate with email addresses, and then assigning the associated email address to the variable "contact", so now your method call looks like this:

MailApp.sendEmail(email, subject, message, {cc: contact});

Ok, if I haven't given you a headache yet, there's one more tweak you can do to increase the usability of the form submission email. Using 'e.values' again, you can pull the user's email address from their form submission, and set it as the reply-to on the resulting email. That way, if the person who gets the email has a question for the submitter, they can just hit reply (default reply-to is the gmail account that you're using to create the form.)

Again, since the time-date stamp is [0], you just need the question number where you ask for the user's email address, and now you've got:

var reply = e.values[array location of user’s 
   email].toString();

MailApp.sendEmail(email, subject, message, {cc: 
   contact, replyTo: reply});

You can check out the whole script, as I use it, here.

If you've done any Google form hacking, I'd love to hear about it in the comments. I've only just begun delving into the possibilities here!

5/23/14 - I just stumbled across this post about using Google Forms for leaderboards/summer reading programs. It's also another good example of hacking Google Forms with formulas and scripts.

anatomy of a crash, part deux

So say your site gets hacked, and you try fixing the index and config files, as I mentioned in the last post. And you try checking the server logs to see what files were messed with so you can replace them with backups. And you turn on error-reporting in your CMS to try to see what's going wrong. And you Google some of the malicious code you found in your files. And say none of these fixes work, or yield any useful information. What now?

Well next you'll want to search for common hacks to your specific CMS and version, to see if anyone can walk you through fixing them. Here's a pro-tip though: in the end, most fixes will just tell you to install a fresh version of the software, and if you're in my situation that's not an option, so learn from our fail. Set up your website in such a way that disaster recovery is a relatively easy job, or at LEAST a viable option.

I will admit that much of this advice is based on my experience with Joomla and Wordpress. I have much less experience with Drupal, so some of it may not apply there. If you're a Drupal person, and have advice for keeping your site safe from hackers, please post it in the comments!

1. Keep your site software up to date.
   Also your plug-ins. Also your themes. Because chances are, if they're all from reputable sources, the developers will be addressing vulnerabilities as they pop up. The world of hacking is a shifting landscape, and what's secure today is not necessarily secure tomorrow.
 

2. Keep your customization modular.
   In WordPress, this means using a child theme, rather than making changes to the main theme. When you update a theme, it will override any changes you made to those files. Now you're in a situation where you have stop updating your theme, and are thus breaking RULE NUMBER ONE. You will regret this.
 

3. Keep your site root clean.
   Actually, not just the site root, but all its sub-directories. Part of the problem with our site is that the root folder is cluttered up with custom includes, images, project folders, etc. If you're not the one who put them there (as in my case, where I'm taking over a site from someone who is no longer here) it's hard to know what folders are part of the CMS's software, and which ones are not.
   
   In general, if you re-install the software, it should just ignore these unrelated files and folders, but if the software contains new files and folders that have the same name as yours, you can accidentally overwrite your files. I'd say either place these files one level up, OR, if you want them to have the site root's url, create one folder in the site root, and put all of it in there. Clearly mark that that folder is NOT part of the CMS's file structure.
 

4. Documentation!!!
   Srsly. Updating or re-installing your CMS may not be a difficult process, but YOU may not be around when it needs to be done. YOU may be on another continent, or at another job. YOU may have gotten hit on the head or killed those particular brain cells with alcohol. There are so many pieces to a CMS (plugins, templates, images, forms, database(s), etc,) it's supremely helpful to know which of these need to be backed up in like six places before you re-install, so you don't lose the hours and hours of work you put into customizing them. Which leads to...
 

5. Keep backups of important files and folders.
   Yes, I know you're backing up your entire site on a regular basis, because to not do so would be INSANE, but even so, keep an extra copy of important stuff, JUST. IN. CASE. I have a folder on my desktop with my config file, my entire child theme folder, and my custom plugin folders. WordPress is smart, and names the blank config file something else, so when you update, that default config file doesn't overwrite yours, but still. (Remember to update these backups every time you make a change. I got in that habit anyway, because I keep an entirely local copy of the site to make changes to before making them live, so it's kind of a reflex at this point that when I make a change in one place, I update those files everywhere else.)
 

6. Minimize the use of 3rd party modules or vulnerable code.
   Wherever possible on our WordPress site, I used CSS/jQuery to create my own custom features (like our tabbed search box) rather than install another plugins. Plugins can increase the vulnerability of your site, so use them with caution (and, not to drill it into your head or anything, but keep them updated!) We've also made the switch to Google forms for all our forms, so we have the benefit of their security features (and so the forms are connected to off-site databases, rather than databases on our servers.)
 

7. Create a simple html backup site ready to go at all times.
   Honestly, I never even thought of this until the head of Media Services suggested it. Libraries subscribe to many services that are hosted off-site (such as the catalog, research guides, databases, resource managers, and discovery services.) These services are the core of our business, and are still available even when your site is down. Create a simple site that links to whatever services and resources are still available, as well as basic information like hours and contact info. I just downloaded a free CSS template and created a quick and dirty 2 page website that can be put up during downtime (unless the entire server is down. Then I guess you have to put them elsewhere and do a redirect? ACK! SERVER STUFF FRIGHTENS AND CONFUSES ME.)

So ok, there you have it. I am by no means an expert on the topic of hacking, or disaster recovery, or even web development for that matter. This is just an attempt to learn from my own experiences, and to put what I learned out there, just in case it can help someone else in a similar situation. If anyone else has some advice on these matters, please share in the comments!

the anatomy of a crash, part 1

In accordance with Finagle's corollary to Murphy's Law, the website broke the day our sysadmin went on a 2 week vacation on another continent. What's most surprising about this is how little it surprised me. First, what happened:

Our library website is using a very old installation of the Joomla content management system (1.5.7 I believe.) Our implementation, for whatever reason, is insecure. I know very little about Joomla or server security, other than to nod sagely and say, "could be an SQL injection attack" (much in the same way dudes will surround an open car hood, though they know nothing about fixing cars, and say, "it's probably the transmission.")

So last Friday, sometime around 11am, our website stopped being an actual website, and started being just a page that displayed the site title. Not so useful for users, I'd imagine. My first instinct is to look at the main index.php page to see if it's been replaced with a different one. I've dealt with this hack in the past, just the result of some asshole saying "LOOK WHAT I CAN DO!" You just delete the new index file they put in, and put yours back in.

When I checked our index file, it was present, not renamed, and all the content was accounted for. At the end of the file there was a php command that was trying to redirect the site to some .biz.tr website, so I took out that code and figured the problem was fixed. Nope, site was still b0rked. I went into all the sub-folders' index files, and found some malicious code in them too, so I decided to just replace all of them with clean backup versions. Still. B0rked. On to the config file. Everything looks fine there, but I replace it with a back up version anyway.

Also it was about this time I sent out an email to the staff that basically said YES I KNOW THE SITE IS DOWN YOU CAN ALL STOP CALLING ME ABOUT IT.

At this point I'm stumped, so I call the head of Media Services, who maintains the servers. He goes in to check which files were accessed at 11am that day. None. Uh, ok. He has me go into the database, to see if the content looks ok, and it does. It occurs to me that I'm able to get into the site from the admin panel, which is a subfolder in the site root, so it's not that the whole site directory is corrupt. Subpages of the actual site, however, are not loading.

We finally realize that this is not going to be an easy fix, so I put up a temporary webpage linking to common services, most of which are on different servers, so they're fine (catalog, database list, LibGuides, and Google forms.)

The head of Media Services then spent his weekend picking through all the myriad of folders on the server to find workable backups of pretty much all the pieces of the site (which, in a content management system, are many.) He then pieced the site back together, file by file. I honestly don't know the details of how he made this happen, because whenever I asked him about it, he sounded like he was going to cry or murder a baby panda, so I'm just gonna let that go. He obviously has some sort of PTSD, and I don't want to poke the painful memories of "the incident." He did mention something about finding out that the site was actually hacked in June, and was only taken down just now by a remotely-issued command that activated the previously-inserted code. Insidious bastards.

I did a Google search for the spam url I found in the main index page, and it's been injected into tons of insecure Joomla installs. I only mention this because people keep asking what kind of douchebag hacker makes it his life work to take down crappy college library websites. It was just a bot that looked for vulnerable targets. Nothing personal, my friends.

The good news is that I learned many lessons from this whole debacle, and have much to share with you along the lines of "how to make sure this doesn't happen to you because it's not fun." I'm going to put that in another post though, because I need to go pour myself a giant tumbler of whiskey right now. Stay tuned...