API function application/json

Dick Honing

I think I should rephrase the question as follows; how do I get the json data into my table? I've already played around with the sample function 'Insert Rows into your Database', but as I'm fairly new to this language, I most likely have not got the syntax correct. Does anybody know what I might be doing wrong in trying to get the json data into my table?

var cloudbackend = require('appdrag-cloudbackend'); 
var appID = process.env.APPID; 
var APIKey = process.env.APIKEY; 
cloudbackend.init(APIKey, appID);
    
exports.handler = (event, context, callback) => {

    var tableName = "trips"; 
    var columns = "id, idfm, name";
    // var values = "'101', '1001', 'test'"; test from example with hardcoded data
    // var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")"; 
    
    callback(null, JSON.stringify(event))
    var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + id + "," + idfm + "," + name + ")"; 

    cloudbackend.sqlExecuteRawQuery(query)
    .then(function(response) {
        callback(null, response);
    });

};

Wassim

@Dick-Honing said in API function application/json:

ink I should rephrase the question as follows; how do I get the json data into my table? I've already played around with the sample function 'Insert Rows into your Database', but as I'm fairly new to this language, I most likely have not got the syntax correct. Does anybody know what I might be doing wrong in trying to get the json data into my table?

Hi,

Can you return after your

callback(null, JSON.stringify(event));

and then make a screenshot of your Postman?

Show us also the body parameters in your Postman

Dick Honing

Hi Wassim, I added the ; and remove the id, as this field gets auto created in the table anyway. Postman is giving me this as a result

{  "status": "KO", "error": "Command not found"}

When I test the API function, I get the following result:

{
    "status": "OK",
    "execTime": 468,
    "billedTime": 1000,
    "payload": {
        "POST": {
            "name": "test",
            "idfm": "1001",
            "APPDRAG_FUNCTION_NAME": "addTrip2",
            "APPDRAG_FUNCTION_FOLDER": "",
            "APPDRAG_FUNCTION_COMMAND": "CloudAPIExecuteFunction",
            "APPDRAG_FUNCTION_APPID": "ecxs-225c83"
        },
        "GET": {},
        "FILES": [],
        "HEADERS": {
            "ip": "89.220.243.40",
            "from-url": "http://api.appdrag.com/CloudBackend.aspx",
            "Content-Length": "159",
            "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
            "Accept": "*/*",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7",
            "Host": "api.appdrag.com",
            "Referer": "https://prod.appdrag.com/cloudbackend.html?appId=ecxs-225c83",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36",
            "X-Forwarded-For": "89.220.243.40",
            "X-Forwarded-Proto": "https",
            "X-Forwarded-Port": "443",
            "X-Amzn-Trace-Id": "Root=1-5f081c53-e1be4c44f2a423cd0fa23048",
            "origin": "https://prod.appdrag.com",
            "sec-fetch-site": "same-site",
            "sec-fetch-mode": "cors",
            "sec-fetch-dest": "empty"
        },
        "body": "name=test&idfm=1001&APPDRAG_FUNCTION_NAME=addTrip2&APPDRAG_FUNCTION_FOLDER=&APPDRAG_FUNCTION_COMMAND=CloudAPIExecuteFunction&APPDRAG_FUNCTION_APPID=ecxs-225c83"
    },
    "logs": ""
}

... but no record gets created.

Wassim

So based on what you have on the payload you can access your parameters by doing that:

var name = event["POST"]["name"];

or this way

var idfm = event.POST.idfm;

these are the values you can use in your query.

I also see that you try to manually insert the id but it is automatically generated, so only insert the name and idfm column values.

Dick Honing

@wassim sorry, but there's probably still something wrong in my code as there are still no records being created

var cloudbackend = require('appdrag-cloudbackend'); 
var appID = process.env.APPID; 
var APIKey = process.env.APIKEY; 
cloudbackend.init(APIKey, appID);
    
exports.handler = (event, context, callback) => {

    var tableName = "trips"; 
    var columns = "idfm, name";
    callback(null, JSON.stringify(event));
    var idfm = event.POST.idfm;
    var name = event.POST.name;
    var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + idfm + ", " + name + ")"; 

    cloudbackend.sqlExecuteRawQuery(query)
    .then(function(response) {
        callback(null, response);
    });

};

sorry to be such a nuisance and thanks again for your help

Daniel Mulroy

Hi Dick,

One of my preferred ways of debugging SQL is returning the query before running it, then copy/pasting it into the "New Query" area of the CloudBackend / Database section.

As for your query, I think your values need to be quoted in SQL.

Node.js supports something called template strings which make building this kind of syntax really easy.

Replace your query:

query = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + idfm + ", " + name + ")";

with a template string version that properly puts quotes around the values:

query = `INSERT INTO ${tableName} (${columns}) VALUES ("${idfm}", "${name}")`;

Two very important points:

This is EXACTLY how you create SQL Injection attacks Either read up on sanitizing input / escaping strings or use a library designed to process your variables before putting them into a SQL string.
Depending on your column naming scheme, the column names might need escaping with quotes too.

Cheers!

Dick Honing

@Daniel-Mulroy Hi Daniel, thanks for pointing me in the right direction! Looks like the values need to be quoted in SQL indeed.

So the following code now works

var cloudbackend = require('appdrag-cloudbackend');
var appID = process.env.APPID;
var APIKey = process.env.APIKEY;
cloudbackend.init(APIKey, appID);

exports.handler = (event, context, callback) => {

    var tableName = "trips";
    var columns = "idfm, name";
    callback(null, JSON.stringify(event));
    var idfm = event.POST.idfm;
    var name = event.POST.name;
    var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES ('" + idfm + "', '" + name + "')"; 

    cloudbackend.sqlExecuteRawQuery(query)
    .then(function(response) {
        callback(null, response);
    });

};

Daniel Mulroy

Great! Glad to hear it.

Just so that I am clear -> The template strings I mentioned are just an easier way of embedding variables into a string in Javascript. The code you have posted is just as vulnerable to SQL injection attacks. Just an FYI, and good luck!

Joseph Benguira

@Dick-Honing check this version wich is safer and prevent SQL Injections:

var cloudbackend = require('appdrag-cloudbackend');
var appID = process.env.APPID;
var APIKey = process.env.APIKEY;
cloudbackend.init(APIKey, appID);

exports.handler = (event, context, callback) => {

    var tableName = "trips";
    var columns = "idfm, name";
    callback(null, JSON.stringify(event));
    var idfm = event.POST.idfm;
    var name = event.POST.name;
    var query = "INSERT INTO " + tableName + " (" + columns + ") VALUES ('" + Clean(idfm) + "', '" + Clean(name) + "')"; 

    cloudbackend.sqlExecuteRawQuery(query)
    .then(function(response) {
        callback(null, response);
    });

};

function Clean(txt) {
    return txt.replace(/\'/g, "''");
}

Dick Honing

Thanks Joseph!

REMINDER

API function application/json