> > 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 <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 <matthe...@gmail.com <javascript:>>: > >> 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 >>>>> >>>>> ``` >>>>> # 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...@googlegroups.com <javascript:>. >> 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.
