Greenstand / Greenstand/treetracker-query-api

SQL injection vulnerabilities when using knex.raw with variable data

Open
#92 2 comments 2 reactions 0 assignees View on GitHub
good first issue postgresql
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:

![image](https://user-images.githubusercontent.com/59486980/156087388-35e29156-1fbf-4ae2-8513-1691da9e509a.png)

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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.