Php Regex to find if string is mysql select statement

Question

I'm trying to validate queries before executing them, If query is not a mysql select statement then i have to show message to user.

I found below regex from this link: Validate simple select query using Regular expression

$reg="/^Select\s+(?:\w+\s*(?:(?=from\b)|,\s*))+from\s+\w+\s+where\s+\w+\s*=\s*'[^']*'$/i"; 

next i wrote below code but it always prints not select query ($match is empty every time)

$string="select * from users where id=1";
preg_match_all($reg,$string,$match);
if(!empty($match)){
echo "select query";
//execute and process result
//$this->user_model->list($string);
}else{
   echo "not select query";
   //show_message('inv_query');
}

Please correct regex to validate sql select statement (select, from,where, join,orderby groupby all can be there in select statement). Or let me know other good way to do the task.

/*
some sample select statements
select * from users where id=1;

select * from users where id=1 AND name= 'Prabhu';

select * from users where id=1 AND name= 'Prabhu' order by name;

Select * from users where id=1 AND name= 'Prabhu' group by id order by name;

Select * from users join role on users.role_id=role.id where id=1 AND name= 'Prabhu' group by id order by name;
*/

Brolly

post an example for the select query. – Present

@AvinashRaj there are lots of permuattion & combination. – Everyday

it would be better if you post all the possible Select statements regex101.com/r/bM7yR8/1 – Present

@Ravi How about this regex101.com/r/bM7yR8/3 ? – Present

OMG Ravi... i can do that but regex101.com won't allow a very long pattern. – Present

Answer 1

Why not have something like:

$reg = "/^(\s*?)select\s*?.*?\s*?from([\s]|[^;]|(['"].*;.*['"]))*?;\s*?$/i";

works for the SQL select query examples: http://www.phpliveregex.com/p/6nP.

It also checks that the only SQL query being run is the select query, therefore it should only validate them. It does this by making sure that there is only one ; unless that ; is within a string, so the below will validate.

select * from users where id=1 AND name= 'Pra;bhu';

But this will not.

select * from users where id=1 AND name= 'Prabhu'; drop table;

And the regular expression which doesn't check for ; within a string and will fail if it is in it:

$reg = "/^(\s*?)select\s*?.*?\s*?from([\s]|[^;])*?;\s*?$/i"

Rheumatic

Very nice, it is useful for admin-interfaces, to prevent drop or update – Scagliola

Very good, I'm using it for an Admin Dashboard for statistics to allow Admin in addition to multiple endpoints, can query database with sql directly. But a little correction for PHP, you need to escape the Double Quotes if you already use double quote for string : $reg = "/^(\s*?)select\s*?.*?\s*?from([\s]|[^;]|(['\"].*;.*['\"]))*?;\s*?$/i"; . Can't edit because it's a less then 6 chars edit. – Swelter

Thanks. It was very helpful but it doesn't cover select statement which does not contain 'FROM' for example: SELECT 22 as kind, 1 as code,'test' as title; – Cyrilla