Android Access to Remote SQL Database

Android access to remote SQL database

This question has popped up several times. You PROBABLY can connect your android device to the SQL server directly if you deployed the MSSQL JDBC drivers to your android device and then exposed your SQL server directly to the internet. If the MSSQL drivers would work properly on Android is a completely different problem.

That is how you might be able to do it. However here is why that is a bad idea.

1. You are exposing your SQL server directly to the internet. Unless you encrypt the data between your MSSQL server and android device it would be relatively easy for a determined hacker to sniff the TDS data stream between the device and MSSQL and reverse engineer it and steal your data. Encryption will probably make it much harder almost impossible for a attacker to steal your data. However an attacker could still launch a DOS/DDOS attack on your database directly. Not a good idea!

2. If you are planning to connect other mobile devices (iPhone, Symbian, BlackBerry and so on) you will need to be able to create a SQL connection from those devices as well. iPhone does not support Java natively(from my memory) for example so you would need to find a way to connect iPhone to the SQL server. BlackBerry might be easier but Symbian you are going to be out of luck with. Thus you will need to almost create a custom solution for each device connecting to your database. Bad Idea LOADS of maintenance

Create a webservice or custom TCP/IP server which can manipulate your database. Connect to this webservice/service from your device. Webservices are the way to go. More than 90% of devices these days are natively capable of doing a webservice call.

Is it possible to remotely run queries to a database on a device through a web interface?

You will have to develop an API backend. The mobile app ( client ) will communicate with the API and do the desired operation based on the response.

It's not possible to directly connect to the app sqlite database. You can send web request and get the info you want, handle it in your app to store it in the sqlite database

You will have to add security measures, so everyone can't access your API.

Correct way of sending queries from Android to a remote server database

Your approach has many problems. Anyone can reverse-engineer your protocol and execute any query they want on your SQL server. Thus your data is not only readable by anyone, it is also modifiable by anyone. In other words, you will get hacked.

The usual way this is done is to split the cake into layers. This means defining an API, built of clear and well-specified methods, with input parameter types, return values, and permissions.

This API can be implemented in any way you like, jsonrpc, SOAP, xmlrpc, your choice, even HTTP GET to a php script returning json would work.

The last option is a bit clunky, but also nice, as it allows you the same api from the javascript running inside your website. No need to have two competing APIs.

An example:

API get_user_profile( user_id INT );

INPUT: integer id of the user

RETURNS: the line in table users for this user, dependent on their permissions.

Since the API executes inside an authenticated session (using cookies or whatever) it knows what user makes the request. Thus, it will let a user see their phone number/email, but it will not return these fields to other users, unless they're admin (that's a simple example of permissions, more complex is possible of course).

So, every operation needs its own API. Some are complicated, for example a generic search. Instead of writing your own mini-language and juggling with parameters to specify the search options, you can simplify things by making it act more or less like a website. The client sends whatever the user typed in the search fields to the server (like a HTTP form) and the server decides how to deal with it.

Obviously, if whatever parameters of your API are bluntly inserted into SQL queries, then SQL injection means you also get hacked. So you need to do it right, just like any website, stuff that is exposed to the evil internets constantly gets attacked.

Think of the client as a browser, the API calls as the URLs, forms, xmlhttprequest etc, and the server as PHP or whatever other server side language. That's basically what it is.

---

Related Topics