From 608ce1d9910cd68ce825838ea313e02c598f908e Mon Sep 17 00:00:00 2001 From: Mica White Date: Mon, 8 Dec 2025 20:08:21 -0500 Subject: Stuff --- src/services/db/client.rs | 784 +++++++++++++++++++++++----------------------- src/services/db/jwt.rs | 398 +++++++++++------------ src/services/db/user.rs | 472 ++++++++++++++-------------- 3 files changed, 827 insertions(+), 827 deletions(-) (limited to 'src/services/db') diff --git a/src/services/db/client.rs b/src/services/db/client.rs index b8942e9..1ad97b1 100644 --- a/src/services/db/client.rs +++ b/src/services/db/client.rs @@ -1,392 +1,392 @@ -use std::str::FromStr; - -use exun::{RawUnexpected, ResultErrorExt}; -use sqlx::{ - mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, FromRow, MySql, Transaction, -}; -use url::Url; -use uuid::Uuid; - -use crate::{ - models::client::{Client, ClientType}, - services::crypto::PasswordHash, -}; - -#[derive(Debug, Clone, FromRow)] -pub struct ClientRow { - pub id: Uuid, - pub alias: String, - pub client_type: ClientType, - pub allowed_scopes: String, - pub default_scopes: Option, - pub is_trusted: bool, -} - -#[derive(Clone, FromRow)] -struct HashRow { - secret_hash: Option>, - secret_salt: Option>, - secret_version: Option, -} - -pub async fn client_id_exists<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result { - query_scalar!( - r"SELECT EXISTS(SELECT id FROM clients WHERE id = ?) as `e: bool`", - id - ) - .fetch_one(executor) - .await - .unexpect() -} - -pub async fn client_alias_exists<'c>( - executor: impl Executor<'c, Database = MySql>, - alias: &str, -) -> Result { - query_scalar!( - "SELECT EXISTS(SELECT alias FROM clients WHERE alias = ?) as `e: bool`", - alias - ) - .fetch_one(executor) - .await - .unexpect() -} - -pub async fn get_client_id_by_alias<'c>( - executor: impl Executor<'c, Database = MySql>, - alias: &str, -) -> Result, RawUnexpected> { - query_scalar!( - "SELECT id as `id: Uuid` FROM clients WHERE alias = ?", - alias - ) - .fetch_optional(executor) - .await - .unexpect() -} - -pub async fn get_client_response<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result, RawUnexpected> { - let record = query_as!( - ClientRow, - r"SELECT id as `id: Uuid`, - alias, - type as `client_type: ClientType`, - allowed_scopes, - default_scopes, - trusted as `is_trusted: bool` - FROM clients WHERE id = ?", - id - ) - .fetch_optional(executor) - .await?; - - Ok(record) -} - -pub async fn get_client_alias<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result>, RawUnexpected> { - let alias = query_scalar!("SELECT alias FROM clients WHERE id = ?", id) - .fetch_optional(executor) - .await - .unexpect()?; - - Ok(alias.map(String::into_boxed_str)) -} - -pub async fn get_client_type<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result, RawUnexpected> { - let ty = query_scalar!( - "SELECT type as `type: ClientType` FROM clients WHERE id = ?", - id - ) - .fetch_optional(executor) - .await - .unexpect()?; - - Ok(ty) -} - -pub async fn get_client_allowed_scopes<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result>, RawUnexpected> { - let scopes = query_scalar!("SELECT allowed_scopes FROM clients WHERE id = ?", id) - .fetch_optional(executor) - .await?; - - Ok(scopes.map(Box::from)) -} - -pub async fn get_client_default_scopes<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result>>, RawUnexpected> { - let scopes = query_scalar!("SELECT default_scopes FROM clients WHERE id = ?", id) - .fetch_optional(executor) - .await?; - - Ok(scopes.map(|s| s.map(Box::from))) -} - -pub async fn get_client_secret<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result, RawUnexpected> { - let hash = query_as!( - HashRow, - r"SELECT secret_hash, secret_salt, secret_version - FROM clients WHERE id = ?", - id - ) - .fetch_optional(executor) - .await?; - - let Some(hash) = hash else { return Ok(None) }; - let Some(version) = hash.secret_version else { return Ok(None) }; - let Some(salt) = hash.secret_hash else { return Ok(None) }; - let Some(hash) = hash.secret_salt else { return Ok(None) }; - - let hash = PasswordHash::from_fields(&hash, &salt, version as u8); - Ok(Some(hash)) -} - -pub async fn is_client_trusted<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result, RawUnexpected> { - query_scalar!("SELECT trusted as `t: bool` FROM clients WHERE id = ?", id) - .fetch_optional(executor) - .await - .unexpect() -} - -pub async fn get_client_redirect_uris<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result, RawUnexpected> { - let uris = query_scalar!( - "SELECT redirect_uri FROM client_redirect_uris WHERE client_id = ?", - id - ) - .fetch_all(executor) - .await - .unexpect()?; - - uris.into_iter() - .map(|s| Url::from_str(&s).unexpect()) - .collect() -} - -pub async fn client_has_redirect_uri<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - url: &Url, -) -> Result { - query_scalar!( - r"SELECT EXISTS( - SELECT redirect_uri - FROM client_redirect_uris - WHERE client_id = ? AND redirect_uri = ? - ) as `e: bool`", - id, - url.to_string() - ) - .fetch_one(executor) - .await - .unexpect() -} - -async fn delete_client_redirect_uris<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result<(), sqlx::Error> { - query!("DELETE FROM client_redirect_uris WHERE client_id = ?", id) - .execute(executor) - .await?; - Ok(()) -} - -async fn create_client_redirect_uris<'c>( - mut transaction: Transaction<'c, MySql>, - client_id: Uuid, - uris: &[Url], -) -> Result<(), sqlx::Error> { - for uri in uris { - query!( - r"INSERT INTO client_redirect_uris (client_id, redirect_uri) - VALUES ( ?, ?)", - client_id, - uri.to_string() - ) - .execute(&mut transaction) - .await?; - } - - transaction.commit().await?; - - Ok(()) -} - -pub async fn create_client<'c>( - mut transaction: Transaction<'c, MySql>, - client: &Client, -) -> Result<(), sqlx::Error> { - query!( - r"INSERT INTO clients (id, alias, type, secret_hash, secret_salt, secret_version, allowed_scopes, default_scopes) - VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)", - client.id(), - client.alias(), - client.client_type(), - client.secret_hash(), - client.secret_salt(), - client.secret_version(), - client.allowed_scopes(), - client.default_scopes() - ) - .execute(&mut transaction) - .await?; - - create_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?; - - Ok(()) -} - -pub async fn update_client<'c>( - mut transaction: Transaction<'c, MySql>, - client: &Client, -) -> Result<(), sqlx::Error> { - query!( - r"UPDATE clients SET - alias = ?, - type = ?, - secret_hash = ?, - secret_salt = ?, - secret_version = ?, - allowed_scopes = ?, - default_scopes = ? - WHERE id = ?", - client.client_type(), - client.alias(), - client.secret_hash(), - client.secret_salt(), - client.secret_version(), - client.allowed_scopes(), - client.default_scopes(), - client.id() - ) - .execute(&mut transaction) - .await?; - - update_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?; - - Ok(()) -} - -pub async fn update_client_alias<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - alias: &str, -) -> Result { - query!("UPDATE clients SET alias = ? WHERE id = ?", alias, id) - .execute(executor) - .await -} - -pub async fn update_client_type<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - ty: ClientType, -) -> Result { - query!("UPDATE clients SET type = ? WHERE id = ?", ty, id) - .execute(executor) - .await -} - -pub async fn update_client_allowed_scopes<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - allowed_scopes: &str, -) -> Result { - query!( - "UPDATE clients SET allowed_scopes = ? WHERE id = ?", - allowed_scopes, - id - ) - .execute(executor) - .await -} - -pub async fn update_client_default_scopes<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - default_scopes: Option, -) -> Result { - query!( - "UPDATE clients SET default_scopes = ? WHERE id = ?", - default_scopes, - id - ) - .execute(executor) - .await -} - -pub async fn update_client_trusted<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - is_trusted: bool, -) -> Result { - query!( - "UPDATE clients SET trusted = ? WHERE id = ?", - is_trusted, - id - ) - .execute(executor) - .await -} - -pub async fn update_client_redirect_uris<'c>( - mut transaction: Transaction<'c, MySql>, - id: Uuid, - uris: &[Url], -) -> Result<(), sqlx::Error> { - delete_client_redirect_uris(&mut transaction, id).await?; - create_client_redirect_uris(transaction, id, uris).await?; - Ok(()) -} - -pub async fn update_client_secret<'c>( - executor: impl Executor<'c, Database = MySql>, - id: Uuid, - secret: Option, -) -> Result { - if let Some(secret) = secret { - query!( - "UPDATE clients SET secret_hash = ?, secret_salt = ?, secret_version = ? WHERE id = ?", - secret.hash(), - secret.salt(), - secret.version(), - id - ) - .execute(executor) - .await - } else { - query!( - r"UPDATE clients - SET secret_hash = NULL, secret_salt = NULL, secret_version = NULL - WHERE id = ?", - id - ) - .execute(executor) - .await - } -} +use std::str::FromStr; + +use exun::{RawUnexpected, ResultErrorExt}; +use sqlx::{ + mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, FromRow, MySql, Transaction, +}; +use url::Url; +use uuid::Uuid; + +use crate::{ + models::client::{Client, ClientType}, + services::crypto::PasswordHash, +}; + +#[derive(Debug, Clone, FromRow)] +pub struct ClientRow { + pub id: Uuid, + pub alias: String, + pub client_type: ClientType, + pub allowed_scopes: String, + pub default_scopes: Option, + pub is_trusted: bool, +} + +#[derive(Clone, FromRow)] +struct HashRow { + secret_hash: Option>, + secret_salt: Option>, + secret_version: Option, +} + +pub async fn client_id_exists<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result { + query_scalar!( + r"SELECT EXISTS(SELECT id FROM clients WHERE id = ?) as `e: bool`", + id + ) + .fetch_one(executor) + .await + .unexpect() +} + +pub async fn client_alias_exists<'c>( + executor: impl Executor<'c, Database = MySql>, + alias: &str, +) -> Result { + query_scalar!( + "SELECT EXISTS(SELECT alias FROM clients WHERE alias = ?) as `e: bool`", + alias + ) + .fetch_one(executor) + .await + .unexpect() +} + +pub async fn get_client_id_by_alias<'c>( + executor: impl Executor<'c, Database = MySql>, + alias: &str, +) -> Result, RawUnexpected> { + query_scalar!( + "SELECT id as `id: Uuid` FROM clients WHERE alias = ?", + alias + ) + .fetch_optional(executor) + .await + .unexpect() +} + +pub async fn get_client_response<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result, RawUnexpected> { + let record = query_as!( + ClientRow, + r"SELECT id as `id: Uuid`, + alias, + type as `client_type: ClientType`, + allowed_scopes, + default_scopes, + trusted as `is_trusted: bool` + FROM clients WHERE id = ?", + id + ) + .fetch_optional(executor) + .await?; + + Ok(record) +} + +pub async fn get_client_alias<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result>, RawUnexpected> { + let alias = query_scalar!("SELECT alias FROM clients WHERE id = ?", id) + .fetch_optional(executor) + .await + .unexpect()?; + + Ok(alias.map(String::into_boxed_str)) +} + +pub async fn get_client_type<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result, RawUnexpected> { + let ty = query_scalar!( + "SELECT type as `type: ClientType` FROM clients WHERE id = ?", + id + ) + .fetch_optional(executor) + .await + .unexpect()?; + + Ok(ty) +} + +pub async fn get_client_allowed_scopes<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result>, RawUnexpected> { + let scopes = query_scalar!("SELECT allowed_scopes FROM clients WHERE id = ?", id) + .fetch_optional(executor) + .await?; + + Ok(scopes.map(Box::from)) +} + +pub async fn get_client_default_scopes<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result>>, RawUnexpected> { + let scopes = query_scalar!("SELECT default_scopes FROM clients WHERE id = ?", id) + .fetch_optional(executor) + .await?; + + Ok(scopes.map(|s| s.map(Box::from))) +} + +pub async fn get_client_secret<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result, RawUnexpected> { + let hash = query_as!( + HashRow, + r"SELECT secret_hash, secret_salt, secret_version + FROM clients WHERE id = ?", + id + ) + .fetch_optional(executor) + .await?; + + let Some(hash) = hash else { return Ok(None) }; + let Some(version) = hash.secret_version else { return Ok(None) }; + let Some(salt) = hash.secret_hash else { return Ok(None) }; + let Some(hash) = hash.secret_salt else { return Ok(None) }; + + let hash = PasswordHash::from_fields(&hash, &salt, version as u8); + Ok(Some(hash)) +} + +pub async fn is_client_trusted<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result, RawUnexpected> { + query_scalar!("SELECT trusted as `t: bool` FROM clients WHERE id = ?", id) + .fetch_optional(executor) + .await + .unexpect() +} + +pub async fn get_client_redirect_uris<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result, RawUnexpected> { + let uris = query_scalar!( + "SELECT redirect_uri FROM client_redirect_uris WHERE client_id = ?", + id + ) + .fetch_all(executor) + .await + .unexpect()?; + + uris.into_iter() + .map(|s| Url::from_str(&s).unexpect()) + .collect() +} + +pub async fn client_has_redirect_uri<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + url: &Url, +) -> Result { + query_scalar!( + r"SELECT EXISTS( + SELECT redirect_uri + FROM client_redirect_uris + WHERE client_id = ? AND redirect_uri = ? + ) as `e: bool`", + id, + url.to_string() + ) + .fetch_one(executor) + .await + .unexpect() +} + +async fn delete_client_redirect_uris<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result<(), sqlx::Error> { + query!("DELETE FROM client_redirect_uris WHERE client_id = ?", id) + .execute(executor) + .await?; + Ok(()) +} + +async fn create_client_redirect_uris<'c>( + mut transaction: Transaction<'c, MySql>, + client_id: Uuid, + uris: &[Url], +) -> Result<(), sqlx::Error> { + for uri in uris { + query!( + r"INSERT INTO client_redirect_uris (client_id, redirect_uri) + VALUES ( ?, ?)", + client_id, + uri.to_string() + ) + .execute(&mut transaction) + .await?; + } + + transaction.commit().await?; + + Ok(()) +} + +pub async fn create_client<'c>( + mut transaction: Transaction<'c, MySql>, + client: &Client, +) -> Result<(), sqlx::Error> { + query!( + r"INSERT INTO clients (id, alias, type, secret_hash, secret_salt, secret_version, allowed_scopes, default_scopes) + VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)", + client.id(), + client.alias(), + client.client_type(), + client.secret_hash(), + client.secret_salt(), + client.secret_version(), + client.allowed_scopes(), + client.default_scopes() + ) + .execute(&mut transaction) + .await?; + + create_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?; + + Ok(()) +} + +pub async fn update_client<'c>( + mut transaction: Transaction<'c, MySql>, + client: &Client, +) -> Result<(), sqlx::Error> { + query!( + r"UPDATE clients SET + alias = ?, + type = ?, + secret_hash = ?, + secret_salt = ?, + secret_version = ?, + allowed_scopes = ?, + default_scopes = ? + WHERE id = ?", + client.client_type(), + client.alias(), + client.secret_hash(), + client.secret_salt(), + client.secret_version(), + client.allowed_scopes(), + client.default_scopes(), + client.id() + ) + .execute(&mut transaction) + .await?; + + update_client_redirect_uris(transaction, client.id(), client.redirect_uris()).await?; + + Ok(()) +} + +pub async fn update_client_alias<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + alias: &str, +) -> Result { + query!("UPDATE clients SET alias = ? WHERE id = ?", alias, id) + .execute(executor) + .await +} + +pub async fn update_client_type<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + ty: ClientType, +) -> Result { + query!("UPDATE clients SET type = ? WHERE id = ?", ty, id) + .execute(executor) + .await +} + +pub async fn update_client_allowed_scopes<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + allowed_scopes: &str, +) -> Result { + query!( + "UPDATE clients SET allowed_scopes = ? WHERE id = ?", + allowed_scopes, + id + ) + .execute(executor) + .await +} + +pub async fn update_client_default_scopes<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + default_scopes: Option, +) -> Result { + query!( + "UPDATE clients SET default_scopes = ? WHERE id = ?", + default_scopes, + id + ) + .execute(executor) + .await +} + +pub async fn update_client_trusted<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + is_trusted: bool, +) -> Result { + query!( + "UPDATE clients SET trusted = ? WHERE id = ?", + is_trusted, + id + ) + .execute(executor) + .await +} + +pub async fn update_client_redirect_uris<'c>( + mut transaction: Transaction<'c, MySql>, + id: Uuid, + uris: &[Url], +) -> Result<(), sqlx::Error> { + delete_client_redirect_uris(&mut transaction, id).await?; + create_client_redirect_uris(transaction, id, uris).await?; + Ok(()) +} + +pub async fn update_client_secret<'c>( + executor: impl Executor<'c, Database = MySql>, + id: Uuid, + secret: Option, +) -> Result { + if let Some(secret) = secret { + query!( + "UPDATE clients SET secret_hash = ?, secret_salt = ?, secret_version = ? WHERE id = ?", + secret.hash(), + secret.salt(), + secret.version(), + id + ) + .execute(executor) + .await + } else { + query!( + r"UPDATE clients + SET secret_hash = NULL, secret_salt = NULL, secret_version = NULL + WHERE id = ?", + id + ) + .execute(executor) + .await + } +} diff --git a/src/services/db/jwt.rs b/src/services/db/jwt.rs index b2f1367..73d6902 100644 --- a/src/services/db/jwt.rs +++ b/src/services/db/jwt.rs @@ -1,199 +1,199 @@ -use chrono::{DateTime, Utc}; -use exun::{RawUnexpected, ResultErrorExt}; -use sqlx::{query, query_scalar, Executor, MySql}; -use uuid::Uuid; - -use crate::services::jwt::RevokedRefreshTokenReason; - -pub async fn auth_code_exists<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, -) -> Result { - query_scalar!( - "SELECT EXISTS(SELECT jti FROM auth_codes WHERE jti = ?) as `e: bool`", - jti - ) - .fetch_one(executor) - .await - .unexpect() -} - -pub async fn access_token_exists<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, -) -> Result { - query_scalar!( - "SELECT EXISTS(SELECT jti FROM access_tokens WHERE jti = ?) as `e: bool`", - jti - ) - .fetch_one(executor) - .await - .unexpect() -} - -pub async fn refresh_token_exists<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, -) -> Result { - query_scalar!( - "SELECT EXISTS(SELECT jti FROM refresh_tokens WHERE jti = ?) as `e: bool`", - jti - ) - .fetch_one(executor) - .await - .unexpect() -} - -pub async fn refresh_token_revoked<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, -) -> Result { - let result = query_scalar!( - r"SELECT EXISTS( - SELECT revoked_reason FROM refresh_tokens WHERE jti = ? and revoked_reason IS NOT NULL - ) as `e: bool`", - jti - ) - .fetch_one(executor) - .await? - .unwrap_or(true); - - Ok(result) -} - -pub async fn create_auth_code<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, - exp: DateTime, -) -> Result<(), sqlx::Error> { - query!( - r"INSERT INTO auth_codes (jti, exp) - VALUES ( ?, ?)", - jti, - exp - ) - .execute(executor) - .await?; - - Ok(()) -} - -pub async fn create_access_token<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, - auth_code: Option, - exp: DateTime, -) -> Result<(), sqlx::Error> { - query!( - r"INSERT INTO access_tokens (jti, auth_code, exp) - VALUES ( ?, ?, ?)", - jti, - auth_code, - exp - ) - .execute(executor) - .await?; - - Ok(()) -} - -pub async fn create_refresh_token<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, - auth_code: Option, - exp: DateTime, -) -> Result<(), sqlx::Error> { - query!( - r"INSERT INTO access_tokens (jti, auth_code, exp) - VALUES ( ?, ?, ?)", - jti, - auth_code, - exp - ) - .execute(executor) - .await?; - - Ok(()) -} - -pub async fn delete_auth_code<'c>( - executor: impl Executor<'c, Database = MySql>, - auth_code: Uuid, -) -> Result { - let result = query!("DELETE FROM auth_codes WHERE jti = ?", auth_code) - .execute(executor) - .await?; - - Ok(result.rows_affected() != 0) -} - -pub async fn delete_expired_auth_codes<'c>( - executor: impl Executor<'c, Database = MySql>, -) -> Result<(), RawUnexpected> { - query!("DELETE FROM auth_codes WHERE exp < ?", Utc::now()) - .execute(executor) - .await?; - - Ok(()) -} - -pub async fn delete_access_tokens_with_auth_code<'c>( - executor: impl Executor<'c, Database = MySql>, - auth_code: Uuid, -) -> Result { - let result = query!("DELETE FROM access_tokens WHERE auth_code = ?", auth_code) - .execute(executor) - .await?; - - Ok(result.rows_affected() != 0) -} - -pub async fn delete_expired_access_tokens<'c>( - executor: impl Executor<'c, Database = MySql>, -) -> Result<(), RawUnexpected> { - query!("DELETE FROM access_tokens WHERE exp < ?", Utc::now()) - .execute(executor) - .await?; - - Ok(()) -} - -pub async fn revoke_refresh_token<'c>( - executor: impl Executor<'c, Database = MySql>, - jti: Uuid, -) -> Result { - let result = query!( - "UPDATE refresh_tokens SET revoked_reason = ? WHERE jti = ?", - RevokedRefreshTokenReason::NewRefreshToken, - jti - ) - .execute(executor) - .await?; - - Ok(result.rows_affected() != 0) -} - -pub async fn revoke_refresh_tokens_with_auth_code<'c>( - executor: impl Executor<'c, Database = MySql>, - auth_code: Uuid, -) -> Result { - let result = query!( - "UPDATE refresh_tokens SET revoked_reason = ? WHERE auth_code = ?", - RevokedRefreshTokenReason::ReusedAuthorizationCode, - auth_code - ) - .execute(executor) - .await?; - - Ok(result.rows_affected() != 0) -} - -pub async fn delete_expired_refresh_tokens<'c>( - executor: impl Executor<'c, Database = MySql>, -) -> Result<(), RawUnexpected> { - query!("DELETE FROM refresh_tokens WHERE exp < ?", Utc::now()) - .execute(executor) - .await?; - - Ok(()) -} +use chrono::{DateTime, Utc}; +use exun::{RawUnexpected, ResultErrorExt}; +use sqlx::{query, query_scalar, Executor, MySql}; +use uuid::Uuid; + +use crate::services::jwt::RevokedRefreshTokenReason; + +pub async fn auth_code_exists<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, +) -> Result { + query_scalar!( + "SELECT EXISTS(SELECT jti FROM auth_codes WHERE jti = ?) as `e: bool`", + jti + ) + .fetch_one(executor) + .await + .unexpect() +} + +pub async fn access_token_exists<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, +) -> Result { + query_scalar!( + "SELECT EXISTS(SELECT jti FROM access_tokens WHERE jti = ?) as `e: bool`", + jti + ) + .fetch_one(executor) + .await + .unexpect() +} + +pub async fn refresh_token_exists<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, +) -> Result { + query_scalar!( + "SELECT EXISTS(SELECT jti FROM refresh_tokens WHERE jti = ?) as `e: bool`", + jti + ) + .fetch_one(executor) + .await + .unexpect() +} + +pub async fn refresh_token_revoked<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, +) -> Result { + let result = query_scalar!( + r"SELECT EXISTS( + SELECT revoked_reason FROM refresh_tokens WHERE jti = ? and revoked_reason IS NOT NULL + ) as `e: bool`", + jti + ) + .fetch_one(executor) + .await? + .unwrap_or(true); + + Ok(result) +} + +pub async fn create_auth_code<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, + exp: DateTime, +) -> Result<(), sqlx::Error> { + query!( + r"INSERT INTO auth_codes (jti, exp) + VALUES ( ?, ?)", + jti, + exp + ) + .execute(executor) + .await?; + + Ok(()) +} + +pub async fn create_access_token<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, + auth_code: Option, + exp: DateTime, +) -> Result<(), sqlx::Error> { + query!( + r"INSERT INTO access_tokens (jti, auth_code, exp) + VALUES ( ?, ?, ?)", + jti, + auth_code, + exp + ) + .execute(executor) + .await?; + + Ok(()) +} + +pub async fn create_refresh_token<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, + auth_code: Option, + exp: DateTime, +) -> Result<(), sqlx::Error> { + query!( + r"INSERT INTO access_tokens (jti, auth_code, exp) + VALUES ( ?, ?, ?)", + jti, + auth_code, + exp + ) + .execute(executor) + .await?; + + Ok(()) +} + +pub async fn delete_auth_code<'c>( + executor: impl Executor<'c, Database = MySql>, + auth_code: Uuid, +) -> Result { + let result = query!("DELETE FROM auth_codes WHERE jti = ?", auth_code) + .execute(executor) + .await?; + + Ok(result.rows_affected() != 0) +} + +pub async fn delete_expired_auth_codes<'c>( + executor: impl Executor<'c, Database = MySql>, +) -> Result<(), RawUnexpected> { + query!("DELETE FROM auth_codes WHERE exp < ?", Utc::now()) + .execute(executor) + .await?; + + Ok(()) +} + +pub async fn delete_access_tokens_with_auth_code<'c>( + executor: impl Executor<'c, Database = MySql>, + auth_code: Uuid, +) -> Result { + let result = query!("DELETE FROM access_tokens WHERE auth_code = ?", auth_code) + .execute(executor) + .await?; + + Ok(result.rows_affected() != 0) +} + +pub async fn delete_expired_access_tokens<'c>( + executor: impl Executor<'c, Database = MySql>, +) -> Result<(), RawUnexpected> { + query!("DELETE FROM access_tokens WHERE exp < ?", Utc::now()) + .execute(executor) + .await?; + + Ok(()) +} + +pub async fn revoke_refresh_token<'c>( + executor: impl Executor<'c, Database = MySql>, + jti: Uuid, +) -> Result { + let result = query!( + "UPDATE refresh_tokens SET revoked_reason = ? WHERE jti = ?", + RevokedRefreshTokenReason::NewRefreshToken, + jti + ) + .execute(executor) + .await?; + + Ok(result.rows_affected() != 0) +} + +pub async fn revoke_refresh_tokens_with_auth_code<'c>( + executor: impl Executor<'c, Database = MySql>, + auth_code: Uuid, +) -> Result { + let result = query!( + "UPDATE refresh_tokens SET revoked_reason = ? WHERE auth_code = ?", + RevokedRefreshTokenReason::ReusedAuthorizationCode, + auth_code + ) + .execute(executor) + .await?; + + Ok(result.rows_affected() != 0) +} + +pub async fn delete_expired_refresh_tokens<'c>( + executor: impl Executor<'c, Database = MySql>, +) -> Result<(), RawUnexpected> { + query!("DELETE FROM refresh_tokens WHERE exp < ?", Utc::now()) + .execute(executor) + .await?; + + Ok(()) +} diff --git a/src/services/db/user.rs b/src/services/db/user.rs index 09a09da..f85047a 100644 --- a/src/services/db/user.rs +++ b/src/services/db/user.rs @@ -1,236 +1,236 @@ -use exun::RawUnexpected; -use sqlx::{mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, MySql}; -use uuid::Uuid; - -use crate::{models::user::User, services::crypto::PasswordHash}; - -struct UserRow { - id: Uuid, - username: String, - password_hash: Vec, - password_salt: Vec, - password_version: u32, -} - -impl TryFrom for User { - type Error = RawUnexpected; - - fn try_from(row: UserRow) -> Result { - let password = PasswordHash::from_fields( - &row.password_hash, - &row.password_salt, - row.password_version as u8, - ); - let user = User { - id: row.id, - username: row.username.into_boxed_str(), - password, - }; - Ok(user) - } -} - -/// Check if a user with a given user ID exists -pub async fn user_id_exists<'c>( - conn: impl Executor<'c, Database = MySql>, - id: Uuid, -) -> Result { - let exists = query_scalar!( - r#"SELECT EXISTS(SELECT id FROM users WHERE id = ?) as `e: bool`"#, - id - ) - .fetch_one(conn) - .await?; - - Ok(exists) -} - -/// Check if a given username is taken -pub async fn username_is_used<'c>( - conn: impl Executor<'c, Database = MySql>, - username: &str, -) -> Result { - let exists = query_scalar!( - r#"SELECT EXISTS(SELECT id FROM users WHERE username = ?) as "e: bool""#, - username - ) - .fetch_one(conn) - .await?; - - Ok(exists) -} - -/// Get a user from their ID -pub async fn get_user<'c>( - conn: impl Executor<'c, Database = MySql>, - user_id: Uuid, -) -> Result, RawUnexpected> { - let record = query_as!( - UserRow, - r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version - FROM users WHERE id = ?", - user_id - ) - .fetch_optional(conn) - .await?; - - let Some(record) = record else { return Ok(None) }; - - Ok(Some(record.try_into()?)) -} - -/// Get a user from their username -pub async fn get_user_by_username<'c>( - conn: impl Executor<'c, Database = MySql>, - username: &str, -) -> Result, RawUnexpected> { - let record = query_as!( - UserRow, - r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version - FROM users WHERE username = ?", - username - ) - .fetch_optional(conn) - .await?; - - let Some(record) = record else { return Ok(None) }; - - Ok(Some(record.try_into()?)) -} - -/// Search the list of users for a given username -pub async fn search_users<'c>( - conn: impl Executor<'c, Database = MySql>, - username: &str, -) -> Result, RawUnexpected> { - let records = query_as!( - UserRow, - r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version - FROM users - WHERE LOCATE(?, username) != 0", - username, - ) - .fetch_all(conn) - .await?; - - Ok(records - .into_iter() - .map(|u| u.try_into()) - .collect::, RawUnexpected>>()?) -} - -/// Search the list of users, only returning a certain range of results -pub async fn search_users_limit<'c>( - conn: impl Executor<'c, Database = MySql>, - username: &str, - offset: u32, - limit: u32, -) -> Result, RawUnexpected> { - let records = query_as!( - UserRow, - r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version - FROM users - WHERE LOCATE(?, username) != 0 - LIMIT ? - OFFSET ?", - username, - offset, - limit - ) - .fetch_all(conn) - .await?; - - Ok(records - .into_iter() - .map(|u| u.try_into()) - .collect::, RawUnexpected>>()?) -} - -/// Get the username of a user with a certain ID -pub async fn get_username<'c>( - conn: impl Executor<'c, Database = MySql>, - user_id: Uuid, -) -> Result>, RawUnexpected> { - let username = query_scalar!(r"SELECT username FROM users where id = ?", user_id) - .fetch_optional(conn) - .await? - .map(String::into_boxed_str); - - Ok(username) -} - -/// Create a new user -pub async fn create_user<'c>( - conn: impl Executor<'c, Database = MySql>, - user: &User, -) -> Result { - query!( - r"INSERT INTO users (id, username, password_hash, password_salt, password_version) - VALUES ( ?, ?, ?, ?, ?)", - user.id, - user.username(), - user.password_hash(), - user.password_salt(), - user.password_version() - ) - .execute(conn) - .await -} - -/// Update a user -pub async fn update_user<'c>( - conn: impl Executor<'c, Database = MySql>, - user: &User, -) -> Result { - query!( - r"UPDATE users SET - username = ?, - password_hash = ?, - password_salt = ?, - password_version = ? - WHERE id = ?", - user.username(), - user.password_hash(), - user.password_salt(), - user.password_version(), - user.id - ) - .execute(conn) - .await -} - -/// Update the username of a user with the given ID -pub async fn update_username<'c>( - conn: impl Executor<'c, Database = MySql>, - user_id: Uuid, - username: &str, -) -> Result { - query!( - r"UPDATE users SET username = ? WHERE id = ?", - username, - user_id - ) - .execute(conn) - .await -} - -/// Update the password of a user with the given ID -pub async fn update_password<'c>( - conn: impl Executor<'c, Database = MySql>, - user_id: Uuid, - password: &PasswordHash, -) -> Result { - query!( - r"UPDATE users SET - password_hash = ?, - password_salt = ?, - password_version = ? - WHERE id = ?", - password.hash(), - password.salt(), - password.version(), - user_id - ) - .execute(conn) - .await -} +use exun::RawUnexpected; +use sqlx::{mysql::MySqlQueryResult, query, query_as, query_scalar, Executor, MySql}; +use uuid::Uuid; + +use crate::{models::user::User, services::crypto::PasswordHash}; + +struct UserRow { + id: Uuid, + username: String, + password_hash: Vec, + password_salt: Vec, + password_version: u32, +} + +impl TryFrom for User { + type Error = RawUnexpected; + + fn try_from(row: UserRow) -> Result { + let password = PasswordHash::from_fields( + &row.password_hash, + &row.password_salt, + row.password_version as u8, + ); + let user = User { + id: row.id, + username: row.username.into_boxed_str(), + password, + }; + Ok(user) + } +} + +/// Check if a user with a given user ID exists +pub async fn user_id_exists<'c>( + conn: impl Executor<'c, Database = MySql>, + id: Uuid, +) -> Result { + let exists = query_scalar!( + r#"SELECT EXISTS(SELECT id FROM users WHERE id = ?) as `e: bool`"#, + id + ) + .fetch_one(conn) + .await?; + + Ok(exists) +} + +/// Check if a given username is taken +pub async fn username_is_used<'c>( + conn: impl Executor<'c, Database = MySql>, + username: &str, +) -> Result { + let exists = query_scalar!( + r#"SELECT EXISTS(SELECT id FROM users WHERE username = ?) as "e: bool""#, + username + ) + .fetch_one(conn) + .await?; + + Ok(exists) +} + +/// Get a user from their ID +pub async fn get_user<'c>( + conn: impl Executor<'c, Database = MySql>, + user_id: Uuid, +) -> Result, RawUnexpected> { + let record = query_as!( + UserRow, + r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version + FROM users WHERE id = ?", + user_id + ) + .fetch_optional(conn) + .await?; + + let Some(record) = record else { return Ok(None) }; + + Ok(Some(record.try_into()?)) +} + +/// Get a user from their username +pub async fn get_user_by_username<'c>( + conn: impl Executor<'c, Database = MySql>, + username: &str, +) -> Result, RawUnexpected> { + let record = query_as!( + UserRow, + r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version + FROM users WHERE username = ?", + username + ) + .fetch_optional(conn) + .await?; + + let Some(record) = record else { return Ok(None) }; + + Ok(Some(record.try_into()?)) +} + +/// Search the list of users for a given username +pub async fn search_users<'c>( + conn: impl Executor<'c, Database = MySql>, + username: &str, +) -> Result, RawUnexpected> { + let records = query_as!( + UserRow, + r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version + FROM users + WHERE LOCATE(?, username) != 0", + username, + ) + .fetch_all(conn) + .await?; + + Ok(records + .into_iter() + .map(|u| u.try_into()) + .collect::, RawUnexpected>>()?) +} + +/// Search the list of users, only returning a certain range of results +pub async fn search_users_limit<'c>( + conn: impl Executor<'c, Database = MySql>, + username: &str, + offset: u32, + limit: u32, +) -> Result, RawUnexpected> { + let records = query_as!( + UserRow, + r"SELECT id as `id: Uuid`, username, password_hash, password_salt, password_version + FROM users + WHERE LOCATE(?, username) != 0 + LIMIT ? + OFFSET ?", + username, + offset, + limit + ) + .fetch_all(conn) + .await?; + + Ok(records + .into_iter() + .map(|u| u.try_into()) + .collect::, RawUnexpected>>()?) +} + +/// Get the username of a user with a certain ID +pub async fn get_username<'c>( + conn: impl Executor<'c, Database = MySql>, + user_id: Uuid, +) -> Result>, RawUnexpected> { + let username = query_scalar!(r"SELECT username FROM users where id = ?", user_id) + .fetch_optional(conn) + .await? + .map(String::into_boxed_str); + + Ok(username) +} + +/// Create a new user +pub async fn create_user<'c>( + conn: impl Executor<'c, Database = MySql>, + user: &User, +) -> Result { + query!( + r"INSERT INTO users (id, username, password_hash, password_salt, password_version) + VALUES ( ?, ?, ?, ?, ?)", + user.id, + user.username(), + user.password_hash(), + user.password_salt(), + user.password_version() + ) + .execute(conn) + .await +} + +/// Update a user +pub async fn update_user<'c>( + conn: impl Executor<'c, Database = MySql>, + user: &User, +) -> Result { + query!( + r"UPDATE users SET + username = ?, + password_hash = ?, + password_salt = ?, + password_version = ? + WHERE id = ?", + user.username(), + user.password_hash(), + user.password_salt(), + user.password_version(), + user.id + ) + .execute(conn) + .await +} + +/// Update the username of a user with the given ID +pub async fn update_username<'c>( + conn: impl Executor<'c, Database = MySql>, + user_id: Uuid, + username: &str, +) -> Result { + query!( + r"UPDATE users SET username = ? WHERE id = ?", + username, + user_id + ) + .execute(conn) + .await +} + +/// Update the password of a user with the given ID +pub async fn update_password<'c>( + conn: impl Executor<'c, Database = MySql>, + user_id: Uuid, + password: &PasswordHash, +) -> Result { + query!( + r"UPDATE users SET + password_hash = ?, + password_salt = ?, + password_version = ? + WHERE id = ?", + password.hash(), + password.salt(), + password.version(), + user_id + ) + .execute(conn) + .await +} -- cgit v1.2.3