Support for case (in)sensitive identifiers

iamnotacake commented 2 years ago

How table/column matching works now?

PostgreSQL

Non-"-wrapped identifiers are converted to lowercase, just like how postgresql treats them. Encryptor config should contain lowercased identifiers for such table/column names.
"-wrapped identifiers are processed "as is". Encryptor config should contain exactly the same identifiers to match them.

MySQL

Table identifiers are treated as case-insensitive by default. Encryptor config should contain lowercased table names in such case.

There is a configuration option to change this:
```
database_config:
mysql_case_sensitive_table_identifiers: true
```
when enabled, encryptor config should contain exactly the same table name for matching to work.
Column names are always case-insensitive, so encryptor config should contain lowercased names.
`-wrapped identifiers do not affect case sensitivity. They are used in cases where, for example, you want to use a reserved word to name table or column, but not like in PostgreSQL.

This PR includes:

Fix for PostgreSQL tokenizer

When reading identifier (table or column name), remember if it was wrapped with quotes, and if so, return DOUBLE_QUOTE_STRING token type. Then, the AST will contain quote used to wrap identifier and converting AST back to text representation with .Format() will include those quotes. Only do this for PostgreSQL, since MySQL behaves differently.

Found this issue while executing insert into "Test" ("A", "BcD") values ('a'::bytea, 'b'::bytea);. Without the fix, Postgres complained about non-existing table name "test". In postgres Test and test are the same, but "Test" and Test are different.

Checklist

[x] Change is covered by automated tests
[x] The coding guidelines are followed
[x] ~Public API has proper documentation in the Acra documentation site or has PR on documentation repository with new changes~
[x] CHANGELOG.md is updated (in case of notable or breaking changes)
[x] CHANGELOG_DEV.md is updated
[x] ~Benchmark results are attached (if applicable)~
[x] ~Example projects and code samples are up-to-date (in case of API changes)~

iamnotacake commented 2 years ago

The problem with the failing test TestGeneralQueryParser_Parse was in encryptInsertQuery():

func (encryptor *QueryDataEncryptor) encryptInsertQuery(...) (bool, error) {
    tableName := insert.Table.Name
    schema := encryptor.schemaStore.GetTableSchema(tableName.String()) // schema was nil here

the .String() method of TableIdent included quotes and then, store.schemas[tableName] failed to find "TableWithoutColumnSchema" since it contained TableWithoutColumnSchema.

One option is to use .RawValue() instead, since it ignores quotes. This fixes test 27, but then 28 is failing, seems like similar replacement is needed somewhere else as well.

Lagovas commented 2 years ago

The problem with the failing test TestGeneralQueryParser_Parse was in encryptInsertQuery():
func (encryptor *QueryDataEncryptor) encryptInsertQuery(...) (bool, error) {
  tableName := insert.Table.Name
  schema := encryptor.schemaStore.GetTableSchema(tableName.String()) // schema was nil here
the .String() method of TableIdent included quotes and then, store.schemas[tableName] failed to find "TableWithoutColumnSchema" since it contained TableWithoutColumnSchema.

One option is to use .RawValue() instead, since it ignores quotes. This fixes test 27, but then 28 is failing, seems like similar replacement is needed somewhere else as well.

as I see, we have incorrect rule for column_id with SINGLE_QUOTE_STRING token. Because nor mysql, nor postgresql don't accept single-quoted identifiers. So, unit-tests incorrect too.

But if fix it, I don't understand why TableIdent has node.val with double-quotes and return it with RawValue() too... can you debug it by yourself?

cossacklabs / acra