maxscale rewrite filter with awk

Question

The following rewrite rule works as expected:

%%
regex_grammar: Awk
case_sensitive: false
ignore_whitespace: true
%
SELECT msg FROM mytable WHERE id = 123
%
SELECT msg FROM mytable WHERE id = sha1(123)

My question now is, how can I write this query with placeholder in regex_grammer: Awk. If I knew that, I would have a breakthrough for the next problem. My actual goal is to rewrite the following query:

Input:
SELECT msg FROM mytable WHERE id IN (123,456,769)

Output:
SELECT msg FROM mytable WHERE id IN (sha1(123),sha1(456),sha1(769))

I assume that this only works with regex_grammar Awk and not with Native. Am I right?

This is the only documentation that I found: https://mariadb.com/kb/en/mariadb-maxscale-2208-rewrite-filter/

I also accept other suggestions or tools! Maxscale looked the most promising solution to me.

Is the number of values in the `IN` clause constant or does it change for every query? If it does change, the problem would require a repeating regex of some sorts for the values. — markusjm, Jul 05 '23 at 06:31
The number of id's cannot be predicted, it can vary depending on the query. And you spotted the exact problem, I need to loop over the values. On the console this is not a problem: echo "123,456,789" | awk '{a=split($0, ff, ","); n=""; for (i in ff) {n=n"sha1("ff[i]"),"}; gsub(",$" , "", n); print n}' But I don't know how to write (syntax) that in maxscale ;) — user3876906, Jul 05 '23 at 07:13
You could try using the `luafilter` from the `maxscale-experimental` package. You can program the replacement to be done with Lua which should allow you to do pretty much anything you want. Another alternative is to use the `regexfilter` that always uses PCRE2 patterns which support recursion and other advanced regular expression features. — markusjm, Jul 06 '23 at 08:15

score 0 · Accepted Answer · answered Jul 06 '23 at 08:42

The type of replacement you're doing requires repeated substitutions with no fixed amount of values. The rewrite filter is intended for simpler use-cases where the patterns are fixed and do not require complex matching.

However, this can still be done with the regexfilter in MaxScale that allows more free-form use of regular expressions. The following regexfilter configuration should handle simple values in an IN list and wrap them in a SHA1 function call.

[regex]
type=filter
module=regexfilter
match=/(?i)(IN\s+\(|,)\s*([^,]+)\s*/
replace=$1 SHA1($2)

Here's the regex101.com page I used to test it.

Note that this won't work with strings with embedded commas in them which means it's not really an universal solution.

Thanks @markusjm, never thought that someone could answer my question. Now I can get down to the miragtion. Works exactly as intended — user3876906, Jul 06 '23 at 19:40

maxscale rewrite filter with awk

1 Answers1