Hacker News Reader

Comment by Joeri | Hacker News Reader

The saddest part is that tons of people will be reading this thinking that they're way smarter than that guy, while in fact their sites are wide open to exploitation as well. That last statement probably applies to me too.

Doing web security well is hard, too hard. Everyone gets caught with a security bug sooner or later, even google. It's easy to laugh with silly coding like this, but I blame the technology for allowing SQL injection in the first place. SQL is simply a bad API to be using in a web app.

View on HN

I don't think SQL is a bad API - it's just that every language makes it so difficult to use prepared statements! It shouldn't be harder than:

    sql_query('SELECT * FROM mytable WHERE name = ?', name)

(I'm aware that this defeats the purpose of prepared statements to be reusable - this is just an API that's better than the current methods)

it's just that every language makes it so difficult to use prepared statements

Huh? Most web frameworks use ORMs and discourage you from touching SQL at all.

Which is great until you discover that your needs can't be fulfilled by the ORM and you need/have to use SQL.

ie, in every non-trivial or legacy-supporting use. Such as everything in existence for a non-brand-new company.

A good ORM such as SQLAlchemy can do (nearly) everything that SQL can, it even knows quite some specific options for SQL dialects.

Yes, there might be cases that a very specifically optimized query needs SQL, but those should be the exception not the rule.

The side benefit of prepared statements, perhaps even more importantly if security is not really your concern, is that you don't need a password page that looks like this (this is really the password requirements page for my school):

A password must:

    be 6-8 characters in length.
    contain a non-alphanumeric character such as ( ! ] & * , + =

A password cannot:

... include a dollar sign ( $ ), a single quote ( ‘ ), a double quote ( “ ), a number sign ( # ), a less-than sign ( < ), a question mark ( ? ), a pipe ( | ), a back quote ( ` ), or a backslash ( \ ). ...

I hate those signups. The worst part about them is that they're usually on sites I'm required to sign up for, like a school, work, or corporate service.

If it was a startup web app I was signing up for, I'd send the developer a polite email saying that I didn't feel comfortable putting my data in such a system. Unfortunately, all I can usually do is gripe a little in private.

Fun fact: Etrade won't let you use a period ( . ) in a password. No error or anything, account creation just silently fails.

You don't need one of those anyway, if you hash your passwords like you should.

This is the second time in a few days someone has made the point that web stacks make it hard to use prepared statements†. This is a one-liner in Rails. Does it not work in Python? How hard is it in PHP?

† Which are not a cure-all for SQLI.

I think the problem angle is slightly different: it's too easy to use simple string concatenation for SQL. That works across pretty much every web stack, so it's the path of least resistance to get something working, and many a developer never bothers to learn the proper idiom anew for every framework.

Side note: We're jumping to conclusions by thinking that the javascript is the entire implementation. It's perfectly possible that the server is already safe against SQL injection and the javascript is just an extra line of defense. Maybe the client and server were done by separate programmers and the client programmer wanted to make sure he wouldn't get blamed. It's a government website: nobody in government ever got fired for being too careful. Or maybe the programmer had to do it to satisfy some non-technical bureaucrat who wanted to think that hacking attempts couldn't even reach his server.

I wrote fairly recently about SQL Injection in Python at http://www.simple-talk.com/sql/learn-sql-server/sql-injectio... .

I do not address web stacks directly there, but Python itself will happily let you use prepared statements, it is up to the programmer to take effective steps to prevent SQL Injection.

Very easy in PHP. http://php.net/manual/en/pdo.prepared-statements.php

Except when the database drivers PDO relies on aren't set up by default:

http://news.ycombinator.com/item?id=2376873

Web security is hard, but I don't think its unreasonable to expect that someone you hire understands the basics of how to make a form submission secure, if they don't perhaps they should be flipping burgers instead. There is just no excuse for something so sloppy.

As for SQL being a "bad API" that might be one of the more ridiculous comments I have heard.

About this case specifically, you're right. This person wrote some dumb code. However...

It is indeed unreasonable to expect a junior developer to make form submission secure. Aside from SQL injection there's DoS, MiTM, CSRF, XSS, session fixation, cache poisoning, clickjacking, timing attacks (to detect valid vs invalid values), rainbow table attacks, and many more. Just go through the list of requirements in OWASP ASVS. It's intimidating how much stuff there is to keep track of. We have a dedicated security engineer on our team who reviews all new code, out of necessity.

Also, about SQL being a bad API, I didn't say it was a bad API in general, just bad for the web. SQL is like eval(), it evaluates code from a parsed string. If eval() is bad for the web, SQL is just as bad.

If indeed it was a junior developer then where was the supervision? I take your point on SQL though.

This is a great point. As engineers, it's a great lesson that we need to stop apologizing for our tools and instead contribute to an effort to make them better. The natural language basis of SQL is very handy, but it does not lend itself well to detainting of input. There should be strict segregation until a late phase between what the developer wrote and what the site user wrote.

There is no excuse why programming languages should require the programmer to work around the inherent deficiencies of SQL. Just like the x86-64 architecture introduced the no-execute bit to the mainstream computing world and made the rampant buffer overflow remote code execution exploits of the 1990s into an endangered species, we should look to a technological solution to solve SQL's deficiencies.

Why should we need to trust that thousands of CMS module developers all use, know, and understand detainting of input in their native language? If we want to allow reuse of code, we put our absolute trust into thousands of developers other than ourselves.

It's not like this is an obscure corner-case that the developer forgot. In that case I'd agree with you.

This is a principal issue. Doing client-side parameter validation for security is a stupid idea in every case. Client-side validation is for user convenience only. This has nothing to do with SQL. Security must be implemented server-side. There are no exceptions to this.

See my comment above :)

Reply on news.ycombinator.com