Package org.kawanfw.sql.api.server

Class StatementNormalizer

java.lang.Object
org.kawanfw.sql.api.server.StatementNormalizer
public class StatementNormalizer
extends Object
Allows to "normalize" the text of a SQL statement. The normalization will remove all excess spaces, tabs, or line breaks. Also, the SQL keywords will appear in uppercase, and columns and table names in lowercase. This ensures that a SQL statement that should be recognized won't be rejected due to differences in capitalization or spaces between words.

For example the two following statements: 
 SELECT *     from     my_table   where my_colum   =   ? 
 SELECT 	*         from     my_table      where     my_colum   =   ?"
will be normalized to the same String with extra spaces removed:
SELECT * FROM my_table WHERE my_colum = ?

Note that all string and numeric values are replaced by interrogation marks.
So, when using normalization, the following input statement are different:
  • SELECT film_title, RENTAL_RATE from FILM where film_title like '%Star%' and rental_rate > 2.20
  • select film_title, rental_rate from film where film_title like '%Alien%' and rental_rate > 3.30
  • select film_title, rental_rate from film where film_title like '%Odyssey%' and rental_rate > 4.40
They will all be normalized to:
  • SELECT film_title , rental_rate FROM film WHERE film_title LIKE ? AND rental_rate > ?
If normalization cannot be applied due to unsupported or sloppy formatting, the original SQl statement is returned and an Exception is set.

The two main reasons of normalization failure are:
  • The input SQL statement contains nested SQL comments which this version's parser do not support and thus cannot treat. This triggers an SQLException. (Regular non-nested SQL comments are successfully parsed.)
  • The input SQl statement is somewhat invalid and triggers an Exception. call.
You may check if the statement is successfully normalized with the isSuccess() call.
The caught Exception may be retrieved by a getException() call.
Note that normalization is used in all provided SqlFirewallManager implementations, this means that the SQL statements are all normalized before the applying the firewall rules and security checks.
Since:
1.0
Author:
Nicolas de Pomereu

  • Constructor Details

    • StatementNormalizer

      public StatementNormalizer​(String sql)
      Constructor
      Parameters:
      sql - the SQL statement to normalize

  • Method Details

    • getNormalized

      public String getNormalized()
      Returns normalized text of the SQL statement. This means that in addition to clean the statement, numbers and strings (contained in '') will be replaced by "?" characters.
      Returns:
      the normalized text of the SQL statement.

    • isSuccess

      public boolean isSuccess()
      Says if the normalization attempt is a success.
      Returns:
      true if the normalization attempt is a success ,else false

    • isWithNestedComments

      public boolean isWithNestedComments()
      Says if the failure reason was that the SQL statement had unsupported nested comments
      Returns:
      if the failure reason was that the SQL statement had unsupported nested comments

    • isWithOddQuotesNumber

      public boolean isWithOddQuotesNumber()
      Says if the failure reason was that the SQL statement had an odd number of single quote and thus could not be treated
      Returns:
      true if the failure reason was that the SQL statement had an odd number of single quote and thus could not be treated, else false

    • getException

      public Exception getException()
      Gets the Exception caught if the normalization was a failure (nested comments, odd number of single quotes or any other cause).
      Returns:
      Gets the Exception caught