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 <https://lucene.apache.org/core/3_6_0/queryparsersyntax.html>. 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 <matthewju...@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 > > 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 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 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- *Benjamin Thomas* -- 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.
