Querying Postgres with multiple parameters Node.js

Question

I'm just starting with Node.js to build a Node.js server with Postgres db, I have in place a few routes and methods for CRUD operations :

router.get('/products', productController.listAllProducts);

exports.listAllProducts = async (req, res) => {
  const response = await db.query('SELECT * FROM products ORDER BY productName ASC');
  if (response.rowCount > 0) {
    res.status(200).send(response.rows);
  } else {
    res.status(404).send({
      message: "No products found"
    });
  }
};

and in Postman the url localhost:3000/api/products works just fine. Now I'm trying to include 3 parameters in the query city, region and country

router.get('/products', productController.findCityProducts); // correct route??

exports.findCityProducts = async (req, res) => {
  const city = req.query.city;
  const region = req.query.region;
  const country = req.query.country;
  const response = await db.query('SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3', [city,region,country]);
  if (response.rowCount > 0) {
    res.status(200).send(response.rows);
  } else {
    res.status(404).send({
      message: 'Product not found'
    });
  }
};

and in Postman I add the 3 parameters so the url is localhost:3000/api/products?city=Bologna&region=Emilia-Romagna&country=Italy but the results are the same as for the listAllProducts method.. and there is no record that satisfies the query..so I guessed the '/products' route can't have more than one method assigned..and is calling listAllProducts..I commented it out and indeed now is calling findCityProduct`.

How would I to enable both the listAllProducts and findCityProduct queries? Many thanks.

score 0 · Accepted Answer · 2021-04-18 05:24:14Z

0

As you found out, you can't have two routes with the same path and HTTP method (GET /products) defined using different controller functions (listAllProducts, findCityProducts).

One simple approach you could take here is to adapt your controller function to behave differently depending on the existence of parameters in the URL.

Also, it is worth noting that you should be sanitizing any external input before using them in SQL queries to avoid injections.

router.get('/products', productController.listProducts);


exports.listProducts = async (req, res) => {

  const { city, region, country } = req.query
  let response

  if (city && region && country) {
    response = await db.query('SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3', [city,region,country]);
  } else {
    response = await db.query('SELECT * FROM products ORDER BY productName ASC'); 
  }

  if (response.rowCount > 0) {
     res.status(200).send(response.rows);
  } else {
     res.status(404).send({ message: "No products found" });
  }
};

edited Apr 18, 2021 at 5:24

answered Apr 18, 2021 at 5:18

user1939584

Sign up to request clarification or add additional context in comments.

4 Comments

Vincenzo Over a year ago

Grat thanks, glad to see I almost got it yesterday. I was trying a check like if (req.query.city != null) and then getting the values like const city = req.query.city and so on.. So I see I can declare the 3 variables without specifying their value taken from a specific query parameter.

Vincenzo Over a year ago

I'm very new to security, but all data will be encrypted/decripted within the cloud functions that are calling the Node API, also the writing/reading to the db are user/password protected as DATABASE_URL=postgres://{db_username}:{db_password}@{host}:{port}/{db_name}stored in a .env file. What else are cautious steps to take in order to get security level up?

user1939584 Over a year ago

You must validate and sanitize the inputs that come via URL in this case. Take a look at this question: stackoverflow.com/questions/41455585/…

Vincenzo Over a year ago

Hi again, in the code from your example, you're using a Parameterized query right? If so SQLI shouldn't work as they state :

Absolutely! The parameterized query support in node-postgres is first class. All escaping is done by the postgresql server ensuring proper behaviour across dialects, encodings, etc...

Sorry to bother again but' I'm getting a little confused about normal queries ( allowing SQLI ) Prepared statements ( which needs pg-promise) and Parameterized queries (done in node-postgres that does the input sanitizing automatically if I got it right..)

Collectives™ on Stack Overflow

Querying Postgres with multiple parameters Node.js

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related