Article

SQL DOM: compile time checking of dynamic SQL statements

Authors:

Russell A. McClure,

Ingolf H. KrügerAuthors Info & Claims

ICSE '05: Proceedings of the 27th international conference on Software engineering

Pages 88 - 96

https://doi.org/10.1145/1062455.1062487

Published: 15 May 2005 Publication History

Abstract

Most object oriented applications that involve persistent data interact with a relational database. The most common interaction mechanism is a call level interface (CLI) such as ODBC or JDBC. While there are many advantages to using a CLI -- expressive power and performance being two of the most key -- there are also drawbacks. Applications communicate through a CLI by constructing strings that contain SQL statements. These SQL statements are only checked for correctness at runtime, tend to be fragile and are vulnerable to SQL injection attacks. To solve these and other problems, we present the SQL DOM: a set of classes that are strongly-typed to a database schema. Instead of string manipulation, these classes are used to generate SQL statements. We show how to extract the SQL DOM automatically from an existing database schema, demonstrate its applicability to solve the mentioned problems, and evaluate its performance.

References

[1]

.NET Framework. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/netfxanchor.asp, 2004.

[2]

ADO.NET. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp, 2004.

[3]

Atkinson, M. P., and Morrison, R. Orthogonally persistent object systems. VLDB Journal, 4(3):319--401, 1995.

[4]

American National Standard for Information Technology. Database languages -- SQLJ -- Part 1: SQL routines using the Java programming language. Technical Report ANSI/INCITS 331.1-1999, InterNational Committee for Information Technology Standards (formerly NCITS), 1999.

[5]

Brant, J., and Yoder, J. W. Creating reports with query objects. In Harrison, N., Foote, B., and Rohnert, H., editors, Pattern Languages of Program Design 4. Addison Wesley, 2000.

[6]

C#. http://msdn.microsoft.com/vcsharp/, 2004.

[7]

Cengija, D. Hibernate your data. onJava.com, 2004.

[8]

Clark, J., and DeRose, S. XML Path Language (XPath) Version 1.0. Technical report, W3C, 1999.

[9]

Cook, W., and Rai, S. Safe Query Objects: Statically-typed objects as remotely-executable queries. http://www.cs.utexas.edu/users/wcook/Drafts/SafeQuery_CookRai.pdf, 2004.

[10]

Dub, J. A., Sapir, R., and Purich, P. Oracle Application Server TopLink application developers guide, 10g (9.0.4). Oracle Corporation, 2003.

[11]

Embedded SQL for C. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlforc/ec_6_epr_01_3m03.asp, 2004.

[12]

Gould, C., Su, Z., and Devanbu, P. Static checking of dynamically generated queries in database applications. In Proceedings, 26 th International Conference on Software Engineering (ICSE). IEEE Press, 2004.

Digital Library

[13]

Hamilton, G., and Cattell, R. JDBC patterns. Sun Microsystems, 2003.

[14]

Howard, M., and LeBlanc, D. Writing Secure Code, Second Edition, Microsoft Press, ch. 12, 2003.

Digital Library

[15]

Keller, W. Mapping objects to tables - a pattern language. In Proceedings of the 1997 European Pattern Languages of Programming Conference, number 120/SW1/FB in Siemens Technical Report, Irsee, Germany, X. EA Generali, Vienna, Austria.

[16]

Leijen, D., and Meijer, E., Domain specific embedded compilers. In Proceedings of the 2 nd conference on Domain-specific languages, pages 109--122. ACM Press, 1999.

Digital Library

[17]

Maier, D. Representing database programs as objects. In Bancilhon, F., and Buneman, P., editors, Advances in Database Programming Languages, Papers from DBPL-1, September 1987, Roscoff, France, pages 377--386. ACM Press / Addison Wesley, 1987.

Digital Library

[18]

Matena, V., and Hapner, M. Enterprise Java Beans Specification 1.0. Sun Microsystems, 1998.

[19]

Oracle SQLJ Roadmap, http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/oracle_sqlj_roadmap.pdf, 2004.

[20]

Russell, C. Java Data Objects (JDO) Specification JSR-12. Sun Microsystems, 1998.

[21]

Sanders, R. E. ODBC 3.5 Developer's Guide. M&T Books, 1998.

Digital Library

[22]

Smith, E. J. CodeSmith. http://www.ericjsmith.net/codesmith/, 2004.

Cited By

Mahesh RChellathurai SThirunavukkarasu MRaman P(2024)SQL Injection Attack Detection and Prevention Based on Manipulating the SQL Query Input AttributesComputational Sciences and Sustainable Technologies10.1007/978-3-031-50993-3_17(213-221)Online publication date: 3-Feb-2024
https://doi.org/10.1007/978-3-031-50993-3_17
Veerabudren KBekaroo G(2022)Security in Web Applications: A Comparative Analysis of Key SQL Injection Detection Techniques2022 4th International Conference on Emerging Trends in Electrical, Electronic and Communications Engineering (ELECOM)10.1109/ELECOM54934.2022.9965264(1-6)Online publication date: 22-Nov-2022
https://doi.org/10.1109/ELECOM54934.2022.9965264
Sabir MSajid AAshraf F(2022)A Systemic Security and Privacy Review: Attacks and Prevention Mechanisms Over IoT LayersBig Data Analytics and Computational Intelligence for Cybersecurity10.1007/978-3-031-05752-6_5(69-89)Online publication date: 2-Sep-2022
https://doi.org/10.1007/978-3-031-05752-6_5
Show More Cited By

Index Terms

