Hacker Newsnew | comments | show | ask | jobs | submit | nupark's comments login

Could you expand a bit on how parameterized queries are not sufficient for defense against SQL injection (assuming, of course, that developers use the escaping and do not concatenate unescaped data into queries)?

As for them being required -- you can obviously escape queries yourself, but the normative reference for escaping is the target database itself, and reproducing escaping locally in the client brings with it the likelihood of introducing an error in the custom implementation.

-----


Not every "input" to a "query" (using these terms loosely) can be bound as a variable. Simple example: ASC and DESC. There are trickier examples that are still common.

It is a bad idea for applications to implement quoting regimes, and it is a bad idea for frameworks to try to create one-size-fits-all quoting regimes like PHP used to. That doesn't mean it's a bad idea for a framework's e.g. MySQL support to provide the capability of sanitizing MySQL inputs under a common database API.

-----


The problem with that is that almost nobody uses the official database APIs. The official APIs are usually C libraries (e.g. libmysqlclient) but pretty much everybody uses third party wrappers (e.g. Perl DBI, the mysql/mysql2 gem for Ruby, the PHP default MySQL bindings, etc). Few people program against the database in C or C++. It was and is up to the third party API providers to provide easy sanitization APIs, I don't see how MySQL could have changed that situation by providing such APIs themselves.

-----


> You don't need parameterized queries or stored procedures for protection against injection ... There's no need for the database's native wire protocol to explicitly support parameterization to implement this.

This means that every protocol client must implement their own query escaping, rather than relying on the database to provide a single, normative implementation of escaping.

> For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood:

ActiveRecord's use of the non-parameterized APIs has led to a number of escaping/injection issues in the past eg, (http://www.ruby-forum.com/topic/152058, http://gsa.ca.com/vulninfo/vuln.aspx?id=36929, etc).

> If you think stored procedures protect you against SQL injection, consider the following code snippet ...

I believe the original poster was referring to the use of stored procedures as a mechanism for preventing or discouraging unintended direct modification of the database by applications, rather than SQL injection, specifically.

-----


It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.

The examples you've provided of Rails SQLI issues are bad ones:

* The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.

* The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.

-----


It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.

The database is the normative reference on what is and is not a special cased character and how escaping should be implemented. I don't think it's reasonable to assert that escaping is a concern that should be adopted by every framework that might ever talk to a database.

The examples you've provided of Rails SQLI issues are bad ones

I'm sure you could supply some better examples since your focus is in security, and there are a vast number of issues that have arisen in ActiveRecord's escaping (especially in early versions of Rails). These are merely what I quickly found while Googling.

The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.

It's also an interface that was repeatedly and unintentionally used by Rails users to insert unescaped queries in a way that did not immediately appear incorrect, as evidenced by the preponderance of questions on the subject.

The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.

Given that this conversation is occurring in the context of discussing how MySQL's design has led to exactly these types of errors, I think this is an applicable example.

-----


A "vast number of issues"? Huh? Citation needed.

I don't know how to respond to any of these points.

The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?".

Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.

-----


A "vast number of issues"? Huh? Citation needed.

The last time I researched this, I started here:

http://www.google.com/search?sourceid=chrome&ie=UTF-8...

... would up and sources like this:

http://lists.rubyonrails.org/pipermail/rails/2004-December/0...

... and spent a good hour reading the Rails source code. My take away was that, historically, a lack of care coupled with a lack of use of the parameterized APIs left the door open to repeated failures in the implementation to protect against SQL injection.

Things have improved in Rails, but as a historical example of the pitfalls of ignoring/eschewing parameterized query APIs, I believe it to be quite valid.

The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?". Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.

No, you're reframing the discussion. The original poster commented on MySQL's longtime begrudging support for parameterized queries (or lack thereof) as one of the major causes of the prevalence of SQL injection issues.

It seems clear to me that the database -- as the central implementation responsible for parsing queries -- is the best equipped to provide safe, correct string interpolation of those queries.

-----


People that don't know Rails are going to think that you're making pointed critiques of the framework, when in fact you appear to be repeatedly citing examples of people going through extra effort to use the interfaces Rails provides for directly using SQL instead of ActiveRecord's finders.

-----


I'm not offering a "pointed critique of Rails" ([edit] although your framing it as such seems to encourage downvotes into the negatives). I'm demonstrating how the use of non-parameterized queries source of error that has repeatedly resulted in SQL injection issues in ActiveRecord. I have provided three different examples:

- In the first, the API design itself made it non-obvious/easy to directly concatenate strings while -- at a glance -- appearing to be correct.

- In the second, the API's implementation resulted in SQL injection because :limit and :offset arguments were not correctly escaped.

- In the third, the API's documentation and recommended usage (in 2004) encouraged users to use constructs that appeared to provide defense against SQL injection, but in reality, did not.

