Bobby Tables

http://blog.cleancoder.com/uncle-bob/2017/12/03/BobbyTables.html

by nikbackm • 9 years ago

33 46 9 years ago

blog.cleancoder.com

wrs• 9 years ago

This is a brilliant suggestion! We should also stop using HTML to prevent HTML injection, and stop using XML to prevent XML injection, and stop using the stack to prevent stack overflow.

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

whack• 9 years ago

"What would replace SQL? An API of course! And NOT an API that uses a textual language. Instead, an API that uses an appropriate set of data structures and function calls to access the necessary data."

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?

Areading314• 9 years ago

Is this a joke? I've never met someone who had a hard time with safe query parameterization.

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.

lostmsu• 9 years ago

Last place I worked at had that problem (code injection) applied a lot to bash scripting.

The problem was solved for SQL after libraries that do parametrized query building gained popularity.

labster• 9 years ago

I've never met a programmer who has a problem with learning parameterized SQL queries. But I've met plenty of old code that does, created before this sort of stuff was taught. In my experience, it's mostly the legacy code which has the greatest risks.

jgh• 9 years ago

I feel like this is a blog post from 2003 or something, and the audience is PHP developers who are writing inline SQL statements.

red_admiral3• 9 years ago

Also, what is "the api" going to use? Text files?

A NoSQL datastore, of course :)

meritt3• 9 years ago

I was considering purchasing this author's books (Clean Coder & Clean Code) but after reading this unintelligible garbage, I'll pass.

tofflos• 9 years ago

I liked Clean Code. Give it a shot.

extrememacaroni2• 9 years ago

This article has nothing to do with the books.

meritt3• 9 years ago

It's by the same author which gives insight into his programming mindset and critical thinking ability.

pkrumins• 9 years ago

Question to fellow coders:

Can anyone comment how, if at all, these books are different from classics in the field, such as The Practice of Programming, The Elements of Programming Style, The Pragmatic Programmer, or Complete Complete?

I can't think of anything new that another book on writing good code could say, that hasn't been said already.

That's why I haven't looked at these books yet. It feels to me these books are like another book about Newtonian mechanics, there just can't be anything new.

Thoughts?

meritt3• 9 years ago

Some quick research led to a number of threads that were fairly critical of this author: "his writings are only here to promote himself, his books and his consultancy" and "a windbag" and "self-aggrandizing".

https://news.ycombinator.com/item?id=14664210

https://www.reddit.com/r/programming/comments/mpusp/9_things...

https://www.reddit.com/r/programming/comments/3q5n9q/just_fi...

Double_a_922• 9 years ago

"Clean Code" is quite good, "The Clean Coder" however is just a collection of condescending stories telling you how to be "professional".

RmDen• 9 years ago

is he trolling or what? You want an API.. it already exists.. Stored Procs, parameterized queries or ORMs which will use parameters........

Double_a_922• 9 years ago

Just another senile moment of our beloved uncle...

BjoernKW• 9 years ago

What would replace SQL? An API of course!

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!

walshemj• 9 years ago

Only a Hundred? I have seen some that where over a 1000 in BT's CSS system

skywhopper• 9 years ago

This is a really poor article, that doesn't give reasonable advice. SQL injection attacks are a big risk, for sure. But SQL persists because it's incredibly useful. "Stop using SQL" is completely pointless advice.

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!"

catnaroek3• 9 years ago

I know of at least one language[0] that makes SQL injection impossible, by simply type-checking SQL alongside with the rest of your application, but I'm not sure Uncle Bob would like using it[1].

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

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

Shoothe3• 9 years ago

For anyone interested in how it looks like: http://impredicative.com/ur/todomvc/todomvc_db.ur.html

It seems DELETE FROM requires WHERE... Good idea :)

catnaroek3• 9 years ago

Far more important IMO are the following features:

(0) The type checker ensures that database and application types are synchronized. It's literally impossible to query inexistent tables, inexistent fields, or assume they have a type that they in fact don't have.

(1) Tables can be private to a module, just like everything else in an ML dialect. This allows you to establish “table invariants” that other modules cannot possibly break. If this still sounds too abstract, here's a concrete benefit: You can dernormalize private tables, without risking other modules putting them in an inconsistent state. If I ever go back to programming ERP systems, I would love to do it in Ur/Web.

paulddraper• 9 years ago

Proper SQL parameterization is a great example of where library/language design has a lot to offer.

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();

The first code is easier and simpler than the second code -- especially if have lots of parameters, or if your queries start mixing arbitrary data and variable parts of the SQL. So people are sometimes going to write the first code.

---

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]()

It's just so easy that no one is ever even _tempted_ to do the wrong thing. Use developers' laziness to your advantage, not your detriment :)

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

rhizome31• 9 years ago

Django ORM is also less error-prone than the Rails example shown:

    User.objects.get(email=email_input)

