14 Years of SQL Injection and still the most dangerous vulnerability • Hacker News Board

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

#31

Post by mikeash » Thu, Aug 22, 2013, 3:28 PM UTC

clarkevans wrote: post #27

mikeash wrote: post #17

Earlier quoted context omitted.

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…

I should have used properly "construct input" (as suggested by dragon-writer) rather than "escape". If possible, you'd use query parameters or some other reusable and verified mechanism to encode user content. If you're embedding a data stream of one type within another, you still have to manage boundaries to encode content properly. I'd note for database languages, query construction is often two phase, where you bu…

I'll move my reply here, then.

You make good points, and I'm trying to figure out exactly how your points and mine hook together.

I think what it ultimately comes down to is distinguishing between data and code. More specifically, data can contain code, but code should never contain (foreign) data. So escaping in general is fine, but escaping in this context, where you're building a string of code, is no good.

That, I think, is why I'm comfortable with e.g. the escaping done to a string when emitting JSON, but not with escaping a string to put it into an SQL query.

With parameterized queries, you're still ultimately passing the code and data over to the database over some sort of stream (assuming an out-of-process database server), but hopefully that stream is designed as data containing both the parameterized SQL code and the parameters, rather than just SQL code with escaped parameters.

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

#32

Post by eru » Thu, Aug 22, 2013, 3:29 PM UTC

praptak wrote: post #26

SQL is hard to get rid of injections and here's why I think it is so. How would an ideal injection-free application look? I'd imagine a set of unmutable precompiled SQL statements (the code) each controlled by a set of parameters (the data.) No gluing of statements from strings at runtime and the parameters are obviously passed out-of band. You can't forget to escape user-provided data because in this setup the SQL c…

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

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

#33

Post by praptak » Thu, Aug 22, 2013, 3:33 PM UTC

eru wrote: post #32

praptak wrote: post #26

SQL is hard to get rid of injections and here's why I think it is so. How would an ideal injection-free application look? I'd imagine a set of unmutable precompiled SQL statements (the code) each controlled by a set of parameters (the data.) No gluing of statements from strings at runtime and the parameters are obviously passed out-of band. You can't forget to escape user-provided data because in this setup the SQL c…

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

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

#34

Post by chriswarbo » Thu, Aug 22, 2013, 3:39 PM UTC

praptak wrote: post #26

SQL is hard to get rid of injections and here's why I think it is so. How would an ideal injection-free application look? I'd imagine a set of unmutable precompiled SQL statements (the code) each controlled by a set of parameters (the data.) No gluing of statements from strings at runtime and the parameters are obviously passed out-of band. You can't forget to escape user-provided data because in this setup the SQL c…

How about we stop calling everything "strings" (and "ints" too, while we're at it)?

  UserInput readFromPost(HttpParam param) {
    // Read param from POST here
  }
  Collection query(SqlQuery q) {
    // Send query to DB here
  }

  UserInput name = readFromPost('name');
  Collection result = query(
    sqlConcat('INSERT INTO users (name) VALUES (',
              stringToSql(userInputToString(name)),
              ')')
  SqlInt userId = sqlRowLookup(result, 'id')
  sendToBrowser(htmlConcat('You are user number ',
                           sqlIntToString(userId)))

Clearly we can get better APIs than this, but it's not as difficult as you make out to program in a safe and sane way.

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

#35

Post by [deleted] » Thu, Aug 22, 2013, 3:40 PM UTC

mikeash wrote: post #31

clarkevans wrote: post #27

Earlier quoted context omitted.

I should have used properly "construct input" (as suggested by dragon-writer) rather than "escape". If possible, you'd use query parameters or some other reusable and verified mechanism to encode user content. If you're embedding a data stream of one type within another, you still have to manage boundaries to encode content properly. I'd note for database languages, query construction is often two phase, where you bu…

I'll move my reply here, then. You make good points, and I'm trying to figure out exactly how your points and mine hook together. I think what it ultimately comes down to is distinguishing between data and code. More specifically, data can contain code, but code should never contain (foreign) data. So escaping in general is fine, but escaping in this context , where you're building a string of code, is no good. That,…

[deleted]

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

#36

Post by chriswarbo » Thu, Aug 22, 2013, 3:44 PM UTC

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.

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 untyped* language (assembly, Javascript, JVM bytecode, whatever)

* By 'untyped' I mean that the type-system that you were using is no longer enforcing anything. There may be another type system in the compiler target.

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

#37

Post by rpedela » Thu, Aug 22, 2013, 3:50 PM UTC

mikeash wrote: post #31

clarkevans wrote: post #27

Earlier quoted context omitted.

I should have used properly "construct input" (as suggested by dragon-writer) rather than "escape". If possible, you'd use query parameters or some other reusable and verified mechanism to encode user content. If you're embedding a data stream of one type within another, you still have to manage boundaries to encode content properly. I'd note for database languages, query construction is often two phase, where you bu…

I'll move my reply here, then. You make good points, and I'm trying to figure out exactly how your points and mine hook together. I think what it ultimately comes down to is distinguishing between data and code. More specifically, data can contain code, but code should never contain (foreign) data. So escaping in general is fine, but escaping in this context , where you're building a string of code, is no good. That,…

Doesn't the database server just escape the data in the parametrized query? So if you used the database's escape functions directly wouldn't it be the same thing? I guess if you are arguing that developers are more likely to mess it up than the database server then I see your point.

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

#38

Post by worseisworse » Thu, Aug 22, 2013, 3:54 PM UTC

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…

>Any sane programming community would string me up by my thumbs for doing this, and rightly so.

How do you explain shell scripting then? Bash and co. have been 'industry best practice' for decades. They feature all these problems of in-band communication and (attempts at) escaping and nobody seems to consider it a problem.

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

#39

Post by mikeash » Thu, Aug 22, 2013, 3:54 PM UTC

rpedela wrote: post #37

mikeash wrote: post #31

Earlier quoted context omitted.

I'll move my reply here, then. You make good points, and I'm trying to figure out exactly how your points and mine hook together. I think what it ultimately comes down to is distinguishing between data and code. More specifically, data can contain code, but code should never contain (foreign) data. So escaping in general is fine, but escaping in this context , where you're building a string of code, is no good. That,…

Doesn't the database server just escape the data in the parametrized query? So if you used the database's escape functions directly wouldn't it be the same thing? I guess if you are arguing that developers are more likely to mess it up than the database server then I see your point.

I sure hope not. If it does, then I'm going to be very sad, and my opinion of humanity will be ever so slightly lowered.

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

#40

Post by mikeash » Thu, Aug 22, 2013, 3:59 PM UTC

worseisworse wrote: post #38

mikeash wrote: post #17

Earlier quoted context omitted.

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…

>Any sane programming community would string me up by my thumbs for doing this, and rightly so. How do you explain shell scripting then? Bash and co. have been 'industry best practice' for decades. They feature all these problems of in-band communication and (attempts at) escaping and nobody seems to consider it a problem.

Shell scripting is another insane domain where stuff that would be crazy everywhere else is suddenly accepted. Fortunately, they at least recognize that it's usually a bad idea to feed potentially hostile data to local scripts.