0

Ive searched all over the web for a simple solution to my problem, but I find it weird that no one has some up with a way to "get a correct connection string if the password contains non-alphanumeric characters".

My problem:
 I have a user which has a password containing one or more of these characters:

` ~ ! @ # $ % ^ & * ( ) _ + - = { } | \ : " ; ' < > ? , . /

since the connectionstring format is "KEY=VALUE;KEY2=VALUE2" it turns out to be a problem if the password contains a semi-colon of course.

So I did a little research and found these "connectionstring-rules"

  • All blank characters, except those placed within a value or within quotation marks, are ignored

  • Blank characters will though affect connection pooling mechanism, pooled connections must have the exact same connection string

  • If a semicolon (;) is part of a value it must be delimited by quotation marks (")

  • Use a single-quote (') if the value begins with a double-quote (")

  • Conversely, use the double quote (") if the value begins with a single quote (')

  • No escape sequences are supported

  • The value type is not relevant

  • Names are case iNsEnSiTiVe

  • If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the last occurrence is used

  • However, if the provider keyword occurs multiple times in the string, the first occurrence is used.

  • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.

  • If a value has preceding or trailing spaces it must be enclosed in single- or double quotes, ie Keyword=" value ", else the spaces are removed.

I then read a bunch of thread where people are trying to implement the above mentioned things into their "Format connectionstring method", but it seemed like even more scenarios came to light when they began.

My question is then:

Is there someone out there who has made a "FormatConnectionstring" method to use in a connectionstring - or am I doing something completely wrong here and my problem really exists elsewhere?

Sayse
  • 42,633
  • 14
  • 77
  • 146
Thomas
  • 11
  • 3
  • Have you tried RegEx? – Arijit Mukherjee Jun 05 '14 at 06:31
  • I have thought about it yes, but haven't come up with a solution since there are many different types of scenarios (i think) – Thomas Jun 05 '14 at 06:34
  • What different scenario you can think of... RegEx works for all – Arijit Mukherjee Jun 05 '14 at 06:35
  • I just think its weird that no one has posted a method or something to take care of this problem.. so either im doing something wrong or the code to fix this problem is way too easy to even bother posting :D – Thomas Jun 05 '14 at 06:35
  • The last two posts in this thread describes it pretty good: http://forums.asp.net/t/1957484.aspx?Passwords+ending+with+semi+colon+as+the+terminal+element+in+connection+strings+ – Thomas Jun 05 '14 at 06:36
  • Doesn't [SqlConnectionStringBuilder](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.aspx) work for you? – Ulugbek Umirov Jun 05 '14 at 06:39
  • How do I use that one Ulugbek? I mean, the constructor takes a connstring.. but will the "ConnectionString" property of the SqlConnectionStringBuilder be different from the one I put into the constructor and thereby be a "valid connstring"? – Thomas Jun 05 '14 at 07:23

1 Answers1

1

Use SqlConnectionStringBuilder; either:

  • set properties, read ConnectionString (to create)
  • set ConnectionString, read properties (to parse)
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Dim conn As New SqlConnectionStringBuilder() conn.DataSource = "localhost" conn.UserID = "TestUser" conn.Password = "'hej;dav?,/test$" Turns out to be: Data Source=localhost;User ID=TestUser;Password="'hej;dav?,/test$" it seems to do the trick.. if the password starts with a quote or contains a semicolon, it puts the password within two "" - otherwise it doesnt.. but have you used the SqlConnectionStringBuilder to solve this type of problem or do you just use it by default when you code? – Thomas Jun 05 '14 at 09:19
  • @Thomas normally connection strings are just passed through without touching them, so it isn't something i do every day: but when i *do* need to manipulate or inspect a CS: yes, i always use CSBuilder – Marc Gravell Jun 05 '14 at 09:39