in order to implement a custom sql function named getUserName and getPassword from an input url, im trying to find a way to extract the user name and password from a given url using regex. the code will be executing on snowflake sql flavor language so my coding tools are limited to sql functions and regex supported by snowflake.

i can find the url scheme and the domain/host, get their indexes and length, and extract as substring every thing in between to get the user name and password, the problem is that it wont work with many edge cases, can you suggest better methods/code execution?

this is an example url:

http://UserName123:P@$$:\/\/[email protected]:34567/3.0.1/file_name.txt?pn1:pv1&pn2:pv2#ref

this is the current regex used to identify the url scheme and domain:

(^[a-z0-9.-] :\/\/)|((?:@)([a-z0-9.-] ))

this is an sql example of getting the scheme:

SPLIT_PART(REGEXP_SUBSTR(A, '^[a-z0-9.-] ://', 1, 1, 'c'), ':', 0) as split

CodePudding user response：

Username and password in a URL are bad practice. URLs, along with the query string (and thus the passwords) will show up in server log files or it can leak/get stolen another way.

So, redesign is needed and not a solution to this problem.

I hope you find it good to know now instead of after solving the problem.