How to handle MS SQL password with Semicolon and Single quotation in VB.Net Code?

Question

We are writing code in vb.net. I have a case where my MS SQL password contains a semicolon, double quotation and single quotation (ilo;veac'h”alle;nge).

Dim ConnectionString As String = "Server=DT2719MOD; Database=abs2; User Id=TestUserLogon; Password= ilo;veac'h”alle;nge"

Can anyone suggest/provide some light to solve this issue?

Does this answer your question? [How to escape double quotes in as a parameter to an NUnit TestCase?](https://stackoverflow.com/questions/7726420/how-to-escape-double-quotes-in-as-a-parameter-to-an-nunit-testcase) — Igor, Mar 23 '20 at 13:16
I suggest using an [`SqlConnectionStringBuilder`](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=netframework-4.8) and letting it take care of those details. — Andrew Morton, Mar 23 '20 at 13:43
I suggest keeping the password out of source code completely. Put the whole the connection string in an App.Config file where you can encrypt that section. — Joel Coehoorn, Mar 23 '20 at 14:34

BiSaM · Answer 1 · 2020-03-23T14:27:40.190

0

You can try

Dim quote as String = Chr$(34)
Dim semi as String = Chr$(59)
Dim password as String "ilo" & semi & "veac'h" & quote & "alle" & semi & "nge"    

Dim ConnectionString As String = String.Format("Server=DT2719MOD; Database=abs2; User Id=TestUserLogon; Password= {0}", password)

The Chr() function can be found at https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/chr-function

edited Mar 23 '20 at 14:27

answered Mar 23 '20 at 13:56

BiSaM

291
1
16

The issue isn't building the string. The issue is getting the SqlConnection object to parse it correctly. – Joel Coehoorn Mar 23 '20 at 14:35
I just understood the question that the string with the password is not working, because of the double quotes and semicolons. – BiSaM Mar 23 '20 at 14:53

jmcilhinney · Accepted Answer · 2020-03-23T15:11:14.080

You escape a double-quote with another double-quote, both in VB and in a connection string, and you can wrap connection string field values in double-quotes. That means that, to write your connection string literally, you would do this:

Dim ConnectionString As String = "Server=DT2719MOD;Database=abs2;User Id=TestUserLogon;Password=""ilo;veac'h""""alle;nge"""

The password gets wrapped in double-quotes in the connection string so that the semicolon is interpreted literally and they need to be escaped in VB. The double-quote in the password needs to be escaped in the connection string and then both of those need to be escaped in VB too.

Note that, as suggested in the comments, a connection string builder will do that for you, e.g.

Dim builder As New SqlConnectionStringBuilder With {
        .DataSource = "DT2719MOD",
        .InitialCatalog = "abs2",
        .UserID = "TestUserLogon",
        .Password = "ilo;veac'h""alle;nge"}
Dim connection As New SqlConnection(builder.ConnectionString)

In that case, the only thing you need to do is escape the double-quote in the password once for VB.

For the record, you could have used the Server Explorer to generate a connection string for you too.

How to handle MS SQL password with Semicolon and Single quotation in VB.Net Code?

2 Answers2