SQL injection is not a web problem. If you create SQL queries based on any untrusted (e.g. user) input on any platform, you have to escape/explicitly type your input.

Injection in general is simply a trust problem. If you can trust all inputs fully (hint: you can't, because nobody can), then you will never have an injection attack.

SQL injection is a problem with SQL, which is similar to problems with HTML. SQL was created as human-friendly query languages, it wasn't created to be built from strings in a programming language. Proper database API should be just a bunch of query builder calls and with this API SQL-injection is not possible.

SQL injection is a problem with incompetent developpers. Most languages have simple constructs to make them immune to injections, like parameterized queries.

If you are exposing code to an untrusted, hostile environment (which is pretty much the web), no language that does anything useful will protect you against not caring about security.

Not all queries can be parameterised - I'm not aware of any DBMS that allows for the parametiersation of identifiers (e.g. table and column names) or variadic operators and clauses (e.g. IN() and optional predicate clauses), this is why "Dynamic SQL" is a thing - which comes with the inherent risk of SQL Injection.

There are many reasons to create SQL dynamically, but I can't think of a good reason for the table name to come from the client.

Even if you absolutely need to inject a string in a sql query, sanitizing it is trivial. In .net / MS SQL, a simple x = x.Replace("'","''") does the trick. For any other common data type, strong typing should be sufficient to prevent any injection.

Like LINQ

Exactly.

The point is that if you know the length of some data up-front before starting to parse it, you don't have to inspect the data in any way to see when it ends. This means that you don't need to know what the SQL injection looks like and protect against it, or what JS looks like to sanitise your inputs – the problem does go away to a large extent.

That doesn't make sense.

Obviously nobody is going to be typing length prefixes manually, so our tools are going to do it for us.

Now we're back where we started where you accidentally inline user content as HTML, except now HTML has the added cruft of someone's HN comment solution.

The solution suggested is not-html, a specific thing for web apps, where data is separate.

This doesn't do anything for Bobby DROP TABLE injections, right? The whole thing is a user-supplied slug, there's no source of truth on how long a user's name is. Or am I missing something?

Bobby tables would be considered data. Or should be. And hopefully it would be obvious that it doesn't belong in the code section.

But like you I'm not totally convinced. I think this idea would make it easier for people trying to do the right thing to get it right; but for the blissfully ignorant? Might not help at all. Either way it needs a more flushed out spec.

This absolutely fixes Bobby DROP TABLE. The source of truth on how long the user's name is is just the length of the user-supplied slug.

From the XKCD:

   Robert'); DROP TABLE Students; --

The issue here is that

    '); 

Is being intepreted as the end of a string; it assumes that there will be something like:

    format("SOME_FN('%s');",user_name)

going into SQL, and this fools the system.

SQL solves this already with parameterized queries, and many HTML libraries also solve this in various ways, but if it were instead:

    format("SOME_FN(%d:%s)", len(user_name), user_name)

then there is no value you can put in user_name that will let you escape the function call.

Length prefixes are one way of working this, but only scratch the surface of the issue. As others have pointed out, it's also the fact that the control elements are inline with the data.

    <p:25><script:14>somethingBad()

Will still run somethingBad(). You are at least sandboxed to the containing element though, so restricting certain elements to only appear in parts of the HTML tree could prevent this (e.g. if all scripts were disallowed in BODY then merely constraining user-generated content to the BODY would work; right now you could still get hit by someone including </body> in their content.

>The problem is not length

Oh thank God. I'm going to forward this to my wife.

"Buffer? I don't even know her!"

Ha ha. I'll get my coat.

Droll. But wasn't context unawareness part of the problem too?

Definitely. It's a fatal flaw of PHP, and any SQL library that lets you build queries from concatenated strings.