Bobby Tables

We have an API for that. It's called prepared statements.

We also have a lot of other APIs that try and do away with SQL, and fail in one of two ways. The first is people write code like personDAO.fetchAll().toList().size() - admittedly you can make the same mistake with SQL but I haven't noticed this quite as much. The second way is that SQL is a very powerful language once you combine cursors, grouping, inline views etc. and I haven't yet met an ORM libary that can come close. So you're back to fetchAll() and do the work on the web server if you think SQL is a scary language that you don't want to touch.

It's wrong to say sql can be replaced with "an api", it does not have the same flexibility, power, performance, etc. And would drastically slow down your development. Also, what is "the api" going to use? Text files?

It's better to teach people to practice safe SQL than to require abstinence.

For example, in Java

    // IMPROPER
    PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM users where email = '" + email + '"'
    );
    statement.execute();

    // PROPER
    PreparedStatement statement = connection.prepareStatement(
        "SELECT * FROM users WHERE email = ?"
    );
    statement.setString(1, email);
    statement.execute();

---

In contrast, Scala has more sophisticated facilities than Java. I contribute to https://github.com/lucidsoftware/relate where the example would simply be

    // GOOD
    sql"SELECT * FROM users WHERE email = $email".asSeq[User]()

You can keep the full power of SQL (index hints, hand-tuned locking, DBMS-specific features), without constantly fighting the propensity towards SQL injection.

(Actually, using an API to build SQL, HTML, and XML, instead of doing string substitution, is exactly the right way to prevent ___ injection, but that's not quite what this says.)

Turtles all the way down. At some point you have to use some sort of data access language.

Providing an API instead of using SQL queries only works if you have a fairly limited number of different procedures to be called and data structures to be returned. Otherwise you'll end up with a combinatorial explosion of API methods and input values.

If you view your SQL database as mere data storage that approach could be valid. If however, you're working with complex relational models and data processing providing an API is a pointless endeavour.

I've seen (legitimate, mind you) SQL queries which were more than a hundred lines long and in turn called multiple additional embedded queries. Some of those queries used groups and window functions alongside with normal tabular data. Good luck with providing that as an API!

[0] http://impredicative.com/ur/

[1] http://blog.cleancoder.com/uncle-bob/2017/01/11/TheDarkPath....

Meanwhile, in the real world, every interface/API/protocol/whatever has the potential for being exploited to do things that weren't intended. You may as well say "stop using computers!"

Isn't this similar to what frameworks and libraries such as Jooq do? They provide data structures and function calls where you pass in the user inputs, and the library will safely construct/execute prepared statements. However, I still think of them as being SQL based tools. Is this what Bob is recommending, or does he have something completely different in mind?

Not to mention your app will be extremely inflexible and likely slow beyond all hell. This is exactly what was done at my current work. The query overhead of the ORM system is 10x slower than the actual runtime of the query. 10 times slower. For overhead. Unnecessary overhead. For an engine that is completely inflexible, unusable, and increases development time by 2-10x (Doctrine 2 ORM if you're curious). Really, this is the worst advice I've ever seen on HN. People have been creating such systems as the author describes for years and they have all failed in comparison to SQL. SQL prepared statements, on the other hand, are available to all languages and prevent injections. Other than specialized systems or DBs like Linq and Datomic, the only decent, solid relational database access systems I've ever seen in my life all relied on SQL.

I really can't even imagine what the author is referring to at this point. Should I build out my own Linq or Datomic like layer for every app? Should I use an ORM with the right architecture that hopefully won't slow my app 10x with its overhead? Should I just write a new DB from scratch? The article says to build an API. Well how is said API supposed to access the DB? Clearly, the author hasn't thought this out at all.

In this articles defense something SHOULD have been done instead of all the books that came out of their years that start off teaching sql injection and then barely mention injection near the end. It is a bit silly we still hear of this problem and the ongoing amazon s3 open links. The solution is typically some start up but the original software should have fixed its own problem, why did we end up treating database queries as low level and as powerful as letting users toss in assembly code.

That literally describes all of 'NoSQL'. Everyone is adding SQL back into their nosql systems, because it's insanely useful. Since anyone remotely interested in databases knows this, I can only assume this is a kind of weird joke.

The safe/unsafe example is obvious to me, and I don't even know Ruby. The unsafe cases have parameters, the others have not (they just build a string). And anyone who knows the basics of injection should know that building a string to be evaluated is naturally unsafe and should be done very cautiously.

The author makes it as some obstacle that is impossible to overcome, like you have to know some arcane symbols and magic. These things are basic operators. They are similar in most languages and any professional programmer should have a pretty good intuition on the matter.

Here is the reasoning :

User.where("email = '#{email}'") : unsafe / the variable is between quotes, it should be a red flag

User.where("email = '%{email}'" % { email: email }) : unsafe / again, a quoted variable, bad. The "%" operator looks like a string manipulation operator rather than a argument.

User.where(email: email) : safe / uhm... looks like a language idiom, need to look that up

User.where("email = ?", email) : safe / a common way of doing prepared statements, seems OK. Unlike with the "%", the "," seems to imply that "where" is going to process the arguments, which is good.

User.where("email = :email", email: email) : safe / another common way of prepared statements, again, the "," implies several arguments.

What happen here is that too many people decide to switch off their brains. The "unsafe" forms are a relic, but some guy do it that way because it is how it used to be done, and some other guy copy because it "works", then another, then another, etc... No one in the chain seems to question the practice, even though safer and more convenient forms exist. Having a new API won't help, because if people are not even bothered to learn the safe way of doing SQL, there is no way they will use that API.

And sure, hiring inexperienced programmers, not giving them proper training, and making sure they can't take the time to actually understand what they are doing doesn't help...

Having a textual representation (with placeholders, of course) has some advantages I wouldn't want to give up:

* learn once, use it everywhere

* it's independent of the host language

* it's a reification of a query or an action, which is very useful for logs (like slow query log), debugging and the likes

http://blog.cleancoder.com/uncle-bob/2017/10/04/CodeIsNotThe...