locked
-replace and RegEx help RRS feed

  • Question

  • Hello everyone,

    I've had this script running for a good time

    Get-ChildItem C:\temp -Include "ConnectionStrings*.config" -recurse | ForEach {
      $file = $_
      (Get-Content $file) | Foreach{
       $_ -replace "password=.{0,};", "password=*****;"} | Set-Content $file
    }

    But that only works if the file contains connection strings in the format of

    <add name="core" connectionString="user id=myuser;password=Sup3rS3cr3tP@ssw0rd;Database=MyDB"/>

    I know have a file with the following format which the above script does not work

    <add name="security" connectionString="Data Source=DBServer;Initial Catalog=MyDB;User ID=securityuser;Password=Sup3rS3cr3tP@ssw0rd" />

    I am not all that great with RegEx, but how can I modify the -replace statement to match any combination of password in a ConnectionString? The replacement string also needs to have the ; removed, as I cannot guarantee that the password will end in ;


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''



    • Edited by clayman2 Wednesday, January 8, 2020 8:02 PM typo
    Wednesday, January 8, 2020 8:00 PM

Answers

  • The replace is not case sensitive. For XML files using xXML is the correct method and avoids all kinds of text canges

    [xml]$onfig = Get-Content file.config

    Now use XML methods to find the node and assign the password you want.  It is fast and always works on any file structure.


    \_(ツ)_/

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Wednesday, January 8, 2020 8:53 PM
  • Once you've acquired the connection string it'd be easier to use something other than a regex to replace it (and I hope you never find your new password to have a semicolon in it!).

    A split, replace, and join are easier to write (and to understand) than a regex would be, given different elements in the two strings you gave as an example.

    Here's an example:

    $ExampleConnectionStrings =     "user id=myuser;password=Sup3rS3cr3tP@ssw0rd;Database=MyDB",
                                    "Data Source=DBServer;Initial Catalog=MyDB;User ID=securityuser;Password=Sup3rS3cr3tP@ssw0rd"
    $ExampleConnectionStrings |
        foreach{
            $x = ($_ -split ";") -replace '^password=.*$','password=*****' -join ";"
            "In:  $_"            # original connection string
            "Out: $x"            # connection string with new password
            "-----------------"
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Thursday, January 9, 2020 2:56 AM
  • [xml]$xml = @'
    <?xml version="1.0" encoding="utf-8"?>
    <connectionStrings>
      <!-- 
        Sitecore connection strings.
        All database connections for Sitecore are configured here.
    
      -->
      <add name="core" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="master" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="web" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
    <!-- <add name="sql4" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/> -->
     <add name="sql2" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer; Database=MyDB"/>
       <add name="test" connectionString="user id=dbUser; password=SuperSecret;Data Source=DbServer;Database=MyDB"/>
    
    </connectionStrings>
    '@
    $xml.connectionStrings.ChildNodes | 
        Where-Object{
            $_.Name -eq '#comment' -and 
            $_.Value -match '<add name='} |
        ForEach-Object{
            $_.Value = $_.Value -replace 'password=.*;', 'Password=newpassword;'
        }
    


    \_(ツ)_/

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Thursday, January 9, 2020 6:39 PM

All replies

  • Hi, 

    I don't know if this works:

    [Pp]assword=(.{0,})["|;][\s|Database|"]


    Wednesday, January 8, 2020 8:39 PM
  • The replace is not case sensitive. For XML files using xXML is the correct method and avoids all kinds of text canges

    [xml]$onfig = Get-Content file.config

    Now use XML methods to find the node and assign the password you want.  It is fast and always works on any file structure.


    \_(ツ)_/

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Wednesday, January 8, 2020 8:53 PM
  • Once you've acquired the connection string it'd be easier to use something other than a regex to replace it (and I hope you never find your new password to have a semicolon in it!).

    A split, replace, and join are easier to write (and to understand) than a regex would be, given different elements in the two strings you gave as an example.

    Here's an example:

    $ExampleConnectionStrings =     "user id=myuser;password=Sup3rS3cr3tP@ssw0rd;Database=MyDB",
                                    "Data Source=DBServer;Initial Catalog=MyDB;User ID=securityuser;Password=Sup3rS3cr3tP@ssw0rd"
    $ExampleConnectionStrings |
        foreach{
            $x = ($_ -split ";") -replace '^password=.*$','password=*****' -join ";"
            "In:  $_"            # original connection string
            "Out: $x"            # connection string with new password
            "-----------------"
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Thursday, January 9, 2020 2:56 AM
  • Thanks for the replies. I took jrv's and Rich's approach and came up with this

    $file = 'C:\Temp\Testing\ConnectionStrings.config'
    $connString = [xml](Get-Content $file)
    $connString.connectionStrings.add | foreach {
      $_.connectionString = ($_.connectionString -split ';') -replace '^password=.*$|\spassword=.*$','password=*****' -join ";"
    }
    
    $connString.Save($file)
    This works great and handles different formats of a ConnectionStrings file, but how would I handle a node that is commented out, as the above approach skips over it and a password is still exposed?


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Thursday, January 9, 2020 4:16 PM
  • Comment elements are separate and enumerable. Look at a root node and the children. You should see the comment node there.

    Post an example of you config if you don't see how to do this.  It will save us time giving you an example.


    \_(ツ)_/

    Thursday, January 9, 2020 5:32 PM
  • Thank you jrv, I appreciate your help. The contents look like below

    <?xml version="1.0" encoding="utf-8"?>
    <connectionStrings>
      <!-- 
        Sitecore connection strings.
        All database connections for Sitecore are configured here.
    
      -->
    
    
      <add name="core" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="master" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="web" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
    <!-- <add name="sql4" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/> -->
     <add name="sql2" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer; Database=MyDB"/>
       <add name="test" connectionString="user id=dbUser; password=SuperSecret;Data Source=DbServer;Database=MyDB"/>
    
    </connectionStrings>


    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Thursday, January 9, 2020 5:51 PM
  • It is missing the hierarch -- or is it a custom file?


    \_(ツ)_/

    Thursday, January 9, 2020 6:18 PM
  • [xml]$xml = @'
    <?xml version="1.0" encoding="utf-8"?>
    <connectionStrings>
      <!-- 
        Sitecore connection strings.
        All database connections for Sitecore are configured here.
    
      -->
      <add name="core" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="master" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
      <add name="web" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/>
    <!-- <add name="sql4" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer ; Database=MyDB"/> -->
     <add name="sql2" connectionString="user id=dbUser; password=SuperSecret; Data Source=DbServer; Database=MyDB"/>
       <add name="test" connectionString="user id=dbUser; password=SuperSecret;Data Source=DbServer;Database=MyDB"/>
    
    </connectionStrings>
    '@
    $xml.connectionStrings.ChildNodes | 
        Where-Object{
            $_.Name -eq '#comment' -and 
            $_.Value -match '<add name='} |
        ForEach-Object{
            $_.Value = $_.Value -replace 'password=.*;', 'Password=newpassword;'
        }
    


    \_(ツ)_/

    • Marked as answer by clayman2 Thursday, January 9, 2020 6:50 PM
    Thursday, January 9, 2020 6:39 PM
  • Yes it is a custom file, the above script works for the commented out sections. I believe I have enough information to take care of everything else. Thank  you so much for your help.

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful. (99,108,97,121,109,97,110,50,64,110,121,99,97,112,46,114,114,46,99,111,109|%{[char]$_})-join''

    Thursday, January 9, 2020 6:50 PM
  • The regex will fail when there's no semicolon following the password value. That was the original problem.

    Add an additional connection string to the test data that has 'password=whatever" />' as the ending character string and rerun the code.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, January 9, 2020 8:17 PM
  • This one works. It's got some superfluous stuff in there for testing though.

    It uses JRV's code to get the #comment data from the XML file, but the regex extracts the connection string. Then the connection string is handled in the same way as my other post. The connection string is reassembled and the #comment rebuilt from the parts matched in the regex.

    $xml = [xml](Get-Content c:\junk\stuff.xml)
    
    $xml.connectionStrings.ChildNodes | 
        Where-Object{
            $_.Name -eq '#comment' -and 
            $_.Value -match '<add name='} |
                ForEach-Object{
                    # the "multiline" modifier "(?m) is necessary because comments may 
                    # begin with newline (0x0A) and the "dot" must match those, too
                    # and the formatting of the 'Value' property should be preserved
                    if ($_.Value -match '(?m)^(.*\<add\s.+connectionString=")([^"]+)(".+)$' ){
                        $ConnString = $matches[2] -split ';' -replace 'password=.*', 'password=*****' -join ';'
                        $_.Value = "{0}{1}{2}" -f $matches[1], $ConnString, $matches[3]
                    }
                    else {
                    "Didn't match!"
                    $matches
                    }
                }
            # this just exhos all the comments in the xml doc. remove it after testing
            $xml.connectionStrings.'#comment'
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, January 9, 2020 8:57 PM
  • RIch. Amazing. You managed to do what I did in twice as many lines.

    I can shorten mine even more to about three lines.

    We can also use the connectionStringBuilder class to fine tune this even more.


    \_(ツ)_/

    Thursday, January 9, 2020 9:04 PM
  • Assuming that the XML comments contain connection strings mentioned in the OP's original post, your code only works if the password component of the connection string is separated from other parts by a semicolon.

    I ran your example, but added the following two comments to the XML:

    <!-- <add name="sql4" connectionString="user id=dbUser; password=SuperSecret"/> -->
    <!-- <add name="security" connectionString="Data Source=DBServer;Initial Catalog=MyDB;User ID=securityuser;Password=Sup3rS3cr3tP@ssw0rd" /> -->

    In both cases (one taken directly from the OP's original post, but changed into a comment) the password value in the connection string remains unaltered.

    So, go easy on the snark.

    If you want to shorten my example, remove the comments, the "else" condition, and the last line. You can even shorten the code in the "if" block to one line, but I left it as you see it to make it easier to understand.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, January 9, 2020 10:37 PM
  • Good assumption but I have never seen it that way. ALl strings are generally built by the string builder and that always ends every pair with a semicolon. Also the rules of the string are that every pair must be ended with a semicolon even it it is the last pair. Systems outside of WIndows will throw an error when a string is missing the semicolon. Of course your method would work for even this possible error.  THe string builder method I mentioned will always work without splitting the string.  We could also change my semicolon to a metacharacter that detects both semicolons and end of string.


    \_(ツ)_/

    Thursday, January 9, 2020 11:44 PM
  • Like this:

    '<!-- <add name="sql4" connectionString="user id=dbUser; password=SuperSecret"/> -->' -replace 'password=.*\b','password=XXXXXXXXXXXX'
    Still only one line.  Find another issue and I will show you how to fix it in the RegEx.


    \_(ツ)_/


    • Edited by jrv Thursday, January 9, 2020 11:47 PM
    Thursday, January 9, 2020 11:46 PM
  • You almost had it! But that greedy ".*" will cause the regex to look for the last "\b", not the first (gotta love backtracking!). Put the password in the middle of the connection string and it'll replace everything after the password.

    You can't simply make the ".*" lazy because that'll match either nothing or something and you'll get "password=XXXXXXXXXXXXSuperSecret".

    I'm pretty sure you were looking for .+?\b. If the password was empty (e.g. 'password=;' there's no need to replace the actual password value, so looking for one or more characters seems to work.

    EDIT: Do NOT use the above regex. It doesn't work the way it looks like it should. Use this regex instead:

        [^ ;"]+

    Note: That's a blank/space character after the caret


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Friday, January 10, 2020 3:39 AM
  • Ok - there is a away to do this without being greedy. I just need to remember what the trick is.


    \_(ツ)_/


    • Edited by jrv Friday, January 10, 2020 10:26 AM
    Friday, January 10, 2020 9:46 AM
  • Here it is. Use a negative character class:

         <string> -replace 'password=[^ ;"]+', 'password=XXXXXXXXXXXX'

    (That's a real blank/space character after the caret. I didn't want to use \s because I'm not sure if any of the characters such as \f, \v, or those in the Unicode \p{Z} set are used in the passwords)

    Don't use the example I gave before: ".+?\b". It will fail even if there's a breaking character in the password. I thought the "\b" would work, but it looks like the even the lazy ".+?" gobbles up the breaking character. :-(


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Proposed as answer by jrv Friday, January 10, 2020 9:32 PM
    Friday, January 10, 2020 9:27 PM
  • Cool. It wasn't what I was thinking of but it looks like it will work.

    I still haven't remembered the easy method which was a way to option the match to break on the first match even when greedy.  If I could remember the way it is referenced I think I could find it.


    \_(ツ)_/

    Friday, January 10, 2020 9:31 PM