-1

I want to know if I am right for retrieving datas from an SQL data base. I have an Android application which use the getCategories method for asking a PHP page on a web server. The web page retrieve datas from a SQL database and send them, with JSON format, to my Android application which display it.

I have made this architecture, a Java code which call a PHP page for retrieving data in a SQL data base. I want to know if I am righ or if there is a better way to retrieve my datas.

My java method for asking the web page :

    
   String webAddress = "www.mywebsite.com";

    public static void getCategories(final Context context,
                                     final String catForeignKeyCountry,
                                     final List<HashMap<String,String>> categories){

        class GetEmployee extends AsyncTask<Void,Void,String> {
            ProgressDialog loading;
            @Override
            protected void onPreExecute() {
                super.onPreExecute();
                loading = ProgressDialog.show(context,"Fetching...","Wait...",false,false);
            }

            @Override
            protected String doInBackground(Void... params) {
                RequestHandler requestHandler = new RequestHandler();
                String result = requestHandler.sendGetRequestParam(webAddress+"/getAllCat.php?cat_fk_country=", "1");
                return result;
            }

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

                try {
                    JSONObject jsonObject = new JSONObject(s);
                    JSONArray  jsonArrayResult  = jsonObject.getJSONArray(Config.TAG_JSON_ARRAY);

                    for(int i = 0; i < jsonArrayResult.length(); i++){
                        JSONObject jo = jsonArrayResult.getJSONObject(i);
                        String id   = jo.getString(Config.KEY_CAT_ID);
                        String name = jo.getString(Config.KEY_CAT_NAME);

                        HashMap<String,String> category = new HashMap<>();
                        category.put(Config.KEY_CAT_NAME,id);
                        category.put(Config.KEY_CAT_NAME,name);
                        categories.add(category);
                    }

                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }

        }
        GetEmployee getEmployee = new GetEmployee();
        getEmployee.execute();
    }

My PHP page for asking the database

    //Getting the key of the country
    $cat_fk_country = $_GET['cat_fk_country'];

    //Importing Database Script
    require_once('dbConnect.php');
    
    //Creating sql query
    $sql_parti = "SELECT * FROM gc_category 
            WHERE cat_fk_country=$cat_fk_country";
    
    //getting result
    $result_parti = mysqli_query($con,$sql_parti);
    
    //creating a blank array
    $result = array();
    
    //looping through all the records fetched
    while($row = mysqli_fetch_array($result_parti)){
        //Pushing name and id in the blank array created
        array_push($result,array("cat_id"=>$row['cat_id'],
                                 "cat_fk_country"=>$row['cat_fk_country'],
                                 "cat_number"=>$row['cat_number'],
                                 "cat_name"=>$row['cat_name']));
    }
    
    $empty = empty($result) ;
    
    if($empty == 1){
        $sql_parti = "SELECT * FROM gc_category
                WHERE cat_fk_country=12";
        
        //getting result
        $result_parti = mysqli_query($con,$sql_parti);
        
        //creating a blank array
        $result = array();
        
        //looping through all the records fetched
        while($row = mysqli_fetch_array($result_parti)){
            //Pushing name and id in the blank array created
            array_push($result,array("cat_id"=>$row['cat_id'],
                                     "cat_fk_country"=>$row['cat_fk_country'],
                                     "cat_number"=>$row['cat_number'],
                                     "cat_name"=>$row['cat_name']));
        }
        
        echo json_encode(array('result'=>$result));
    }else{
        echo json_encode(array('result'=>$result));
    }
    
    mysqli_close($con);
Fred37b
  • 822
  • 2
  • 10
  • 29
  • 2
    What you've done in PHP is called an API. It's the standard way of doing it. However, Your PHP is _wide open_ to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) by using prepared statements with bound parameters instead of injecting variables directly into your queries. It's not just about security; if your data contains, for example, a single quote `'`, your query will break. – M. Eriksson Aug 31 '23 at 08:43
  • Please define what you mean by "better". Something is only "better" if you have an objective measure of how it has improved a situation compared to another approach. Is there something in this code causing you a problem currently? That could then be a focus for any improvements. (Obviously there is the security issue mentioned above which I suspect you weren't aware of until now, but that can be fixed without altering the overall architecture.) – ADyson Aug 31 '23 at 09:06
  • 1
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Aug 31 '23 at 10:45
  • Thanks for yours answers, I thinked there was a a SQL Injection risk, I will correct that. I don't know if it is the right way to retrieve my SQL data. Shouldn't I retrieve my data from my Java instead of using PHP ? I wanted to use PHP in order to retrieve my data from an application or a website. – Fred37b Aug 31 '23 at 11:56
  • 1
    Most likely the Android application does not have direct access to the database on the server. That's why you use something like php to provide an API which other applications can use to interact with the data – ADyson Sep 01 '23 at 05:55
  • What other ways than PHP are there of retrieving my information? – Fred37b Sep 01 '23 at 08:50

0 Answers0