0

I inherited a MySQL server that has CC numbers stored in plaintext. due to PCI requirements, I need to find the numbers and mask them. The trick is they are stored in a field with other text as well. I need to find a way to search for cc numbers and change just those, not the rest of the text.

I have tried the masking feature in MySQL, but it doesn't work for this version. I also looked up a few different sites but can't seem to find anything that will really help with my particular instance.

Edit to explain better. the previous admin didn't tell the operators to not take CC info through the live chat system. The system is using SSL but the chat history is stored in plain text in a MySQL DB. The company isn't PCI compliant (as far as getting scanned and SAQ is concerned) so we cannot have CC numbers stored anywhere. but the numbers are given in the middle of a conversation. If they were in their own column then that wouldn't be a big deal.

EDIT I have tried using regexp to just try and search for CC #'s but now I am getting an operand error, which is lazy quantifiers I believe.

SELECT * FROM table_name Where text regexp '^4[0-9]{12}(?:[0-9]{3})?$'

Any Ideas?

user3542866
  • 181
  • 12
  • What version of MySQL are you using, and can you not upgrade? – Strawberry Jul 08 '20 at 07:22
  • And see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Jul 08 '20 at 07:23
  • Hello @Strawberry. We can't update our MySQL server, there are some legacy apps that I am told will not work in a newer version. I also did see that post. I hoped I explained it well enough just to garner up some conversation. I can't hire someone due to the nature of the data involved. if there is more info required I would love to know so I can provide what I can in the hopes of some assistance. – user3542866 Jul 08 '20 at 16:12

1 Answers1

0

You could potentially use a regular expression to search for 16-19 consecutive characters with (using LIKE if you have the numbers separated from the text, or just REGEXP):

The example is given here (where 5 is the number of items to search for, and ^$ requires it to be those at the beginning and end):

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Would end up something like: REGEXP '^([0-9]{16|17|18|19})$'

https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html

And lookie here too:

Regex to match a digit two or four times

user26533
  • 21
  • 3