Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

14 Years of SQL Injection and still the most dangerous vulnerability

mavitunasecurity.com

Post reply on HN
Print view View original

51–58 of 58 posts

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#51

Post by dragonwriter »

eli wrote: post #41
dragonwriter wrote: post #29

Earlier quoted context omitted.

i.e., the problem is that "properly secures access to data" is all too often not part of the definition of "works" applied to systems.

Sure, probably true. But I would assume most people who write insecure code do so because they don't know how to do it the right way, not because "must be secure" wasn't in the requirements doc.

If security (operationalized properly) was part of the requirements against which it was evaluated prior to release, insecure code wouldn't be released.

("Must be secure" is a much higher level requirement than anything that is testable, but a high level requirement is meaningless except to the extent its operationalized into lower-level requirements that are testable -- or analytically provable, but that's even harder.)

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#52

Post by SigmundA »

dragonwriter wrote: post #28
SigmundA wrote: post #14

Isn't this more about using strings to build executing code from untrusted input? This kind of stuff always seems to surface when one language is embedded in another and this tends to be database query languages and one of the most popular is SQL, but it's not limited to it, just look at javascript injection from parsing JSON using eval. I am not seeing injection going away until we stop sending bits of code between…

> Isn't this more about using strings to build executing code from untrusted input? No. Its about lack of care in building executing code from untrusted input. If you took data from the outside world, converted it into x86 machine code, and sent to it another system, you could still have the same problem if you didn't handle the input properly. SQL-by-string-interpolation/concatenation is an easy target because its a…

My point is it's much easier to lack care when using strings. If for example SQL where only exposed as an AST object model to the client language injection would be much harder to accidentally allow. Also you could have another layer of your app actually examine the AST for security issues (no DML statements allowed) or certain tables restricted etc.

We actually use a component in our app the builds an AST from SQL which can then be verified, this is not trivial. Also LINQ builds an AST which is then transformed to SQL statements in LINQ to SQL, this AST can also be examined before executed and prevents injection.

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#53

Post by dragonwriter »

mikeash wrote: post #30
dragonwriter wrote: post #20

Earlier quoted context omitted.

> Occasionally people talk about prepared statements and parameterized queries and such, but usually people just talk about escaping. Occasionally? For many years "preferred prepared statements and use user input, sanitized (via escaping and/or more involved means) or not, only with a very special need that where prepared statements don't do what you need, and then be as restrictive as possible in what you accept" ha…

Lucky you! I've not seen it be so common. Obviously the smart guys who properly understand what's going on are all about parameterized queries, but I still see a ton of other people talking about escaping. To double-check and make sure I'm not just being biased, I did a Google search for "php mysql tutorial" and read through the top five results. Not a single one mentioned parameterized queries . One of the tutorials…

> I did a Google search for "php mysql tutorial"

I have no problem acknowledging that the dominant advice in the domain of PHP MySQL tutorials has been to use escaping, and that that's a serious problem.

Clearly, we were thinking about different scopes.

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#54

Post by mikeash »

astrodust wrote: post #49
mikeash wrote: post #48

Earlier quoted context omitted.

I completely agree. However, the PHP community is still a huge part of the server-side web community, so they're largely representative even if nobody else follows their lead.

Representative of what? No other community follows PHP. If anything, PHP is trying to imitate other languages with varying degrees of success.

Representative of themselves, really. The point being that even if PHP is different from everything else, PHP is big enough that their wacky antics are still significant, and PHP alone is enough to say that escaping parameters in SQL is still widespread.

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#55

Post by mikeash »

dragonwriter wrote: post #53
mikeash wrote: post #30

Earlier quoted context omitted.

Lucky you! I've not seen it be so common. Obviously the smart guys who properly understand what's going on are all about parameterized queries, but I still see a ton of other people talking about escaping. To double-check and make sure I'm not just being biased, I did a Google search for "php mysql tutorial" and read through the top five results. Not a single one mentioned parameterized queries . One of the tutorials…

> I did a Google search for "php mysql tutorial" I have no problem acknowledging that the dominant advice in the domain of PHP MySQL tutorials has been to use escaping, and that that's a serious problem. Clearly, we were thinking about different scopes.

Unfortunately, the scope for many web applications, even big and popular ones, seems closer to mine than yours.

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#56

Post by troels »

praptak wrote: post #33
eru wrote: post #32

Earlier quoted context omitted.

> The number grows exponentially with the complexity of the filter, so the runtime construction of SQL statements is inevitable. You can still construct at runtime. Just let your typesystem handle the distinction between SQL code and data.

Good point, but this would require composable SQL in the typesystem, right? Maybe libraries like SQLAlchemy come close to that (or maybe are already there?) but look how long it took.

In a typical loosely typed language, such as php et al., just keep the query and the data separate. E.g.:

    $params = array();
    $sql = "select * from foobars where 1";
    if (isset($_GET['name'])) {
      $sql .= " and name = :name"
      $params[':name'] = $_GET['name'];
    }
    $stmt = $db->prepare($sql);
    $stmt->execute($params);

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#57

Post by cmsj »

mikeash wrote: post #17
betterunix wrote: post #11

Earlier quoted context omitted.

"this is done by escaping inputs" Something about that sounds wrong. It is as though you are suggesting that we use in-band signaling with a bunch of notch filters to ensure that Cap'n Crunch whistles cannot be used to get free calls. The right answer is out-of-band signaling -- in other words, not constructing queries / command strings / etc. from user inputs. Major SQL databases all support prepared statements; thi…

I think that comment is a succinct summary of why SQL injections are still common, anyway. The answer: because people think "escaping" is the answer. Imagine constructing a function in some other language this way: function square(x) { code = x + "*" + x; return eval(code); } This is obviously wrong. Now, let's say I suggest fixing it with escaping: function square(x) { code = escape(x) + "*" + escape(x); return eval…

Thank you for stating what seems like it ought to be an obvious truth, with such a stark example :)

Re: 14 Years of SQL Injection and still the most dangerous vulnerability

#58

Post by chriswarbo »

chriswarbo wrote: post #36
praptak wrote: post #33

Earlier quoted context omitted.

Good point, but this would require composable SQL in the typesystem, right? Maybe libraries like SQLAlchemy come close to that (or maybe are already there?) but look how long it took.

Type systems tell you when your code is wrong. You don't have to do any SQL manipulation in your type system, you just make sure that SQL and non-SQL data have different types, and carry on as normal. If your code is joining functions together in an unsafe way, the type system will reject it and you have to go back and change it. If it's safe, they type system accepts it and then the code can be compiled into some un…

It would actually be possible to use a type system to compose SQL; we could use this to guarantee there are no syntax errors. For example we could have a types "SqlTable", "SqlQueryType", "SqlWhereCondition", "SqlComparisonOperator", etc.

However, we don't need to do anything nearly so elaborate to stop SQL injection. We just need a type "SqlQuery", since that will be instantly unusable by all string concatenation functions. We then make a concatenation function for SqlQuery values and a "stringToSqlQuery" function (or ".toSqlQuery" method, if you prefer) which converts strings to SqlQuery values by escaping them.

This way, we've turned SQL escaping into a type coercion, so we can only pass the type checker by escaping every string we put in our queries. Note also that it solves the double-escaping problem: since escaped strings have a different type to unescaped strings, we can't send them back through the escape function; ie. "stringToSqlQuery(stringToSqlQuery(foo))" is a type error.

Post reply on HN