SQL DOM

1
SQL DOM
Dan Osicka SE681 Fall 2006

• Compile Time Checking of Dynamic SQL Statements

Ingolf H. Krüger and Russell A. McClure, SQL
DOM compile time checking of dynamic SQL
statements, International Conference on Software
Engineering, IEEE Computer Society, University of
California, San Diego, La Jolla, CA, 2005, pp.
88-96.
2
Summary

• Current Situation
• Call Level Interfaces
• Dynamic SQL Strings
• Problems
• Schema Changes
• Typing Mistakes
• Security Flaws
• Proposed Solution SQL DOM
• Concept SQL DOM Explained
• Solutions to Problems
• Compile Time Error Notification
• Object Oriented Model
• IDE Integration
• Performance Considerations

3
Current Situation - CLI

• Call Level Interfaces (CLI)
• Most commonly used database interaction in
  applications requiring persistence.
• Examples ODBC, JDBC
• Requires strings of SQL to be dynamically
  generated and sent to back office database
• Errors most often occur at runtime

4
Current Situation - Problems

• Difficult to debug dynamic statements
• Developers may avoid necessary database schema
  changes when considering the code changes
  required
• Type mismatches may not be caught until runtime
• Often vulnerable to SQL Injection Attacks

5
Proposed Solution SQL DOM

• The SQL DOM Concept uses an executable
  (sqldomgen) to
• Connect to the database via connection string
• Derive the database schema using ODBC
• Create a DLL with a class for each table and SQL
  select, insert, update, and delete statement
• Embed the DLL in the target application.
  Developers utilize the DLL to generate SQL
  strings in a fixed and secured format

6
Proposed Solution SQL DOM
Consider the ERD on the left Instead of using a
SQL string of return SELECT OrderID, ProductID
FROM OrderDetails We use the SQL DOM
classes OrderDetailsTblSelectSQLStmt txt new
OrderDetailsTblSelectSQLStmt( EOrderDetailsTblCol
umns.OrderID, EOrderDetailsTblColumns.ProductID
) return txt.getSQL While more verbose, the SQL
DOM method prevents typos by avoiding text,
catches errors at compile time, and integrates
with IDE IntelliSense.
7
SQL DOM Advantages

• Spelling mistakes eliminated
• Database schema changes result in immediate
  compile time errors and corrections
• Injection attacks minimized due to built in
  security features
• Developers use IDE assistance and do not need to
  memorize database layout
• Type mismatches eliminated since SQL DOM requires
  type matching at compile time

8
SQL DOM Performance

• SQL DOM performance is slower than dynamic
  strings

Note that while SQL DOM performance is
significantly slower than dynamic strings, when
compared to the cost of database access, the DOM
string generation equates to a small .2 of the
total access cost
9
Article Critique

• Excellent coverage of SQL DOM advantages over CLI
• Lacks detail in defending its ability to prevent
  SQL Injection Attacks
• Lacks detail on web server processing costs and
  total impact on web page generation

10
Review Questions

• What are some of the disadvantages of using a CLI
  dynamic SQL String approach?
• How does SQL DOM address and assist with these
  disadvantages?
• What is the overall application performance hit
  of using the SQL DOM product?