larust_db/sql/
mutate.rs

1//! Builds and runs parameterized `INSERT`/`UPDATE`/`DELETE` against an
2//! already-introspected table, plus the deliberately-unrestricted raw-SQL
3//! path the dashboard's own "Run SQL" page uses.
4//!
5//! **Two different security postures, on purpose.** The structured
6//! functions ([`insert_row`], [`update_row`], [`delete_row`],
7//! [`fetch_row`]) only ever interpolate a table or column name into SQL
8//! text after validating it against that table's own freshly-introspected
9//! column list (`introspect::table_columns`) - every *value* is always a
10//! bound parameter via [`codec::bind_any`], never interpolated as text.
11//! Column-name validation is enforced *inside* this module, not left to
12//! the caller: `values`/`settable` are filtered against `columns` before
13//! use, and `pk` is checked by [`require_known_pk_columns`] - the latter
14//! closes a real SQL-injection gap found in a security review (`pk`'s
15//! column names previously reached a `WHERE` clause unchecked, since they
16//! originate from a request's raw field *names*, not values, which
17//! nothing upstream ever validated). The raw functions ([`run_raw`],
18//! [`run_script`]) are the deliberate opposite: they execute whatever SQL
19//! text they're given, unrestricted, the same way phpMyAdmin's own SQL tab
20//! and Import feature are - their safety is the dashboard's existing
21//! double gate (`DB_DASHBOARD_PASSWORD` + `APP_DEBUG`-gated registration),
22//! not query validation, because restricting either would defeat the
23//! entire point of those features.
24
25use crate::sql::codec::{bind_any, json_to_any_value, row_to_json};
26use crate::sql::introspect::ColumnInfo;
27use axum::http::StatusCode;
28use larust_core::AppError;
29use serde_json::Value as Json;
30
31fn internal<E: std::error::Error + Send + Sync + 'static>(error: E) -> AppError {
32    AppError::Internal(Box::new(error))
33}
34
35fn column_kind<'a>(columns: &'a [ColumnInfo], name: &str) -> sqlx::any::AnyTypeInfoKind {
36    columns
37        .iter()
38        .find(|c| c.name == name)
39        .map(|c: &'a ColumnInfo| c.kind)
40        .unwrap_or(sqlx::any::AnyTypeInfoKind::Text)
41}
42
43/// `pk` column *names* end up interpolated directly into a `WHERE` clause
44/// as SQL identifiers (values are always bound - see this module's own
45/// doc comment) - so, unlike `insert_row`'s `values`/`update_row`'s
46/// `settable` (both already filtered against `columns` before being used
47/// to build SQL text), every `pk` entry MUST be checked against the same
48/// introspected column list before it reaches a `format!` call. This was
49/// a real, exploitable gap: `pk` originates from `sql_views::extract_pk`,
50/// which builds it straight from a request's raw query-string/form-field
51/// *keys* (`pk_<anything>`) with no validation at all - an authenticated
52/// dashboard user could submit `pk_<injection>` as a field name and inject
53/// arbitrary SQL into the WHERE clause of `update`/`delete`/the edit
54/// form's row fetch, bypassing the "only real columns, only bound values"
55/// guarantee every doc comment in this file already claimed to provide.
56fn require_known_pk_columns(columns: &[ColumnInfo], pk: &[(String, Json)]) -> Result<(), AppError> {
57    for (name, _) in pk {
58        if !columns.iter().any(|c| &c.name == name) {
59            return Err(AppError::Http {
60                status: StatusCode::BAD_REQUEST,
61                message: format!("unknown primary key column: {name}"),
62            });
63        }
64    }
65    Ok(())
66}
67
68/// Inserts one row. `values` keys not found in `columns` are silently
69/// ignored (a form field the caller shouldn't have sent) rather than
70/// erroring - the caller (the HTTP handler) only ever builds `values` from
71/// `columns` in the first place, so this is a defense-in-depth backstop,
72/// not the primary guard.
73pub async fn insert_row(
74    table: &str,
75    columns: &[ColumnInfo],
76    values: &[(String, Json)],
77) -> Result<(), AppError> {
78    let backend = larust_orm::backend();
79    let present: Vec<&(String, Json)> = values
80        .iter()
81        .filter(|(name, _)| columns.iter().any(|c| &c.name == name))
82        .collect();
83    if present.is_empty() {
84        return Ok(());
85    }
86
87    let column_list = present
88        .iter()
89        .map(|(name, _)| format!("\"{name}\""))
90        .collect::<Vec<_>>()
91        .join(", ");
92    let placeholders = (1..=present.len())
93        .map(|n| larust_orm::placeholder(backend, n))
94        .collect::<Vec<_>>()
95        .join(", ");
96    let sql = format!("INSERT INTO \"{table}\" ({column_list}) VALUES ({placeholders})");
97
98    let pool = larust_orm::pool()?;
99    let mut query = sqlx::query(&sql);
100    for (name, value) in &present {
101        query = bind_any(query, json_to_any_value(value, column_kind(columns, name)));
102    }
103    query.execute(pool).await.map_err(internal)?;
104    Ok(())
105}
106
107/// Updates one row identified by `pk` (composite-safe - pass more than
108/// one entry for a composite key). Any entry in `values` whose column is
109/// also part of `pk` is skipped for the `SET` clause (the primary key
110/// itself is never rewritten by an edit form).
111pub async fn update_row(
112    table: &str,
113    columns: &[ColumnInfo],
114    pk: &[(String, Json)],
115    values: &[(String, Json)],
116) -> Result<(), AppError> {
117    require_known_pk_columns(columns, pk)?;
118    let backend = larust_orm::backend();
119    let settable: Vec<&(String, Json)> = values
120        .iter()
121        .filter(|(name, _)| {
122            columns.iter().any(|c| &c.name == name)
123                && !pk.iter().any(|(pk_name, _)| pk_name == name)
124        })
125        .collect();
126    if settable.is_empty() {
127        return Ok(());
128    }
129
130    let mut n = 0usize;
131    let set_clause = settable
132        .iter()
133        .map(|(name, _)| {
134            n += 1;
135            format!("\"{name}\" = {}", larust_orm::placeholder(backend, n))
136        })
137        .collect::<Vec<_>>()
138        .join(", ");
139    let where_clause = pk
140        .iter()
141        .map(|(name, _)| {
142            n += 1;
143            format!("\"{name}\" = {}", larust_orm::placeholder(backend, n))
144        })
145        .collect::<Vec<_>>()
146        .join(" AND ");
147    let sql = format!("UPDATE \"{table}\" SET {set_clause} WHERE {where_clause}");
148
149    let pool = larust_orm::pool()?;
150    let mut query = sqlx::query(&sql);
151    for (name, value) in &settable {
152        query = bind_any(query, json_to_any_value(value, column_kind(columns, name)));
153    }
154    for (name, value) in pk {
155        query = bind_any(query, json_to_any_value(value, column_kind(columns, name)));
156    }
157    query.execute(pool).await.map_err(internal)?;
158    Ok(())
159}
160
161/// Deletes one row identified by `pk` (composite-safe, same shape as
162/// [`update_row`]).
163pub async fn delete_row(
164    table: &str,
165    columns: &[ColumnInfo],
166    pk: &[(String, Json)],
167) -> Result<(), AppError> {
168    require_known_pk_columns(columns, pk)?;
169    let backend = larust_orm::backend();
170    let mut n = 0usize;
171    let where_clause = pk
172        .iter()
173        .map(|(name, _)| {
174            n += 1;
175            format!("\"{name}\" = {}", larust_orm::placeholder(backend, n))
176        })
177        .collect::<Vec<_>>()
178        .join(" AND ");
179    let sql = format!("DELETE FROM \"{table}\" WHERE {where_clause}");
180
181    let pool = larust_orm::pool()?;
182    let mut query = sqlx::query(&sql);
183    for (name, value) in pk {
184        query = bind_any(query, json_to_any_value(value, column_kind(columns, name)));
185    }
186    query.execute(pool).await.map_err(internal)?;
187    Ok(())
188}
189
190/// Fetches one row identified by `pk`, if it still exists - used to
191/// prefill an edit form. `pk` originates from a query string a user can
192/// tamper with, so both its column *names* ([`require_known_pk_columns`])
193/// and its *values* ([`bind_any`]) are validated/bound before any SQL is
194/// built - unlike [`run_raw`]'s deliberately-unrestricted user-typed SQL.
195pub async fn fetch_row(
196    table: &str,
197    columns: &[ColumnInfo],
198    pk: &[(String, Json)],
199) -> Result<Option<Json>, AppError> {
200    require_known_pk_columns(columns, pk)?;
201    let backend = larust_orm::backend();
202    let mut n = 0usize;
203    let where_clause = pk
204        .iter()
205        .map(|(name, _)| {
206            n += 1;
207            format!("\"{name}\" = {}", larust_orm::placeholder(backend, n))
208        })
209        .collect::<Vec<_>>()
210        .join(" AND ");
211    let sql = format!("SELECT * FROM \"{table}\" WHERE {where_clause}");
212
213    let pool = larust_orm::pool()?;
214    let mut query = sqlx::query(&sql);
215    for (name, value) in pk {
216        query = bind_any(query, json_to_any_value(value, column_kind(columns, name)));
217    }
218    let row = query.fetch_optional(pool).await.map_err(internal)?;
219    Ok(row.as_ref().map(row_to_json))
220}
221
222/// Runs arbitrary, user-typed SQL and renders whatever comes back - see
223/// this module's own doc comment for why this one is deliberately
224/// unrestricted. `fetch_all` works uniformly for both queries and
225/// statements: a `SELECT` returns real rows; `INSERT`/`UPDATE`/`DELETE`/
226/// DDL return zero rows on success, so the same call reports "0 rows"
227/// rather than needing to sniff the statement's kind from its text first.
228pub async fn run_raw(sql: &str) -> Result<Vec<Json>, AppError> {
229    let pool = larust_orm::pool()?;
230    let rows = sqlx::query(sql).fetch_all(pool).await.map_err(internal)?;
231    Ok(rows.iter().map(row_to_json).collect())
232}
233
234/// Runs an uploaded `.sql` file's full contents (the SQL Import feature) -
235/// unlike [`run_raw`], this doesn't return or render rows, because an
236/// imported file is typically many statements, not one query to display.
237/// Uses `sqlx::raw_sql` rather than `fetch_all`, the same primitive
238/// `larust_orm::migrate::run` already relies on for exactly this reason:
239/// it delegates statement parsing to the backend itself, so multi-statement
240/// files, comments, and string literals containing semicolons all work
241/// correctly (splitting on `;` by hand does not - a real bug this
242/// framework already hit once in the migration runner). Same deliberately-
243/// unrestricted security posture as [`run_raw`].
244pub async fn run_script(sql: &str) -> Result<(), AppError> {
245    let pool = larust_orm::pool()?;
246    sqlx::raw_sql(sql).execute(pool).await.map_err(internal)?;
247    Ok(())
248}
249
250#[cfg(test)]
251mod tests {
252    use super::*;
253
254    fn id_column() -> ColumnInfo {
255        ColumnInfo {
256            name: "id".to_string(),
257            not_null: true,
258            kind: sqlx::any::AnyTypeInfoKind::BigInt,
259        }
260    }
261
262    #[test]
263    fn require_known_pk_columns_accepts_a_real_column() {
264        let columns = [id_column()];
265        let pk = [("id".to_string(), Json::from(1))];
266        assert!(require_known_pk_columns(&columns, &pk).is_ok());
267    }
268
269    #[test]
270    fn require_known_pk_columns_rejects_an_unknown_column_name() {
271        // The exact shape of the real gap: a `pk_*` field *name* an
272        // attacker fully controls, crafted to break out of the `"..."`
273        // identifier quoting a naive `format!` would otherwise use.
274        let columns = [id_column()];
275        let pk = [(r#"id" OR "1"="1"#.to_string(), Json::from(1))];
276        let error = require_known_pk_columns(&columns, &pk).unwrap_err();
277        assert!(matches!(
278            error,
279            AppError::Http {
280                status: StatusCode::BAD_REQUEST,
281                ..
282            }
283        ));
284    }
285}
larust_db/sql/mutate.rs

larust_db/sql/
mutate.rs
