Repeat until you've no more single language single tier programmers...
Create Proc dbo.uspDoDatabaseAccessHere as
Begin
Select MiddleTierObjectRelationalObsessivesFirstName,
dbo.ufnShootThem (IDX)
From dbo.Team
End
No secret to stopping XSS and SQL injection attacks
SQL injection attacks and cross-site scripting exploits just won't die. The most recent and high-profile incident was a mass webpage attack on more than 100,000 pages, which included victims as diverse as The Wall Street Journal, TomTom, and the UK's Strathclyde police. There was a teetering stack of exploits involved in this …
-
Wednesday 23rd June 2010 20:52 GMT Steven Knox
Sanitized.
"Protecting against this type of attack isn't simply a case of "sanitizing" the single-quotes, as this excludes valid names such as "Brer O'Hare", in which a quote is a perfectly valid character."
Yes it is. "Sanitizing" does not mean "removing". It means "making clean" (in this case, you might say "making sure the data's in an acceptable format"). And that is exactly what you have to do to protect against this specific type of attack.
-
Thursday 24th June 2010 08:15 GMT Lou Gosselin
Clients don't really care about security.
Businesses can blame the developers, and that's fair since they coded the buggy things, but truth of the matter is that the developers have no incentive for finding and fixing exploits. Clients don't care about security faults, not until something blows up. As often as not, they won't even pay for proper testing.
I've marketed my security skills for years now, but no one has ever inquired about how they can make their systems secure when I'm on the job. It's always the same story, get the job done as quickly and cheaply as possible.
In this environment, it's no surprise that bugs aren't getting fixed.
-
Wednesday 23rd June 2010 21:55 GMT Anonymous Coward
A couple of suggestions
1) Don't split the development by tier - get developers who can implement every part of the stack and understand the interactions between each component. Split by groups of web pages (or forms, or whatever) with related functionality. Developers can write the page, the calls to underlying logic layers and calls to the database from there.
2) Never construct ad-hoc SQL statements. Use parameterised stored procedures, and use the provided underlying object model to build the calls (rather than a string such as "EXEC MySP '%1', '%2', '%3'").
3) In my mind, unit tests are good for regression testing - proving a bug fix or new feature does not modify existing, expected behaviour. But I've seen developers implement things, or perform a bug fix and declare it works because the unit tests pass. There is no substitute for proper end to end testing in the first case by the implementor, and then more thoroughly by a QA team (if you have that luxury).
4) XSS - if you are spitting back to the browser anything which has been entered by the user via a post, or taken as a query parameter (or any other source of external data for that matter), always pass it through a HTML encoder so it will not be executable.
That's what initially came to mind reading the article. Look forward to recommendations from others with more/different experience.
-
Wednesday 23rd June 2010 21:57 GMT pan2008
Stored procedures!!!
As a DBA I insist on everyone using stored procedures to access the database. Stored procs offer additional security because of extra security settings, the application database user doesn't even have read or write access to the database, only permission to execute stored procs. The tester would be anaware of this, they are nornally not technical or trained hackers. It's DBA's and developers fault. Use stored procs, I think even mySQL offers stored procs in the latest version!
-
Thursday 24th June 2010 00:01 GMT Spamfast
Spamfast
We shouldn't be constructing DB queries by concatenating parameter strings onto constant strings containing SQL statements. The SQL query should be preprepared with parameter placeholders and then have the variables containing the data from the user bound to it. This not only does away with the risk of the sanitization not being thorough enough but is also generally quicker.
-
Thursday 24th June 2010 00:01 GMT Charlie Clark
Really?
"Protecting against this type of attack isn't simply a case of "sanitizing" the single-quotes, as this excludes valid names such as "Brer O'Hare", in which a quote is a perfectly valid character."
Wow - back to 1998 are we?
Statement and parameters should always be passed separately to the database. Not that this guarantees 100% security as the database still has to sanitise the parameters but this process is a damn sight easier to isolate and test on a per database basis. This just leaves the application developer to worry about all the other possible vectors (shell access, XSS). So, roll on automated security testing
-
Thursday 24th June 2010 00:05 GMT Richard IV
Don't forget user permissions
Will the website ever need to do a table drop? Probably not - so why give the user it's accessing the DB through permission to do so? This kind of thing is the next step down from just running everything as sa.
Don't string build - this includes DB-side via something like executesql. There's nearly no excuse. Parameterised SQL or stored procs will do the job just as well, and you get an awful lot of the sanitisation thrown in for free.
XSS is flipping hard to deal with when working on legacy code! Add a "Does this look like it's OK" function and call it before you do any other processing. There will always be more gaps if you instead try for a "Does this look dodgy" function.
Alternatively, institute a "no Irish, no yahoos with multi-barrelled surnames" rule on your site. There can't be too many of them ;)
-
Thursday 24th June 2010 08:32 GMT John Smith 19
@RichardIV
"Don't forget user permissions "
You sir get the "Chief Programmer Team" award for solving the problem in the simplest, most general and highest level way *first*.
0 lines of code written. Security significantly improved from day 1. Box in the user at the *highest* level.
Seriously I like the idea of remote managing web sites from home as much as anyone but allowing casual visitors to a site and expose *that* level of power to them seems like asking for trouble.
"XSS is flipping hard to deal with when working on legacy code! Add a "Does this look like it's OK" function "
Subtle point. Many developers would think them more or less synonymous. I sense management types whining about "But it *might* reject legitimate users, and loose us business". This is where you need to devise examples of how tough it would be to *be* a
legitimate string which wasn't designed to hack the site and give them some numbers about what it would cost if the site crashed.
"Alternatively, institute a "no Irish, no yahoos with multi-barrelled surnames" rule on your site. There can't be too many of them ;)"
This *might* be a bit more contentious. Mostly from the population of the Irish Republic, about half that of Norther Ireland and all those merkins of such descent (at least half of Boston for a start).
.
-
-
Thursday 24th June 2010 08:15 GMT Bruno Girin
Bind variables anyone?
Well, there are a couple of things here:
First you should always validate your input on the server side. Validating in Javascript is a way to improve the UI, it does not replace server validation.
Second, most database engines now offer a very simple way to sanitise parameters by using bind variables. That's why if you are using Java for example, you should always use java.sql. PreparedStatement rather than java.sql.Statement: create the SQL statement with place-holders for the parameters, then bind all parameters: the DBMS will ensure that everything is escaped properly. The other advantage of using bind variables is that it enables the DBMS to optimise execution by fetching pre-compiled statements because the statement itself is independent of its parameters. I mean it's not every day that you have a mechanism that improves security, improves performance, is easy to use and actually results in code that's easier to read!
I hear the argument of the DBAs who say "I only want stored procedures" but my issue with stored procedures is that they are not portable so it's a way to lock yourself in with a given vendor and they are not easy to write or debug. So, when I have the choice, I'd rather have SQL statements that use bind variables rather than stored procedures.
The frustrating thing though is how often I meet development teams that have absolutely no idea about that sort of issues. I got a very surprised group of developers not so long ago when we did some penetration testing on their system and the test came back with SQL injection issues. They had never heard of bind variables either. And they also managed to include a regexp injection bug in there, first time I had ever seen that. It was quite funny to see the results when entering a * or any other funny regexp characters into that field though.
-
Thursday 24th June 2010 09:19 GMT Kristian B
PHP's MySQL / MySQLi interfaces are safer
That wouldn't work with PHP's MySQL or MySQLi interfaces as they only allow execution of a single query, not multiple queries separated by semicolons. So in that example it would only be possible to read data, not delete/update/insert anything.
I'm surprised so many high profile sites make such basic mistakes and harbour vulnerabilities like this!
-
Thursday 24th June 2010 09:19 GMT NB
jebus jonesy
seriously, who the fuck gives the cgi the privileges necessary to drop tables? A web form cgi should have select and insert only and possibly update, only in very rare cases do you want to allow a user to be able to delete data, follow that up with server side validation and always use placeholders (perls DBI module is particularly good for this), hell if it's a big app then seriously consider using an ORM.
I find it pretty galling that anyone suffers from SQL injections any more considering how long they've been around for and how easy they are to avoid by simply implementing a teensy bit of good practice.
-
Thursday 24th June 2010 11:42 GMT Seanmon
Sigh.
Having been ignored for years on this subject, I've pretty much given up. You'd be astonished how many so-called professional web devs have never even *heard* of SQL injection, and how many of them run their app servers using the "sa" account. But:
1) A lot of vulns are caused by a "that'll never happen to us" attitude in management, who don't understand the issues and don't want to bill for the time taken to do proper testing
2) A lot of SQL attacks are a lot sneakier than the one given in the article. See http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx
-
Thursday 24th June 2010 13:56 GMT Richard IV
Yes, it's depressing
I suspect that at least part of the issue is the number of Web Development books that are geared to getting the devs up and running *quickly*. The annoying bit is that they always waste a chapter at the start of the book telling you how to download and install things, when 90% of it could be replaced with "here's a few URLs to grab what you'll need".
Would a couple of extra pages on creating a database user with lower permissions than "sa" hurt THAT much??? Even the more thorough books simply put caveats "Don't do this in a production environment" all over the place instead of telling or giving you a clue what to do in such an environment.
Trying to add security on later is at least a billion times harder than adding it in from the start. I can't help but think of The Dude's plank/chair security efforts. This may be why it gets relegated to "expert level" ie ignore unless your job/rug depends on it.
-
-
-
Thursday 24th June 2010 12:58 GMT Stephen Byrne
@Bruno Girin
"I hear the argument of the DBAs who say "I only want stored procedures" but my issue with stored procedures is that they are not portable so it's a way to lock yourself in with a given vendor and they are not easy to write or debug. So, when I have the choice, I'd rather have SQL statements that use bind variables rather than stored procedures."
1.If your T-SQL won't run on Oracle or your PL-SQL won't run on mySQL then it doesn't make a difference if it's being constructed dynamically or executed in a Stored Procedure - Unless it's pure ANSI, SQL iteslf isn't ncessarily portable.
2. Secondly, an SP is only as hard to write/debug as its underlying SQL, also see point 1 above.
3. "Bind Parameters" can work with SPs too, but will not protect against every SQL injection type.
4. You also do need to implement Richard IV's suggestion, do not give the ability for the data layer user to drop or delete anything.
This topic is closed for new posts.
Biting the hand that feeds IT
