Feeds

back to article 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 …

COMMENTS

This topic is closed for new posts.
Anonymous Coward

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

0
0
Silver badge
Boffin

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.

1
0
Silver badge

Quite right...

In fact you have to sanitise to make "Brer O'Hare" even work as a name. If you didn't it would close the SQL query term and add Hare' on the end, causing an error.

1
0

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.

1
0
Megaphone

Cost cutting

You also need to look at some companies hiring policies. More and more places are hiring people on the cheap with little skill or experience and dropping them onto these roles which ultimately are coming back to bite them.

There is no replacement for skill and experience.

2
0
Silver badge
Welcome

May I be the first to welcome...

... little Bobby Tables...

http://bobby-tables.com/

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

3
0
Thumb Up

Little Bobby Tables

Ob XKCD (you'll be getting a lot of these) - http://xkcd.com/327/

1
0
Thumb Up

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!

3
1
Silver badge

Prepared statements...

...are exactly as secure and a lot easier to read/write/understand - especially in a hurry.

1
0

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.

2
0
Silver badge
Coat

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

1
0

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 ;)

3
0
Gold badge
Thumb Up

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

.

0
1
Pirate

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.

1
0
Alert

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!

1
1
NB
FAIL

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.

1
0
Unhappy

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

1
0

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.

1
0
Thumb Down

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

1
0
Boffin

strong datatyping

..in my world view it comes first:

1. strong data typing

2. cleansing

3. validation

4. escaping

5. parameterisation

6. stored procedures

PHP example:

$customerID=(int) $_POST["customerID"]; # cast as integer

0
0
This topic is closed for new posts.