[Go] SQLMetastore does not work with pgx PostgreSQL driver

[developerDemetri]

Describe the bug When using github.com/godaddy/asherah/go/appencryption v0.2.6's SQLMetastore on Go 1.20.x with github.com/jackc/pgx/v5 as the database/sql driver, SQL queries fail with invalid syntax errors. eg.

myapp-postgres.local-1   | 2023-07-25 22:59:24.851 UTC [150] ERROR:  syntax error at or near "ORDER" at character 52
myapp-postgres.local-1   | 2023-07-25 22:59:24.851 UTC [150] STATEMENT:  SELECT key_record from encryption_key WHERE id = ? ORDER BY created DESC LIMIT 1
myapp.gin-1    | {"level":"error","error":"error from scanner: ERROR: syntax error at or near \"ORDER\" (SQLSTATE 42601)","time":"2023-07-25T22:59:24Z","message":"Failed to encrypt payload!"}

This appears to be a PostgreSQL syntax issue with the loadKeyQuery, storeKeyQuery, and loadLatestQuery placeholders: https://github.com/jackc/pgx/issues/1265

The following diff resolves the issue, verified by copy + pasting sql.go locally and updating

 const (
-       loadKeyQuery    = "SELECT key_record FROM encryption_key WHERE id = ? AND created = ?"
-       storeKeyQuery   = "INSERT INTO encryption_key (id, created, key_record) VALUES (?, ?, ?)"
-       loadLatestQuery = "SELECT key_record from encryption_key WHERE id = ? ORDER BY created DESC LIMIT 1"
+       loadKeyQuery    = "SELECT key_record FROM encryption_key WHERE id = $1 AND created = $2"
+       storeKeyQuery   = "INSERT INTO encryption_key (id, created, key_record) VALUES ($1, $2, $3)"
+       loadLatestQuery = "SELECT key_record from encryption_key WHERE id = $1 ORDER BY created DESC LIMIT 1"
 )

I'm happy to contribute a fix, but I need to know how maintainers would like to manage the MySQL vs PostgreSQL query strings in a seamless and backwards compatible fashion?

Maybe I could add an optional parameter to NewSQLMetastore that sets the correct set of query strings?

To Reproduce Call Encrypt() with a SQLMetastore that uses a PostgreSQL driver such as github.com/jackc/pgx/v5.

Expected behavior SQLMetastore works for all common PostgreSQL and MySQL drivers without hitting query syntax errors.

[aka-bo]

Hi @developerDemetri, thanks for the detailed write-up. Please have a look at #925 and let me know if this works for you...bonus points if you can take it for a spin against a PostgreSQL db 😄

[developerDemetri]

Hi @developerDemetri, thanks for the detailed write-up. Please have a look at #925 and let me know if this works for you...bonus points if you can take it for a spin against a PostgreSQL db 😄

Thanks @aka-bo, I confirmed that #925 fixes this issue (against my local PostgreSQL instance) by:

1. copy + pasting the updated sql.go locally
2. Updating my SQLMetastore creation to

   metastore := NewSQLMetastore(db, WithSQLMetastoreDBType(Postgres))

[aka-bo]

Thanks for confirming @developerDemetri, we should be able to get a new release rolled out shortly.