Esoteric Connection String Parsing Problem When Ending the Password with an Apostrophe
If you look at ConnectionStrings.com, the guideance for creating a connection to Microsoft SQL Server through a .NET application using SQL Server Authentication (standard security) is to use a connection string of the form:
Data Source=server;Initial Catalog=database;User Id=userID;Password=password;
I found a little gotcha today. If the password ends in an apostrophe attempting to assign the connection string to the SqlConnection object's ConnectionString property throws an ArgumentException. The password may include an apostrophe within the password and things will run smoothly, but if it ENDs with a password, all hell breaks loose.
Run the following code to repro:
Dim myConnectionString As String = “Data Source=server;Initial Catalog=database;User Id=userID;Password=somePassword'“
'Create the connection object
Dim myConnection As New SqlConnection
myConnection.ConnectionString = myConnectionString
The last line will throw the exception; it doesn't even try to connect to the database, so you can make up values for the connection string properties, just be sure to have the password end with an apostrophe. After some tinkering and testing, the following appears to be a workaround:
Changing the connection string to the following will operate as expected:
Data Source=server;Initial Catalog=database;User Id=userID;Password='somePassword'''
The problem, if you're interested stems from code in the System.Data.Common.DBConnectionString class's ParseInternal() method. When attempting to break down the connection string into its various tokens, the parser doesn't know if the ' at the end of the password is part of the password itself or delimits some quoted text. The inner conflict caused by this ambiguity is resolved by the method by throwing an exception. (I've had jobs before where I wish I could throw an ArgumentException at someone!)