Re: [go-nuts] Re: General question: complex search form and query params

[matthewjuran]

>
> 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

On Sunday, March 4, 2018 at 12:55:43 AM UTC-6, Benjamin Thomas wrote:
>
> 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.
>
>
>
> 2018-03-03 22:46 GMT+01:00 :
>
>> 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
>>
>> On Friday, March 2, 2018 at 12:38:23 PM UTC-6, Benjamin Thomas wrote:
>>>
>>> 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?
>>>  
>>>
>>> Le vendredi 2 mars 2018 15:11:19 UTC+1, matthe...@gmail.com a écrit :

 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 

Re: [go-nuts] Re: General question: complex search form and query params

[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.



2018-03-03 22:46 GMT+01:00 :

> 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
>
> On Friday, March 2, 2018 at 12:38:23 PM UTC-6, Benjamin Thomas wrote:
>>
>> 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?
>>
>>
>> Le vendredi 2 mars 2018 15:11:19 UTC+1, matthe...@gmail.com a écrit :
>>>
>>> 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/Mtqvr1N1
>>> zAI
>>>
>>> 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
>>>
>>> On Friday, March 2, 2018 at 7:45:19 AM UTC-6, Benjamin Thomas wrote:

 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 

[go-nuts] Re: General question: complex search form and query params

[matthewjuran]

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

On Friday, March 2, 2018 at 12:38:23 PM UTC-6, Benjamin Thomas wrote:
>
> 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?
>  
>
> Le vendredi 2 mars 2018 15:11:19 UTC+1, matthe...@gmail.com a écrit :
>>
>> 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
>>
>> On Friday, March 2, 2018 at 7:45:19 AM UTC-6, Benjamin Thomas wrote:
>>>
>>> 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
>>>
>>> ```
>>> # 

[go-nuts] Re: General question: complex search form and query params

[benjamin . guy . thomas]

>
> 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?
 

Le vendredi 2 mars 2018 15:11:19 UTC+1, matthe...@gmail.com a écrit :
>
> 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
>
> On Friday, March 2, 2018 at 7:45:19 AM UTC-6, Benjamin Thomas wrote:
>>
>> 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

[go-nuts] Re: General question: complex search form and query params

[matthewjuran]

>
> 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

On Friday, March 2, 2018 at 7:45:19 AM UTC-6, Benjamin Thomas wrote:
>
> 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!
>

-- 
You received this message because you are subscribed to the Google Groups 
"golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to golang-nuts+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.