Skip to main content
SpringerLink
Log in

Extending symbolic execution for automated testing of stored procedures

  • Published:
Software Quality Journal Aims and scope Submit manuscript

Abstract

Stored procedures in database management systems are often used to implement complex business logic. Correctness of these procedures is critical for flawless working of the system. However, testing them remains difficult due to many possible database states and constraints on data. This leads to mostly manual testing. Newer tools offer automated execution for unit testing of stored procedures but the test cases are still written manually. We propose an approach of using dynamic symbolic execution for generating automated test cases and corresponding database states for stored procedures. We model the constraints on data imposed by the schema and the SQL statements, treating values in database tables as symbolic. We use SMT solver to find values that will drive the stored procedure on a particular execution path. We instrument the internal execution plans generated by PostgreSQL to extract constraints. We use Z3 to generate test cases consisting of table data and procedure inputs. Our evaluation using stored procedures from a large business application and various GitHub repositories quantifies the evidence of effectiveness of our technique by generating test cases that lead to schema constraint violations and user-defined exceptions.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16
Fig. 17
Fig. 18

Similar content being viewed by others

Notes

  1. http://www.postgresql.org

  2. http://www.xtuple.com/postbooks

  3. https://github.com/Abstrct/Schemaverse.git

  4. https://github.com/Yizhe-FAN/ParisVII_ProjectDataBase.git

  5. https://github.com/LSostaric/PostgreSQL-Node-Tree.git

  6. https://github.com/ashenoy95/plpgsql.git

  7. https://sourceforge.net/p/riskitinsurance

  8. https://www.enterprisedb.com/

  9. https://www.visualstudio.com