-----


> It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.

Sure, but it is unreasonable to assert that SQLI defense is not also a database issue.

Using the provided apis in a way that prevents SQLI is a framework concern. Providing apis that make that possible without re-implementing basic things like escaping is a database concern, otherwise you're just asking people to re-solve the same problems so they each get a chance to screw it up.

-----


SQL defense isn't a database issue.

The SQL Injection vulnerability is, "user coerces application into submitting an unexpected and unauthorized query".

Blaming the database for that is like blaming the filesystem for pathname injection vulnerabilities.

It could, after all, send a Unix signal to a calling process when a filename contained "..", and demand that the process re-assert it's desire to really reference a different directory.

We may be spiraling here. Parameterized queries are a good thing. I'm glad MySQL has them. I'm not, however, going to wag a finger at MySQL every time someone finds an SQLI vulnerability in an app that uses MySQL, just because 6-7 years ago they didn't have parameterized queries. For one thing, it's not a useful comment (do you want them to implement parameterized queries... again?); for another, it's not particularly valid architectural point; and finally, it's really boring.

-----


I think we definitely are spiraling. My argument is about how the stance MySQL took on parameterized queries has affected the product's community. I don't disagree with you on the technical issues. Well, except that providing useful tools to handle SQL defense correctly is a database issue. And pathnames are a read herring, as they are not a transport for data manipulation commands.

I guess part of it is that I'm just done watching various problems with MySQL (transaction support, parameterized queries, subqueries, bizarro query optimization) be given a pass as "not quite MySQL's problem". Maybe I'm just taking that out on this thread.

-----


> you can solve it by blindly calling #to_i on your inputs

"You can solve it by blindly calling #h on your outputs"

Ouch. We know that scheme doesn't work too well: it's why we had #h and we now have #html_safe...

-----


Not comparable. h() was a one-sized-fits-all problem to quoting things that could occur anywhere in an HTML DOM. #to_i assures that a bit of syntax that can only ever accept integers is in fact given a real integer.

In case we're misunderstanding each other, I'm also saying that the framework should be doing that, not the caller (as was the case with h()).

-----


Thanks, yes misunderstanding: agree should be a framework protection not something developer should be sprinkling over code.

-----


For example, lots of programs written today still have buffer overflow vulnerabilities. Those are even older. The fix is also very simple: "Check the bounds of your arrays before you use them". That is, again, just telling the developer to not make mistakes.

Actually, the fix isn't to stop making mistakes. The fix is to stop using APIs and/or runtimes that make it easy to make mistakes. Design your APIs such that buffer overflows aren't possible.

The same thing applies to web development and escaping of output data. In a proper API, it should literally not be possible to accidentally write unescaped data to the page.

-----


> Actually, the fix isn't to stop making mistakes.

I know that. I wrote "The fix is also very simple: "Check the bounds of your arrays before you use them"" in order to demonstrate that saying "Sanitize your input" is equally wrong.

-----


The fix IS that simple. Stop using non-sanitizing APIs. Your equated that with saying "stop making mistakes," which is what I objected to. The original poster was right: people need to simply stop using frameworks that do not sanitize their data by default.

-----


All you've done now is shipped off the responsibility to the API. The API is not bug free, I guarantee you. There will still be XSS vulnerabilities, but they'll just live in the API now.

Imagine you decide to fix buffer overflows by switching from C to Java. You're no longer relying on the fact that your code won't have buffer overflows, you're relying on the fact that (1) the JVM code doesn't have buffer overflows and (2) the JVM will catch all your buffer overflows and throw exceptions.

I feel very confident Java has few, if any, bugs. I do not, however, hold that same level of confidence with most APIs in use today. They simply haven't had the time to become as robust.

However, even then, this does not solve all of your issues. There are times when you need to insert tags dynamically, and once you start doing that, there are a million more ways things can go wrong. Sure, you can put another restrictive API in front of that which requires the programmer to take ten lines to describe the single tag she wants, but those APIs will rarely get used because of this.

At the end, for any class of vulnerabilities, there is always a way to entirely eliminate it. Rarely is it the case, however, that you can do so without dramatic losses in other areas.

-----


I honestly have no idea what you're talking about or advocating, if anything.

If you switch to the JVM, a buffer overflow triggers determinate behavior -- it throws an exception rather than writing over the saved return address or the heap.

The attack surface is by it's nature is much smaller than if you rely on programmer correctness in C.

As for inserting tags "dynamically," this sounds like a broken templating system if that means exposing the programmer to fail-fast (instead of fail-safe) escaping APIs.

-----


How many NEW desktop apps have you installed recently?

I don't install new desktop apps very often because my needs are already solved. I do nearly all my work in:

- Mail.app

- Xcode

- Eclipse

- TeXShop

- Terminal

