How can I fix a blind SQL injection, when searching for an array, that can change depending on a users input

user_16946183 0
Viewed 90

I'm a junior developer, and just working on solving an instance of blind sql injection that was pointed out to me in one of my projects.

For starters here is my code, I'll explain a bit better afterward.

 for (const room of query.rooms) {
      roomQuery.push(`rooms.name = '${room}'`)
    }
for (const category in categories) {
      categoryQuery.push(`categories.name = '${categories[category].name}'`)
    }


let productsRoomAndCategories = (await db.query(`SELECT products.*
                    FROM products
                    WHERE products.room_id
                    IN (SELECT DISTINCT rooms.id FROM rooms 
                    WHERE ${roomQuery.join(' OR ')})
                    AND products.category_id
                    IN (SELECT DISTINCT categories.id FROM categories 
                    WHERE ${categoryQuery.join(' OR ')})
                    ORDER BY products.price`,
      )).rows

So essentially a user selects a list of categories and rooms that they are looking for, gets posted to the backend, and then I am querying them to find products matching what they are looking for.

Unfortunately, like an innocent child, I forgot you can simply edit headers, and I won't always get what im expecting sent to the backend, and am currently subject to blind sql attacks.

so roomsQuery may look something like:

[ 'rooms.name = \'common area\'',
  'rooms.name = \'kitchen\'',
  'rooms.name = \'bathroom\'',
  'rooms.name = \'bedroom\'',
  'rooms.name = \'laundryroom\'',
  'rooms.name = \'entryway\'' ]

and categoryQuery may look like:

[ 'categories.name = \'speakers\'',
  'categories.name = \'hubs\'',
  'categories.name = \'vaccumes\'',
  'categories.name = \'refrigerators\'',
  'categories.name = \'stoves\'',
  'categories.name = \'dishwashers\'',
  'categories.name = \'washers\'',
  'categories.name = \'dryers\'',
  'categories.name = \'coffee makers\'',
  'categories.name = \'televisions\'',
  'categories.name = \'thermostats\'',
  'categories.name = \'yard cameras\'',
  'categories.name = \'interior camears\'',
  'categories.name = \'door locks\'',
  'categories.name = \'door bells\'' ]

I'm using NodeJS and PSQL for this. Everything I've seen talks about implementing parameterized queries, but I don't know how to do it in this sense, the issue is, I don't want the rooms.name to be in quotation marks other wise the query will fail. Any advice is appreciated thank you :)

node.js sql-injection postgresql
1 Answers

You can use in operator instead of a big chain of or.

where rooms.name in ('foo', 'bar')

This at least avoids having to build SQL, but you need to put each value in separately and know how many values you'll have: where rooms.name in ($1, $2).

Instead, use the any operator which takes a Postgres array.

where rooms.name = any('{foo,bar}').

This needs only one parameter: where rooms.name = any($1). You'd supply your query with an array of room names. node-postgres should convert this into a Postgres array.

category_names = []
for (const category in categories) {
  category_names.push(categories[category].name)
}

sql = `
  SELECT products.*
  FROM products
  WHERE products.room_id IN (
    SELECT DISTINCT rooms.id
    FROM rooms 
    WHERE rooms.name = any($1)
  )
  AND products.category_id IN (
    SELECT DISTINCT categories.id
    FROM categories 
    WHERE categories.name = any($2)
  )
  ORDER BY products.price
`

let productsRoomAndCategories = (
  await db.query(sql, [query.rooms, category_names])
).rows

Something like that.

Note that this can probably be done better with a join.

select products.*
from products
join rooms on products.room_id = rooms.id
join categories on products.category_id = categories.id
where rooms.name = any($1)
  and categories.name = any($2)
order by products.price
user_146600
Related