This could have been a useful article if he actually gave an example of what “an API that uses an appropriate set of data structures and function calls to access the necessary data” actually looked like. And provided an explanation about why it doesn’t have the same issues as SQL, why it doesn’t have other issues, and why it’s a suitable replacement for SQL (unlike f.ex ORMs, which only replace a small subset of SQL).
Without that, it isn’t any better than saying “use lizard magic!” We don’t have any basis for what that means, much less whether it actually meets our needs.
Also, is it just me or is this a complete 180 from Bob’s usual stance? He’s always like “if something goes wrong it’s always the programmer’s fault!” but here he’s saying that using SQL leads to bugs.
This year Martin has written articles arguing against not-even-state-of-the-art type systems and against, broadly, the general concept of good tooling. Now he writes an article about a pervasive error in stringly-typed programming that would be trivially impossible with types or several other kinds of improved tooling.
Taken together these articles are not even wrong, they’re incoherent. This article argues that SQL is a fundamentally flawed tool that should have been abandoned in 1998; “Tools are Not the Answer” explicitly says “Tools are fine; but the solution to the software apocalypse is not more tools. The solution is better programming discipline.”
I’m all for changing your mind, but he doesn’t say he’s actually done it and I can’t charitably read that into this latest post.
I don’t think he’s changed his mind. I don’t even think he realizes he’s contradicting himself. I’m just a little surprised and disappointed that he’s being inconsistent. I like mocking him as much as anyone, but I guess I (perhaps naively) assumed he genuinely believed in “testing is everything!” screed. Versus throwing it away the moment he gets mad at SQL.
The problem of SQL isn’t the text interface.
That is the problem of OS’s that failed to provide a reasonable interface between processes (and processors) that was language neutral and at a higher level of abstraction than text.
CORBA was an attempt at doing that and went too complex….. but failed, and then we gave up totally and just went for “stringly typed” interfaces.
That bugger up came from, as he said, “horrific schedule pressure”.
At every stage the industry has taken the “quick way home”.
SQL has many many problems. It’s stringly typed interface is not one of them. That is a failure at the OS level. Have a close look at the why’s and wherefore’s of DBUS for some idea of what is required to address that failure.
CJ Date has for decades catalogued the failures of SQL…. if I was to rant about the failures of SQL, I would simply say read SQL and Relational Theory, 2nd Edition How to Write Accurate SQL Code By Chris Date
In fact, if you want your DB to serve you facts instead of merely opinions, read that book now.
The vulnerability vector he describes has been handled for a long time by RDBMS it’s called ‘prepared statements’ in MySQL, Oracle, DB2, SQL Server & PostgreSQL. I know what he is trying to say but blaming it on the EVIL SQL is just lack of research.
Instead of picking on SQL, stop using string interpolation in your language for SQL statements.
I’ve had an idea for a while and started working on it in Postgres but gave up because I was having trouble with C/some representational issues.
Normally the PostgreSQL pipeline is “take SQL command -> build tree of commands -> optimize”. Turns out your SQL isn’t at all representative of the internal structure of commands, but the command tree is a (at least for modern programming) really straightforward pull-based stream system.
So my idea was to give a way for people to directly provide the actual command tree instead of hoping that their SQL is written in the right way to trigger the right indexes. This would be somewhat easier to type, and easier to compose on our end, while avoiding the traps of building up a “program string” in the client.
The biggest issue is that I was having some trouble figuring out how to inject indexes nicely into this model in a way that wasn’t overly verbose for most commands. But I do think there’s a really valuable space for SQL DBs without the SQL.
Maybe I am being very naive, but isn’t that a solved problem by simply using prepared statements?
Also using an ORM while saying SQL seems a bit strange. I know it’s still SQL, yet I think that “what combinations of question marks, hash marks, parentheses, and percent signs makes a statement vulnerable” is a very related issue. Using prepared statements and passing in values (in Postgres) via $index doesn’t seem to make it likely to make such mistakes.
However, I do see that SQL has downsides, and everyone using ORMs seems to suggest that it would probably make sense to offer an interface that is better suited, because it may even standardize those a bit more and be able to learn something like this in a library and language agnostic manner. It also would prevent one from having to deal with huge limitations these ORMs tend to have when wanting to utilize certain functionalities (PostGIS for example). Since I never tried this and didn’t really think about it deeply, I do not know if that’s a good idea at all though. Maybe someone else thought about this though?
You’re not being naive; prepared statements are one of several ways to make this class of bugs impossible.
ActiveRecord (the ORM used in this post) does not support them. Further, it exposes SQL strings for predicates, so the mistake of the code in the post are certainly possible. But the docs start with a prominent warning and the community is pretty well aware of the danger. In 10+ years of coding on Rails apps (mostly with quality issues), I’ve seen an opportunity for SQL injection maybe twice.