Systems and methods for SQL type evaluation to detect evaluation flaws
Methods and apparatuses for detecting an evaluation flaw in a SQL query, the SQL query configured to access data in a database table are disclosed. The method includes creating a parse tree from the SQL query and evaluating the parse tree to ascertain whether a condition of the SQL query results in a type or value that is independent of contents of the database table. For type evaluation, if, responsive to the evaluating, the condition is found, designating the SQL query at risk for having the tautology in the SQL query. For value evaluation, if, responsive to the evaluating, the condition is found, determining whether the condition is always true or whether the condition is always false; and if, responsive to the determining, the condition is found to be always true or always false, designating the SQL query at risk for having the evaluation flaw in the SQL query.
1. A method for detecting a tautology in a SQL query, said SQL query configured to access data in a database table, comprising:
creating a parse tree from said SQL query;
evaluating said parse tree to ascertain whether a condition of said SQL query is a type that is independent of contents of said database table by determining a type of a unary expression and passing the type up to a parent node in the parse tree for further evaluation based upon component types to determine an evaluated expression type, recording the evaluated expression type at a where clause; and
when, responsive to said evaluating, said condition is found, designating said SQL query at risk for having said tautology in said SQL query,
wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table and at least one of said two static types being a deterministic function operating on a static type.
2. The method of claim 1 , wherein said evaluating includes a bottom-up evaluation of said parse tree.
3. The method of claim 1 , wherein said condition is a result of a comparison operation.
4. The method of claim 1 , wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table.
5. The method of claim 1 , wherein said condition is deemed found when said SQL query involves an operation that always evaluates to be true irrespective of contents of said database table.
6. The method of claim 1 , wherein said evaluating said parse tree further including storing a type of a select list element for subsequent expression evaluation.
7. The method of claim 6 , further comprising performing said subsequent expression evaluation when said select list element is a part of a sub-query.
8. The method of claim 1 , wherein said condition is deemed found when said SQL query involves a comparison between two literals.
9. A method for detecting a tautology in a SQL query, said SQL query configured to access data in a database table, comprising:
creating a parse tree from said SQL query;
evaluating step-wise, using a bottom-up methodology, through said parse tree to ascertain whether a condition of said SQL query results in a type that is independent of contents of said database table by determining a type of a unary expression and passing the type up to a parent node in the parse tree for further evaluation based upon component types to determine an evaluated expression type, recording the evaluated expression type at a where clause; and
when said condition is found, responsive to said evaluating, designating said SQL query at risk for having said tautology in said SQL query,
wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table and at least one of said two static types being a deterministic function operating on a static type.
10. The method of claim 9 , wherein said evaluating includes a bottom-up evaluation of said parse tree.
11. The method of claim 9 , wherein said evaluating includes employing a comparison operation.
12. The method of claim 9 , wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table.
13. The method of claim 9 , wherein said condition is deemed found when said SQL query involves an operation that always evaluates to be true irrespective of contents of said database table.
14. The method of claim 9 , wherein said evaluating said parse tree further including storing a type of a select list element for subsequent expression evaluation.
15. The method of claim 14 , further comprising performing said subsequent expression evaluation when said select list element is a part of a sub-query.
16. The method of claim 9 , wherein said condition is deemed found when said SQL query involves a comparison between two literals.
17. A method for detecting a tautology in a SQL query, said SQL query configured to access data in a database table, comprising:
creating a parse tree from said SQL query;
evaluating step-wise, using a bottom-up methodology, through said parse tree said parse tree, including:
evaluating a type of a unary expression object;
passing the type up to a parent node in the parse tree for further evaluation based upon component types to determine an evaluated expression type;
recording the evaluated expression type at a where clause;
evaluating a condition for said data access, said condition employing said expression; and
when said condition is satisfied independent of contents of said database table, designating said SQL query at risk for having said tautology in said SQL query,
wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table and at least one of said two static types being a deterministic function operating on a static type.
18. The method of claim 17 , wherein said evaluating said expression includes evaluating a comparison operation.
19. The method of claim 17 , wherein said condition is deemed found when said SQL query involves a comparison between two static types, each of said two static types being independent of contents of said database table.