Today a small post that I had in mind since some time... a quick-list of how to fortify against SQL Injection (and some more general best practices).
- You can do some javascript, client-side validations to avoid postbacks, but rembember: never, ever trust client-side code and data.
- Check all input data before sending it to your application logic. Parse integer data types into actual integers and validate data with the expected formats.
- Server.HtmlEncode() and Server.HtmlDecode() are vital in web applications, do not forget them.
- You can use a black-list approach for eliminating or escaping undesired characters, but I prefer a white-list approach: regular expressions are cool and not so difficult, use them and if username can only contain letters and numbers, do not allow anything else in.
- Using custom entities, business objects or similar object oriented approaches helps avoiding injections. If you missed validating the new user page age field, when creating a new user entity it's integer age property will throw an exception.
- Never, ever build querystrings without at least using CommandParameters... Stored procedures are fast and more secure, but if you don't want to (or can't) use them, CommandParameters are strong-typed and will avoid injections too.
- Do not use the SA/admin accout from your code! Create a new user, map it only to the application's database, and give him only the specific, minimum rights. Few applications need DROP statements. And if you need to, maybe having two users is the solution (one just for delicate operations and the other for normal SELECT,UPDATE...).
- Just in case everything else fails... use Logs! Log every important operation, every non-trivial method call, every exception... Each day I rely more on logs and less on just debugging to watch for errors or undesired behaviors, because you don't always have the option to debug ;)
I think I'm not letting anything out... but if it happens, I'll update the post. Comments will be appreciated ;)