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 :)
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