diff options
| author | James Elliott <james-d-elliott@users.noreply.github.com> | 2023-10-27 20:20:29 +1100 |
|---|---|---|
| committer | James Elliott <james-d-elliott@users.noreply.github.com> | 2024-03-04 20:28:24 +1100 |
| commit | c0dbdd97ab2ac580e3da07a0137dbc7a1b9c9b83 (patch) | |
| tree | 57daff9cacd6a06524a87e40d9ee5d1dbcb483d3 /internal/storage/sql_provider_queries.go | |
| parent | 358b6679b545d5227a8d5bd2c9e0f95e59ebc4f7 (diff) | |
feat(web): multiple webauthn credential registration
This implements multiple WebAuthn Credential registrations by means of a generic user settings UI.
Closes #275, Closes #4366
Signed-off-by: James Elliott <james-d-elliott@users.noreply.github.com>
Co-authored-by: Clément Michaud <clement.michaud34@gmail.com>
Co-authored-by: Stephen Kent <smkent@smkent.net>
Co-authored-by: Amir Zarrinkafsh <nightah@me.com>
Diffstat (limited to 'internal/storage/sql_provider_queries.go')
| -rw-r--r-- | internal/storage/sql_provider_queries.go | 76 |
1 files changed, 45 insertions, 31 deletions
diff --git a/internal/storage/sql_provider_queries.go b/internal/storage/sql_provider_queries.go index 46a9635b6..882258d5c 100644 --- a/internal/storage/sql_provider_queries.go +++ b/internal/storage/sql_provider_queries.go @@ -118,62 +118,76 @@ const ( WHERE username = ?;` ) +//nolint:gosec // The following queries are not hard coded credentials. const ( - queryFmtSelectWebAuthnDevices = ` - SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning + queryFmtSelectWebAuthnCredentials = ` + SELECT id, created_at, last_used_at, rpid, username, description, kid, aaguid, attestation_type, attachment, transport, sign_count, clone_warning, discoverable, present, verified, backup_eligible, backup_state, public_key FROM %s LIMIT ? OFFSET ?;` - queryFmtSelectWebAuthnDevicesEncryptedData = ` - SELECT id, public_key - FROM %s;` - - queryFmtSelectWebAuthnDevicesByUsername = ` - SELECT id, created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning + queryFmtSelectWebAuthnCredentialsByUsername = ` + SELECT id, created_at, last_used_at, rpid, username, description, kid, aaguid, attestation_type, attachment, transport, sign_count, clone_warning, discoverable, present, verified, backup_eligible, backup_state, public_key FROM %s WHERE username = ?;` - queryFmtUpdateWebAuthnDevicePublicKey = ` - UPDATE %s - SET public_key = ? + queryFmtSelectWebAuthnCredentialsByRPIDByUsername = ` + SELECT id, created_at, last_used_at, rpid, username, description, kid, aaguid, attestation_type, attachment, transport, sign_count, clone_warning, discoverable, present, verified, backup_eligible, backup_state, public_key + FROM %s + WHERE rpid = ? AND username = ?;` + + queryFmtSelectWebAuthnCredentialByID = ` + SELECT id, created_at, last_used_at, rpid, username, description, kid, aaguid, attestation_type, attachment, transport, sign_count, clone_warning, discoverable, present, verified, backup_eligible, backup_state, public_key + FROM %s WHERE id = ?;` - queryFmtUpdateWebAuthnDeviceRecordSignIn = ` + queryFmtUpdateUpdateWebAuthnCredentialDescriptionByUsernameAndID = ` UPDATE %s - SET - rpid = ?, last_used_at = ?, sign_count = ?, - clone_warning = CASE clone_warning WHEN TRUE THEN TRUE ELSE ? END - WHERE id = ?;` + SET description = ? + WHERE username = ? AND id = ?;` - queryFmtUpdateWebAuthnDeviceRecordSignInByUsername = ` + queryFmtUpdateWebAuthnCredentialRecordSignIn = ` UPDATE %s SET - rpid = ?, last_used_at = ?, sign_count = ?, + rpid = ?, last_used_at = ?, sign_count = ?, discoverable = ?, present = ?, verified = ?, backup_eligible = ?, backup_state = ?, clone_warning = CASE clone_warning WHEN TRUE THEN TRUE ELSE ? END - WHERE username = ? AND kid = ?;` - - queryFmtUpsertWebAuthnDevice = ` - REPLACE INTO %s (created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);` + WHERE id = ?;` - queryFmtUpsertWebAuthnDevicePostgreSQL = ` - INSERT INTO %s (created_at, last_used_at, rpid, username, description, kid, public_key, attestation_type, transport, aaguid, sign_count, clone_warning) - VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) - ON CONFLICT (username, description) - DO UPDATE SET created_at = $1, last_used_at = $2, rpid = $3, kid = $6, public_key = $7, attestation_type = $8, transport = $9, aaguid = $10, sign_count = $11, clone_warning = $12;` + queryFmtInsertWebAuthnCredential = ` + INSERT INTO %s (created_at, last_used_at, rpid, username, description, kid, aaguid, attestation_type, attachment, transport, sign_count, clone_warning, discoverable, present, verified, backup_eligible, backup_state, public_key) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);` - queryFmtDeleteWebAuthnDevice = ` + queryFmtDeleteWebAuthnCredential = ` DELETE FROM %s WHERE kid = ?;` - queryFmtDeleteWebAuthnDeviceByUsername = ` + queryFmtDeleteWebAuthnCredentialByUsername = ` DELETE FROM %s WHERE username = ?;` - queryFmtDeleteWebAuthnDeviceByUsernameAndDescription = ` + queryFmtDeleteWebAuthnCredentialByUsernameAndDescription = ` DELETE FROM %s WHERE username = ? AND description = ?;` + + queryFmtSelectWebAuthnCredentialsEncryptedData = ` + SELECT id, public_key + FROM %s;` + + queryFmtUpdateWebAuthnCredentialsEncryptedData = ` + UPDATE %s + SET public_key = ? + WHERE id = ?;` +) + +const ( + queryFmtInsertWebAuthnUser = ` + INSERT INTO %s (rpid, username, userid) + VALUES (?, ?, ?);` + + queryFmtSelectWebAuthnUser = ` + SELECT id, rpid, username, userid + FROM %s + WHERE rpid = ? AND username = ?;` ) const ( |