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.
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