1 / 10

SQL DOM

Dan Osicka SE681 – Fall 2006. SQL DOM. Compile Time Checking of Dynamic SQL Statements.

theola
Download Presentation

SQL DOM

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dan Osicka SE681 – Fall 2006 SQL DOM 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( EOrderDetailsTblColumns.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?

More Related