SQL DOM: compile time checking of dynamic SQL statements
1. Software and its engineering
  1. Software creation and management
    1. Software post-development issues
      1. Software reverse engineering
  2. Software organization and properties
    1. Software system structures
      1. Abstraction, modeling and modularity

Recommendations

SQL: From Traditional Databases to Big Data
SIGCSE '16: Proceedings of the 47th ACM Technical Symposium on Computing Science Education

The Structured Query Language (SQL) is the main programing language designed to manage data stored in database systems. While SQL was initially used only with relational database management systems (RDBMS), its use has been significantly extended with ...
Comparing NoSQL MongoDB to an SQL DB
ACMSE '13: Proceedings of the 51st annual ACM Southeast Conference

NoSQL database solutions are becoming more and more prevalent in a world currently dominated by SQL relational databases. NoSQL databases were designed to provide database solutions for large volumes of data that is not structured. However, the ...
Generating SQL/XML query and update statements
CIKM '09: Proceedings of the 18th ACM conference on Information and knowledge management

The XML support in relational databases and the SQL/XML language are still relatively new as compared to purely relational databases and traditional SQL. Today, most database users have a strong relational and SQL background. SQL/XML enables users to ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

ICSE '05: Proceedings of the 27th international conference on Software engineering

May 2005

754 pages

ISBN:1581139632

DOI:10.1145/1062455

General Chair:
Gruia-Catalin Roman
Washington University in St. Louis
,
Program Chairs:
William Griswold
University of California, San Diego
,
Bashar Nuseibeh
The Open University, UK

Copyright © 2005 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 15 May 2005

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Article

Conference

ICSE05

Sponsor:

ICSE05: 27th International Conference on Software Engineering

May 15 - 21, 2005

MO, St. Louis, USA

Acceptance Rates

Overall Acceptance Rate 276 of 1,856 submissions, 15%

Upcoming Conference

ICSE 2025

2025 IEEE/ACM 46th International Conference on Software Engineering

April 26 - May 3, 2025

Ottawa , ON , Canada

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

81
Total Citations
View Citations
1,726
Total Downloads

Downloads (Last 12 months)22
Downloads (Last 6 weeks)2

Reflects downloads up to 13 Sep 2024

Other Metrics

View Author Metrics

Citations

Cited By

Mahesh RChellathurai SThirunavukkarasu MRaman P(2024)SQL Injection Attack Detection and Prevention Based on Manipulating the SQL Query Input AttributesComputational Sciences and Sustainable Technologies10.1007/978-3-031-50993-3_17(213-221)Online publication date: 3-Feb-2024
https://doi.org/10.1007/978-3-031-50993-3_17
Veerabudren KBekaroo G(2022)Security in Web Applications: A Comparative Analysis of Key SQL Injection Detection Techniques2022 4th International Conference on Emerging Trends in Electrical, Electronic and Communications Engineering (ELECOM)10.1109/ELECOM54934.2022.9965264(1-6)Online publication date: 22-Nov-2022
https://doi.org/10.1109/ELECOM54934.2022.9965264
Sabir MSajid AAshraf F(2022)A Systemic Security and Privacy Review: Attacks and Prevention Mechanisms Over IoT LayersBig Data Analytics and Computational Intelligence for Cybersecurity10.1007/978-3-031-05752-6_5(69-89)Online publication date: 2-Sep-2022
https://doi.org/10.1007/978-3-031-05752-6_5
Bayyapu N(2021)SQL Injection Attacks and Mitigation Strategies: The Latest ComprehensionAdvances in Cybersecurity Management10.1007/978-3-030-71381-2_10(199-220)Online publication date: 16-Jun-2021
https://doi.org/10.1007/978-3-030-71381-2_10
Courant J(2021)Developer-Proof Prevention of SQL InjectionsFoundations and Practice of Security10.1007/978-3-030-70881-8_6(82-99)Online publication date: 27-Feb-2021
https://doi.org/10.1007/978-3-030-70881-8_6
Bhateja NSikka SMalhotra A(2021)A Review of SQL Injection Attack and Various Detection ApproachesSmart and Sustainable Intelligent Systems10.1002/9781119752134.ch34(481-489)Online publication date: 24-Mar-2021
https://doi.org/10.1002/9781119752134.ch34
Sheykhkanloo N(2020)A Learning-based Neural Network Model for the Detection and Classification of SQL Injection AttacksDeep Learning and Neural Networks10.4018/978-1-7998-0414-7.ch026(450-475)Online publication date: 2020
https://doi.org/10.4018/978-1-7998-0414-7.ch026
Jana ABordoloi PMaity D(2020)Input-based Analysis Approach to Prevent SQL Injection Attacks2020 IEEE Region 10 Symposium (TENSYMP)10.1109/TENSYMP50017.2020.9230758(1290-1293)Online publication date: 2020
https://doi.org/10.1109/TENSYMP50017.2020.9230758
Jana AMaity D(2020)Code-based Analysis Approach to Detect and Prevent SQL Injection Attacks2020 11th International Conference on Computing, Communication and Networking Technologies (ICCCNT)10.1109/ICCCNT49239.2020.9225575(1-6)Online publication date: Jul-2020
https://doi.org/10.1109/ICCCNT49239.2020.9225575
Jeya Mala DEswaran MDeepika Malar N(2018)Intelligent Vulnerability Analyzer – A Novel Dynamic Vulnerability Analysis Framework for Mobile Based Online ApplicationsSmart and Innovative Trends in Next Generation Computing Technologies10.1007/978-981-10-8660-1_60(805-823)Online publication date: 9-Jun-2018
https://doi.org/10.1007/978-981-10-8660-1_60
Show More Cited By

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents