cheminfo / cheminfo/smart-sqlite3-filter

Provide new API where this lib would not need a better-sqlite3 connection and would not build the sql.

Open
#13 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
1
Forks
1
PR merge metrics
No merged PRs in 30d

Description

Using this lib in a project using an ORM / Query-builder (Lucid / Knex) is not trivial, and I'm not confident to using it in a webserver context with criteria-query from user input, I'm pretty sure the actual code is open to SQL-injection.

For me, the added-value of this package is this: https://github.com/cheminfo/smart-sqlite3-filter/blob/main/src/utils/parseQueryString.ts#L20
parsing the query-string. Table schema could be provided from user of the package, and injection criteria should be done with a query-builder, with parametrized query.

Schema type could be simplier, this package need only to have column name associated with column type: `Record`.
`ColumnType` could be a small set of what is supported for conversion to sql value. The whole API could be agnostic from dbms.

I would like to add export method `getCriteria`

```ts
export type ColumnType = 'INTEGER' | 'REAL' | 'TEXT' | 'BLOB'; // | 'DATE' | 'DATETIME' if we want to support them with
export type Schema: Record;
export type Parsing: Partial Primitive>>;

export interface GetCriteriaOptions {
query: string;
schema: Schema;
parsing?: Parsing;
}

export interface Criterion {
// for best-case scenario
columnName: string;
operator: Operator;
value: Primitive;

// for agnostic, if default value to primitive is not what is expected
columnType: ColumnType;
queryValue: string; // original value from query string, could be used to parse the value and format it differently
}

export type Criteria = Criterion[];

export function getCriteria(options: GetCriteriaOptions): Criteria {
// impl
}
```

With theses `Criteria`, developer have responsibility to add them in their sql queries, they can do it in a secure way with a query-builder like knex.js. And they can use it outside sqlite.

What do you think @lpatiny ?

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.