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

Question

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

Navigation

#1 by (2 votes)

3

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 possible 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 think this can be done with regular expressions or some other comparison function.

php regex preg-match

asked by anonymous 12.07.2017 / 17:25

1 answer

What is the hreflang attribute for? Block reading, opening and editing file

score 2 · Accepted Answer

If you just want to know if one or more functions exist in the string, you can mount a regex by looking for the exact term with the \b set. When you put a term between \b it means that you are looking for a complete word and not an excerpt, so do not capture patrimony even though the term TRIM is 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>';
        }
    }
}

Return:

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))