regexproxysql

ProxySQL data masking for multiple columns

I want to mask sensitive information on multiple columns in a table named my_table using ProxySQL.

I've followed this tutorial to successfully mask a single column named column_name in a table using the following mysql_query_rules:

/* only show the first character in column_name */
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,match_pattern,re_modifiers,replace_pattern,apply)
          VALUES (1,1,'developer','my_table','(\(?)(`?\w+`?\.)?\`?column_name\`?(\)?)([ ,\n])','caseless,global', 
                "\1CONCAT(LEFT(\2column_name,1),REPEAT('X',CHAR_LENGTH(column_name)-1))\3 column_name\4",1);

But when I add a second rule for masking another column called second_column_name in the table, proxysql fails to mask the second column. Here's the second rule:

/* masking the last 3 characters in second_column_name */
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,match_pattern,re_modifiers,replace_pattern,apply)
          VALUES (2,1,'developer','my_table','(\(?)(`?\w+`?\.)?\`?second_column_name\`?(\)?)([ ,\n])','caseless,global', 
                "\1CONCAT(LEFT(\2second_column_name,CHAR_LENGTH(second_column_name)-3),REPEAT('X',3))\3 second_column_name\4",1);

Here's the query result after the 2 rules are added:

SELECT column_name FROM my_table; returns a masked column_name.

SELECT second_column_name FROM my_table; returns a masked second_column_name.

SELECT column_name, second_column_name FROM my_table; returns data with column_name masked, but second_column_name is not masked.

SELECT second_column_name, column_name FROM my_table; also returns data with column_name masked, but second_column_name is not masked.

Does this mean that 1 query can only be matched with 1 rule?

How can I mask data in multiple columns with ProxySQL?

Solution

  • Using flagIN, flagOUT, and apply allows me to mask data on multiple columns.

    Here's the final mysql_query_rules I have:

    /* only show the first character in column_name */
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,flagIN,match_pattern,re_modifiers,flagOUT,replace_pattern,apply)
          VALUES (1,1,'developer','my_db',0,'(\(?)(`?\w+`?\.)?\`?column_name\`?(\)?)([ ,\n])','caseless,global',6,    "\1CONCAT(LEFT(\2column_name,1),REPEAT('X',CHAR_LENGTH(column_name)-1))\3 column_name\4",0);

    /* masking the last 3 characters in second_column_name */
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,flagIN,match_pattern,re_modifiers,flagOUT,replace_pattern,apply)
          VALUES (2,1,'developer','my_db',6,'(\(?)(`?\w+`?\.)?\`?second_column_name\`?(\)?)([ ,\n])','caseless,global',NULL,
                "\1CONCAT(LEFT(\2second_column_name,CHAR_LENGTH(second_column_name)-3),REPEAT('X',3))\3 second_column_name\4",1);

    The meanings of the 3 variables are as the following: