The mysql join query runned on nodejs gets null results though the given query has no problem-CodePudding

I'm now trying to get some data by executing a 'JOIN' query on the server(nodejs), but it keeps giving a nulled result.

Below is the query:

    const selectNameSql=`SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`

and below is the code where the query executed:

    exports.test=async function(req,res,next){
    const selectNameSql=`SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;
    var a = await db.query(selectNameSql, [2, 3, 4], function(err, rows, fields){
        if(err){ console.log(err);}
        else{console.log('SO SAD');}
    });
    console.log(a);}

Though executing the query directly on the database works really well, execution of the code upper on the nodejs server shows a result like below:

<ref *1> Query {
  _events: [Object: null prototype] {
    error: [Function (anonymous)],
    packet: [Function (anonymous)],
    timeout: [Function (anonymous)],
    end: [Function (anonymous)]
  },
  _eventsCount: 4,
  _maxListeners: undefined,
  _callback: [Function (anonymous)],
  _callSite: Error
      at Protocol._enqueue (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at Connection.query (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/mysql/lib/Connection.js:198:25)
      at exports.test (/home/tv/Desktop/CapTeen_repo/CapTeen/routes/analysis/analysis.controller.js:334:22)
      at Layer.handle [as handle_request] (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/layer.js:95:5)
      at next (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/route.js:144:13)
      at Route.dispatch (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/route.js:114:3)
      at Layer.handle [as handle_request] (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/layer.js:95:5)
      at /home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:284:15
      at Function.process_params (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:346:12)
      at next (/home/tv/Desktop/CapTeen_repo/CapTeen/node_modules/express/lib/router/index.js:280:10),
  _ended: false,
  _timeout: undefined,
  _timer: Timer { _object: [Circular *1], _timeout: null },
  sql: 'SELECT \n'  
    '    data_db.assets.name_assets, data_db.assets.id_assets, \n'  
    '    data_db.threats.name_threats, data_db.threats.id_threats, \n'  
    '    data_db.vulns.name_vulns, data_db.vulns.id_vulns \n'  
    '    FROM \n'  
    '    data_db.threats \n'  
    '    RIGHT JOIN data_db.assets \n'  
    '    ON data_db.assets.id_assets=data_db.threats.id_assets \n'  
    '    RIGHT JOIN data_db.vulns \n'  
    '    ON data_db.vulns.id_assets=data_db.assets.id_assets \n'  
    '    WHERE \n'  
    '    data_db.assets.id_assets=2 AND data_db.vulns.id_vulns=3 AND data_db.threats.id_threats=4',
  values: [ 2, 3, 4 ],
  typeCast: true,
  nestTables: false,
  _resultSet: null,
  _results: [],
  _fields: [],
  _index: 0,
  _loadError: null,
  _connection: <ref *2> Connection {
    _events: [Object: null prototype] {},
    _eventsCount: 0,
    _maxListeners: undefined,
    config: ConnectionConfig {
      host: '-----(I erased it)-----',
      port: 3306,
      localAddress: undefined,
      socketPath: undefined,
      user: '----(I erased it)----',
      password: '-----(I erased it)----',
      database: 'data_db',
      connectTimeout: 10000,
      insecureAuth: false,
      supportBigNumbers: false,
      bigNumberStrings: false,
      dateStrings: false,
      debug: undefined,
      trace: true,
      stringifyObjects: false,
      timezone: 'local',
      flags: '',
      queryFormat: undefined,
      pool: undefined,
      ssl: false,
      localInfile: true,
      multipleStatements: false,
      typeCast: true,
      maxPacketSize: 0,
      charsetNumber: 33,
      clientFlags: 455631,
      protocol41: true
    },
    _socket: Socket {
      connecting: false,
      _hadError: false,
      _parent: null,
      _host: null,
      _readableState: [ReadableState],
      _events: [Object: null prototype],
      _eventsCount: 4,
      _maxListeners: undefined,
      _writableState: [WritableState],
      allowHalfOpen: false,
      _sockname: null,
      _pendingData: null,
      _pendingEncoding: '',
      server: null,
      _server: null,
      timeout: 0,
      [Symbol(async_id_symbol)]: 8,
      [Symbol(kHandle)]: [TCP],
      [Symbol(lastWriteQueueSize)]: 0,
      [Symbol(timeout)]: Timeout {
        _idleTimeout: -1,
        _idlePrev: null,
        _idleNext: null,
        _idleStart: 150,
        _onTimeout: null,
        _timerArgs: undefined,
        _repeat: null,
        _destroyed: true,
        [Symbol(refed)]: false,
        [Symbol(kHasPrimitive)]: false,
        [Symbol(asyncId)]: 11,
        [Symbol(triggerId)]: 1
      },
      [Symbol(kBuffer)]: null,
      [Symbol(kBufferCb)]: null,
      [Symbol(kBufferGen)]: null,
      [Symbol(kCapture)]: false,
      [Symbol(kSetNoDelay)]: false,
      [Symbol(kSetKeepAlive)]: false,
      [Symbol(kSetKeepAliveInitialDelay)]: 0,
      [Symbol(kBytesRead)]: 0,
      [Symbol(kBytesWritten)]: 0
    },
    _protocol: Protocol {
      _events: [Object: null prototype],
      _eventsCount: 7,
      _maxListeners: undefined,
      readable: true,
      writable: true,
      _config: [ConnectionConfig],
      _connection: [Circular *2],
      _callback: null,
      _fatalError: null,
      _quitSequence: null,
      _handshake: true,
      _handshaked: true,
      _ended: false,
      _destroyed: false,
      _queue: [Array],
      _handshakeInitializationPacket: [HandshakeInitializationPacket],
      _parser: [Parser],
      [Symbol(kCapture)]: false
    },
    _connectCalled: true,
    state: 'authenticated',
    threadId: 57,
    [Symbol(kCapture)]: false
  },
  [Symbol(kCapture)]: false
}
SO SAD

I can't figure out how to solve this problem, so if anyone knows, please tell me how to troubleshoot it.

CodePudding user response：

The mysql package does not support async await, you can either try to change your code like this and use the callback:

exports.test = function (req, res, next) {
  const selectNameSql = `SELECT 
    data_db.assets.name_assets, data_db.assets.id_assets, 
    data_db.threats.name_threats, data_db.threats.id_threats, 
    data_db.vulns.name_vulns, data_db.vulns.id_vulns 
    FROM 
    data_db.threats 
    RIGHT JOIN data_db.assets 
    ON data_db.assets.id_assets=data_db.threats.id_assets 
    RIGHT JOIN data_db.vulns 
    ON data_db.vulns.id_assets=data_db.assets.id_assets 
    WHERE 
    data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;

  db.query(
    selectNameSql,
    [2, 3, 4],
    function (err, rows, fields) {
      if (err) {
        console.log(err);
      } else {
        console.log(rows);
      }
    }
  );
};

