Access database securely from iOS App

Question

I chose MySQL after looking between MySQL and SQLite for accessing because my iPhone app needs to pull information from an online database that is already in MySQL.

I believe the traditional way of accessing information would be: To have a php file on the server that does the accessing for you.

The iPhone app would call this php file and it would return the results.

iOS app will call http://somewebsite.com/index.php?id=234 and the website would print out the username of id=234.

Now, how secure is this process?... I would obviously use prepared statements and https. But what if someone found the URL for this website? How do I protect myself against misuse (someone could generate a list of all my users)? Is this the standard way to have your iPhone app connect and get info from a database?

---

Edit: Furthermore, lets say I needed to create an app login page... I have a MySQL database with username and password (hashed obviously). Would it be safe to use $_GET variables to see if they are authenticated. Like for example: https://somewebsite.com/checkauth.php?username=test&password=C3LyiJvTCQ14Q and have the php print out yes or no. Picture examples below:

I would assume the above method would not be safe to do... but I need to be enlightened.

Also, I'd prefer to stay away from calling the database within the app using third party API, not supported by Apple.

Solution 1

The best way to go about this would to setup an API to interact with the database on the server and your iPhone app just queries the API and returns the data in a machine readable format such as JSON, see http://en.wikipedia.org/wiki/JSON and http://json.org/. So for user login the server would return maybe something like:

{
    "result": false,
    "error": "Invalid username or password"
}

This would be generated by PHP with the following code:

echo json_encode(array(
    "result" => false, 
    "error" => "Invalid username or password"
));

Also note that, you should use HTTP response codes in conjunction with this, eg 401 for unauthorised.

JSON can use boolean and other data structures within its format. Nearly all major languages have support/libraries for it.

The benefits of this is that it allows you to build other applications using the same API such as an android version or an actual website.

This SO question is a good starting point on the security of mobile applications:

Creating an API for mobile applications - Authentication and Authorization

The main points are make sure to use HTTPS. When sending over user credentials you could return a user token (api key) that can be used for future requests and stored within the iPhone app for future access.

Eg: https://iphoneapp.com/notifications.json?key=98fy92473r92hAAIYEFG397qbqwiuUEAF

Your key should be sent in a HTTP header or in the POST so it is not recorded in logs etc...

Note: This is just a random string typed on the keyboard.

This method allows you to delete/regenerate the key if it gets compromised. You can also set rate limiting on the keys and various other parameters.

Another huge benefit is by building an API that your own app uses means that it will be maintained to a high standard and other third party companies can also use the API (if you allow them).

Edit: Furthermore, lets say I needed to create an app login page... I have a MySQL database with username and password (hashed obviously). Would it be safe to use $_GET variables to see if they are authenticated. Like for example: https://somewebsite.com/checkauth.php?username=test&password=C3LyiJvTCQ14Q

You should send that sensitive data using POST instead, but any service has to login at some point. Using HTTPS should help the most as it prevents eavesdropping. After the first authentication you can return the token and reap the benefits mentioned above.

As for the user login as along as your PHP conforms to good practices you should have no issues. See http://www.phptherightway.com/ it will help a lot if you have questions.

Definitely research OAuth and utilize that if you can/want to.

This is just a starting point and is NOT meant to be used word for word, further reading and googling is required.

Solution 2

If you're looking for an alternative to a "build an API from scratch" approach we've used a web based service called Kumulos available at kumulos.com for a quick and easy solution.

This service allows a developer to connect to a MySQL database and build the data model and APIs via a web page then deploy a native library to your platform. I believe you can also import an existing data model as well.

Once the data model is built on the web page you can then build APIs and specify input and output parameters. The APIs are modeled based on the type of SQL operation you are performing such as SELECT, UPDATE, INSERT, DELETE.

In your case you would want to model a login/authentication UI which accepts the username and (hashed) password, validates the data against the Users table and return the authentication results.

Once your APIs are modeled via the web page you can then "deploy" your configuration and generate native libraries for iOS, Android, PHP, and others.

The generated Obj C library gets dropped into your project and you make and respond to APIs using objective c calls and delegates.

Kumulos includes some other features as well like data export, API call metering, and what they call KScript. This is essentially the ability to wrap your call in javascript at the server (also configured via the web page) to greatly expand the flexibility and capability of the API call functionality you can build.

We've had a couple of questions or support issues over the past few months and their support has been top notch. Their backbone is on Rackspace. We've got about 8 or 10 production apps running APIs through them at the moment and are quite satisfied not having to hire an API developer :)

Solution 3

Many mobile applications use APIs to get and store information in servers. Figuring out some of these endpoints is not complicated, and having unsecured endpoints returning sensitive information is a dangerous thing to do.

The first level of protection of your API could be to create an "API key" that identifies the application. This key is stored it in the server and checked on every request. Request with no API key should return a HTTP 401 (Unauthorized) status code.

API keys are okay, but insufficient when some calls can only be performed by certain users. For example a user needs to update his information, only the owner of the information should be able to perform this call, and not another user. For this you can pass authentication information that identifies the user to perform the update action.

I do not recommend using username/password on every request, instead have the user authenticate once, and let the server send back authentication tokens that can be used by the application to perform future authenticated calls. Take a look at OAuth2 as a potential Authorization Framework. Also check out OAuth 2.0 - The Good, the Bad & the Ugly.

I suggest using BShaffer OAuth2 Server in PHP. Also see Best Practices for securing a REST API / web service for alternatives.

From your question it sounds like there is an existing subsystem, I recommend creating a simple interface that makes the subsystem easier to use, and reusable across multiple clients instead of modifying the subsystem to accommodate an API. This is commonly known as a Facade Design Pattern.

Many PHP Frameworks have packages to implement custom RESTlike APIs. Symfony has FOSRestBundle, FuelPHP has a REST controller out of the box and CodeIgniter has a REST server.

To summarize:

1. Create a simple interface to access information from the existing system (a REST API).
2. Protect your private information using a proper authentication mechanism (maybe OAuth2).
3. Use existing libraries and/or frameworks to speedup development.
4. Your code will be reusable across multiple applications and platforms as a result!

Comments

[Tom]

Just out of interest, have you considered using a 3rd party OAuth supplier to do your authentication? E.g.: stackoverflow.com/questions/10296833/…

[CouchDeveloper]

Why don't you use just one of the many great web application frameworks? Additionally, there exist beginner level tutorials for virtually any web application framework which of curse also cover security and possibly also have a login example.

[Arian Faurtosh]

I understand that, but how safe is it to use a middleware solution... for example, I would be using PHP as my middleware.

[Wayne]

"You should send that sensitive data using POST instead, but any service has to login at some point. Using HTTPS should help the most as it prevents eavesdropping." Only the second sentence has any meaning here. POST is not more secure than GET.

[SamV]

@lwburk I didn't mention that using one HTTP method had any benefit. What if some error occurs and shows the URL? The GET parameters can be seen more easily by the user ~ when there is no need. I understand your point though.

[Wayne]

Using POST for security is like writing a secret on a postcard and then arguing that the secret is safer if the card is laying on the table secret-side down. It doesn't matter. Anybody can flip over the card.

[SamV]

@lwburk That's not what I'm saying, I am talking about the general user not someone who is actively seeking this information out. I am not implying in anyway that different HTTP methods increase security. Because they don't.