How can I store a regex pattern in a MySQL field and check an input against it?

My application is made up of several microservices and I am now working on the API Gateway and Authorization Microservice. Each incoming request will be validated and authorized depending on the user's permissions to perform this activity. An activity is made up of a verb (GET, POST, PUT, DELETE) and an ENDPOINT (/interpretations/q44nAj91aH6nd/)

I have a MySQL table listing all activities and want to match the user's request's endpoint to the matching activity (row) in the table.

    CREATE TABLE `activities` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `url` varchar(255) NOT NULL DEFAULT '',
      `method` varchar(20) NOT NULL DEFAULT 'GET',
      `url_regex` varchar(255) DEFAULT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `activities` (`id`, `url`, `method`, `url_regex`)
    VALUES
        (1,'/interpretations/','POST',''),
        (2,'','GET','/interpretations/[a-Z0-9]+/');

My question is, how can I match the row 2 for this given request's endpoint: /interpretations/q44nAj91aH6nd/ (which should match the regex pattern /interpretations/[a-Z0-9]+/).

When I try the below, it does not work:

SELECT * FROM `activities` WHERE `url_regex` RLIKE "/interpretations/q44nAj91aH6nd/";

# also tried inverting

SELECT * FROM `activities` WHERE "/interpretations/q44nAj91aH6nd/" RLIKE `url_regex`;

Solution

In MySQl, the regex pattern comes AFTER the RLIKE and the input data comes before, hence:

SELECT * FROM `activities` WHERE "/interpretations/q44nAj91aH6nd/" RLIKE `url_regex`;

PS, your regexp has a syntax error in the character range. Try this instead:

/interpretations/[a-zA-Z0-9]+/

To test your regexes first, pass the input and the pattern as straight strings:

SELECT * FROM `activities` WHERE  '/interpretations/q44nAj91aH6nd/' RLIKE  '/interpretations/[a-Z0-9]+/';

Gives "invalid character range". You don't see this in a query, because the pattern error doesn't halt the query, it just drops the row

Some testing/demo available at: http://sqlfiddle.com/#!9/82e917/2