Hacker News Reader

Comment by reificator | Hacker News Reader

> I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check.

That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent.

Prepared statements are what you use here. You provide a query with placeholders for the values you want to fill in, and then you call that prepared statement with the arguments you need. These are typically type-checked and hardened against SQL injection, but of course you should verify with your particular language/library combination.

View on HN

The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings!

With an ORM you can construct the prepared statements programmatically.

It sounds to me like there's a reasonable middle ground here, where you treat your SQL query more like an AST than a string. So you can do something like myquery = select("col1").where("col2 < {}", my_value), then later myquery.limit(10).offset(20).

You're still working with SQL as a language, but you're manipulating it directly instead of trying to go via string formatting.

Sounds like you want a query builder.

A quick search for "query builder golang" (We are on HN after all) provides the following library:

https://github.com/fragmenta/query

An example from the README:

    // In your handlers, construct queries and ask your models for the data

    // Find a simple model by id
    page, err := pages.Find(1)

    // Start querying the database using chained finders
    q := page.Query().Where("id IN (?,?)",4,5).Order("id desc").Limit(30)

    // Build up chains depending on other app logic, still no db requests
    if shouldRestrict {
        q.Where("id > ?",3).OrWhere("keywords ~* ?","Page")
    }

SQLAlchemy has this. It sits alongside and works in tandem with the ORM, and I've used both in a projects (and also raw SQL), each where appropriate to the use case.

https://docs.sqlalchemy.org/en/13/core/tutorial.html

> The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings!

Absolutely, prepared statements would not be appropriate for that.

> With an ORM you can construct the prepared statements programmatically.

That's not limited to ORMs though. You can also just use a query builder. In fact I would argue that an ORM that provides that functionality is a query builder with additional ORM features on top.

this is absolutely not true.

You can use functions to create basic "getters" for application data structures. Layering these through further functions for limits/offsets/etc is simple (and you can write those layers in a generic way).

You just have to get used to the mental mindshift that all your data manipulation code lives in the database, and your application simply consumes data from single function calls.

> That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent.

I don't think others mean concatenation of values where placeholders should be, but the fact that the query itself is a big string. Unless some sort of query builder is used to literally build this big string for you.

> I don't think others mean concatenation of values where placeholders should be, but the fact that the query itself is a big string.

They explicitly contrast using an ORM to the practice of concatenating strings here:

> > SQL is backward to a programming language. Creating statements by concatenating strings?

> Unless some sort of query builder is used to literally build this big string for you.

I'm not entirely sure they're aware of the existence of query builders outside of an ORM given the dichotomy presented.

That “unless” is the whole point...

Reply on news.ycombinator.com