MySQL Query Validation

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

Post Reply
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

MySQL Query Validation

Post 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.
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: MySQL Query Validation

Post 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

Code: Select all

select this from that where id=5
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)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL Query Validation

Post 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.
User avatar
ridgerunner
Forum Contributor
Posts: 214
Joined: Sun Jul 05, 2009 10:39 pm
Location: SLC, UT

Re: MySQL Query Validation

Post 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).
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: MySQL Query Validation

Post 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
Post Reply