none
SqlConnectionStringBuilder fails when user id has quotes RRS feed

  • Question

  • SqlConnectionStringBuilder   fails only when user id is set to '\"abc'd\"' (' "abc'd" ')  or \"'abc\"d'\"  (" 'abc"d' " ),

    t looks like there is issue with handling the double and single quotes when we use DbConnectionStringBuilder\SqlConnectionStringBuilder

    static void Main(string[] args)
        {
       
     try
            {
                string connectString =
                    "Server=(local);Database=AdventureWorks;UID=\"'ab\"cd'\";Pwd= a!Pass@@";
                //string connectString =
                //            "Server=(local);Database=AdventureWorks;UID='\"ab'cd\"';Pwd= a!Pass@@";
       
          //SqlConnection SQLConnection = new SqlConnection();

          //SQLConnection.ConnectionString = connectString;
          //  SQLConnection.Open();
                Console.WriteLine("Original: " + connectString);
                SqlConnectionStringBuilder builder =
                    new SqlConnectionStringBuilder(connectString);
                Console.WriteLine("Modified: " + builder.ConnectionString);
                foreach (string key in builder.Keys)
                    Console.WriteLine(key + "=" + builder[key].ToString());
                Console.WriteLine("Press any key to finish.");
                Console.ReadLine();

            }
            catch (System.Collections.Generic.KeyNotFoundException ex)
            {
                Console.WriteLine("KeyNotFoundException: " + ex.Message);
            }
            catch (System.FormatException ex)
            {
                Console.WriteLine("Format exception: " + ex.Message);
            }

          }

    System.ArgumentException was unhandled
      HResult=-2147024809
      Message=Format of the initialization string does not conform to specification starting at index 39.
      Source=System.Data
      StackTrace:
           at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
           at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
           at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
           at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
           at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
           at sqlClientException.Program.Main(String[] args) in C:\Users\vsajjan\Documents\Visual Studio 2010\Projects\SqlClient\SqlClient\sql_conn.cs:line 26
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    Wednesday, February 25, 2015 12:00 PM

Answers

  • I think that single quotes (apostrophes) can be written directly. In case of double quotes, for example ab"cd’ef, which includes both types, try this string:

        "…;UID=\"ab\"\"cd'ef\";…"

    or

        @"…;UID=""ab""""cd'ef"";…"

    Wednesday, February 25, 2015 12:48 PM