CartoSQLService in Carto Mobile SDK

Question

I am working on a data collection application for Android and I am able to use CartoSQLService to read information from my table in Carto, however I am having difficultly adding data. I have searched online for any guidance with no luck.

Does anyone know the proper procedure to add data to a Carto table using the Carto Mobile SDK? Is the API Key suppose to be included? If so, how is this done since the Carto documentation does not show how this is accomplished for Android applications?

Here is what I have so far:

final CartoSQLService service = new CartoSQLService();
service.setAPITemplate("https://{username}.carto.com");

Thread thread = new Thread(new Runnable() {  
@Override
     public void run() {

          try {
            service.queryData("INSERT INTO {table_name} (lat,long,sod) VALUES (" + locArray[0] + "," + locArray[1] + "," + checkBox.isChecked() + ")");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }}); 
thread.start();

Error:

E/carto-mobile-sdk: HTTPClient::makeRequest: Bad status code: 401, URL:

{
    "error": [
        "permission denied for relation acorn_table"
    ]
}

Answer 1

From the end-user side you can only use SELECT queries against private or "shared with key" tables. Other requests to SQL API require using your secret API key.

The problem is that end-user side - web browser or mobile app - is not under your control, so you may easily accidentally expose your API key. API key is a bit too powerful thing - bad users can use this in SQL API to delete all your data, misuse your quota etc. Therefore we have not enabled API key as option to mobile SDK at all, to avoid potential leakage of the key.

I can see two options to resolve it in secure way:

a) you can set up own gateway server (middleware), which communicates with mobile with whatever protocol you feel secure and convenient, and talks SQL API using your API key. As the API key does not move outside servers, then this is much secure. In fact, it is as secure as you create you middleware API. You need to build communication between mobile app and your gateway yourself, to communicate with SQL API you can use a CARTO client, e.g. for python this: https://github.com/CartoDB/carto-python

b) you can define own plsql stored function in your CARTO account using SQL API and API Key, e.g. function saveData(data). Then you call this function in SQL API using SELECT, without API Key. This way you use only SELECT which is allowed for the API, and also control what actions exactly can be done with INSERT - check inputs etc, as any hacker can misuse this API call. Here is a blog post describing this approach for website, as in principle for mobile the security issue and solution is very similar: https://carto.com/blog/read-and-write-to-cartodb-with-the-leaflet-draw-plugin . Writing and troubleshooting a SQL function can be quite painful compared to separate middleware, but this option does not require own server at all.