References

  • Barrett, C., & Tinelli, C. (2007). CVC3. In Proceedings 19th international conference on computer aided verification (CAV) (pp. 298–302).

  • Binnig, C., Kossmann, D., Lo, E. (2007). Reverse query processing. In IEEE 23rd international conference on data engineering (ICDE) (pp. 506–515).

  • Bush, W.R., Pincus, J.D., Sielaff, D.J. (2000). A static analyzer for finding dynamic programming errors. Software Practice Experience, 30(7), 775–802.

    Article  Google Scholar 

  • Cadar, C., & Engler, D. (2005). Execution generated test cases: how to make systems code crash itself. In Proceedings of the international SPIN workshop on model checking of software (pp. 2–23).

  • Cadar, C., Ganesh, V., Pawlowski, P.M., Dill, D.L., Engler, D.R. (2006). EXE: automatically generating inputs of death. In Proc 13th Conference on computer and communications security (CCS) (pp. 322–335).

  • Cadar, C., Dunbar, D., Engler, D.R. (2008). KLEE: unassisted and automatic generation of high-coverage tests for complex systems programs. In Proceedings of the 8th symposium on operating systems design and implementation (OSDI) (pp. 209–224).

  • Castelein, J., Aniche, M., Soltani, M., Panichella, A., van Deursen, A. (2018). Search-based test data generation for sql queries. In Proceedings of the 40th international conference on software engineering (pp. 1230–1230). ACM.

  • Chan, M.-Y., & Cheung, S-C. (1999). Testing database applications with sql semantics. CODAS, 99, 363–374.

    Google Scholar 

  • Chen, T., Zhang, X.-S., Guo, S.-Z., Li, H.g.-Y., Wu, Y. (2013). State of the art: dynamic symbolic execution for automated test generation. Future Generation Computer Systems, 29(7), 1758–1773.

    Article  Google Scholar 

  • Clarke, L.A. (1976a). A system to generate test data and symbolically execute programs. IEEE Transactions on Software Engineering (TSE), 2(3), 215–222.

  • Clarke, L.A. (1976b). Test data generation and symbolic execution of programs as an aid to program validation. PhD thesis, University of Colorado at Boulder.

  • De La Riva, C, Suárez-Cabal, MJ, Tuya, J. (2010). Constraint-based test database generation for SQL queries. In Proceedings of the 5th workshop on automation of software testing (pp. 67–74).

  • de Moura, L, & Bjørner, N. (2008). Z3: an efficient SMT solver. In International conference on tools and algorithms for the construction and analysis of systems (TACAS) (pp. 337–340).

  • Deng, Y, Frankl, P, Chays, D. (2005). Testing database transactions with AGENDA. In Proceedings of the 27th international conference on software engineering (pp. 78–87).

  • Elkarablieh, B., Garcia, I., Suen, Y.L., Khurshid, S. (2007). Assertion-based repair of complex data structures. In Proceedings of the 22nd international conference on automated software engineering (ASE) (pp. 64–73).

  • Emmi, M., Majumdar, R., Sen, K. (2007). Dynamic test input generation for database applications. In Proceedings of the 2007 international symposium on software testing and analysis (pp. 151–162).

  • Ghafoor, M.A., Mahmood, M.S., Siddiqui, J.H. (2016). Effective partial order reduction in model checking database applications. In 2016 IEEE international conference on software testing, verification and validation (ICST) (pp. 146–156). IEEE.

  • Godefroid, P. (2007). Compositional dynamic test generation. In Proceedings of the 34th symposium on principles of programming languages (POPL) (pp. 47–54).

  • Godefroid, P., Klarlund, N., Sen, K. (2005). DART: directed automated random testing. In Proceedings of the 2005 conference on programming languages design and implementation (PLDI) (pp. 213–223).

  • Jackson, D. (2002). Alloy: a lightweight object modelling notation. ACM Transactions on Software Engineering and Methodology (TOSEM), 11(2), 256–290.

    Article  Google Scholar 

  • Khalek, SA, & Khurshid, S. (2011). Systematic testing of database engines using a relational constraint solver. In Proceedings of the fourth IEEE international conference on software testing, verification and validation (ICST) (pp. 50–59).

  • Khurshid, S., Pasareanu, C.S., Visser, W. (2003). Generalized symbolic execution for model checking and testing. In Proceedings of the 9th international conference on tools and algorithms for the construction and analysis of systems (TACAS) (pp. 553–568).

  • King, J. C. (1976). Symbolic execution and program testing. Communications ACM, 19(7), 385–394.

    Article  MathSciNet  Google Scholar 

  • Li, C, & Csallner, C. (2010). Dynamic symbolic database application testing. In Proceedings of the third international workshop on testing database systems (DBTest).

  • Mahmood, MS, Ghafoor, M, Siddiqui, JH. (2016). Symbolic execution of stored procedures in database management systems. In 2016 31st IEEE/ACM international conference on automated software engineering (ASE) (pp. 519–530). IEEE.

  • Marcozzi, M., Vanhoof, W., Hainaut, J.-L. (2013). A relational symbolic execution algorithm for constraint-based testing of database programs. In IEEE 13th international working conference on source code analysis and manipulation (SCAM) (pp. 179–188).

  • Marcozzi, M., Vanhoof,W., Hainaut, J.-L. (2014). Towards testing of full-scale SQL applications using relational symbolic execution. In Proceedings of the 6th international workshop on constraints in software testing, verification, and analysis (pp. 12–17).

  • Marcozzi, M., Vanhoof, W., Hainaut, J.-L. (2015). Relational symbolic execution of sql code for unit testing of database programs. Science of Computer Programming, 105, 44–72.

    Article  Google Scholar 

  • Matthew, N, & Stones, R. (2005). Beginning Databases with PostgreSQL. Apress.

  • McMinn, P., Wright, C.J., Kapfhammer, G.M. (2015). An analysis of the effectiveness of different coverage criteria for testing relational database schema integrity constraints. Department of Computer Science, University of Sheffield, Tech. Rep.

  • Mohamed, M.A., Altrafi, O.G., Ismail, M.O. (2014). Relational vs. nosql databases: a survey. International Journal of Computer and Information Technology, 3(03), 598–601.

    Google Scholar 

  • Pan, K., Wu, X., Xie, T. (2011). Database state generation via dynamic symbolic execution for coverage criteria. In Proceedings of the fourth international workshop on testing database systems (p. 4).

  • Pan, K., Wu, X., Xie, T. (2014). Guided test generation for database applications via synthesized database interactions. ACM Transactions on Software Engineering and Methodology (TOSEM), 23(2), 12.

    Article  Google Scholar 

  • Pan, K., Wu, X., Xie, T. (2015). Program-input generation for testing database applications using existing database states. Automated Software Engineering, 22(4), 439–473.

    Article  Google Scholar 

  • Person, S., Yang, G., Rungta, N., Khurshid, S. (2011). Directed incremental symbolic execution. In Proceedings of the 2011 conference on programming languages design and implementation (PLDI) (pp. 504–515).

  • Ramos, DA, & Engler, DR. (2011). Practical, low-effort equivalence verification of real code. In Proceedings of the 23rd international conference on computer aided verification (CAV) (pp. 669– 685).

  • Sen, K, Marinov, D, Agha, G. (2005). CUTE: a concolic unit testing engine for C. In Proceedings of the 5th joint meeting of the European software engineering conference and symposium on foundations of software engineering (ESEC/FSE) (pp. 263–272).

  • Seo, C, Malek, S, Medvidovic, N. (2008). Component-level energy consumption estimation for distributed java-based software systems. In Proceedings of the 11th international symposium on component-based software engineering (pp. 97–113).

  • Siddiqui, JH, & Khurshid, S. (2010). ParSym: parallel symbolic execution. In Proceedings of the 2nd international conference on software technology and engineering (ICSTE) (pp. pages V1: 405– 409).

  • Siddiqui, JH, & Khurshid, S. (2012a). Staged symbolic execution. In Proceedings of the 27th symposium on applied computing (SAC): software verification and testing track (SVT).

  • Siddiqui, JH, & Khurshid, S. (2012b). Scaling symbolic execution using ranged analysis. In Proceedings of the 27th annual conference on object oriented programming systems, languages, and applications (OOPSLA).

  • Silberschatz, A, Korth, HF, Sudarshan, S, et al. (1997). Database system concepts (Vol. 4). New York: McGraw-Hill.

    MATH  Google Scholar 

  • Sörensson, N, & Een, N. (2003). An extensible SAT-solver. In Proceedings of the 6th international conference on theory and applications of satisfiability testing (SAT) (pp. 502–518).

  • Staats, M, & Pǎsǎreanu, C. (2010). Parallel symbolic execution for structural test generation. In Proceedings of the 19th international symposium on software testing and analysis (ISSTA) (pp. 183–194).

  • Suárez-Cabal, M J, de la Riva, C, Tuya, J, Blanco, R. (2017). Incremental test data generation for database queries. Automated Software Engineering, 24(4), 719–755.

    Article  Google Scholar 

  • Tsumura, K, Washizaki, H, Fukazawa, Y, Oshima, K, Mibe, R. (2016). Pairwise coverage-based testing with selected elements in a query for database applications. In 2016 IEEE ninth international conference on software testing, verification and validation workshops (ICSTW) (pp. 92–101). IEEE.

  • Tuya, J, Suárez-Cabal, MJ, de la Riva, C. (2010). Full predicate coverage for testing SQL database queries. Journal of Software Testing, Verification and Reliability, 20(3), 237–288.

    Article  Google Scholar 

  • Veanes, M, Grigorenko, P, De Halleux, P, Tillmann, N. (2009). Symbolic query exploration. In Formal methods and software engineering (pp. 49–68). Springer.

  • Wei, K., Muthuprasanna, M., Kothari, S. (2006). Preventing sql injection attacks in stored procedures. In Proceedings of the Australian software engineering conference (ASWEC). ISBN 0-7695-2551-2. https://doi.org/10.1109/ASWEC.2006.40(pp. 191–198).

  • Yang, G, Păsăreanu, C S, Khurshid, S. (2012). Memoized symbolic execution. In Proceedings of the 2012 international symposium on software testing and analysis (ISSTA), ISSTA 2012. ISBN 978-1-4503-1454-1. https://doi.org/10.1145/2338965.2336771. (pp. 144–154).

  • Zhang, J, Xu, C, Cheung, S-C. (2001). Automatic generation of database instances for white-box testing. In 25th annual international computer software and applications conference, 2001. COMPSAC 2001 (pp. 161–165). IEEE.

  • Zhu, H., Hall, P.A.V., May, J.H.R. (1997). Software unit test coverage and adequacy. ACM Computing Surveys (CSUR), 29(4), 366–427.

    Article  Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Lahore University of Management Sciences, Lahore, Pakistan

    Maryam Abdul Ghafoor & Junaid Haroon Siddiqui

  2. University of Illinois at Urbana Champaign, Champaign, IL, USA

    Muhammad Suleman Mahmood

Authors
  1. Maryam Abdul Ghafoor
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Muhammad Suleman Mahmood
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Junaid Haroon Siddiqui
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Maryam Abdul Ghafoor.

Additional information

Publisher’s note

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Abdul Ghafoor, M., Mahmood, M.S. & Siddiqui, J.H. Extending symbolic execution for automated testing of stored procedures. Software Qual J 28, 853–887 (2020). https://doi.org/10.1007/s11219-019-09453-6

Download citation

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s11219-019-09453-6

Keywords

Search

Navigation