Using Positive LookAhead and LookBehind To Mask Password In Database Connection String

Question

I have an exception handler method in my VB.NET application that retrieves details from the last exception to occur and e-mails that information to our Help Desk (me) for diagnostics and troubleshooting. One instance of a possible exception is when the application attempts to connect or execute some SQL on one of our database servers. In those cases - especially when the exception is because of a failure to connect - I want to see the database connection string that's being used to make certain it's properly formatted.

However, database connection strings usually contain the user's ID and password, and I want to mask the password within the string. The challenge, however, is that we connect to multiple database types (e.g., PostgreSQL, MySQL, SQLite, MS Access, etc.) and, depending on the database, there may or may not be a password in the connection string. Also, connection string formats vary from one provider to the next.

I'm extremely unfamiliar with RegEx but, using https://regexr.com/, I've managed to come up with the following RegEx pattern that seems to work sometimes:

(?<=;?[Pp][Aa][Ss][Ss].*=)(.*?)(?=;)|$

An example connection string would look like this:

Host=SERVERNAME;Port=####;Database=DBNAME;Username=USERID;Password=MyPa$$Word;Integrated Security=False

The pattern above correctly matches MyPa$$Word in the string if it's explicitly in this order, but if I move the Password key/value pair closer to the beginning of the connection string like this:

Host=SERVERNAME;Port=9999;Password=MyPa$$Word;Database=DBNAME;Username=USERID;Integrated Security=False

it then matches MyPa$$Word, DBNAME, and USERID. If I move it to the end of the string:

Host=SERVERNAME;Port=9999;Database=DBNAME;Username=USERID;Integrated Security=False;Password=MyPa$$Word

the pattern doesn't find any matches. Just to make sure the key/value with the space (Integrated Security=False) wasn't confusing the pattern, I removed that from the string and got the same results.

Because the connection string may be structured in a variety of ways depending on the database type, the user input, etc., I'd like to be able to use RegEx to find the (case-insensitive) password key/value pair anywhere in the connection string, extract only the actual password value, and replace it with something (like [HIDDEN]). I know that I could probably just do a String.Split(Convert.ToChar(";")) on the whole connection string and check each key/value pair, but I'd prefer to do it with RegEx, if possible.

Answer 1

Something like this could work:

((^|;)Password=)(.*?)(;|$)

The password will be in $3 so just make sure to omit that capture group when performing the replacement.

Click on "Code Generator" on the left side of this page to see it in action in PHP.

https://regex101.com/r/gxztmy/1

Answer 2

In VB.NET, you may consider

text = Regex.Replace(text, "(?<=(?<![^;])pass\w*=).*?(?=;[\w\s]+=|$)", "[HIDDEN]", RegexOptions.IgnoreCase)

Or

text = Regex.Replace(text, "(?<![^;])(pass\w*=).*?(?=;[\w\s]+=|$)", "$1[HIDDEN]", RegexOptions.IgnoreCase)

C# versions in case one needs this:

text = Regex.Replace(text, @"(?<=(?<![^;])pass\w*=).*?(?=;[\w\s]+=|$)", "[HIDDEN]", RegexOptions.IgnoreCase);
text = Regex.Replace(text, @"(?<![^;])(pass\w*=).*?(?=;[\w\s]+=|$)", "$1[HIDDEN]", RegexOptions.IgnoreCase);

See a lookbehind regex demo and the capturing group solution demo.

Details

• (?i) - case insensitive modifier (or RegexOptions.IgnoreCase)
• (?<=(?<![^;])pass\w*=) - a positive lookbehind that requires a string pass (preceded with , or start of string position) + any 0 or more word chars followed with = immediately to the left of the current location
• .*? - any 0+ chars other than an LF symbol as few as possible
• (?=;[\w\s]+=|$) - a position in string immediately followed with ;, 1+ word or whitespace chars and = or end of string.

In the capturing group solution, the captured substring is put back into the result using $1 placeholder.

Why (?<![^;]) and not (?<=^|;)? Because an alternation in an unanchored lookbehind is costly in terms of performance. It makes sense to minimize the overhead if an equivalent pattern without alternation exists. (?<![^;]) matches any location that is either at the start of string or preceded with ;, so it should be preferred. This kind of pattern is not possible if the left-hand side context is a multicharacter string, or if the multiline mode is required, though.