How to create a function that validates if there is a Mysql function in a given string?

Question

I am looking for a function that identifies (return true) if there is a Mysql function in a string. These would be some examples of possibility of input to function.

<?php

$randomstrings = [
    "foo", //String comum
    "NOW()", //Função sem parâmetros
    "CONCAT_WS('foo','doo','boo')", //fun. com parâmetros
    "ST_AsText(ST_GeomFromGeoJSON('{\"type\":\"Point\",\"coordinates\":[-48.23456,20.12345]}'))", //Funções dentro de funções
    "ST_AsText(ST_GeomFromGeoJSON(@json))", //Variávies dentro da função
    "patrimony", //Não identificar paTRIMony como a function TRIM()
]

 foreach ($randomstrings as $value) {
 $functionList = array('LOAD_FILE', 'sql_to_decimal', '@userid', 'COALESCE', 'getVersaoEO', 'getPessoaById', 'CONVERT', 'IS NULL', 'IS NOT NULL',
            'ST_GeomFromText', 'ST_AsGeoJSON', 'ST_GeomFromGeoJSON', 'ST_AsText', 'CONCAT_WS', 'CONCAT', /*'TRIM',*/ 'json_extract', 'JSON_OBJECT', 'CURRENT_TIMESTAMP');
        foreach ($functionList as $function) {

            $find = strpos(strtolower($value), strtolower($function));
            if (!($find === false)) {
                return true;
            }
        }
 }

I believe this can be done with regular expressions or some other comparison function.

Answer 1

If you just want to know if one or more functions exist in the string, you can mount a regex looking for the exact term with âconra \b. By placing a term between \b means you’re looking for a full word and not a snippet, so don’t capture patrimony even having the term TRIM in the middle/inside.

$randomstrings = [
        "foo", //Non-function strings
        "NOW()", //Without parameters
        "CONCAT_WS('foo','doo','boo')", //With parameters
        "ST_AsText(ST_GeomFromGeoJSON('{\"type\":\"Point\",\"coordinates\":[-48.23456,20.12345]}'))", //Maybe functions inside functions
        "ST_AsText(ST_GeomFromGeoJSON(@json))", //Variables as parameters
        "patrimony", //Do not identify paTRIMony as  a function TRIM()
];

$functionList = array('LOAD_FILE', 'sql_to_decimal', '@userid', 'COALESCE', 'getVersaoEO', 'getPessoaById', 'CONVERT', 'IS NULL', 'IS NOT NULL','ST_GeomFromText', 'ST_AsGeoJSON', 'ST_GeomFromGeoJSON', 'ST_AsText', 'CONCAT_WS', 'CONCAT', /*'TRIM',*/ 'json_extract', 'JSON_OBJECT', 'CURRENT_TIMESTAMP');

foreach ($randomstrings as $value) {
    foreach($functionList as $function){
        if(preg_match("#\b$function\b#i", $value)){
            echo 'procurou por: '. $function  .' acho em: '. $value .'<br>';
        }
    }
}

Returns:

procurou por: CONCAT_WS acho em: CONCAT_WS('foo','doo','boo')
procurou por: ST_GeomFromGeoJSON acho em: ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'))
procurou por: ST_AsText acho em: ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'))
procurou por: ST_GeomFromGeoJSON acho em: ST_AsText(ST_GeomFromGeoJSON(@json))
procurou por: ST_AsText acho em: ST_AsText(ST_GeomFromGeoJSON(@json))

Related:

What’s the use of a Boundary ( b) in a regular expression?