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.

Answer 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.

Answer 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 :)