☰

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