0

I started to develop some Nodejs APIs (with Express) today to retrieve some data from MySQL. I am familiar with some programming languages like Python but not with javascript. My first API is now working, but I'm not sure whether it's optimal or not. I am also not understanding some of its behavior.

The values I would like to retrieve :

  1. label
  2. description
  3. latitude
  4. longitude
  5. photos, an array of id_photos

1), 2), 3), and 4) are unique values from a first MySQL table; the id_photos of 5) are multiple rows from a second MySQL table.

Here is the code I wrote :

PoI.findById = (idPoI, result) => {
  sql.query(`SELECT label,description,ST_Y(geo) AS latitude,ST_X(geo) AS longitude FROM poi WHERE id_poi=${idPoI}`, (err, res1) => {
    if (err) {
      console.log("error: ", err);
      result(err, null);
      return;
    }

    if (res1.length) {
            sql.query(`SELECT id_photo FROM photo WHERE id_poi=${idPoI}`, (err, res2) => {
                if (err) {
                    console.log("error: ", err);
                    result(err, null);
                    return;
                }
                console.log("found poi: ", {...res1[0],photo:res2});
                result(null, {...res1[0],photo:res2});
                return;
            });
    }

    // result({ kind: "not_found" }, null);

  });
};

EDIT : As highlighted by nbk in the comments, this code is vulnerable to sql injections. The query now looks like that sql.query('SELECT label,description,ST_Y(geo) AS latitude,ST_X(geo) AS longitude FROM poi WHERE ?', args, (err, res1) => {}) with args = {id_poi: idPoI};

My questions:

  1. Is it a correct way to combine the results from two different MySQL queries ?

  2. The console shows found poi : label: 'Museum...' ........ photo: [ RowDataPacket { id_photo: 1 }, RowDataPacket { id_photo: 2 } ]; Thus, the data from the first query look correctly handled, but the data from the second query appear as "RowDataPacket"; It does not seem to affect the final api output though; Is it an issue ?

  3. If I uncomment result({ kind: "not_found" }, null);, I get ERR_HTTP_HEADERS_SENT, why ?

Corlin
  • 35
  • 5
  • 1
    this code is **vulnerable to sql injection** so use **prepared statements with parameters** see https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js – nbk Sep 12 '20 at 23:15
  • Callback-driven code is really hard to keep coherent and in general is a complete mess to work with, so consider using Promises instead. Even better: Use [Sequelize](https://sequelize.org/master/) and `await`? – tadman Sep 12 '20 at 23:17
  • Thanks nbk, I fixed the queries; tadman : I started to read about Sequelize, I may go for it but I still don't really understand how it helps; Any explanation regarding the ERR_HTTP_HEADERS_SENT ? – Corlin Sep 13 '20 at 13:35

0 Answers0