Meaning in nodejs/postgreSQL : query.query2+='DELETE FROM public.category_qb_map WHERE qb_id=${id};';

27
May 15, 2019, at 11:30 PM

I am writing an api in nodejs, in which some data are taken from the body of the request and insert, update and delete operations are performed. I am having difficulty in understanding some portions of the code. It would be of great help if someone would help me understand the portions of the code I don't understand. My code is as follows:

router.post('/dynamic_questionbank', function(req, res) {
    let query={},query2={};
    query.org_id=req.headers['org-id'];
    query.user_id=req.headers['user-id'];
    query.id=req.body.id;  
    query.type_id=req.body.type_id;  
    query.difficulty_level=req.body.difficulty_level;  
    query.weightage=req.body.weightage;  
    query.marks=req.body.marks;  
    query.used=req.body.used;  
    query.body=req.body.body;  
    query.archive_status=req.body.archive_status;  
    query.cal_logic=req.body.cal_logic;  
    query.hint=req.body.hint;  
    query.tolerance=req.body.tolerance;  
    query.status=req.body.status;  
    query.operation=req.body.operation;  
    query.qb_id=req.body.qb_id;  
    query.qb_map_status=req.body.qb_map_status;  
    query.options_array=req.body.options_array;  
    if(query.org_id!=null &&query.user_id!=null &&query.id!=null &&query.type_id!=null &&query.difficulty_level!=null &&query.weightage!=null &&query.marks!=null &&query.used!=null &&query.body!=null &&query.archive_status!=null &&query.cal_logic!=null &&query.hint!=null &&query.tolerance!=null &&query.status!=null &&query.operation!=null &&query.qb_id!=null &&query.qb_map_status!=null &&query.options_array!=null ){
        query.query=(query.operation==='insert')?'INSERT INTO public.questions (user_id,org_id,type_id,difficulty_level,weightage,marks,used,body,archive_status,cal_logic,hint,tolerance,status,modified_date_time,create_date_time) VALUES (${user_id},${org_id},${type_id},${difficulty_level},${weightage},${marks},${used},${body},${archive_status},${cal_logic},${hint},${tolerance},${status},now(),now()) RETURNING id;':
        (query.operation==='update')?'UPDATE public.questions SET user_id=${user_id},org_id=${org_id},type_id=${type_id},difficulty_level=${difficulty_level},weightage=${weightage},marks=${marks},used=${used},body=${body},archive_status=${archive_status},cal_logic=${cal_logic},hint=${hint},tolerance=${tolerance},status=${status},modified_date_time=now() WHERE id=${id} RETURNING id;':
        (query.operation==='delete')?'DELETE FROM public.questions WHERE id=${id} RETURNING id;':
        null;
        query.query2='';
        if(query.query!=null){
            if(query.operation==='delete'||query.operation==='update')
                query.query2+='DELETE FROM public.questions_qb_map WHERE ques_id=${id};DELETE FROM public.options WHERE ques_id=${id};';
            if(query.operation==='insert'||query.operation==='update'){
                    query.query2+='INSERT INTO public.questions_qb_map(ques_id,qb_id,org_id,user_id,create_date_time,status) VALUES (${id},${qb_id},${org_id},${user_id},now(),${qb_map_status});';
                query.options_array.forEach(options => {
                    query.query2+='INSERT INTO public.options(ques_id,body,order,weightage,correct_flag,calculation_type) VALUES (${id},\''+options.body+'\',\''+options.order+'\',\''+options.weightage+'\',\''+options.correct_flag+'\',\''+options.calculation_type+'\');';
                });
            }
            db.tx(t=>{
                return t.one(query.query,query)
                .then(result=>{
                    query.id=result.id;
                    return t.multi(query.query2,query)
                    .then(result=>{
                        result.status=1;
                        return result;
                    })
                    .catch(error_min=>{
                        var ret={status:0};
                        console.log(error_min);
                        return ret;
                    })                })
                .catch(error_min=>{
                    var ret={status:0};
                    console.log(error_min);
                    return ret;
                })
            })
            .then(ret=>{
                res.status(200).send(ret);
            }); 
        }
        else res.status(400).send("Operation not permitted.");

    }
    else{
        res.status(400).send("Please check the documentation for usage.");
    }
});

DOUBTS:

1)

query.query2+='INSERT INTO public.questions_qb_map(ques_id,qb_id,org_id,user_id,create_date_time,status) VALUES (${id},${qb_id},${org_id},${user_id},now(),${qb_map_status});';

What does query.query2+= mean?

When we studied C or java,

a+=10 meant a = a + 10

But here, we are writing an SQL query. So I don't understand if it is something similar or something altogether different.

Also, why has '; been used in the end?

2)Can anyone explain what is happening in this section of the code?

db.tx(t=>{
                return t.one(query.query,query)
                .then(result=>{
                    query.id=result.id;
                    return t.multi(query.query2,query)
                    .then(result=>{
                        result.status=1;
                        return result;
                    })

For return t.one(query.query,query) , only one row would be affected. In addition to that, in query.query, the sql query for either insert, update or delete will be present based on whatever data is fetched from req.body. Now my question is, what is the second query used for?

Also, can anyone explain what is happening in rest of the lines of code in the above snippet?

Answer 1
  1. It's performing string concatenation. For example:
let helloWorld = 'hello';
helloWorld += ' world';
console.log(helloWorld);

prints hello world.

The ; at the end allows multiple queries to be stored in a single string for execution.

  1. It appears to be using the pg-promise library (https://vitaly-t.github.io/pg-promise/index.html) for database access. It's creating a transaction and running the queries within that transaction, so if any fail queries previously run in the transaction will rollback so that any changes made are not saved to the database. As to the logic involved, for some operations it's doing multiple things in the database that would normally be handled by foreign keys and constraints. Like when deleting a question, it not only has to delete from the questions table, but also the questions_qb_map and options tables.

It's pretty ugly code, and is open SQL injection.

READ ALSO
Get a variable from a file to another file in node.js( problem not solve )

Get a variable from a file to another file in node.js( problem not solve )

I tried too many time but I couldn't a get solution to my problemmy file structure is like following:

12
Why is my express code not rendering css and javascipt files?

Why is my express code not rendering css and javascipt files?

I am trying to write a basic express server codeHowever, the HTML file that gets served on a particular route does not render the static files (css, js and image files)

30