- Chrome (JIRA, and browsing documents-not-webapps)

I'd prefer a native application for JIRA, but I haven't found a decent one yet.

Same is true for mobile, I don't see another space for innovation except improving web performance, integration & so on. Maybe you see some other directions where iPhone & Android can go?

I expect the iOS and Android platforms to continue to evolve their core libraries, providing significant additional functionality and features for use by mobile applications. Eventually that will slow as the mobile platforms mature.

I'd like to say that I expect the development of higher-level common platforms out of web browsers, but outside of (possibly) WebOS, this seems unlikely.

Compared to the web, iOS and Android have comprehensive platform APIs with consistent widgets and user experiences (iOS more than Android when it comes to consistency), support for multiple programming languages and runtimes, immediate access to platform vendor's APIs (playing video doesn't involve an browser vendors arguing over <video>, etc.

-----


Your boss wasn't far off base. A web application is not a replacement for a native application, even if you bundle it up in a UIWebView and sell it through the app store.

My organization works in a variety of languages/runtimes, from ObjC to C to assembly to the JVM. We do web, mobile, server, and OS development -- whatever is needed.

I was in a meeting recently discussing complex image processing we had implemented in C++ using OpenCV. We considered OpenCV and C++ to be the best tools for the specific job at hand, which is why we chose them over the alternatives.

A life-long Flash developer -- who has a heavy investment in Flash remaining relevant in his organization -- chimed in to tell us that Flash can do the same thing, and there was no reason to use C++. In theory, we could have made Flash do our bidding, but it was the wrong tool for the job, and we quite intentionally didn't use it.

Web developers seem to apply the same logic as this Flash developer to the development of native apps. There's a much shorter description for this:

When all you have is a hammer, every problem looks like a nail.

-----


