I've done security review, white-box and black-box, for dozens (hundreds?) of applications and sites.
XSS and SQL injection get a lot of press, but know what I find the most common security flaw to be? Leaving debug and test functionality in production code. Either by tampering with POST parameters (isDebug=True) or via spidering a site and finding leftover pages, these are the worst mistakes I see regarding security. If you're including test/debug code, put it in a separate code branch, or at least prepare a checklist for removal prior to launch.
The next most common vulnerability I've seen is simply the ability to bypass security mechanisms by grabbing a URL from the page source. The technical name is 'Forceful Navigation' or 'Forced Browsing' This is something anyone who can read HTML can do, yet I'm surprised by the variety of applications vulnerable. Reviewing a ticket-purchasing site yesterday, I was able to buy tickets for sold-out shows using this method. On previous sites, I was able to skip paying altogether (many, many Paypal sites pass the "purchase complete" URL to paypal via POST parameters - yoink!). You need some sort of back-end statefulness or check to ensure completion, payment, availability, accuracy, etc.
To be frank, I usually let tools like AppScan, BURP proxy, WebScarab, Fortify, FindBugs, or YASCA (depending on budget and source code accessibility) find XSS and SQL injection attacks for me. I'll try the simple stuff on my own, look for obvious holes, but there's too many known combinations to try yourself. I keep a small collection of scripts and test cases for more advanced or recently discovered flaws.
I'm going to stop at 3, because I really could go on all day, I'm losing focus from your question, and nobody wants to read a wall of text.
Some resources for new and seasoned web security gurus:
(ARGH. I can't officially post links yet. Copy/paste. Sorry)
The Open Web Application Security Project (OWASP)
http://www.owasp.org/
Web Security Testing Cookbook
This book is written for auditors, testers, and less for developers. Which is pretty unusual for an O'Reilly book.
websecuritytesting.com
Vulnerability Categorization by Fortify
www.fortify.com/vulncat/
Common Weakness Enumeration (warning: extensive)
nvd.nist.gov/cwe.cfm
Common Attack Pattern Enumeration and Classification (warning: even more extensive)
capec.mitre.org/
Google's Web Security Tutorials
(rather weak)
code.google.com/edu/security/index.html