正确的将查询从Android发送到远程服务器数据库的方法

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

我有一个Android应用程序，应该发送查询并从远程数据库服务器（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.

我通过实际以JSON的形式将查询发送到服务器来完成此操作。然后，服务器脚本（PHP）将获取查询并执行它。然后将结果集再次解析为JSON并发送回Android应用程序。

My PHP script

我的PHP脚本

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.

如您所见，'query'键包含MySQL服务器执行的整个查询字符串。

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.

我的一个项目伙伴建议我应该将查询分成不同的部分（Like-“query_type”：“SELECT”，“table_name”：“LOGIN_TABLE”，“where_args”：“WHERE x = x”，依此类推）并将其发送到服务器，然后在那里重建查询并执行。

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

但是，我不明白这会有什么帮助。任何建议将不胜感激。先谢谢你。

4 个解决方案

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

你的方法有很多问题。任何人都可以对您的协议进行反向工程并在SQL服务器上执行他们想要的任何查询。因此，您的数据不仅可以被任何人读取，而且任何人都可以修改。换句话说，你会被黑客入侵。

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.

通常的做法是将蛋糕分成几层。这意味着使用输入参数类型，返回值和权限定义一个由明确且指定良好的方法构建的API。

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.

这个API可以用你喜欢的任何方式实现，jsonrpc，SOAP，xmlrpc，你的选择，甚至HTTP GET到PHP脚本返回json都可以。

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.

最后一个选项有点笨重，但也很好，因为它允许你从你的网站内运行的javascript相同的api。无需拥有两个竞争API。

An example:

一个例子：

API get_user_profile( user_id INT );

API get_user_profile（user_id INT）;

INPUT: integer id of the user

INPUT：用户的整数id

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

RETURNS：此用户的表用户中的行，具体取决于他们的权限。

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

由于API在经过身份验证的会话中执行（使用cookie或其他），因此它知道用户发出请求的内容。因此，它会让用户看到他们的电话号码/电子邮件，但它不会将这些字段返回给其他用户，除非他们是管理员（这是一个简单的权限示例，当然可能更复杂）。

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.

因此，每个操作都需要自己的API。有些很复杂，例如通用搜索。您可以通过使其行为或多或少像网站一样简化，而不是编写自己的迷你语言并使用参数来指定搜索选项。客户端将用户在搜索字段中键入的内容发送到服务器（如HTTP表单），服务器决定如何处理它。

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.

显然，如果您的API的任何参数直接插入到SQL查询中，那么SQL注入意味着您也会被黑客入侵。因此，您需要做到正确，就像任何网站一样，暴露于邪恶互联网的东西会不断受到攻击。

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.

可以将客户端视为浏览器，将API称为URL，表单，xmlhttprequest等，将服务器称为PHP或其他任何服务器端语言。这基本上就是它。

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

Rest api是android到远程服务器通信的最佳方法。 HttpClient，volley和Fast android网络库是一些简化其余api通信的库。 HttpClient太慢，因此不建议使用HttpClient。建议使用快速的Android网络库，因为它简单快速。

Usage of Fast Android Network Library

快速Android网络库的使用

Tutorial of fast android network library

快速android网络库教程

#3

The best way to communicate between client side application and a remote database is by using RESTful API with it's famous HTTP requests GET, PUT, POST and DELETE. When you have a REST API you can have a multiple client side apps using the same database, like Android, IOS or JAVASCRIPT. And it will be secured by an API_KEY so only requests that are authorized to do the queries or modifications will be accepted.
There are many ways to create an REST API since you are a PHP developer I would recommend Slim PHP framework since it's lightweight and easy to use.

在客户端应用程序和远程数据库之间进行通信的最佳方式是使用RESTful API及其着名的HTTP请求GET，PUT，POST和DELETE。当您拥有REST API时，您可以使用相同的数据库（如Android，IOS或JAVASCRIPT）拥有多个客户端应用程序。它将由API_KEY保护，因此只有被授权进行查询或修改的请求才会被接受。有许多方法可以创建REST API，因为您是PHP开发人员我会推荐Slim PHP框架，因为它轻巧且易于使用。

#4

suggestions 1 : Add mySql user with certain permissions. or add permissions to sensitive tables like tables that have other user's information ...

建议1：添加具有特定权限的mySql用户。或者向敏感表添加权限，例如具有其他用户信息的表...

suggestions 2 : Create an access key for every devices, refresh it in every request and give new access key as responds, if access key invalid ask the device to login in again. if login is successful, give that device new access key.

建议2：为每个设备创建一个访问密钥，在每个请求中刷新它，并在响应时给出新的访问密钥，如果访问密钥无效则要求设备再次登录。如果登录成功，请为该设备提供新的访问密钥。

• keep a crone to clear inactive devices in time interval wises
• 保持一个老太太以时间间隔wises清除非活动设备
• keep access key in SharedPreference in android devices
• 在Android设备的SharedPreference中保持访问密钥

please check YQL they had same working idea

请检查YQL他们有相同的工作想法

TRY THIS CLASS instead of volley

尝试这个课程而不是凌空

