nodedb_sql/
params.rs

1// SPDX-License-Identifier: Apache-2.0
2
3//! AST-level parameter binding for prepared statements.
4//!
5//! Every `Value::Placeholder("$N")` in a parsed statement is rewritten to
6//! a concrete literal value via sqlparser's `VisitorMut`. The visitor
7//! traverses every expression position the AST defines — CTE bodies,
8//! window specs, `Update.from/returning/limit`, `Delete.returning`,
9//! `Insert.on_conflict`, `Expr::Array` elements, `Expr::AnyOp`/`AllOp`
10//! right-hand sides, `Expr::Interval`, and any variant sqlparser adds in
11//! the future — without us maintaining a hand-written walker.
12//!
13//! # Why a visitor, not a hand-written walker
14//!
15//! The previous implementation was a recursive match on ~20 `Expr` /
16//! `Statement` / `Query` variants. Every new sqlparser variant it didn't
17//! enumerate was a silent bug: placeholders survived into the planner and
18//! surfaced as "unsupported expression: $1" in the resolver. The walker
19//! was opt-in where it had to be exhaustive to be correct. `VisitorMut`
20//! moves the exhaustiveness burden to sqlparser itself.
21
22use core::ops::ControlFlow;
23
24use sqlparser::ast::{Statement, Value, ValueWithSpan, VisitMut, VisitorMut};
25
26/// Parameter value for AST substitution.
27///
28/// Converted from pgwire binary parameters + type info in the Control Plane.
29#[derive(Debug, Clone)]
30pub enum ParamValue {
31    Null,
32    Bool(bool),
33    Int64(i64),
34    Float64(f64),
35    /// Exact arbitrary-precision decimal from a NUMERIC/DECIMAL pgwire parameter.
36    Decimal(rust_decimal::Decimal),
37    Text(String),
38    /// Naive (no-timezone) timestamp from a TIMESTAMP pgwire parameter.
39    Timestamp(nodedb_types::datetime::NdbDateTime),
40    /// UTC (timezone-aware) timestamp from a TIMESTAMPTZ pgwire parameter.
41    Timestamptz(nodedb_types::datetime::NdbDateTime),
42}
43
44/// Substitute all `$N` placeholders in a parsed statement with concrete values.
45pub fn bind_params(stmt: &mut Statement, params: &[ParamValue]) {
46    if params.is_empty() {
47        return;
48    }
49    let mut binder = ParamBinder { params };
50    let _ = stmt.visit(&mut binder);
51}
52
53/// Visitor that rewrites every `Value::Placeholder("$N")` it encounters.
54///
55/// sqlparser's `VisitMut` impls take us into every expression position of
56/// every `Expr`, `Statement`, `Query`, `SetExpr`, `TableFactor`, etc. —
57/// so we only care about the leaf: the `Value` itself.
58struct ParamBinder<'a> {
59    params: &'a [ParamValue],
60}
61
62impl VisitorMut for ParamBinder<'_> {
63    type Break = ();
64
65    fn pre_visit_value(&mut self, value: &mut ValueWithSpan) -> ControlFlow<Self::Break> {
66        // Only the literal is rewritten; `value.span` keeps pointing at the
67        // placeholder token, so a later diagnostic still names the source
68        // position the client wrote rather than a synthetic one.
69        if let Value::Placeholder(p) = &value.value
70            && let Some(v) = placeholder_to_value(p, self.params)
71        {
72            value.value = v;
73        }
74        ControlFlow::Continue(())
75    }
76}
77
78fn placeholder_to_value(placeholder: &str, params: &[ParamValue]) -> Option<Value> {
79    let idx_str = placeholder.strip_prefix('$')?;
80    let idx: usize = idx_str.parse().ok()?;
81    let param = params.get(idx.checked_sub(1)?)?;
82    Some(match param {
83        ParamValue::Null => Value::Null,
84        ParamValue::Bool(true) => Value::Boolean(true),
85        ParamValue::Bool(false) => Value::Boolean(false),
86        ParamValue::Int64(n) => Value::Number(n.to_string(), false),
87        ParamValue::Float64(f) => Value::Number(float_literal_text(*f), false),
88        ParamValue::Decimal(d) => Value::Number(d.to_string(), false),
89        ParamValue::Text(s) => Value::SingleQuotedString(s.clone()),
90        // Timestamp/Timestamptz: emit as a typed SQL literal so the resolver
91        // can produce the correct SqlValue variant (Timestamp vs Timestamptz).
92        ParamValue::Timestamp(dt) => Value::SingleQuotedString(dt.to_iso8601()),
93        ParamValue::Timestamptz(dt) => Value::SingleQuotedString(dt.to_iso8601()),
94    })
95}
96
97/// The number-literal text of a float parameter.
98///
99/// The text always carries an exponent: `1.5e0`, `2e0`. The literal
100/// resolver reads an exponent as a float, so the value stays a float. The
101/// plain form reads `1.5` as a decimal and `2` as an integer. The mantissa
102/// is the shortest text that round-trips the `f64`. A non-finite float is
103/// `NaN`, `inf` or `-inf`, which the resolver also reads as a float.
104pub(crate) fn float_literal_text(f: f64) -> String {
105    format!("{f:e}")
106}
107
108#[cfg(test)]
109mod tests {
110    use super::*;
111    use crate::parser::statement::parse_sql;
112
113    fn bind_and_format(sql: &str, params: &[ParamValue]) -> String {
114        let mut stmts = parse_sql(sql).unwrap();
115        for stmt in &mut stmts {
116            bind_params(stmt, params);
117        }
118        stmts
119            .iter()
120            .map(|s| s.to_string())
121            .collect::<Vec<_>>()
122            .join("; ")
123    }
124
125    #[test]
126    fn bind_select_where() {
127        let result = bind_and_format(
128            "SELECT * FROM users WHERE id = $1",
129            &[ParamValue::Int64(42)],
130        );
131        assert!(result.contains("id = 42"), "got: {result}");
132    }
133
134    #[test]
135    fn bind_string_param() {
136        let result = bind_and_format(
137            "SELECT * FROM users WHERE name = $1",
138            &[ParamValue::Text("alice".into())],
139        );
140        assert!(result.contains("name = 'alice'"), "got: {result}");
141    }
142
143    #[test]
144    fn bind_null_param() {
145        let result = bind_and_format("SELECT * FROM users WHERE name = $1", &[ParamValue::Null]);
146        assert!(result.contains("name = NULL"), "got: {result}");
147    }
148
149    #[test]
150    fn bind_multiple_params() {
151        let result = bind_and_format(
152            "SELECT * FROM users WHERE age > $1 AND name = $2",
153            &[ParamValue::Int64(18), ParamValue::Text("bob".into())],
154        );
155        assert!(result.contains("age > 18"), "got: {result}");
156        assert!(result.contains("name = 'bob'"), "got: {result}");
157    }
158
159    #[test]
160    fn bind_insert_values() {
161        let result = bind_and_format(
162            "INSERT INTO users (id, name) VALUES ($1, $2)",
163            &[ParamValue::Int64(1), ParamValue::Text("eve".into())],
164        );
165        assert!(result.contains("1, 'eve'"), "got: {result}");
166    }
167
168    #[test]
169    fn bind_bool_param() {
170        let result = bind_and_format(
171            "SELECT * FROM users WHERE active = $1",
172            &[ParamValue::Bool(true)],
173        );
174        assert!(result.contains("active = true"), "got: {result}");
175    }
176
177    #[test]
178    fn no_params_noop() {
179        let result = bind_and_format("SELECT 1", &[]);
180        assert!(result.contains("SELECT 1"));
181    }
182
183    #[test]
184    fn bind_cte_body_placeholders() {
185        let result = bind_and_format(
186            "WITH x AS (SELECT $1 AS v) SELECT v FROM x",
187            &[ParamValue::Int64(42)],
188        );
189        assert!(
190            result.contains("SELECT 42"),
191            "CTE body placeholder not substituted: {result}"
192        );
193        assert!(!result.contains("$1"), "placeholder survived: {result}");
194    }
195
196    #[test]
197    fn bind_recursive_cte_placeholders() {
198        let result = bind_and_format(
199            "WITH RECURSIVE chain AS (SELECT $1 AS id UNION ALL SELECT chain.id + 1 FROM chain WHERE chain.id < $2) SELECT * FROM chain",
200            &[ParamValue::Int64(1), ParamValue::Int64(10)],
201        );
202        assert!(
203            !result.contains("$1") && !result.contains("$2"),
204            "got: {result}"
205        );
206    }
207
208    #[test]
209    fn bind_array_elements() {
210        let result = bind_and_format(
211            "SELECT ARRAY[$1, $2, $3]",
212            &[
213                ParamValue::Int64(1),
214                ParamValue::Int64(2),
215                ParamValue::Int64(3),
216            ],
217        );
218        assert!(
219            !result.contains("$1"),
220            "array placeholder survived: {result}"
221        );
222        assert!(
223            result.contains('1') && result.contains('2') && result.contains('3'),
224            "got: {result}"
225        );
226    }
227
228    #[test]
229    fn bind_any_op_rhs() {
230        let result = bind_and_format(
231            "SELECT * FROM t WHERE id = ANY($1)",
232            &[ParamValue::Text("{a,b}".into())],
233        );
234        assert!(!result.contains("$1"), "got: {result}");
235    }
236
237    #[test]
238    fn bind_all_op_rhs() {
239        let result = bind_and_format(
240            "SELECT * FROM t WHERE id = ALL($1)",
241            &[ParamValue::Text("{a,b}".into())],
242        );
243        assert!(!result.contains("$1"), "got: {result}");
244    }
245
246    #[test]
247    fn bind_update_returning() {
248        let result = bind_and_format(
249            "UPDATE t SET n = 1 WHERE id = $1 RETURNING $2 AS tag",
250            &[ParamValue::Int64(7), ParamValue::Text("note".into())],
251        );
252        assert!(result.contains("id = 7"), "got: {result}");
253        assert!(result.contains("'note'"), "got: {result}");
254        assert!(!result.contains("$2"), "got: {result}");
255    }
256
257    #[test]
258    fn bind_delete_returning() {
259        let result = bind_and_format(
260            "DELETE FROM t WHERE id = $1 RETURNING $2 AS tag",
261            &[ParamValue::Int64(3), ParamValue::Text("gone".into())],
262        );
263        assert!(
264            result.contains("id = 3") && result.contains("'gone'"),
265            "got: {result}"
266        );
267        assert!(!result.contains("$2"), "got: {result}");
268    }
269
270    #[test]
271    fn bind_update_limit() {
272        let result = bind_and_format(
273            "UPDATE t SET n = 1 WHERE id > 0 LIMIT $1",
274            &[ParamValue::Int64(5)],
275        );
276        assert!(!result.contains("$1"), "got: {result}");
277    }
278
279    #[test]
280    fn bind_interval_value_placeholder() {
281        let result = bind_and_format(
282            "SELECT now() - INTERVAL $1",
283            &[ParamValue::Text("1 day".into())],
284        );
285        assert!(!result.contains("$1"), "got: {result}");
286    }
287
288    #[test]
289    fn bind_update_from_subquery() {
290        let result = bind_and_format(
291            "UPDATE t SET n = s.y FROM (SELECT $1 AS y) s WHERE t.id = s.y",
292            &[ParamValue::Int64(7)],
293        );
294        assert!(!result.contains("$1"), "got: {result}");
295    }
296
297    #[test]
298    fn bind_insert_on_conflict_update_placeholder() {
299        let result = bind_and_format(
300            "INSERT INTO t (id, n) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET n = $3",
301            &[
302                ParamValue::Int64(1),
303                ParamValue::Int64(2),
304                ParamValue::Int64(99),
305            ],
306        );
307        assert!(!result.contains("$3"), "got: {result}");
308    }
309
310    #[test]
311    fn bind_window_partition_by_placeholder() {
312        let result = bind_and_format(
313            "SELECT LAG(x) OVER (PARTITION BY $1 ORDER BY b) FROM t",
314            &[ParamValue::Text("user_id".into())],
315        );
316        assert!(!result.contains("$1"), "got: {result}");
317    }
318
319    /// A float parameter resolves to a float, whole or fractional, never to
320    /// the decimal or integer its plain text reads as.
321    #[test]
322    fn a_float_param_resolves_to_a_float() {
323        use crate::resolver::expr::convert_value;
324        use crate::types::SqlValue;
325        for f in [1.5, 2.0, 0.1, -3.25, 1e300, 1e-300, f64::INFINITY] {
326            let literal = placeholder_to_value("$1", &[ParamValue::Float64(f)])
327                .expect("the placeholder names the one parameter");
328            assert_eq!(
329                convert_value(&literal).expect("a float literal resolves"),
330                SqlValue::Float(f),
331                "{f}"
332            );
333        }
334        let literal = placeholder_to_value("$1", &[ParamValue::Float64(f64::NAN)])
335            .expect("the placeholder names the one parameter");
336        assert!(
337            matches!(convert_value(&literal), Ok(SqlValue::Float(f)) if f.is_nan()),
338            "{literal}"
339        );
340    }
341
342    #[test]
343    fn bind_window_order_by_placeholder() {
344        let result = bind_and_format(
345            "SELECT LAG(x) OVER (PARTITION BY a ORDER BY $1) FROM t",
346            &[ParamValue::Text("ts".into())],
347        );
348        assert!(!result.contains("$1"), "got: {result}");
349    }
350}
nodedb_sql/params.rs

nodedb_sql/
params.rs
