Static Checking of Dynamically Generated Queries in Database Applications

1
Static Checking of Dynamically Generated Queries
in Database Applications

• Carl Gould, Zhendong Su, and Premkumar Devanbu
• Department of Computer Science
• University of California, Davis
• Presentation by Rick Fast

2
How do we find SQL errors in this dynamically
generated query at runtime?

• Several possible errors in the SQL generated by
  this Java code. How can we statically(at compile
  time) check them?

3
Solution Overview

• Group of members of the Department of Computer
  Science, University of California, Davis came up
  with a solution.
• Static checking of dynamically created SQL
  strings.
• Two terms Meta-Language- in this case Java.
  Object Language- in the case of this article
  SQL
• Meta-Language is converted to Finite State
  Automata.
• Context-Free Language (CFL) reachability
  algorithm is used to find all possible context
  paths through the dynamic SQL.
• Apply the a grammar (that specifies productions
  for SQL syntax/types) to the FSA to determine
  types.

4
Static String Analysis / Finite State Automata

• Find Hotspots in the program. EX return
  stmt.executeQuery(query)
• String analysis done in byte code! (Class files)
• String analysis isolates string operating code
  and builds FSA.
• This FSA is checked against a grammar. Verifies
  syntax.
• Also finds all possible contexts
• How do we type check the dynamic SQL?

5
Context Free Language Reachability Algorithm

• What type is NAME?
• Type is determined by the table the field comes
  from. CFL algorithm has visibility to db types.
• Adds new edges to automata until the simplest
  grammar is met.
• TABLE1 TABLE2 eventually become table_list.
• Eventually, the whole FSA has one edge
  select_stmt

6
No errortype edges, the SQL is OK!
7
Errors

• If an error edge is added to the FSA, then there
  are potential errors in the code!
• In many versions of SQL, cant concatenation a
  string and a number!

8
Strengths / Weaknesses

• Strength The method is proven in the paper to
  find any errors/potential errors in basic dynamic
  SQL with Java.
• Weakness The paper was very hard to read, and a
  lot of information that would have been useful in
  a quicker understanding was omitted from the
  paper.
• Weakness Although a major problem when
  developing dynamic SQL with Java, the problem has
  already been efficiently solved with Microsoft
  .NETs ADO and Typed Data Sets!
• Weakness The results from the testing of the
  procedure were based on very few, simple programs.

9
Questions

• 1. In the case of the examples given, what
  language does the term meta-language refer to?
• 2. After the CFL algorithm is run, how many edges
  will the Finite State Automata contain?
• 3. For projects that you have worked on at school
  or work that generate dynamic SQL, do you think
  that this method is worth the effort for static
  checking of generated SQL? Why (or why not)?