|
Title: Oracle query validation Post by: Gena01 on May 21, 2010, 05:39:10 pm So we got an interesting use case at work where we have an admin interface to setup filters in our system. Our staff is able to enter expressions in the web interface and we have a way of checking that whatever they enter is valid or not.
While trying to optimize the validation code to do stricter checking with lower overhead I ran into a little gotcha. oci_parse() function doesn't actually do validation. This was a huge surprise to me and something I didn't grok. I went and pulled up the PHP manual page that has a note (from http://php.net/oci_parse ): Quote Note: This function does not validate sql_text. The only way to find out if sql_text is a valid SQL or PL/SQL statement is to execute it. This was a big surprise to me. I thought that you use oci_parse to parse the statement and then oci_execute() to actually execute/run the query with bind variables. It seems that oci_parse does basic sql parsing on client side without going to the server. There's also no validation of the query, It seems that the only thing oci_parse() does is prepare for using bind variables to be used later with oci_execute(). For me the biggest challenge was that an improper or big and complex filter expression could run quite a bit and I had no reason to actually run the query, I just needed a way to validate it. From PHP Manual it seemed that the only option I had was oci_execute(). I didn't want to quit and admit defeat just yet. I reached out to my Oracle friend who pointed me to another php_manual page. It seems that oci_execute has some little known flags that you can pass. Specifically ( from http://www.php.net/manual/en/function.oci-execute.php ): Quote OCI_DESCRIBE_ONLY Make query meta data available to functions like oci_field_name() but do not create a result set. Any subsequent fetch call such as oci_fetch_array() will fail. The description of this flag is not quite obvious, but this one will do something like explain plan for the query and provide back projected column information. As a side-effect it will also return an error if the query is not valid. Bazinga!!! :) Just wanted to share this with the community in case others will find this useful for either validating SQL queries or to be able to pull up column information that a query will return without actually running the query itself. Gena01 |