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

Question

I am not very experienced in handling databases.

I have an Android Application that is supposed to send queries to and get the subsequent result set from a remote database sever (MySQL).

I have done this by actually sending the query in the form of a JSON to the server. The server script (PHP) then fetches the query and executes it. The result set is then parsed into a JSON again and sent back to the Android app.

My PHP script

function run_query(mysqli $con, $query){
    $res=$con->query($query);
    return $res;
}

$reply = array();

if(isset($_POST['json'])) {
    require_once __DIR__.'/config.php';
    require_once __DIR__.'/DbController.php';
    $json = json_decode($_POST['json'], true);
    $query = $json['query'];
    $con = (new DbController())->connect(DBNAME);
    if(!$con){
        $reply['suc']=false;
        $reply['err_msg']=$con->error;
    }
    else{
        $res = run_query($con, $query);
        if(gettype($res)=="boolean"){
            $reply['query_reply']=$res;
            $reply['suc']=true;
            die(json_encode($reply));
        }
        $i=0;
        $reply['query_reply']= array();
        while($row = $res->fetch_row()){
            $reply['query_reply'][$i] = array();
            for($j=0;$j<sizeof($row);$j++)
                $reply['query_reply'][$i][$j]=$row[$j];
            $i++;
        }
        $reply['suc']=true;
    }
    echo json_encode($reply);
}

As you can see, the 'query' key contains the entire query string that is executed by the MySQL server.

My question is- does this way contain any security (or other) loopholes that I am not aware of? Also, is there a better way to do this?

One of my project-mates suggest that I should chop the query into distinct sections (Like- "query_type" : "SELECT", "table_name" : "LOGIN_TABLE", "where_args": "WHERE x = x", and so on) and send it to the server and thereafter reconstruct the query there and execute.

However, I do not get how this would help. Any suggestions would be greatly appreciated. Thank you in advance.

Answer 1

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.

Answer 2

Rest api are the best method for android to remote server communication. HttpClient,volley and Fast android network library are some libraries which simplify the rest api communication. HttpClient is too slow so usage of HttpClient is not recomended. Fast Android network library is the recommended because it is simple and fast.

Usage of Fast Android Network Library

Tutorial of fast android network library