cheminfo / cheminfo/smart-sqlite3-filter
Provide new API where this lib would not need a better-sqlite3 connection and would not build the sql.
- 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.