· Mar 19, 2019

Rule to validate SQL syntaxis

Hi! We have received a request to create a new rule on CachéQuality to identify when a developer uses double quotes (" ") within any SQL statement.

We have been asked many times about SQL validation rules, and we would like to open a debate to allow everyone discuss what would you like to be checked on a SQL statement.

Current examples are for basic situations:

  • Using SQL.Statement class:

Set stmt = ##CLASS(%SQL.Statement).%New()
Set query = "Select Val1, Val2 FROM Table WHERE Val1=""SomeCondition"""

  • Using embedded SQL

&SQL(SELECT Val1, Val2
                INTO :val1, :val2
                FROM Table
                WHERE Val1="SomeCondition")

  • Ideally these should be :

Set stmt = ##CLASS(%SQL.Statement).%New()
Set query = "Select Val1, Val2 FROM Table WHERE Val1='SomeCondition'"
&SQL(SELECT Val1, Val2
                INTO :val1, :val2
                FROM Table
                WHERE Val1='SomeCondition')


All your feedback/explanations/requests are welcome!!

Discussion (13)2
Log in or sign up to continue

First, I would not use the input values directly in the query text, but would use Input Host Variables
Second, if it is necessary, it is more elegant to use the following syntax:

Set stmt ##CLASS(%SQL.Statement).%New()
Set query $$$FormatText("Select Val1, Val2 FROM Table WHERE Val1=%1",$$$quote("SomeCondition"))
Ideally these should be :
&SQL(SELECT Val1, Val2
INTO :val1, :val2
FROM Table
WHERE Val1='SomeCondition')

This is and now works:

               INTO :val1:val2
               FROM "Table"
               WHERE "Val1"='SomeCondition')

This is less about a community wide coding guideline and more about the validity and helpfulness of a code smell being added within CachéQuality.

In the OP the examples are basic but provide code that executes and just fine even though the code/syntax could be much better.

The code smell that Dainel is speaking of would identify areas where this, less than ideal, syntax is used so it can either be prevented or worked on as tech debt.

If the above code existed and there was a business need to enable support for delimited identifiers there would be problems.

This rule may break into multiple rules, one to identity the use of double quotes in embedded SQL, one for the use in SQL Statements.  Then also as Eduard suggested, a rule for parametrization as a whole.