☰

Specification

Introduction

This section defines the SQL commenter algorithm which augments a SQL statement with a comment containing serialized key value pairs that are retrieved from the various ORMs and frameworks in your programming language and environment of choice.

A preview of the result can be seen as per exhibit

SELECT * FROM FOO /*action='%2Fparam*d',controller='index,'framework='spring', traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01', tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/

Read along to see how you can conform to the specification and produce similar output.

Format

The final comment SHOULD be affixed to the final SQL statement in the format

<SQL STATEMENT> /*<ATTRIBUTE_KEY_VALUE_PAIRS>*/

Comment escaping

Comments within SQL comments are of the format

If a comment already exists within a SQL statement, we MUST NOT mutate that statement.

Separator

Each key value pair MUST be separated by a comma “,” so for example, given

Values: [<FIELD_1>, <FIELD_2>, <FIELD_3>, ...]

Expected concatenation result: <FIELD_1>,<FIELD_2>,<FIELD_3>,<FIELD_N...>

Meta characters

Meta characters such as ' should be escaped with a slash \. That creates the following algorithm:

Algorithm

algorithm(value):     escaped := value.escape_with_slash_any_of(')      return escaped

Key serialization

  1. URL encode the key e.g. given route parameter, that’ll become route%20parameter

Which produces the following algorithm:

Algorithm

key_serialization(key):     encoded := url_encode(key)     meta_escaped := escape_meta_characters(encoded)      return meta_escaped

Value serialization

  1. URL encode the value e.g. given /param first, that SHOULD become %2Fparam%20first

  2. Escape meta-characters within the raw value; a single quote ' becomes \'

  3. SQL escape the value by placing it within two single quotes e.g.

    DROP should become 'DROP'

    FOO 'BAR should become 'FOO%20\'BAR'

And when generalized into an algorithm:

Algorithm

value_serialization(value):     encoded := url_encode(value)     meta_escaped := escape_meta_characters(encoded)     final := sql_escape_with_single_quotes(meta_escaped)      return final

and running the algorithm on the following table will produce

value url_encode(value) sql_escape_with_single_quotes
DROP TABLE FOO DROP%20TABLE%20FOO 'DROP%20TABLE%20FOO'
/param first %2Fparam%20first '%2Fparam%20first'
1234 1234 '1234'

Key Value format

Given a key value pair (key, value):

  1. Run the Key serialization algorithm on key

  2. Run the Value serialization algorithm on value

  3. Using an equals sign =, concatenate the result from 1. and 2. to give

    <SERIALIZED_KEY>=<SERIALIZED_VALUE> gotten from:

    serialize_key(key)=serialize_value(value)

Thus given for example the following key value pairs

key value pair serialized_key serialized_value Final
route=/polls 1000 route '%2Fpolls%201000' route='%2Fpolls%201000'
name='DROP TABLE FOO' route '%2Fpolls%201000' route='%2Fpolls%201000'
name''="DROP TABLE USERS'" name='' DROP%20TABLE%20USERS' name=''=‘DROP%20TABLE%20USERS'’

Sorting

With a list of serialized key=value pairs, sort them by lexicographic order.

Algorithm

sort(key_value_pairs):     sorted = lexicographically_sort(key_value_pairs)      return sorted

Exhibit

Thus

    sort([         traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',         tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7',         route='%2Fparam*d',         controller='index',     ])

produces

 [         controller='index',         route='%2Fparam*d',         traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',         tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7',  ]

Concatenation

After all the keys and values have been serialized and sorted, they MUST be joined by a comma ,.

If no values are present, concatenate MUST return the empty value ''

Algorithm

concatenate(key_value_pairs):      if len(key_value_pairs) == 0:         return ''      return ','.join(key_value_pairs)

Exhibit

Therefore

    concatenate([         controller='index',         route='%2Fparam*d',         traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',         tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7',     ])

produces

controller='index',route='%2Fparam*d',traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'

Affix comment

After serialization, sorting, concatenation, the final form MUST be placed between /* and */

Algorithm

affix_comment(sql, concatenated):     if is_empty(concatenated):         return sql // Do NOT modify the SQL if concatenated is blank.      affixed := sql + '/*' + concatenated + '*/'      return affixed

Exhibit

for example given

affix_comment('SELECT * from FOO', '')

produces

SELECT * from FOO 
affix_comment('SELECT * from FOO', "route='%2Fparam*d'")

produces

SELECT * from FOO /*route='%2Fparam*d'*/

SQL commenter

Wrapping all the steps together, we thus have the following algorithm

sql_commenter(sql, attributes):     if contains_sql_comment(sql):         return sql # DO NOT mutate a statement with an already present comment.      serialized_key_value_pairs := []      for each attribute in attributes:         serialized := serialize_key_value_pair(attribute)         if serialized:             serialized_key_value_pairs.append(serialized)      sorted := sort(serialized_key_value_pairs)     concatenated := concatenate(sorted)     final := affix_comment(sql, concatenated)      return final

Exhibit

Running sql_commenter on an ORM integration that extracts the respective attributes:

sql_commenter('SELECT * FROM FOO', [         tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7',         traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',         framework='spring',         action='%2Fparam*d',         controller='index', ])

finally produces

SELECT * FROM FOO /*action='%2Fparam*d',controller='index,'framework='spring', traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01', tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/

Parsing

Parsing is the step to reverse sql-commenter and extract the key value attributes.

It’ll follow the following steps:

  1. Find the last comment so search for and strip out /* and */
  2. Split the comment by comma ,
  3. Split each key='value' pair so extract key and 'value'
    • 3.1. For key, unescape_meta_characters then url_decode
    • 3.2. For value, sql_unescape/trim the ' at the beginning and end of 'value' -> value
      • 3.2.1. Unescape the meta characters in value
      • 3.2.2. URL Decode the value

Algorithm

parse(sql_with_comment):     if !contains_sql_comment(sql_with_comment):         return sql_with_comment, null      // Since we now have a SQL comment, let's extract the serialized attributes.     sql_stmt, serialized_attrs := extract_sql_commenter(sql_with_comment)      if is_empty(serialized_attrs):         return sql_stmt, null      attrs := {}     kv_splits := split_by_comma(serialized_attrs)     for kv in kv_splits:         e_key, e_value := split_by_equals(kv)         key := decode_key(e_key)         value := decode_value(e_value)          attrs[key] = value      // Some attributes such as traceparent, tracestate, sampled     // might need need some grouping and reconstruction.     final := deconstruct_and_group_attributes(attrs)      return sql_stmt, final

Exhibit

Given the value from SQLCommenter exhibit

SELECT * FROM FOO /*action='%2Fparam*d',controller='index,'framework='spring', traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01', tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/

Running parse on the value

sql, attributes = parse(`SELECT * FROM FOO /*action='%2Fparam*d',controller='index,'framework='spring', traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01', tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/`)

produces

sql: SELECT * FROM FOO attributes: {     controller: 'index',     framework: 'spring',     action: '/param*d',     trace: {         sampled: true,         span_id: 'c532cb4098ac3dd2',         trace_id: '5bd66ef5095369c7b0d1f8f4bd33716a',         trace_state: [{'congo': 't61rcWkgMzE'}, {'rojo': '00f067aa0ba902b7'}],     }, }

References

Resource URL
URL Encoding https://en.wikipedia.org/wiki/Percent-encoding
Comments within SQL comments https://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements006.htm