<p>So, I have this simple web app in Go which is not really REST (HTTP + JSON api + mobile/SPA frontend). This is backed by PostgreSQL and acessed using Sqlx and the usual wrappers.</p>
<p>Now say I want to write an admin page (for the client) and this cool project <a href="https://marmelab.com/admin-on-rest/" rel="nofollow">https://marmelab.com/admin-on-rest/</a> does a lot of the hard parts... And it uses parameters in this way, understandably:</p>
<p>GET <a href="http://my.api.url/posts?_sort=title&_order=ASC&_start=0&_end=24&title=bar" rel="nofollow">http://my.api.url/posts?_sort=title&_order=ASC&_start=0&_end=24&title=bar</a></p>
<p>Now, I'm thinking that doing this <em>sorting</em> and <em>filtering</em> in Golang (read all results and do it in the app I mean) would be a hassle (due to no generic sort) and in SQL it would mean "string concatenation" meaning injection attacks</p>
<p>How do people usually do this? I'm using only Sqlx for now.</p>
<p>Thanks in advance.</p>
<hr/>**评论：**<br/><br/>saturn_vk: <pre><p>People usually do this in SQL. Either with prepared statements + placeholders, or by safely quoting the user input.</p></pre>aboukirev: <pre><p>You'll have to do SQL string concatenation but you have means to avoid SQL injections by validating parameters. For instance, the start and end should be numbers (and you'll concatenate those into OFFSET LIMIT clause), the order can be either ASC or DESC. The column must match one of the available columns - you should know the list of sortable columns for a given table and validate against it. That is how I do it.
Also, I construct 2 SQL statements in parallel: one retrieves a range of data and another counts total number of records to implement proper pager widget. I typically have a search filter as well, hence counting records.</p></pre>HugoWeb: <pre><p>Thanks for the explanation - I have something almost working - I guess I was just trying to avoid doing "ugly stuff" but ugly stuff will always exist somewhere..</p></pre>HugoWeb: <pre><p>Something like this <a href="https://gist.github.com/hugows/38cfdfcc5a6dff57b04621231a5d4c2b" rel="nofollow">https://gist.github.com/hugows/38cfdfcc5a6dff57b04621231a5d4c2b</a> worked :)</p></pre>Mikojan: <pre><p>You can use libraries like <a href="https://github.com/Masterminds/squirrel" rel="nofollow">squirrel</a> do that. They're concatenating your SQL statements correctly for you. No clumsy ORM required.</p>
<p>What I do a lot is, I have some base parameters, like:</p>
<pre><code>var builder squirrel.SelectBuilder = squirrel.Select("turtles.id", "turtles.name").From("turtles")
</code></pre>
<p>Things like <code>_start</code> and <code>_end</code> you could probably just append, if I'm interpreting the above correctly, so you get</p>
<pre><code>builder := builder.Limit(START).Offset(END)
</code></pre>
<p>And then for more complicated things you could just use closures or something. Here's what I'd do to get all red and blue turtles:</p>
<pre><code>type Filter struct {
Colors []string
}
func (self *Filter) Execute(sb squirrel.SelectBuilder) squirrel.SelectBuilder {
if len(self.Colors) == 0 {
return sb
}
return sb.
LeftJoin("colors ON turtles.color_id = colors.id").
Where(squirrel.Eq{"colors.name": self.Colors})
}
filter := Filter{ Colors: []string{"red", "blue"} }
filtered := filter.Execute(builder)
// turn everything into a correct string
filtered.ToSQL()
</code></pre>
<p>Surely this can be done nicer and more performant but I think the basic idea is sound</p></pre>
