A client recently asked me to integrate their site with the JangoMail mass mailing system. I wanted to keep them happy so agreed to investigate, but was horrified by what I saw in the JangoMail API documentation.
JangoMail appears to be optimised for those with existing databases of email addresses they want to maintain and contact. For those wanting to keep those databases in sync they offer a script you can download and install on your server that they can call with details of various actions (user unsubscribed, user clicked link, job completed, etc) as well as to extract the list of email addresses they should send a given campaign to. So far, so good.
The problem is in the implementation. Once you have downloaded and installed the script on your server, they ask you for your database credentials and then send these, along with an SQL query, over HTTP (or HTTPS) for the script to execute. In the FAQ attached to their blog entry on the topic the obvious question “Is this method of connecting to my data secure?” is asked, with the response:
Yes. It is inherently secure if you opt to have JangoMail connect over https instead of http. It can be additionally secured by restricting the range of IP addresses allowed to connect to the custom script file. JangoMail’s range of IP addresses are: 209.173.141.193 - 209.173.141.255"
That answer is far from satisfactory. I refuse to give a third party my database credentials, still less to to execute arbitrary SQL received over an HTTP request, even if that is SSL and includes a password.
Surely if they want to keep the steps for the user to a minimum they could still provide an interface that takes credentials (via SSL)–along with appropriate other details like “enforce SSL?”–and generates a PHP script that contains those credentials embedded within it, along with code to generate the SQL from a set of parameters? It’s not a hard thing to do (witness the way wordpress/drupal/etc will generate a config file for you – it’s the same thing). That way JangoMail can take responsibility for making sure that the credentials are only ever sent a small number of times (and via SSL), and the endpoint can contain appropriate validation.
In this case I decided to take matters into my own hands and write a sane script to receive their input. There’s nothing forcing you to put genuine database credentials into their form, so instead I used those fields to provide a username and password I’d use to authenticate their request. In each of the boxes to enter the SQL for your queries I entered some code to generate JSON containing the relevant details.
With that done it’s a relatively trivial matter to parse the JSON, do any validation you may want to do and update your database accordingly. A rough-and-ready (and barely tested) script that does just that can be found in this gist. A perfectly satisfactory (if slightly laborious) solution for any competent web developer.
But of course JangoMail’s target market isn’t competent web developers. They’re clearly trying to target a general audience, and for that audience their lackadaisical approach to security is indefensible.