Building Secure Public API with PHP/MYSQL

Question 1

I'm currently building an API for a very busy internet website. Its being written in PHP with MySQL. Now this is my first API that i'm writing that allows people to access their account remotely. Once the API is online, developers will be able to write their own tools from it.

Now I have the API working, but I'm not sure if its entirely safe.

An example URL that would work is: http://domain.com/api.php?api_option=list&api_user_name=USERNAME&api_user_password=PASSWORD

USERNAME: would be the users actual username

PASSWORD: would be the MD5 encoded string of their actual password.

If the details match, a result is returned, if not, and error.

All external $_GET inputs get the mysql_real_escape_string() treatment.

I wanted to keep things simple, but I'm not sure if this way is a SAFE way of having a public API that taps directly into users accounts data.

Ideas and suggestions are much appreciated.

Question 2

How about signing requests using HMAC_SHA1 and the user's password? For example, your URL: http://domain.com/api.php?api_option=list&api_user_name=USERNAME&api_user_password=PASSWORD

Add the timestamp and/or a random string (nonce) and build a normalized base_string:

$base_string = "api_option=list&api_user_name=USERNAME&timestamp=1296875073&nonce=hgg65JHFj";
$signature = hmac_sha1($base_string, PASSWORD);

then the new URL would be: http://domain.com/api.php?api_option=list&api_user_name=USERNAME&timestamp=1296875073&nonce=hgg65JHFj&signature=kfvyhgfytgyr6576yfgu

What your server does is to get all the options, excluding the signature, then generate the signature using the same method and compare it to the signature sent by the client, which should be the same.

Question 3

The Twitter API offers a streaming API that is probably what you want to do to ensure you capture everything: http://dev.twitter.com/pages/streaming_api_methods

If I understand what you're looking for, you'll probably want a statuses/filter, using the track parameter with whatever distinguishing characteristics (hashtags, words, phrases, locations, users) you're looking for.

Many Twitter API libraries have this built in, but basically you keep an HTTP connection open and Twitter continuously sends you tweets as they happen. See the streaming API overview for details on this. If your library doesn't do it for you, you'll have to check for dropped connections and reconnect, check the error codes, etc - it's all in the overview. But adding them as they come in will allow you to completely eliminate duplicates in the first place (unless you only allow one entry per user - but that's client-side restrictions you'll deal with later).

As far as not hammering your DB, once you have Twitter just sending you stuff, you're in control on your end - you could easily have your client cache up the tweets as they come in, and then write them to the db at given time or count intervals - write whatever it has gathered every 5 minutes, or write once it has 100 tweets, or both (obviously these numbers are just placeholders). This is when you could check for existing usernames if you need to - writing a cached-up list would allow you the best chance to make things efficient however you want to.

Update: My solution above is probably the best way to do it if you want to get live results (which it seems like you do). But as is mentioned in another answer, it may well be possible to just use the Search API to gather entries after the contest is over, and not worry about storing them at all - you can specify pages when you ask for results (as outlined in the Search API link), but there are limits as to how many results you can fetch overall, which may cause you to miss some entries. What solution works best for your application is up to you.

Question 4

You want to use GROUP_CONCAT

Something like this (not 100% accurate probably :) )

$sql = "
    SELECT
         v.id, v.venue_name, v.active,
         GROUP_CONCAT(i.image_path) as venue_image_string
    FROM
         venues v
    LEFT JOIN 
         venue_images i ON i.image_venue_id = v.id
    WHERE
         v.active = 1
    GROUP BY i.image_venue_id
    LIMIT 0, 10
";

You may have to fiddle a little but should put you on the right track (note: provides venue_image_string as CSV)

Question 5

There are several schemes for authenticating API requests, and they're different than normal authentication provided by plugins like restful_authentication or acts_as_authenticated. Most importantly, clients will not be maintaining sessions, so there's no concept of a login.

HTTP Authentication

You can use basic HTTP authentication. For this, API clients will use a regular username and password and just put it in the URL like so:

http://myusername:mypass@www.someapp.com/

I believe that restful_authentication supports this out of the box, so you can ignore whether or not someone is using your app via the API or via a browser.

One downside here is that you're asking users to put their username and password in the clear in every request. By doing it over SSL, you can make this safe.

I don't think I've ever actually seen an API that uses this, though. It seems like a decently good idea to me, especially since it's supported out of the box by the current authentication schemes, so I don't know what the problem is.

API Key

Another easy way to enable API authentication is to use API keys. It's essentially a username for a remote service. When someone signs up to use your API, you give them an API key. This needs to be passed with each request.

One downside here is that if anyone gets someone else's API key, they can make requests as that user. I think that by making all your API requests use HTTPS (SSL), you can offset this risk somewhat.

Another downside is that users use the same authentication credentials (the API key) everywhere they go. If they want to revoke access to an API client their only option is to change their API key, which will disable all other clients as well. This can be mitigated by allowing users to generate multiple API keys.

API Key + Secret Key signing

Deprecated(sort of) - see OAuth below

Significantly more complex is signing the request with a secret key. This is what Amazon Web Services (S3, EC2, and such do). Essentially, you give the user 2 keys: their API key (ie. username) and their secret key (ie. password). The API key is transmitted with each request, but the secret key is not. Instead, it is used to sign each request, usually by adding another parameter.

IIRC, Amazon accomplishes this by taking all the parameters to the request, and ordering them by parameter name. Then, this string is hashed, using the user's secret key as the hash key. This new value is appended as a new parameter to the request prior to being sent. On Amazon's side, they do the same thing. They take all parameters (except the signature), order them, and hash using the secret key. If this matches the signature, they know the request is legitimate.

The downside here is complexity. Getting this scheme to work correctly is a pain, both for the API developer and the clients. Expect lots of support calls and angry emails from client developers who can't get things to work.

OAuth

To combat some of the complexity issues with key + secret signing, a standard has emerged called OAuth. At the core OAuth is a flavor of key + secret signing, but much of it is standardized and has been included into libraries for many languages.

In general, it's much easier on both the API producer and consumer to use OAuth rather than creating your own key/signature system.

OAuth also inherently segments access, providing different access credentials for each API consumer. This allows users to selectively revoke access without affecting their other consuming applications.

Specifically for Ruby, there is an OAuth gem that provides support out of the box for both producers and consumers of OAuth. I have used this gem to build an API and also to consume OAuth APIs and was very impressed. If you think your application needs OAuth (as opposed to the simpler API key scheme), then I can easily recommend using the OAuth gem.

Question 6

API Gateway will not charge you for unauthenticated requests, however you would be charged by Lambda for the invocation on the authorizer.

API Gateway offers a semi-useful mitigation to this problem in the form of the 'identity validation expression' on the Authorizer, which is just a regex that is matched against the incoming identity source header.

Besides that, you might want to just implement some kind of negative cache or validation yourself in the Authorizer function to minimize the billed milliseconds.

Building Secure Public API with PHP/MYSQL

Answers

php,mysql,api