I want to get multiple types as query parameters by using type.split(',') in WHERE IN().

I have service.js and DAO.js files. And I tried this like below.

service.js

const productTypeSort = async (name, type, productSort, page) => {
  const sort = orderBy(productSort);
  const categoryType = getCategoryType(name, type);

  return await productsDao.productTypeSort(categoryType, sort, page);
};

const getCategoryType = (name, type) => {
const filter = type.toString().split(',')
  const FilterType = {
    category: `WHERE c.name = "${name}"`,
    categoryType: `WHERE c.name = "${name}" AND t.name IN ("${filter}")`,
  };

  if (name && type) {
    return FilterType.categoryType;
  } else if (name) {
    return FilterType.category;
  }

  return '';
};

DAO.js

const productTypeSort = async (categoryType, sort, page) => {
  const products = await myDataSource.query(
    `SELECT
      c.name AS category,
      t.name AS type,
      pr.name,
      pr.description,
      price_origin,
      pr.created_at,
      count(*) OVER() AS totalCount
    FROM products pr
    JOIN category c ON c.id = pr.category_id
    JOIN product_types t ON t.id = pr.type_id
    ${categoryType}
    GROUP BY pr.id
    ${sort}
    LIMIT ?, 9`,
    [(page - 1) * 9]
  );
  return products;
};

and the query I sent is as follows.

?name=abc&type=aaa,bbb&sort=review&page=1

But this returns an empty data column.

{
     "data": []
}

What am I doing wrong? Maybe the location of .split(',') is wrong? I need help.

CodePudding user response：

Your problem is that your filter array is ["aaa,bbb"] and that ends up making a query string which looks like:

WHERE t.name IN ("aaa,bbb")

What you need is to map each value in filter to a single-quote enclosed string, and remove the double-quotes from the query:

filter = type.split(',').map(s => `'${s}'`)
// ...
categoryType: `WHERE c.name = "${name}" AND t.name IN (${filter})`,

This will give you something that looks like:

WHERE t.name IN ('aaa','bbb')

which should give you your desired result.

You should take note of what @Andreas referred to in their comment; you should preferably be using prepared statements rather than injecting user input directly into your query. If someone sent type="); DROP table products -- you would get a nasty surprise.