How to properly use UNNEST without being vulnerable to sql injections

Question

I'm trying to insert multiple rows at once in a many to many relationship table using Express.js and postgresql. The below solution works, however, I know that since I'm not passing the homeType using a value it's vulnerable to sql injections.

Create Home Controller

 const {
    title,
    description,
    rules,
    telephone,
    homeType, // Array of ids example [1,2]
  } = req.body;

  try {
    const results = await db.query(
      'WITH step_one AS (INSERT INTO home (home_title, home_description, rules, telephone, user_account_id, created_on) VALUES ($1, $2, $3, $4, $5, NOW()) RETURNING home_id) INSERT INTO home_category (home_id, home_category_id) SELECT home_id, UNNEST(ARRAY' +
        JSON.stringify(homeType) +
        ') FROM step_one',
      [title, description, rules, telephone, req.user]
    );
  } catch (err) {
    console.error(err.message);
    return res.status(500).send('Server Error');
  }

  res.status(201).json({ message: 'Home created successfully' });

How can I insert multiple rows from an array the correct way?

Answer 1

Preventing the SQL Injection by passing the value and converting/casting the parameter input to an actual array in the query statement.

To cast the object to an array of integers in postgresql use $6::integer[]

Full query as follows:

 const results = await db.query(
      'WITH step_one AS (INSERT INTO home (home_title, home_description, rules, telephone, user_account_id, created_on) VALUES ($1, $2, $3, $4, $5, NOW()) RETURNING home_id) INSERT INTO home_category (home_id, home_category_id) SELECT home_id, UNNEST($6::integer[]) FROM step_one',
      [title, description, rules, telephone, req.user, homeType]
    );