Or use the mysql2 package with async await:

exports.test = async function (req, res, next) {
    const selectNameSql = `SELECT 
        data_db.assets.name_assets, data_db.assets.id_assets, 
        data_db.threats.name_threats, data_db.threats.id_threats, 
        data_db.vulns.name_vulns, data_db.vulns.id_vulns 
        FROM 
        data_db.threats 
        RIGHT JOIN data_db.assets 
        ON data_db.assets.id_assets=data_db.threats.id_assets 
        RIGHT JOIN data_db.vulns 
        ON data_db.vulns.id_assets=data_db.assets.id_assets 
        WHERE 
        data_db.assets.id_assets=? AND data_db.vulns.id_vulns=? AND data_db.threats.id_threats=?`;
  
    try {
      const mysql = require('mysql2/promise');
      const connection = await mysql.createConnection({
        host: '<your-host>',
        user: '<your-user>',
        database: '<your-database>',
      });
  
      const a = await connection.execute(selectNameSql, [2, 3, 4]);
      console.log(a);
    } catch (err) {
      console.log(err);
    }
  };

For more on mysql2 check out their official docs

CodePudding user response：

The problem is possibly caused by mixing callbacks with promises (the "SO SAD" message is being logged from inside the callback, but you're not doing anything with the results there).

Either stick with a promise-based solution or a callback-based solution, but not both (although I can't find anything in the mysql package documentation that suggests it actually supports promises?).