Modeling and Analysis of SQL Queries in PHP Systems

Abstract

PHP is a common language used for creating dynamic websites. These websites often include the use of databases to store data, with embedded SQL queries constructed within the PHP code and executed through the use of database access libraries. One of these libraries is the original MySQL library that, despite not being supported in current versions of PHP, is still widely used in existing PHP code. As a first step towards developing program comprehension and transformation tools for PHP systems that use this library, this research presents a query modeling tool that models embedded SQL queries in PHP systems and an empirical study conducted through analysis of these models. A main focus of this study was to establish common patterns developers use to construct SQL queries and to extract information about their occurrences in actual PHP systems. Using these patterns, the parts of queries that are generally static, and the parts that are often computed at runtime were extracted. For dynamically computed query parts, we also extracted data about which PHP language features are used to construct them. Finally, information about which clauses most often differ based on control flow was extracted as well as counts for how often each SQL query type and SQL clause is used in practice. We believe this information is useful for future work on building program understanding and transformation tools to renovate PHP code using database libraries.