May include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection
It won’t be so surprising, but I tell you that SQL Server has some great optimization features. One of these is that it caches query plans. A query plan is a compiled form of what the database engine should do. It stores cached plans in a normalized form, and performs hash lookups when it meets with the same hash to skip compiling the given query, and use an existing execution plan. Marvelous.
Sometimes you need quite a lot of flexibility – let’s say you are dealing with optimal parameters. Optimal parameters tend to result in different query execution plans, thus slower performance. This is the time when dynamic SQL comes into place.
May include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles
Let’s start with the boring definitions: authentication is the act of identifying yourself, while authorization is that one when you gain access to resources, based on your identity.
SQL Server allows two types of authentication methods: Windows authentication allows you to connect SQL Server with an existing Windows account , while SQL Server authentication allows connections from anywhere – as long as you set it up this way (which is a bad idea). You should use Windows Authentication, because this way you make your life easier (don’t have to store passwords in config files to connect), but if it isn’t possible, SQL Server authentication is the way to go.
Now how to build up your authentication model – there’s an easy way to go – connect with a fixed application credential. This way you can control what the app can do in the database server. However, sometimes you need more granularity – let’s say you are interested in who did what. If you connect with a single application credential, you’ll won’t get user-detailed information. Then you should use built-in user accounts.
Dealing with errors is an important part of every application. Web applications aren’t an exception, in the contrary, they should be even better in graceful error handling, because of their huge amount of users.
We have three possible locations of caching errors in an ASP.NET application. The first one is the source of the error itself, by wrapping our code, which is likely to fail, in a try-catch block. This is a very good solution for the problem, since the error gets treated on the spot of its occurrence. You can decide to bubble the error forth, and catch it in the Page_Error event, or even further, in the Application_Error event, defined in global.asax. There’s a fourth opportunity, but it isn’t the best solution: define custom error pages in the customErrors section of your web.config. They are part of the apologize, not the solution for the problem, so you should restrict the use of them. Even worse, when you are on a custom error page, you have no way to get and deal with the error lead you there.
Before writing error-handling code, make sure that you do everything to prevent errors from happening. The best way to do so is to use a massive amount of validation. String.Empty and the Exists method should be very close friends of you.
But if the worse happened, you should fail elegantly, and without showing any inner workings of your application. Hold the detailed error messages to server administrators, and give user-friendly and meaningless errors to the everyday users. Also, you should log the exception (Health monitoring comes handy for this task).
I really don’t know what to think about this one. Microsoft gives the following guidelines: hash and salt passwords, encrypting information. Now this topic is a bit broad, but let’s see it. If you don’t find my post detailed enough, feel free to refer this Patterns & practices article on MSDN.
Our first issue is the connection to a database. The main recommendation is: whenever it’s possible, use Windows Authentication. This has many benefits, including that you don’t need to store authentication information in your application, you don’t need to send this authentication info across the network, etc.
In this section, I’d like to provide a guideline which helps you build a secure website in ASP.NET. The following list is from the Pro ASP.NET 3.5 in C# 2008 book, refer to it for further information.
Never trust user input: use strong validation method when you’re dealing with user input. Whenever possible, grant a white-list of values that are acceptable for the current input.
Never, never use string concatenation for creating SQL statements: really never do it. Use parameters instead, data source controls have natural support for them. In the lower level, every ADO.NET command class supports them either.
Never output user-entered data before validating and encoding it: this one’s barely need any explanation. If you do output that information, you expose your site to serious XSS attacks. To gain an idea about the seriousness of them, check out this video.
Never store sensitive or business logic data in hidden fields: your users aren’t dumb, they can open the source of your site, tamper with it, and send it back to you.
Never store sensitive data in the View State: View State is little more than another hidden field. If you assume that its encrypted, you are wrong. However, you can make sure that it’s the same what you’ve sent to the user by setting EnableViewStateMAC to true.
Enable SSL when using Forms Authentication: no comment, enable SSL if you can.
Protect your cookies: and don’t forget to set timeouts on them.
Before you start using profiles, you should know some limitations of the SqlProfileProvider shipped with ASP.NET. First of all, it can become a performance-bottleneck. Each time you query profile information for a user, the profile API retrieves all of the user profile information from the underlying database (a roundtrip to SQL Server). However, after that you are free to process the requested information as long as you don’t post back your page to the server. When you modify profile information, another roundtrip is taken to the server.
Given these facts, you should store relatively small amount of information in user profiles. Another problem is that this information is serialized as clear text (or binary data/XML) but in any way, it’s not encrypted. So you should never include sensitive data in user profiles. Good news that your custom classes (as long as they are serializable) can be stored in profiles. I’ve kept the worst to the last: all profile information is stored in two cells for each user. In the first, all the names of the properties, in the second, all their values. So it will be very hard to share profile information with a desktop application, for example.