|
In most applications requiring database access, two common issues are how to store and protect connection strings, and how to build and manage them programmatically.
July 26, 2005
URL:http://www.drdobbs.com/managing-connection-strings/184406136
To connect to a database, you need a connection string. Typically made of semicolon-separated pairs of names and values, a connection string contains settings and information for the database runtime. Canonical information packed into a connection string includes the name of the database, location of the server, and user credentials. Other more operational information, such as connection timeout and connection pooling settings, can be specified too.
In many enterprise applications, the usage of connection strings is related to a couple of issues: how to store and protect them, how to build and manage them programmatically. The .NET Framework 2.0 provides excellent answers to both issues in the form of ad hoc classes and command-line tools.
It is best practice to keep the connection string out of the compiled code so that it can be changed at will without recompiling and redeploying the application. Many .NET applications save their connection strings as clear text in the configuration file. Although other storage options exist, this is seemingly the one that weds ease of use with ease of deployment. As we’ll see in a future article, in the .NET Framework 2.0, you can encrypt some portions of the configuration file so that any stored connection string appears scrambled and impossible to read and examine for a human. Finding a convenient location for storing the connection string, is actually your second problem, though. The first problem is determining if you have to consider the connection string just as a constant to read from somewhere or a part of your application to build dynamically.
In some cases, in fact, you need to construct it based on user input—for example, when retrieving user ID and password information from a dialog box. In ADO.NET 1.x, you can only build the string by blindly concatenating any name/value pairs. There are two major drawbacks with this technique. One is the use of wrong keywords, which is caught only when the application goes under testing. More than the lack of compile-time check, a blind pair concatenation leaves room for undesired data injections to attach users to a different database or change the final goal of the connection. Any measures to fend off injections and check the syntax should be manually coded, resulting in a specialized builder class—just like the brand new connection string builder classes you find in ADO.NET 2.0.
All default data providers support connection string builders in a guise that perfectly applies to the underneath provider. The following code snippet builds and displays a connection string for SQL Server:
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = serverName; builder.UserID = userid; builder.Password = pswd; NewConnString.Text = builder.ConnectionString;
By using connection string builders, you gain a lot in security because you dramatically reduce injection. Connection string builders exist for all the supported managed providers and expose easy-to-remember properties for all the keywords recognized by the database. These properties show up through Visual Studio .NET 2005 IntelliSense, making it a snap to build a connection string programmatically.
Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant
based in Rome, Italy. Dino is a contributing editor to Windows Developer
Network and MSDN Magazine, and the author of several books for Microsoft
Press including Building Web Solutions with ASP.NET and ADO.NET
and Applied XML Programming for .NET. Contact Dino at [email protected].