use sqlx::{Sqlite, Transaction};
use crate::error_handling::DatabaseError;
use crate::parse::ExposedSecret;
pub(crate) async fn insert_exposed_secrets_in_tx(
tx: &mut Transaction<'_, Sqlite>,
url_status_id: i64,
secrets: &[ExposedSecret],
) -> Result<Vec<i64>, DatabaseError> {
if secrets.is_empty() {
return Ok(Vec::new());
}
let mut ids = Vec::with_capacity(secrets.len());
for secret in secrets {
let row: (i64,) = sqlx::query_as(
"INSERT INTO url_exposed_secrets (url_status_id, secret_type, matched_value, severity, location, context)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(url_status_id, secret_type, matched_value) DO UPDATE SET
severity=excluded.severity, location=excluded.location, context=excluded.context
RETURNING id",
)
.bind(url_status_id)
.bind(&secret.secret_type)
.bind(&secret.matched_value)
.bind(secret.severity.as_str())
.bind(secret.location.as_ref())
.bind(&secret.context)
.fetch_one(&mut **tx)
.await
.map_err(DatabaseError::SqlError)?;
ids.push(row.0);
}
Ok(ids)
}
#[cfg(test)]
mod tests {
use super::*;
use crate::parse::{ExposedSecret, SecretSeverity};
use sqlx::Row;
use crate::storage::insert::enrichment::commit_in_tx;
use crate::storage::test_helpers::{create_test_pool, create_test_url_status_default};
#[tokio::test]
async fn test_insert_exposed_secrets_basic() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let secrets = vec![ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPLE".to_string(),
context: "var key = AKIAIOSFODNN7EXAMPLE;".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("inline_script"),
decoded_jwt: None,
}];
let result = commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &secrets).await
});
assert!(result.is_ok());
let rows = sqlx::query(
"SELECT secret_type, matched_value, severity, location, context FROM url_exposed_secrets WHERE url_status_id = ?",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("Failed to fetch");
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].get::<String, _>("secret_type"), "aws-access-token");
assert_eq!(
rows[0].get::<String, _>("matched_value"),
"AKIAIOSFODNN7EXAMPLE"
);
assert_eq!(rows[0].get::<String, _>("severity"), "high");
assert_eq!(rows[0].get::<String, _>("location"), "inline_script");
assert_eq!(
rows[0].get::<String, _>("context"),
"var key = AKIAIOSFODNN7EXAMPLE;"
);
}
#[tokio::test]
async fn test_insert_exposed_secrets_empty() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let result = commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &[]).await
});
assert!(result.is_ok());
}
#[tokio::test]
async fn test_insert_exposed_secrets_multiple() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let secrets = vec![
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPLE".to_string(),
context: "context1".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "gcp-api-key".to_string(),
matched_value: "AIzaSyA1234567890abcdefghijklmnopqrstuv".to_string(),
context: "context2".to_string(),
severity: SecretSeverity::Medium,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
},
];
commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &secrets).await
})
.unwrap();
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_exposed_secrets WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.unwrap();
assert_eq!(count, 2);
}
#[tokio::test]
async fn test_insert_exposed_secrets_upsert() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let secret = ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPLE".to_string(),
context: "original context".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
};
commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &[secret]).await
})
.unwrap();
let secret2 = ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPLE".to_string(),
context: "updated context".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
};
commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &[secret2]).await
})
.unwrap();
let count: i64 =
sqlx::query_scalar("SELECT COUNT(*) FROM url_exposed_secrets WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.unwrap();
assert_eq!(count, 1);
let row = sqlx::query("SELECT context FROM url_exposed_secrets WHERE url_status_id = ?")
.bind(url_status_id)
.fetch_one(&pool)
.await
.unwrap();
assert_eq!(row.get::<String, _>("context"), "updated context");
}
#[tokio::test]
async fn test_insert_exposed_secrets_rolls_back_on_constraint_violation() {
let pool = create_test_pool().await;
sqlx::query("PRAGMA foreign_keys = ON")
.execute(&pool)
.await
.expect("enable FKs");
let good_url_status_id = create_test_url_status_default(&pool).await;
let pre_insert = vec![ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7PRESET1".to_string(),
context: "pre-existing row".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
}];
commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, good_url_status_id, &pre_insert).await
})
.expect("pre-insert succeeds");
let nonexistent_id: i64 = 9_999_999;
let failing_batch = vec![
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7BAD0001".to_string(),
context: "should not land".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7BAD0002".to_string(),
context: "should not land".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7BAD0003".to_string(),
context: "should not land".to_string(),
severity: SecretSeverity::High,
location: std::borrow::Cow::Borrowed("html_body"),
decoded_jwt: None,
},
];
let result = commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, nonexistent_id, &failing_batch).await
});
assert!(
result.is_err(),
"FK violation must surface as Err, got {result:?}"
);
let bad_count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_exposed_secrets WHERE matched_value LIKE 'AKIAIOSFODNN7BAD%'",
)
.fetch_one(&pool)
.await
.expect("count");
assert_eq!(
bad_count, 0,
"transaction must roll back on FK violation - no bad rows allowed"
);
let preset_count: i64 = sqlx::query_scalar(
"SELECT COUNT(*) FROM url_exposed_secrets WHERE matched_value = 'AKIAIOSFODNN7PRESET1'",
)
.fetch_one(&pool)
.await
.expect("count");
assert_eq!(
preset_count, 1,
"pre-existing committed row must remain after the failing batch"
);
}
#[tokio::test]
async fn test_insert_exposed_secrets_cow_location_round_trip() {
let pool = create_test_pool().await;
let url_status_id = create_test_url_status_default(&pool).await;
let dynamic_loc: std::borrow::Cow<'static, str> =
std::borrow::Cow::Owned("external_script:https://cdn.example.com/app.js".to_string());
let static_loc: std::borrow::Cow<'static, str> =
std::borrow::Cow::Borrowed("inline_script");
let empty_loc: std::borrow::Cow<'static, str> = std::borrow::Cow::Borrowed("");
let unicode_loc: std::borrow::Cow<'static, str> =
std::borrow::Cow::Owned("html_body:\u{1f512}".to_string());
let secrets = vec![
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPL1".to_string(),
context: "ctx1".to_string(),
severity: SecretSeverity::High,
location: dynamic_loc.clone(),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPL2".to_string(),
context: "ctx2".to_string(),
severity: SecretSeverity::High,
location: static_loc.clone(),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPL3".to_string(),
context: "ctx3".to_string(),
severity: SecretSeverity::High,
location: empty_loc.clone(),
decoded_jwt: None,
},
ExposedSecret {
secret_type: "aws-access-token".to_string(),
matched_value: "AKIAIOSFODNN7EXAMPL4".to_string(),
context: "ctx4".to_string(),
severity: SecretSeverity::High,
location: unicode_loc.clone(),
decoded_jwt: None,
},
];
commit_in_tx!(&pool, |tx| {
insert_exposed_secrets_in_tx(&mut tx, url_status_id, &secrets).await
})
.expect("insert");
let rows = sqlx::query(
"SELECT matched_value, location FROM url_exposed_secrets WHERE url_status_id = ? ORDER BY matched_value",
)
.bind(url_status_id)
.fetch_all(&pool)
.await
.expect("read-back");
assert_eq!(rows.len(), 4, "all four secrets must be stored");
let by_match: std::collections::HashMap<String, String> = rows
.into_iter()
.map(|r| {
(
r.get::<String, _>("matched_value"),
r.get::<String, _>("location"),
)
})
.collect();
assert_eq!(
by_match["AKIAIOSFODNN7EXAMPL1"],
"external_script:https://cdn.example.com/app.js"
);
assert_eq!(by_match["AKIAIOSFODNN7EXAMPL2"], "inline_script");
assert_eq!(by_match["AKIAIOSFODNN7EXAMPL3"], "");
assert_eq!(by_match["AKIAIOSFODNN7EXAMPL4"], "html_body:\u{1f512}");
}
}