General question: complex search form and query params

[benjamin....@gmail.com]

Hello gophers,

Sorry if this is considered noise to some, as I have a question which is not specifically go related.

I have a personal project in which I'd like to use go though.

Basically, I'd like to create a complex search form, returning data backed by an SQL database.

To prevent SQL injection and for flexibility, I'm set on using an sql builder library.

However I'm not sure how to go about querying the data itself, via query params, without creating lots of boiler plate and duplication.

I'm wondering if a solution similar to what I'm looking for exists, as I've never stumbled upon one...

 

I'm submitting my thoughts below, and would greatly appreciate feedback :)

===NOTES_START===

# Idea for query params, for a search form.

Upon UI changes, javascript would generate the appropriate final query string

A query string could be typed in by a power user, to handle cases not covered by a simpler UI (via the url or text input)

## First, create a solid CLI app. Then port it to the web via a JSON API, that would simply consume the query string.

```

go run ./cmd/query/main.go QUERY_STRING

```

## Query string format would follow this principle

    PARAM_NAME : VALUE : OPERATOR

```

# Commands

columns:posted_on,short_descr:eq

columns:posted_on,short_descr:hide

columns:posting_id,posted_on,short_descr:show

limit:10:eq

limit:10  # would default to `eq`?

page:1

page:2

offset:20

# Filtering

euros:11.94 # would default to `eq`?

euros:11.94:eq

euros:100:lt

euros:100:lte

comment:FIXME # would default to `eq`?

comment:FIXME:eq

comment:NULL:eq

comment:NULL:ne

comment:%tickets%:like

comment:%Tickets%:ilike

payee:Amazon|Google:re # regex

payee:AMAZON|Google:rei # regex, case insensitive

```

## Question: how would I chain commands? I cannot use & in urls.

### Maybe with a pipe char

    QUERY_STRING | QUERY_STRING | QUERY_STRING

### Or via AND, OR keywords

```

    qs=QUERY_STRING

    qs AND qs OR qs

```

### Boolean logic, force the use of parentheses?

```

    qs=QUERY_STRING

    (qs AND qs) OR (qs OR qs)

```

===NOTES_END===

Basically, I guess I'm looking for some kind of DSL.

I'm thinking of implementing a lexer/parser for this, but first I'd like to make sure I'm not going to reinvent the wheel :)

Thanks for your interest and input!

[matthe...@gmail.com]

To prevent SQL injection and for flexibility, I'm set on using an sql builder library.

I believe correctly used database/sql (with the argument placeholders) protects against SQL injection.

There’s a query builder for postgres with MIT license posted here a few days ago: https://groups.google.com/forum/#!topic/golang-nuts/Mtqvr1N1zAI

Otherwise strings.Builder (or bytes.Buffer pre-1.10), + string concatenation, or fmt.Sprintf can do it.

## First, create a solid CLI app. Then port it to the web via a JSON API, that would simply consume the query string.

In Go this might be best done as a non-main package with a cmd folder that has a folder for the server and a folder for the CLI app.

I'm thinking of implementing a lexer/parser for this, but first I'd like to make sure I'm not going to reinvent the wheel :)

It sounds like you are reinventing SQL. Why do you need a DSL?

Matt

[Alex Efros]

Hi!

Many years ago I've implemented something similar in Perl, which was later
released as https://metacpan.org/pod/DBIx::SecureCGI. Nowadays I suppose
best way to do something like this is using GraphQL (for ex.
https://github.com/graphql-go/graphql).

--
WBR, Alex.

[Lutz Horn]

> Nowadays I suppose
> best way to do something like this is using GraphQL (for ex.
> https://github.com/graphql-go/graphql).

But GraphQL != SQL. Building SQL from HTTP query parameters is not made
more simple and secure by building GraphQL from HTTP query parameters.

Lutz

[benjamin....@gmail.com]

I believe correctly used database/sql (with the argument placeholders) protects against SQL injection

Yeah I badly explained this, an SQL builder solves security AND flexibility for me. Standard database/sql placeholders are too painful when the user params are too complex, and I can't just pass around SQL fragments.

It sounds like you are reinventing SQL. Why do you need a DSL?

Yes, in a way SQL would be awesome, but way too powerful (and too verbose). You can potentially access data from other tables, update/delete data, etc.

And how could you pass along raw SQL securely?

I guess restricting data access could solve some issues. But let's say I'd like to give access to a regex filter for some columns, but not others (for performance reasons). Not sure if this would be possible at all via db policies.

I guess I'm looking for a "dumbed down" query language.

In other words, as a programmer I've always been frustrated by search forms I've developed. As a user, same thing, I always find them too restrictive.

I feel access to a DSL could be interesting for a power user, rather than trying to anticipate every combination of search params a user would want to perform. 

Look at github for example, their advanced search form is interesting, and love how readable the url can be: https://github.com/search?q=language:Go+stars:<100+forks:>500

However every params seems to be ANDed, so let's say you'd like to search golang repos with less than 100 stars OR forks greater than 500, you can't do it.

Also if you pass invalid input, you seem to get garbage : /search?q=language:whatever+stars:<100, so I feel that overall the user experience is not that great.

You see my point?

[benjamin....@gmail.com]

Thanks for the feedback.

I'm only vaguely familiar with graphql. From my understanding, it's meant to facilitate data query for the frontend dev.

But I'm looking at things more from a user perspective here, see my github example.

I might have to dig into this though.

[matthe...@gmail.com]

Mapping a subset DSL to SQL doesn’t sound too difficult since SQL already has those boolean expressions and such. The database/sql library uses context for cancellation, so queries that take too long could be cancelled by a timer goroutine.

One thing for me that would be helped by a library is keeping track of the argument placeholders ($1 $2 $3) and their indexing in the slice input to database/sql for the variadic part. Adding FOR UPDATE sometimes is also kind of ugly.

Here’s a case where I dynamically constructed a query: https://github.com/pciet/wichess/blob/master/game.go#L209

Matt

[Benjamin Thomas]

Unless I'm misunderstanding something, that kind of string concatenation looks dangerous to me.

I'v been doing a bit of digging...

Turns out there is a DSL that looks like what I'm looking for: the Lucene query syntax.

Some variant of it is even specifically designed to be opened up to direct user input it seems.

However I don't really want to duplicate data to a search DB like elasticsearch (or bleve), as it seems overkill for the size of the dataset, and SQL will be perfectly fine for query performance.

I also stumbled upon this interesting article: http://www.recursion.org/query-parser/

The author advocates building a custom parser, for domain flexibility, performance and security, basically what I'm looking for it seems.

Although this is ruby code, and the queried DB is elasticsearch, and not an SQL database, the same concepts apply.

I've looked around, but haven't found any library that would take a lucene like query syntax as input, and generate some kind of SQL abstraction as output.

--
You received this message because you are subscribed to a topic in the Google Groups "golang-nuts" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/golang-nuts/Odr-LrUEAS8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to golang-nuts+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Benjamin Thomas

[matthe...@gmail.com]

Unless I'm misunderstanding something, that kind of string concatenation looks dangerous to me.

This approach may be error prone so testing is important but I believe the database/sql placeholders avoid any SQL injection. Here the caller also has responsibility to validate the input (such as to avoid a person making moves for others). I wouldn't call it flexible in an instantly readable sense but it's not too bad to work with.

Matt

To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Benjamin Thomas