SQLInspect: a static analyzer to inspect database usage in Java applications

ABSTRACT

We present SQLInspect, a tool intended to assist developers who deal with SQL code embedded in Java applications. It is integrated into Eclipse as a plug-in that is able to extract SQL queries from Java code through static string analysis. It parses the extracted queries and performs various analyses on them. As a result, one can readily explore the source code which accesses a given part of the database, or which is responsible for the construction of a given SQL query. SQL-related metrics and common coding mistakes are also used to spot inefficiently or defectively performing SQL statements and to identify poorly designed classes, like those that construct many queries via complex control-flow paths. SQLInspect is a novel tool that relies on recent query extraction approaches. It currently supports Java applications working with JDBC and SQL code written for MySQL or Apache Impala. Check out the live demo of SQLInspect at http://perso.unamur.be/~cnagy/sqlinspect.

References

1. David Anderson and Mark Hills. 2017. Supporting Analysis of SQL Queries in PHP AiR. In Proc. of the 17th IEEE Int. Work. Conf. on Source Code Analysis and Manipulation.
2. Aivar Annamaa, Andrey Breslav, Jevgeni Kabanov, and Varmo Vene. 2010. An Interactive Tool for Analyzing Embedded SQL Queries. In Proc. of the 8th Asian Conf. on Prog. Languages and Systems (APLAS2010). Springer-Verlag, 131--138.
3. Huib van den Brink, Rob van der Leek, and Joost Visser. 2007. Quality Assessment for Embedded SQL. In Proc. of the 7th Int. Working Conf. on Source Code Analysis and Manipulation (SCAM2007). IEEE, 163--170.
4. Aske Simon Christensen, Anders Møller, and Michael I. Schwartzbach. 2003. Precise Analysis of String Expressions. In Proc. of the 10th Int. Conf. on Static Analysis (SAS2003). Springer-Verlag, 1--18.
5. Bill Karwin. 2010. SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers). Pragmatic Bookshelf.
6. Mario Linares-Vásquez, Boyang Li, Christopher Vendome, and Denys Poshyvanyk. 2016. Documenting Database Usages and Schema Constraints in Database-centric Applications. In Proc. of the 25th Int. Symp. on Soft. Testing and Analysis. ACM, 12.
7. Loup Meurice, Csaba Nagy, and Anthony Cleve. 2016. Static Analysis of Dynamic Database Usage in Java Systems. In Proc. of the 28th Int. Conf. on Advanced Information Systems Engineering (CAiSE2016). Springer LNCS.
8. Csaba Nagy and Anthony Cleve. 2017. Static Code Smell Detection in SQL Queries Embedded in Java Code. In Proc. of the 17th IEEE Int. Working Conf. on Source Code Analysis and Manipulation (SCAM 2017). IEEE Comp. Soc.
9. Csaba Nagy, Loup Meurice, and Anthony Cleve. 2015. Where Was This SQL Query Executed? A Static Concept Location Approach. In Proc. of the 22nd Int. Conf. on Software Analysis, Evolution, and Reeng. (SANER2015). IEEE Comp. Soc., 580--584.