Raw Python DB-API, on the other hand, can easily be misused, as I've see on several occasions.

mangecoeur• 9 years ago

What would replace SQL? An API of course!

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.

red_admiral3• 9 years ago

If I could downvote topics, this would get one.

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.

k__• 9 years ago

You're right. Most ORMs are rather bad.

I like query builders tho, so I don't have to fiddle around with SQL strings.

Shoothe3• 9 years ago

Couldn't agree more. Just recently I've used SQL to write a "lock and fetch" data query in one relatively simple instruction reducing the application logic complexity considerably.

By the way can anyone recommend a good book on advanced SQL (not a specific DBMS)?

red_admiral3• 9 years ago

There was a HN topic on this about a year ago: https://news.ycombinator.com/item?id=13417326

The problem with "advanced SQL, not a speficic DBMS" is the more advanced you get, the less overlap there is between different dialects. MariaDB/MySQL for example didn't support WITH until the 10.x versions (WITH RECURSIVE was added in 10.2.2 according to the site) and it still needs an explicit alias for SELECT ... FROM (SELECT ...) style constructions. SQLite can't do RIGHT/FULL OUTER JOIN etc.

Karwin's SQL Antipatterns is a good book for a "second look at SQL" but I wouldn't really call it advanced yet. The manuals for postgres and MariaDB, while written as manuals and not tutorials, contain a lot of useful information and a fair part of it applies to most relational databases.

Performance tuning is even more specialised. Although I've been praised as a consultant before now for simply adding an index to something, my experience is mostly on the MySQL side of things - I don't think it would be hard to beat me in an "advanced postgres" quiz.

flukus• 9 years ago

Prepared statements only get you so far though, they're fine for parameters, but often you run into situations where you do have to build sql manually to be able to order and filter on fields dynamically. Then once part of the query is generated by string concatenation the temptation is always there to just add that one little string parameter. I've seen various mechanisms to order dynamically, but nothing as simple and readable as "if orderBy == 'foo' then sql += 'order by foo'". Dynamic filtering has a few approaches but I've seen a lot of performance issues from them. Most sql optimizers have a much better time if you concatenate the exact query you need in code.

I would argue that 99% of the time such UI's arise from a failure to understand and cater to user workflows, but they still exist in the wild and I don't seem them disappearing any time soon.

mnm1• 9 years ago

"And if you make absolutely certain that no text that crosses that boundary going towards the SQL engine has any SQL in it. Then you will absolutely prevent SQL attacks."

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.

Glide• 9 years ago

What he wants is to stir up some controversy so that an executive is convinced that his engineers are doing things wrong and will bring him in to "fix" things.

His part about pushing that stuff down the stack reads to me like: "read my latest book!"

cdevs• 9 years ago

Clean code and clean architecture are great books. I find sql injection from kids fresh out of college all the time in php but to be honest I really can't tell the difference in that rails code. At work I've build a query helper function that takes a query string and a array of bindkeys/bindvalues and everyone must use this, easy logging, easy security etc...

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.

pmontra• 9 years ago

The unsafe Rails code accepts a string as argument and copies it as is into the SQL code. The safe statement accepts a map and transforms it into SQL code using all the safety features made available by the underlying database driver. It's what your helper function seems to do.

A solution would be banning any string argument, maybe except the inevitable one for fin_by_sql.

The post probably argues for databases exposing directly their inner API. Let's suppose there is a standard. I bet that there would be still ways to write vulnerable code.

dctoedt• 9 years ago

Give the author (a bit of) credit for this much, though: His title seemingly alludes to https://xkcd.com/327/ but he never mentions it, perhaps taking it granted that his readers will be familiar with it. Maybe the title was designed to attract attention; if so, it worked, at least with me.

msangi2• 9 years ago

Wait, I thought that Uncle Bob said that programmers should just be 'professional' and stop being sloppy instead of reaching for tools that can help them

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

perlgeek• 9 years ago

There are APIs around SQL that are pretty good in avoiding SQL injections; for example SQLAlchemy (a Python ORM) allows you watch pretty much any SQL query without writing literal SQL. You can use raw/literal SQL, but it's buried pretty deep in the docs.

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

shapiro92• 9 years ago

There is only blame in this article. I kept scrolling bellow when he mentioned API, hoping for an elaborate answer. Nothing just complaining.

obsession• 9 years ago

Now that we are on the topic of prepared statements, does anyone know how to do "SELECT * FROM WHERE x IN (list of values...)" properly? As far as I know, you cannot do it with prepared statements (as they can only be single values) and you are forced to roll your own escaping mechanism.

otherflavors• 9 years ago

array of "?"s joined with ","

tatersolid• 9 years ago

ANSI SQL and Postgres has had an array type since forever, MSSQL has table variables and types, other SQL databases have similar if not perfectly ANSI standard features (well probably not MySQL)

GuB-42• 9 years ago

Argument by ignorance.

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

Bobby Tables

Related Stories