"Lux Living" is a satirically named blog focused on the huge Stuy Town (http://www.stuytown.com/) complex in the East Village.

The full set of articles: http://stuytownluxliving.com/cgi-bin/mt-search.cgi?blog_id=1...

-----


Why not? My servers have no passwords at all -- they either use SSH keys or kerberos.

Once you have access to a local shell, the game is over. Acquiring root is easy, between local exploits or simply piggybacking on (or sniffing) a valid, passworded sudo login.

-----


Once you have access to a local shell, the game is over. Acquiring root is easy, between local exploits

uh, maybe you should work on that.

-----


Work on it how? Acquiring root locally is easy because local exploits are a dime a dozen, and once you have local access you can simply piggyback on valid authentication:

  alias sudo="sudo and do something evil instead"
Once an attacker has local access, you've lost. It's a short leap from there to root. What's more, by requiring that users supply passwords to use sudo, you're practically guaranteeing that they will reuse passwords that thy use elsewhere, exposing your network to further compromise should those passwords be acquired from a compromised server.

-----


do you know how many web/ISP hosting providers are out there that give out non-root SSH access to their customers? or how many do shared PHP hosting that make it easy to run local commands on the server? do you really think all of those servers have been rooted? here are two free shell providers:

http://www.devio.us/ http://sdf.lonestar.org/

if your operating system has no local user security, you should really upgrade it or switch to a different operating system.

-----


do you know how many web/ISP hosting providers are out there that give out non-root SSH access to their customers? or how many do shared PHP hosting that make it easy to run local commands on the server?

Yes. In the late 90s, I worked in security for a company that ran one of the largest (at the time) shared hosting providers.

do you really think all of those servers have been rooted?

If someone was interested enough to capture an administrator's user account, then yes, they have been rooted.

if your operating system has no local user security, you should really upgrade it or switch to a different operating system.

There's no such thing. The local user attack surface is just too large.

As related to sudo, it's silly to think that local OS security can protect you from someone with access to an administrator's shell -- even if using sudo requires a password.

Let me break this down into simple steps:

Scenario 1:

- I acquire access to your password.

- I log in as you, then use your password to sudo to root. Game over.

Scenario 2:

- I acquire access to your SSH key or an active terminal on your server. I do not have your password.

- Logged in as you, I notice that sudo requires your password.

- Since you use zsh, I add the following to your zshrc:

  sudo () { /usr/bin/sudo sh -c "echo \"pwned. Game over.\" && $==*" }
- You log in, and run some innocious sudo command. You enter your password, and then get a surprise:

  nupark@fish:~> sudo ls /var/cores
  Password:
  pwned. Game over.
  tar-3493.core
  nupark@fish:~>
Under what scenario will the password requirement protect you? If the attacker can't take advantage of the situation immediately, they can just as easily leave a landmine that you won't notice until it's too late, if ever.

Once someone has a local shell -- especially an administrator's shell -- the machine is effectively compromised.

Requiring a password for sudo is at best a minor speed bump, not an obstacle, and it results in the propagation of password usage throughout your infrastructure. Many of those passwords, once acquired by an attacher, will likely grant access to other, possibly more critical systems.

-----


Speaking as a former Apple engineer in Core OS, this is a bit too glowing. Serlet wrote quite a bit of code in the NeXT days, much of which does not meet what you would call modern best practices, and even when written was fairly unusual.

However, Serlet, despite no longer writing code, was incredibly stubborn about any changes to his pet implementations, even 10-20 years later. This included large changes, such as updating malloc to a more modern implementation (see Jason Evan's later work on jemalloc), and small changes, such as updating top(1) to better match its Linux and BSD counterparts and implement a standardized library (libtop) for accessing process/host statistics (he wrote top, too).

Serlet was like many technical individuals that graduated into fulltime management; stuck with the last technology they'd worked on, as it was when they last worked on it. Avie was the same with his fixation on Mach, and Mac OS X was honestly poorer for it.

-----


Heh, ok…I'll take that. I find your perspective a bit entertaining coming from Core OS (I was on Server, as it so happens). At least, when I was there Core OS had a reputation as being the more "academic" and "by the book" group. That's compared to Frameworks, which was much more pragmatic than dogmatic. One of the things I admired was how Bertrand managed those conflicting viewpoints with some grace. Of course, things being what they were, I'd expect someone from Core OS to blame Bertrand for being too "old fashioned". At the same time I know plenty of people from Frameworks who were upset that he "adopted new technology too soon"…there was especially a lot of that with respect to libdispatch.

As for malloc, I find it hard to believe that he was stubborn about changes. The one chance I had to sit and have coffee with Bertrand, we discussed what API's we'd most like to rewrite given infinite time/resources. His answer was "malloc". I think rather than being stuck with the last technology, he had a difficult time balancing the pressures of change with the need for consistence, and did so admirably.

Oh, and as for top…when they did change it in early SnowLeopard builds, it broke a whole host of tooling, etc., and they had to revert some changes...

-----


Given where Core OS sits in the software stack (kernel, libc, file systems, etc), being "academic" and "by the book" shouldn't be surprising. :)

As to the rest; my point was that Bertrand was very opposed to change in 'his' code, not that he wasn't open to any change. Regarding malloc, I think Bertrand still thought he'd be best suited to do it, and he has always objected strenuously to others approaching his problems.

Oh, and as for top…when they did change it in early SnowLeopard builds, it broke a whole host of tooling, etc., and they had to revert some changes...

That's pretty normal when tools depend on text output of commands (something Core OS has told everyone not to do, and then they do anyway). The initial conversion to modern top/libtop actually occurred back in the 10.{1,2,3} release cycles, and was subject to a very heavy amount of push-back and compromise with Bertrand about "his" top(1) :)

-----


Oh, I never had a problem with Core OS's academic bent...enjoyed it actually. I was more commenting about the unique sort of tension that existed there, and that managing that tension while also developing kick-ass software is a relatively impressive feat. Also, you're right about the "I can do it better than anyone" attitude regarding fixing things that he wrote...but then my experience was that this was a pervasive attitude at Apple. The only way you could get someone to listen to you was to not only do it better but prove that you had done it better. So, maybe that's not the best attitude for a manager to take, but hardly out of the ordinary (for Apple).

I also feel that as time moved on, Bertrand was making more bad decisions, but that this might have been a consequence of pressure from the top. It may not look like it to the outside observer, but there is a major metamorphosis going on at Apple internally. I think this move will be for the best for Bertrand. As for Apple... * shrug *

...oh, and thanks for bringing back memories of the Core OS "you're doing it wrong, stupid" lessons I got so familiar with. If only we could all be so perfect as Core OS! ;-)

-----


Interesting - in that case I cheer his departure. One of the most important abilities of a great engineer is to be able to throw away his own code (so it can be replaced by something better).

-----


Figuring out a way around the standardized admissions process is the admissions process for nonstandard applicants.

This is no different than anything else in life, from job applications to pitching a client, and the world hasn't changed as much as you seem to imply.

-----


> Figuring out a way around the standardized admissions process is the admissions process.

So true.

-----


Providing a poor quality application doesn't necessarily tell you anything about the audience a proper port would have, it can be significantly damaging to your brand, and is a waste of press attention.

-----


Doesn't necessarily tell you anything. But it can. If you can find for 4-5k that "Yeah, people will buy stuff on mac like our iOS stuff, pull out the whole hog" it makes an excellent business case.

Also, not everything is brand based. For many things, removing the old thing from the store completely obliterates all mention ever made of it (as it wasn't the sort of thing that gets press attention anyhow).

Not everyone is selling apps that make 6-7 figure incomes. Many people are selling multiples that rake in 4-5 and doing quite nicely with that, and with no marketing.

-----

More

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact

Search: