The only thing that’s tricky is when you need dynamic queries
So what you’re saying is, it’s easy to not use a query builder, so long as you don’t need to do the exact thing query builders are designed to make easier and safer.
To be fair, the vast majority of queries are not that dynamic in most programs. But it depends heavily on exactly what you’re doing, of course.
You also have to consider the complexity of query builders. The useful ones are typically a huge pile of code, and yet another dependency to manage, often with a big security impact.
To be fair, the vast majority of queries are not that dynamic in most programs.
I think we must work on very different types of program.
Give the history of the web and databases, removing the risk of SQLi by using a query builder in place of ad hoc dynamic query likely negates way more risks than the query builder introduces.
True, the risk historically has been very high. I don’t know why - I blame the ease of string interpolation combined with shitty tutorials and books that teach it that way. But I do know that passing in user-supplied data as separate parameters solves the problem for 90%. The remaining 10% are rare enough that solving them ad-hoc can be done quite safely, as long as you’re in a competent team. With code reviews, those won’t slip through easily.
Also, query builders are not immune to SQLi either. There have been several CVEs in common query builders, and in some cases columns are spliced in unsafely and it’s unaddressed(!). As a concrete example, I remember having to convince ~the Laravel team~Taylor Otwell to add that warning after the first paragraph. He just didn’t take this very seriously, but it meant that anywhere a user could supply a column name (e.g. in an interface that allows sorting on arbitrary columns), it would be injected unsafely. I would be extremely surprised if there were no codebases in the wild affected by this because the authors are unaware, missed that the note (which isn’t present in most tutorials) or wrote the code before the note was added.
But I do know that passing in user-supplied data as separate parameters solves the problem for 90%.
Parameterisation does indeed solve the majority - the problem is that without the query builder, the developer inevitably has to resort to “building” at least some queries themselves - which is arguably more likely to result in issues again.
That’s not what I’m saying at all. It’s tricky because we’re not used to write real SQL when using query builders, but as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
And no, builders are not safer than writing SQL short circuits or CTEs
It’s tricky because we’re not used to write real SQL when using query builders
Speak for yourself. I quite frequently write ad-hoc queries (yes writing the queries, not asking a fucking robot in 8 iterations of increasingly verbose detail to write it for me). That works perfectly well because I know what I want at the time I’m writing the query.
That isn’t at all the same thing as using a query builder to allow the application to be able to filter on arbitrary columns, or complex value types, or have conditional conditions, or conditional columns returned, or any number of other dynamic aspects that are literally the reason for query builders to exist.
as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
Ok fuck it, I’ll bite. What’s the “trivial, pure SQL” to take a variable length array/list/set/tuple/<whatever structure your language uses for a bunch of values>, and check if a column is equal to one of them.
i.e convert ['foo', 'bar', 'baz'] into ...WHERE col IN ('foo', 'bar', 'baz');.
I guess that’s great if your DB supports an array type, and supports it for ANY(), and the language bindings/driver have support for binding arrays natively.
So what you’re saying is, it’s easy to not use a query builder, so long as you don’t need to do the exact thing query builders are designed to make easier and safer.
Who would have possibly guessed that?
score 1 · canonical
To be fair, the vast majority of queries are not that dynamic in most programs. But it depends heavily on exactly what you’re doing, of course.
You also have to consider the complexity of query builders. The useful ones are typically a huge pile of code, and yet another dependency to manage, often with a big security impact.
score 1 · canonical
I think we must work on very different types of program.
Give the history of the web and databases, removing the risk of SQLi by using a query builder in place of ad hoc dynamic query likely negates way more risks than the query builder introduces.
score 1 · canonical
True, the risk historically has been very high. I don’t know why - I blame the ease of string interpolation combined with shitty tutorials and books that teach it that way. But I do know that passing in user-supplied data as separate parameters solves the problem for 90%. The remaining 10% are rare enough that solving them ad-hoc can be done quite safely, as long as you’re in a competent team. With code reviews, those won’t slip through easily.
Also, query builders are not immune to SQLi either. There have been several CVEs in common query builders, and in some cases columns are spliced in unsafely and it’s unaddressed(!). As a concrete example, I remember having to convince ~the Laravel team~Taylor Otwell to add that warning after the first paragraph. He just didn’t take this very seriously, but it meant that anywhere a user could supply a column name (e.g. in an interface that allows sorting on arbitrary columns), it would be injected unsafely. I would be extremely surprised if there were no codebases in the wild affected by this because the authors are unaware, missed that the note (which isn’t present in most tutorials) or wrote the code before the note was added.
score 2 · canonical
Parameterisation does indeed solve the majority - the problem is that without the query builder, the developer inevitably has to resort to “building” at least some queries themselves - which is arguably more likely to result in issues again.
score 1 · canonical
The good ones are separately a huge pile of code, and separately a small subpile of code with a big security impact.
score 1 · canonical
That’s not what I’m saying at all. It’s tricky because we’re not used to write real SQL when using query builders, but as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
And no, builders are not safer than writing SQL short circuits or CTEs
score 1 · canonical
Speak for yourself. I quite frequently write ad-hoc queries (yes writing the queries, not asking a fucking robot in 8 iterations of increasingly verbose detail to write it for me). That works perfectly well because I know what I want at the time I’m writing the query.
That isn’t at all the same thing as using a query builder to allow the application to be able to filter on arbitrary columns, or complex value types, or have conditional conditions, or conditional columns returned, or any number of other dynamic aspects that are literally the reason for query builders to exist.
Ok fuck it, I’ll bite. What’s the “trivial, pure SQL” to take a variable length array/list/set/tuple/<whatever structure your language uses for a bunch of values>, and check if a column is equal to one of them.
i.e convert
['foo', 'bar', 'baz']into... WHERE col IN ('foo', 'bar', 'baz');.score 1 · canonical
I do it with
col = ANY($1), and $1is bound as a single valueARRAY[]on the app side (in Go I usepq.Arrayfromlib/pqwhich does this)ANYis easier to handle thanINin this kind of queriesscore 1 · canonical
I guess that’s great if your DB supports an array type, and supports it for ANY(), and the language bindings/driver have support for binding arrays natively.
score 1 · canonical