public class SyncToServer extends AsyncTask<String,String,String> {
Context context;
String url;
JSONObject jsonObjectToSend;
JSONArray jsonArrayToSend;
OnWebServiceListener onWebServiceListener;
ProgressDialog progressDialog;

/**
 * basic initializing, only use this contractor if you don't have anything to send.
 * @param context it can be null, if you don't want {@link ProgressDialog} pass it as null
 * @param url the url for data to be passed
 */
public SyncToServer(Context context,String url){
    this.context=context;
    this.url=url+"";
    Log.e("SyncToServer123",url+"");
    if(context!=null) {
        progressDialog = new ProgressDialog(context);
        progressDialog.setMessage("loading");
        progressDialog.setCancelable(false);
    }
        jsonObjectToSend=null;
}

/**
 * basic initializing, only use this contractor if you had anything to send.
 * @param context it can be null, if you don't want {@link ProgressDialog} pass it as null
 * @param url the url for data to be passed
 * @param jsonObject the {@link JSONObject} to pass server
 */
public SyncToServer(Context context,String url,JSONObject jsonObject){
    this.context=context;
    this.url=url;
    this.jsonObjectToSend=jsonObject;
    if(context!=null) {
        progressDialog = new ProgressDialog(context);
        progressDialog.setMessage("loading");
        progressDialog.setCancelable(false);
    }
}
/**
 * basic initializing, only use this contractor if you had anything to send.
 * @param context it can be null, if you don't want {@link ProgressDialog} pass it as null
 * @param url the url for data to be passed
 * @param jsonObject the {@link JSONArray} to pass server
 */
public SyncToServer(Context context,String url,JSONArray jsonObject){
    this.context=context;
    this.url=url;
    this.jsonArrayToSend=jsonObject;
    if(context!=null) {
        progressDialog = new ProgressDialog(context);
        progressDialog.setMessage("loading");
        progressDialog.setCancelable(false);
    }
}

/**
 * this method will register a callback, and start web service
 * @param onWebServiceListener this interface used to callback mechanism
 */
public void start(OnWebServiceListener onWebServiceListener){
    Log.i("SyncToServer123","start");
    this.onWebServiceListener=onWebServiceListener;
    this.execute();
}

@Override
protected void onPreExecute() {
    super.onPreExecute();
    Log.i("SyncToServer123","init onPre");
    if(context!=null) {
        progressDialog.show();
    }
}

@Override
protected String doInBackground(String... strings) {
    HttpClient client = new DefaultHttpClient();
    Log.i("SyncToServer123","doIn");
            try {
                InputStream is;
                HttpPost post = new HttpPost(url);
                HttpResponse httpResponse;
                if (jsonObjectToSend != null) {
                    Log.i("SyncToServer123", jsonObjectToSend.toString());
                    StringEntity se = new StringEntity(jsonObjectToSend.toString());
                    Log.i("SyncToServer123", jsonObjectToSend.toString());
                    se.setContentType(new BasicHeader(HTTP.CONTENT_TYPE, "application/json"));
                    post.setEntity(se);
                    post.setHeader("Accept", "application/json");
                    post.setHeader("Content-type", "application/json");
                    post.setHeader("connection", "close");
                    httpResponse = client.execute(post);
                }else if(jsonArrayToSend!=null){
                    Log.i("SyncToServer123", jsonArrayToSend.toString());
                    StringEntity se = new StringEntity(jsonArrayToSend.toString());
                    Log.i("SyncToServer123", jsonArrayToSend.toString());
                    se.setContentType(new BasicHeader(HTTP.CONTENT_TYPE, "application/json"));
                    post.setEntity(se);
                    post.setHeader("Accept", "application/json");
                    post.setHeader("Content-type", "application/json");
                    post.setHeader("connection", "close");
                    httpResponse = client.execute(post);
                }else{
                    HttpGet httpGet=new HttpGet(url);
                    httpGet.setHeader("connection", "close");
                    httpResponse = client.execute(httpGet);
                }


                HttpEntity httpEntity = httpResponse.getEntity();
                is = httpEntity.getContent();
                try {
                    BufferedReader reader = new BufferedReader(new InputStreamReader(
                            is, "iso-8859-1"), 8);
                    StringBuilder sb = new StringBuilder();
                    String line = null;
                    while ((line = reader.readLine()) != null) {
                        sb.append(line + "");
                        System.out.println(line);
                        Log.i("SyncToServer123","PRE RESPONDS : "+line);
                    }
                    is.close();
                    String json = sb.toString();
                    JSONObject jObj;
                    jObj = new JSONObject(json);
                    Log.i("resultJson",json);

                    Log.i("SyncToServer123","RESPONDS : "+json);
                    reader.close();
                    httpEntity.consumeContent();

                    if(client != null && client.getConnectionManager() != null)
                    {
                        client.getConnectionManager().shutdown();
                    }

                    return json;
                } catch (Exception e) {
                    Log.i("SyncToServer123","Error "+e);
                    Log.e("Buffer Error", "Error converting result " + e.toString());
                }
                //customDbHelper.notifiSyncCompleted();
            } catch (Exception e) {
                Log.i("SyncToServer123","Error "+e);
                e.printStackTrace();
            }

    return null;
}

@Override
protected void onPostExecute(String s) {
    super.onPostExecute(s);

    System.out.print("SyncToServer123:   "+s);

    if(s!=null){
        this.onWebServiceListener.onComplete(s);
    }else{
        this.onWebServiceListener.onError();
    }
    if(context!=null) {
        progressDialog.dismiss();
    }
}

/**
 * for callbacks the web service status,
 */
public interface OnWebServiceListener{
    /**
     * when service completed without any exception, including {@link JSONException}
     * @param result the responds given from server
     */
    void onComplete(String result);

    /**
     * if any exception occurred this method will call
     */
    void onError();
}
}

USAGE

用法

SyncToServer syncToServer=new SyncToServer(null,"url",jsonObject);
    syncToServer.start(new SyncToServer.OnWebServiceListener() {
        @Override
        public void onComplete(String result) {

        }

        @Override
        public void onError() {

        }
    });

Library used

使用的库

        useLibrary 'org.apache.http.legacy'