Question 20710 in Mysql Question posted in
The official MySQL documentation can be found here.

Typescript doesnt wait before executing next sql – Mysql

I am using NestJS and Mysql without using TypeORM.
I need to execute 2 or more simple SQL’s, second SQL will use result from the first SQL. The problem is although I am using async/await the program doesnt wait for getUser() to complete before processing getGetMenuParents() . Hence the role passed as argument to getGetMenuParents() is undefined.
Please help me to identify the problem.

async findMenu(header) {  
    if (!header.userid) throw new HttpException('Userid not supplied', HttpStatus.FORBIDDEN);
    let a = await this.getUser(header.userid)           
    let b = await this.getGetMenuParents(a[0].role)
    return b[0];
}

async getUser(userid:string) {
    const sql =`select role from gb_users where userid = '${userid}'`
    const result  = await this.conn.query(sql);
    return result;
}

async getGetMenuParents(role:string) {
    const sql =`select * from gb_roles where role = '${role}'`;   
    const result  = await this.conn.query(sql);
    return result;
}

Regards,
Suraj

This is my connection

const dbProvider = {
    inject: [ConfigService],
    provide: DB_CONNECTION,
    useFactory: async (configService: ConfigService) => {
    return await mysql.createConnection({
        user: configService.get<string>('database.username'),
        host:  configService.get<string>('database.host'),
        database:  configService.get<string>('database.database'),
        password:   configService.get<string>('database.password'),
        port: parseInt( configService.get<string>('database.port'), 3306),
     })
   },
};

I tried changing DB_CONNECTION to "ASYNC_CONNECTION"

Tags:

2

Answers


  1. 0

    According to your DB specification, the field is in all-caps, therefore the sql should also use all-caps:

    async findMenu(header) {  
    if (!header.userid) throw new HttpException('Userid not supplied', HttpStatus.FORBIDDEN);
    let a = await this.getUser(header.userid)           
    let b = await this.getGetMenuParents(a[0].role)
    return b[0]; // Data is 
}

async getUser(userid:string) {
    const sql =`select ROLE as role from gb_users where USERID = '${userid}'`
    const result  = await this.conn.query(sql);
    return result;
}

async getGetMenuParents(role:string) {
    const sql =`select * from gb_roles where ROLE = '${role}'`;   
    const result  = await this.conn.query(sql);
    return result;
}

    Though something like this would probably be preferable since it uses the query builder abstraction to mitigate SQL injection:

    async findMenu(header) {  
    if (!header.userid) throw new HttpException('Userid not supplied', HttpStatus.FORBIDDEN);
    let a = await this.getUser(header.userid)           
    let b = await this.getGetMenuParents(a.role)
    return b[0];
}

async getUser(userid:string) {
    const result = this.conn
        .createQueryBuilder()
        .select("ROLE", "role")
        .from("gb_users")
        .where("gb_users.USERID = :id", { id: userid })
        .getOne();
    return result;
}

async getGetMenuParents(role:string) {
    const result = this.conn
        .createQueryBuilder()
        .select("ROLE", "role") // make sure these are lower case as well, since this is most likely assumed further down the code.
        .select("ROLEDESC", "roledesc")
        .from("gb_roles")
        .where("gb_roles.ROLE = :role", { role })
        .getMany();
    return result;
}
    Login or Signup to reply.
  2. 0

    This sort of chained query is usually a bad idea, and can be replaced by a single query with a JOIN.

    In your case you can replace the two separate queries

    select role from gb_users where userid = '${userid}'
select * from gb_roles where role = '${role}'

    With a single query:

    select * from gb_users left join gb_roles using(role) where userid='${userid}'

    which matches gb_roles to gb_users on the role column, then returns everything from both tables, filtered by userid

    Your code above becomes:

    async findMenu(header) {  
   
    if (!header.userid) throw new HttpException('Userid not supplied', HttpStatus.FORBIDDEN);

    const sql =`select * from gb_users left join gb_roles using(role) where userid='${userid}'`
    const result  = await this.conn.query(sql);
    return result[0];
}

    SQL Demo:https://www.db-fiddle.com/f/jpZC7WzUVWRPB1wFFJhwVW/0

    Note This addresses the gist of your question by eliminating the second query. The code is vulnerable to SQL injections (as is yours). I refer you to the answer by Elias for methods to combat that threat.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search