Validate MySQL query before executing?

[djr33]

I'm currently generating a dynamic query based on user input. Everything is only indirectly used and escaped, so I'm 99% confident in my script, but I also know that there's some chance some very unpredictable format will cause problems. In general this might help too.

What I'm wondering is if there is a php function that can validate mysql. It wouldn't need to verify that the data is escaped or anything like that, but just that I'd rather check that the format is valid before sending it to mysql-- so that I don't risk the database with a potentially badly formed query.

So basically:
if (valid($query)) { $result = mysql_query($query); }

valid() should return true if it is (any) well formed query, and false if there is a syntax error.

Does anything like this exist?
It's not a huge problem, just something I'm wondering about...

[traq]

not to my knowledge, though someone has probably made something.

the problem I see, though, is that while such a function might protect you from errors, it would not protect you from sql injection attacks (which do use well-formed queries - just not the ones you expect).

[djr33]

In this particular case, I'm not worried about sql injection: the user is generating the sql syntax but not the content, based on indirect input. If you remember from a while ago, I made a search engine mysql generator thing where, for example, parentheses were valid in the search input. So the user types term (term2 OR term3) and that works fine. I have tested everything I can think of and it works fine, but along the way I've found a couple problems and fixed them: for example, if the user types term () then it created an invalid query. I fixed this, but there might be other similar things I can't predict, so I was just wondering if there was some way to validate the syntax before submitting it to mysql. The user's actual text is never used in the query except when escaped or indirectly in generating the syntax.

[traq]

I recall.

However, I've never seen such a thing, and I've done a few searches to no avail... I bet it'd be really complex to make a SQL validator that actually works.

[djr33]

It would certainly be complex, so I wouldn't bother attempting it, but I thought that there might be some existing function since of course mysql itself must validate the query. But I guess in that sense trying to run it and failing is validation in itself.

[traq]

yup. build your error handling on top of mysql_error()

[djr33]

Thanks. That seems like the easiest way to go. Does anyone know if there's any danger of running badly formed MySQL syntax against a database? I mean-- does it first validate this or does it try to guess/execute it? If it verifies it first, then I don't really mind. These are just select statements so I'm not worried about anything aside from confusing it.

[traq]

I don't know, but I suspect it validates first. Of course, that's based solely on the observation that my badly formed statements have never actually broken anything :p ... Maybe you could try to take a peek at phpmyAdmin's error handling routines, it seems to catch stuff and return errors without actually executing bad statements.