Validate SQL Code before Execution

[Tazerenix]

Is there any function or code that will validate SQL Syntax before executing and return an error?

[PFMaBiSmAd]

Nope, but since you should only be putting data into a preexisting tested query where the syntax of the query is known to be correct, that should not be a problem if you have validated and escaped the data.

 

What sort of problem are you having that you are trying to solve?

 

 

[Tazerenix]

I was just wondering really, it would be a useful feature to have in a database class im creating to check syntax and return an error without running the query

[PFMaBiSmAd]

If the query is a SELECT, you can use EXPLAIN to do what you want.

 

None of the other types of queries could be checked without actually executing them.

[fenway]

That's not technically correct -- use LIMIT 0, and it most cases, nothing will happen, but the parser will still have to validate the query.

[PFMaBiSmAd]

Playing the devil's advocate, what if the dynamically produced SQL that was being validated for syntax errors was for a DELETE query and the resulting bad syntax either did not get built with the LIMIT 0 term or it was treated as a comment or got enclosed in quotes so that it was not seen (perhaps as an argument NOT in a WHERE condition) and all the rows just got deleted?

 

If you are talking about needing to do this as the result of putting data into an existing syntactically correct query, as long as you properly validate and escape the data, you cannot produce a syntax error.

 

There are some database errors that are not due to syntax, such as the 'database has gone away' type that are out of your control and you still need error checking, error reporting, and error recovery logic in your code to address them. It does not really make sense to add the extra overhead of trying to detect a syntax error in a query before you run it in order to prevent the syntax error that you would get when you actually do run it.

[fenway]

My advice pertained to SELECT queries only.