Greenstand / Greenstand/treetracker-query-api
SQL injection vulnerabilities when using knex.raw with variable data
- Dominant language
- TypeScript
- Stars
- 18
- Forks
- 65
- PR merge metrics
- No merged PRs in 30d
Description
When `knex.raw()` is used with template strings it may leave the application open to SQL injection attacks, especially if user submitted data is being placed into the string. Here is one example of this I was able to find in the app:
```ts
const sql = `
SELECT *
FROM
wallet.wallet
WHERE
id = '${walletIdOrName}'
OR
name = '${walletIdOrName}'`
```
SQL can be injected with a route like this:
```
https://dev-k8s.treetracker.org/query/wallets/' OR 1='1
```
Replace `` with a randomly generated uuid such as one from this website: https://www.uuidgenerator.net
The app will consider this a valid query and return a different wallet:

There are probably other injections that could be made with this vulnerability, and possibly other vulnerable routes in the app.
There is an [eslint rule](https://www.npmjs.com/package/eslint-plugin-knex) that can be used to place an error on any template strings being used with `knex.raw`, but this may be too strict.
On the topic of inserting values into SQL: Typescript's `const enum` is one limited option which can be safely used because [it compiles to a raw value: ](https://www.typescriptlang.org/play?#code/MYewdgzgLgBAlgExgXhgcgBYFMA2ORoCwAUCaJLFmAK4C2MAKgIYBGOWAck7VhDAN4kYMAO5M8WWKjRiJUIsQC+JMuGgwoWWgAccTTShgADTTr2aAXDAAk-RIpv9mbTt14A6WeyiKjQA)
TS:
```ts
const id = 'hello'
const enum TableNames {
wallet = 'wallet'
}
const template = `template: ${id} ${TableNames.wallet}`
```
compiled JS:
```js
const id = 'hello';
const template = `template: ${id} ${"wallet" /* wallet */}`;
```
Contributor guide
Assessment
This issue has not been assessed yet.