MYSQL SELECT QUERY, finding a letter number word in a string-CodePudding

So the company I work for has its employees update the CRM with comments, which then are stored in the database. And sometime they put in usernames and passwords into those comments.

We are trying to remove all username/passwords from any comment in our database.

I originally used this:

SELECT 
    logDate, ID, comment
FROM 
    logs
WHERE  
    comment LIKE '%username:%'
    OR comment LIKE '%user id%'
    OR comment LIKE '%userid%'
    OR comment LIKE '% u:%'
    OR comment LIKE '%login:%'
    OR comment LIKE '%password:%'
    OR comment LIKE '%pw:%'
    OR comment LIKE '% p:%'
ORDER BY created DESC;

Which worked and we removed a lot but I still feel like there could be more. My thinking is if there is a way I can search a text string (comment) for any word that contains letters and numbers I would be able to get and record in the database that contains a password (because most sites make you uses at least letters and numbers for the password, I rarely see a password without numbers anymore)

Example would be like:

Record Date: 2022-07-08; RecordID : 1 Comment: " I talked to the customer and they had me help them log in, their username is bob and password is taco123."

Record Date: 2022-07-08; RecordID : 2 Comment: "I helped a customer login."

Result from the query would pull only 1 result Date: 2022-07-08 | ID: 1 | taco123

I just cant figure out how to do it if the comment didnt include common words like username/password. Like if the comment was "helped customer login, (bob/taco123)"

My original query would not pick it up because it didn't have any of the indicators.

I was thinking REGEXP in the where clause but any I use only work if the comment is 1 word long.

Any help would be appriciated.

CodePudding user response：

This will match any comment where a letter is immediately followed by a number or a number is immediately followed by a letter.

WHERE comment REGEXP '[a-zA-Z][0-9]|[0-9][a-zA-Z]`

CodePudding user response：

The problem with regular expressions is, that you need a exact match.

So if the pattern ist not 'password is ' it wil not find it.

So the pattern to search for can be more complex, but for your purpose is it enough

CREATE TABLe t1 (logDate date , ID  int , comment varchar(300))

INSERT INTO t1 VALUES('2022-07-08', 2
, "I talked to the customer and they had me help them log in, their username is bob and password is taco123"
),('2022-07-08', 2 ,"I helped a customer login.")

SELECT logDate,ID,
REPLACE(REGEXP_SUBSTR(comment, 'password is [a-zA-Z0-9] '), 'password is ', '') FROM t1
WHERe REGEXP_LIKE(comment, 'password is ');

logDate    | ID | REPLACE(REGEXP_SUBSTR(comment, 'password is [a-zA-Z0-9] '), 'password is ', '')
:--------- | -: | :------------------------------------------------------------------------------
2022-07-08 |  2 | taco123

db<>fiddle here