Generico code for building mysql queries

Question

Well, I need to build a generic code that builds a mysql query, I want to find out the best way to do it. Through a JSON I will send the information, it is they:

"Group"(TAG, EMPRESA, CARRO);
"Group_value"(id's das tags, empresas, carros);
"Function"(INSERT, REMOVE, DELETE);
"Condition"(seria o meu WHERE).

At the moment I am recognizing the group and through a switch case I define the table and column that will be edited, and the column that will be applied to Where.

When "Function" is DELETE the only thing you can do is delete the selected clients, so I don’t have big problems with this condition.

Follows draft code:

function (event, context) => {
var table, column, condition;

switch (event.group) {
    case 'Tags':
        table = 'client_tags'
        column = 'client_tag_id'
        condition = 'client_id'
        break;
    case 'Cars':
        table =  'clients'
        column = 'car_id'
        condition = 'id'
        break;
    case 'Empresas':
        table = 'clients'
        column = 'company_id'
        condition = 'id'
        break;
    // default:
    // 
}

var query = "UPDATE " +table+ " SET " +column+ " = " +event.group_values_id+ " WHERE " +condition+ " = " +event.condition



return query
};

Basically the Update is built in the right way, but when the group is TAG will be applied DELETE or INSERT, and there is the possibility of a client having several tags, (Tags have a table the part linked to Clients through foreign key, already COMPANIES and CARS are columns in table clients).

I would like a north to build the best code that will treat these conditions!

Answer 1

Mathias' comment was very helpful!

I ended up replacing the switch case with an object from tiopo const

    const GROUPS = {
    TAGS: {
        table: "client_tags",
        column: "client_tag_id",
        condition: "client_id"
    },
    CARS: {
        table: "clients",
        column: "car_id",
        condition: "id"           
    },
    COMPANIES: {
        table: "clients",
        column: "company_id",
        condition: "id"           
    }
}

and immediately after assigning the GROUP variable the result of the verification

const GROUP = GROUPS[event.group]

Finally I used Knex to build my queries, leaving the code cleaner, and the only check I do is if Event.group = Tags.

Answer 2

When constructing SQL statements dynamically, one of your first concerns should be security, as attacks of the type are common SQL Injection. If the queries will be generated only server-side, the problem becomes minor (as long as the client application does not pass query snippets, only parameters for it). If there is a small amount of operations to be carried out, predefined queries may be a safer approach than dynamic generation.

Regarding the implementation you explained, basic operations CRUD (Create, Read, Update, Delete) are supported by most of the available frameworks. I recommend using some (such as Laravel or Symfony) to abstract the implementation of these operations and focus on developing the most complex business rules for your application.

For mounting queries per se, I suggest using a library like the Laravel Query Builder or Doctrine ORM Query Builder. One of them, coupled with a good architecture of your query file (JSON) should be a good solution to the problem.