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

How not to protect against SQL injection (view source)

cadw.wales.gov.uk

Post reply on HN
Print view View original

51–60 of 125 posts

Re: How not to protect against SQL injection (view source)

#51

Post by upgrayedd »

uxp wrote: post #41
upgrayedd wrote: post #24

Earlier quoted context omitted.

I don't do webapps, but I'm sure everyone's vulnerable somewhere, if they do. I just dislike the whole "upvote for x" comment that dredges up from Reddit. 'Just lowers the signal-to-noise ratio, and I hate to see things like that creep up on HN.

I wrote an iPhone app with a sqlite backend that was vulnerable to SQLi. Don't think you aren't vulnerable even if your application doesn't touch the internet.

Sorry, I just don't do any apps that touch the internet...just some programming for fun on the side.

The only thing that I've written that could be applied to this is our POS system at the restaurant I work at as a dishwasher and cleaner for. It's in Django though, and the Django project takes care of most issues with that...not that they're really priority #1 security-wise...

Re: How not to protect against SQL injection (view source)

#52

Post by graywh »

moe wrote: post #23
Skalman wrote: post #18

Earlier quoted context omitted.

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.

Re: How not to protect against SQL injection (view source)

#53

Post by pbhjpbhj »

julianc wrote: post #10
ZeroComplete wrote: post #2

I'm going to assume that they have a server-side validation script running and the client side code is just to prevent/explain to mistaken users and if the server-side script every activates they know that someone's being malicious.

Maybe the javascript is intentional, like a honeypot for hackers :)

Yeah, maybe the 100+ validation errors in the markup are like a honeypot for web designers too ... I've not seen 1x1 gifs for a few years now.

And leaking the MS SQL server errors and IIS errors are just adverts for MS (I only did genuine searches, "hotel" got me to an error page).

I'm sure the silly long names are part of the ruse too.

There is much that could be done with this site. Perhaps I could drop them a CV.

Re: How not to protect against SQL injection (view source)

#54

Post by timwiseman »

tptacek wrote: post #29
Skalman wrote: post #18

Earlier quoted context omitted.

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)

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

Re: How not to protect against SQL injection (view source)

#55

Post by tptacek »

danparsonson wrote: post #50
tptacek wrote: post #39

Earlier quoted context omitted.

So, in other words, it's bulletproof if you only use simple queries. In MySQL, for instance, LIMIT and OFFSET have to be integer constants; the wire protocol won't allow you to bind variables to them. Does your SQL engine allow you to parameterize a table name? Can you parameterize columns? What about ASC and DESC? And this is just simple stuff. What about pages with "Advanced Search" that have to implement query bui…

All of those scenarios can be handled in such a way that you're only concatenating known strings rather than user input - e.g. stmt = "SELECT col1 FROM table ORDER BY col2 " + (isDescendingSort ? "DESC" : "ASC") As long as all your user input has been filtered through type checking, enumerations, etc. (aside from parameters), is that not a safe approach?

Of course you can build these queries safely.

Of course you should use prepared statements when possible.

But web devs do have a bad habit of saying "we're safe, we used prepared statements", and then losing their app within 5 minutes because of the code than handles sortable columns in their table views.

Re: How not to protect against SQL injection (view source)

#56

Post by yuhong »

tptacek wrote: post #30
MatthewPhillips wrote: post #9

Since they're using SQL Server (hint is that they are checking for "xp_"), you can get a list of all of their databases with "SELECT name FROM sys.databases", then loop through and drop them. Hope the web login doesn't have drop permissions.

Are they actually vulnerable? How do you know? People have gotten in serious trouble in the UK for "innocuously" testing web apps for SQL problems. Know that in both the UK and the US, you are taking a significant risk by prodding websites like this.

Yea, dropping tables or otherwise destroying their server is a very bad idea. FYI: http://news.ycombinator.com/item?id=2358058

Re: How not to protect against SQL injection (view source)

#57

Post by reemrevnivek »

steve-howard wrote: post #25
Skalman wrote: post #18

Earlier quoted context omitted.

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)

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 dou…

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.

Re: How not to protect against SQL injection (view source)

#58

Post by jrockway »

tptacek wrote: post #30
MatthewPhillips wrote: post #9

Since they're using SQL Server (hint is that they are checking for "xp_"), you can get a list of all of their databases with "SELECT name FROM sys.databases", then loop through and drop them. Hope the web login doesn't have drop permissions.

Are they actually vulnerable? How do you know? People have gotten in serious trouble in the UK for "innocuously" testing web apps for SQL problems. Know that in both the UK and the US, you are taking a significant risk by prodding websites like this.

That's what those 5-euros-a-month VPN services are for, I assume.

Re: How not to protect against SQL injection (view source)

#59

Post by [deleted] »

tptacek wrote: post #39
Devilboy wrote: post #38

Earlier quoted context omitted.

It's bulletproof if you don't use string concatenation in your prepared statements. EDIT: No this doesn't limit you to 'simple queries'! How do you figure that? There are only a VERY small subset of problems you can't solve like this. So small that in 10 years I've only had to do it once and I write SQL Server 5 hours a day. Want to give me an example please?

So, in other words, it's bulletproof if you only use simple queries. In MySQL, for instance, LIMIT and OFFSET have to be integer constants; the wire protocol won't allow you to bind variables to them. Does your SQL engine allow you to parameterize a table name? Can you parameterize columns? What about ASC and DESC? And this is just simple stuff. What about pages with "Advanced Search" that have to implement query bui…

[deleted]

Re: How not to protect against SQL injection (view source)

#60

Post by mixmax »

I just fired off an e-mail to point out that they have a potentially serious security problem and they should get it fixed ASAP.

I see this as a civic duty, and think that this is the kind of action you're required to perform if you see a serious problem. Writing an e-mail takes ten seconds, but the potential damage could well cost serious money.

Post reply on HN