Page 1 of 1
MySQL Query Validation
Posted: Thu Jul 29, 2010 11:04 am
by IGGt
I've been looking for a way to validate MYSQL Queries passed to PHP, in that I need to confirm that they always start with either 'SELECT', or 'SHOW' (e.g. no 'deletes', or 'drops' etc), but I also want to check that they all contain just one ';' and that it is at the end.
So far I have come up with
Code: Select all
$string = "SELECT * from tablename;";
if (preg_match("/^SELECT/" ,$string)) && (preg_match("/;$/" ,$string)) {
echo "query is OK";
} else {
"query incorrect"; }
The 'SELECT' part works but that's about it. If anyone can point me in the right direction it would be appreciated.
Re: MySQL Query Validation
Posted: Thu Jul 29, 2010 11:20 am
by mayanktalwar1988
thats easy do it like this
Code: Select all
preg_match("/\"((select)|(show))[\w\s*]+\";/i",$string)
show|select will make sure any query staring with select and show and ; at the end will specify ending with ;
and it will match the simple quer like you have shown
for other querie like this
or whatever
to match the above query just add "="in square barckets like this
Code: Select all
preg_match("/\"((select)|(show))[\w\s*=]+\";/i",$string)
Re: MySQL Query Validation
Posted: Thu Jul 29, 2010 12:12 pm
by Benjamin
I would just create a new mysql user with only SELECT privileges, and mysql_query does not support multiple queries, but even if you are using MySQLi, the queries would all be selects anyway, because the rest would fail.
Re: MySQL Query Validation
Posted: Thu Jul 29, 2010 2:06 pm
by ridgerunner
Your stated requirement of:
"they all contain just one ';' and that it is at the end" is not good because a query may include a quoted string (which can contain any char - including a semi-colon). However, your initial code is actually quite good and accurately meets your other stated requirements. Here is another version that you might like to try:
Code: Select all
$string = "SELECT * from tablename;";
if (preg_match('/^\s*+(?:select|show)\b.*;\s*+$/is', $string)) {
echo("query is OK");
} else {
echo("query incorrect");
}
This one meets your requirements but also tolerates leading and trailing whitespace and upper or lowercase SELECT|SHOW.
Hope this helps!
p.s. mayanktalwar1988, your regex has several problems: 1.) It is missing the (very important) ^ beginning and $ end of string anchors. 2.) It requires double quotes at the beginning and end of the query for some unknown reason. 3.) There are lots of other valid characters that your regex fails to match between the SELECT|SHOW and the ; terminator (e.g. it does not allow quotes or the contents of quoted strings, which may contain any character).
Re: MySQL Query Validation
Posted: Mon Aug 02, 2010 7:39 am
by IGGt
cheers guys, I'm not 100% convinced I fully understand it yet, but it works great. I think the rest of the day will be spent deconstructing it so I can see exactly how it works.
cheers again