duckdb/

params.rs

use crate::{Error, Result, Statement, ToSql};
use std::{
    borrow::Borrow,
    collections::{HashMap, HashSet},
    hash::{BuildHasher, Hash},
};

mod sealed {
    /// This trait exists just to ensure that the only impls of `trait Params`
    /// that are allowed are ones in this crate.
    pub trait Sealed {}
}
use sealed::Sealed;

/// Trait used for [sets of parameter][params] passed into SQL
/// statements/queries.
///
/// [params]: https://duckdb.org/docs/stable/clients/c/prepared.html
///
/// Note: Currently, this trait can only be implemented inside this crate.
/// Additionally, its methods (which are `doc(hidden)`) should currently not be
/// considered part of the stable API, although it's possible they will
/// stabilize in the future.
///
/// # Passing parameters to DuckDB
///
/// Many functions in this library let you pass parameters to DuckDB. Doing this
/// lets you avoid any risk of SQL injection, and is simpler than escaping
/// things manually. Aside from deprecated functions and a few helpers, this is
/// indicated by the function taking a generic argument that implements `Params`
/// (this trait).
///
/// ## Positional parameters
///
/// For cases where you want to pass a list of parameters where the number of
/// parameters is known at compile time, this can be done in one of the
/// following ways:
///
/// - Using the [`duckdb::params!`](crate::params!) macro, e.g.
///   `thing.query(duckdb::params![1, "foo", bar])`. This is mostly useful for
///   heterogeneous lists of parameters, or lists where the number of parameters
///   exceeds 32.
///
/// - For small heterogeneous lists of parameters, they can either be passed as:
///
///     - an array, as in `thing.query([1i32, 2, 3, 4])` or `thing.query(["foo",
///       "bar", "baz"])`.
///
///     - a reference to an array of references, as in `thing.query(&["foo",
///       "bar", "baz"])` or `thing.query(&[&1i32, &2, &3])`.
///
///       (Note: in this case we don't implement this for slices for coherence
///       reasons, so it really is only for the "reference to array" types —
///       hence why the number of parameters must be <= 32 or you need to
///       reach for `duckdb::params!`)
///
///     - a tuple of mixed types (up to 16 elements), as in
///       `thing.execute((1i32, "foo", 3.14f64))`. This is the most ergonomic
///       way to pass heterogeneous parameters.
///
///   Unfortunately, in the current design it's not possible to allow this for
///   references to arrays of non-references (e.g. `&[1i32, 2, 3]`). Code like
///   this should instead either use `params!`, an array literal, a `&[&dyn
///   ToSql]` or if none of those work, [`ParamsFromIter`].
///
/// - As a slice of `ToSql` trait object references, e.g. `&[&dyn ToSql]`. This
///   is mostly useful for passing parameter lists around as arguments without
///   having every function take a generic `P: Params`.
///
/// ### Example (positional)
///
/// ```rust,no_run
/// # use duckdb::{Connection, Result, params};
/// fn update_rows(conn: &Connection) -> Result<()> {
///     let mut stmt = conn.prepare("INSERT INTO test (a, b) VALUES (?, ?)")?;
///
///     // Using `duckdb::params!`:
///     stmt.execute(params![1i32, "blah"])?;
///
///     // array literal — non-references
///     stmt.execute([2i32, 3i32])?;
///
///     // array literal — references
///     stmt.execute(["foo", "bar"])?;
///
///     // Slice literal, references:
///     stmt.execute(&[&2i32, &3i32])?;
///
///     // Note: The types behind the references don't have to be `Sized`
///     stmt.execute(&["foo", "bar"])?;
///
///     // Tuple of mixed types:
///     stmt.execute((1i32, "blah"))?;
///
///     // However, this doesn't work (see above):
///     // stmt.execute(&[1i32, 2i32])?;
///     Ok(())
/// }
/// ```
///
/// ## Named parameters
///
/// Named parameters can be passed using [`duckdb::named_params!`](crate::named_params!)
/// or a manually constructed `&[(&str, &dyn ToSql)]` for heterogeneous values,
/// or as any `HashMap` whose keys borrow as `str` and whose values implement a
/// single concrete [`ToSql`] type, including maps with custom hashers. The keys
/// must not include the `$` prefix. Named-parameter APIs must be used with
/// named SQL placeholders such as `$name`, not positional placeholders such as
/// `?` or `?1`.
///
/// Returns [`Error::InvalidParameterName`](crate::Error::InvalidParameterName)
/// if a SQL placeholder has no matching key, a provided key is not present in
/// the SQL, slice parameters contain duplicate keys, or positional placeholders
/// are used with named-parameter APIs.
///
/// ### Example (named parameters with a HashMap)
///
/// ```rust,no_run
/// use std::collections::HashMap;
/// use duckdb::{Connection, Result};
///
/// fn execute_query(conn: &Connection) -> Result<Vec<String>> {
///     let params = HashMap::from([("min", 23), ("max", 42)]);
///     let mut stmt = conn.prepare("SELECT name FROM people WHERE age BETWEEN $min AND $max")?;
///     let rows = stmt.query_map(&params, |row| row.get::<_, String>(0))?;
///     rows.collect()
/// }
/// ```
///
/// ### Example (named parameters with mixed types)
///
/// ```rust,no_run
/// use duckdb::{Connection, Result, named_params};
///
/// fn execute_query(conn: &Connection, min_age: i32, name: &str) -> Result<bool> {
///     conn.query_row(
///         "SELECT $name = 'Alice' AND $age >= 18",
///         named_params! {
///             "name": name,
///             "age": min_age,
///         },
///         |row| row.get(0),
///     )
/// }
/// ```
///
/// ## No parameters
///
/// You can use an empty array literal or the empty tuple for no params.
///
/// ### Example (no parameters)
///
/// ```rust,no_run
/// # use duckdb::{Connection, Result, params};
/// fn delete_all_users(conn: &Connection) -> Result<()> {
///     // Empty array:
///     conn.execute("DELETE FROM users", [])?;
///     // Or empty tuple:
///     conn.execute("DELETE FROM users", ())?;
///     Ok(())
/// }
/// ```
///
/// ## Dynamic parameter list
///
/// If you have a number of parameters which is unknown at compile time (for
/// example, building a dynamic query at runtime), you have two choices:
///
/// - Use a `&[&dyn ToSql]`, which is nice if you have one otherwise might be
///   annoying.
/// - Use the [`ParamsFromIter`] type. This essentially lets you wrap an
///   iterator some `T: ToSql` with something that implements `Params`.
///
/// A lot of the considerations here are similar either way, so you should see
/// the [`ParamsFromIter`] documentation for more info / examples.
pub trait Params: Sealed {
    // XXX not public api, might not need to expose.
    //
    // Binds the parameters to the statement. It is unlikely calling this
    // explicitly will do what you want. Please use `Statement::query` or
    // similar directly.
    //
    // For now, just hide the function in the docs...
    #[doc(hidden)]
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()>;
}

// Explicitly impl for empty array. Critically, for `conn.execute([])` to be
// unambiguous, this must be the *only* implementation for an empty array. This
// avoids `NO_PARAMS` being a necessary part of the API.
impl Sealed for [&dyn ToSql; 0] {}
impl Params for [&dyn ToSql; 0] {
    #[inline]
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        // Note: Can't just return `Ok(())` — `Statement::bind_parameters`
        // checks that the right number of params were passed too.
        // TODO: we should have tests for `Error::InvalidParameterCount`...
        stmt.bind_parameters(&[] as &[&dyn ToSql])
    }
}

// Empty tuple — allows `conn.execute("...", ())`.
impl Sealed for () {}
impl Params for () {
    #[inline]
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        stmt.bind_parameters(&[] as &[&dyn ToSql])
    }
}

impl Sealed for &[&dyn ToSql] {}
impl Params for &[&dyn ToSql] {
    #[inline]
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        stmt.bind_parameters(self)
    }
}

macro_rules! impl_for_array_ref {
    ($($N:literal)+) => {$(
        // These are already generic, and there's a shedload of them, so lets
        // avoid the compile time hit from making them all inline for now.
        impl<T: ToSql + ?Sized> Sealed for &[&T; $N] {}
        impl<T: ToSql + ?Sized> Params for &[&T; $N] {
            fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
                stmt.bind_parameters(self)
            }
        }
        impl<T: ToSql> Sealed for [T; $N] {}
        impl<T: ToSql> Params for [T; $N] {
            #[inline]
            fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
                stmt.bind_parameters(&self)
            }
        }
    )+};
}

// Following libstd/libcore's (old) lead, implement this for arrays up to `[_;
// 32]`. Note `[_; 0]` is intentionally omitted for coherence reasons, see the
// note above the impl of `[&dyn ToSql; 0]` for more information.
impl_for_array_ref!(
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
    18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
);

macro_rules! impl_params_for_tuple {
    ($($T:ident),+) => {
        impl<$($T: ToSql),+> Sealed for ($($T,)+) {}
        impl<$($T: ToSql),+> Params for ($($T,)+) {
            fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
                #[allow(non_snake_case)]
                let ($($T,)+) = &self;
                stmt.bind_parameters(&[$($T as &dyn ToSql),+])
            }
        }
    };
}

// Implement Params for tuples up to arity 16, matching rusqlite.
impl_params_for_tuple!(T1);
impl_params_for_tuple!(T1, T2);
impl_params_for_tuple!(T1, T2, T3);
impl_params_for_tuple!(T1, T2, T3, T4);
impl_params_for_tuple!(T1, T2, T3, T4, T5);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15);
impl_params_for_tuple!(T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12, T13, T14, T15, T16);

/// Adapter type which allows any iterator over [`ToSql`] values to implement
/// [`Params`].
///
/// This struct is created by the [`params_from_iter`] function.
///
/// This can be useful if you have something like an `&[String]` (of unknown
/// length), and you want to use them with an API that wants something
/// implementing `Params`. This way, you can avoid having to allocate storage
/// for something like a `&[&dyn ToSql]`.
///
/// This essentially is only ever actually needed when dynamically generating
/// SQL — static SQL (by definition) has the number of parameters known
/// statically. As dynamically generating SQL is itself pretty advanced, this
/// API is itself for advanced use cases (See "Realistic use case" in the
/// examples).
///
/// # Example
///
/// ## Basic usage
///
/// ```rust,no_run
/// use duckdb::{Connection, Result, params_from_iter};
/// use std::collections::BTreeSet;
///
/// fn query(conn: &Connection, ids: &BTreeSet<String>) -> Result<()> {
///     assert_eq!(ids.len(), 3, "Unrealistic sample code");
///
///     let mut stmt = conn.prepare("SELECT * FROM users WHERE id IN (?, ?, ?)")?;
///     let _rows = stmt.query(params_from_iter(ids.iter()))?;
///
///     // use _rows...
///     Ok(())
/// }
/// ```
///
/// ## Realistic use case
///
/// Here's how you'd use `ParamsFromIter` to call [`Statement::exists`] with a
/// dynamic number of parameters.
///
/// ```rust,no_run
/// use duckdb::{Connection, Result};
///
/// pub fn any_active_users(conn: &Connection, usernames: &[String]) -> Result<bool> {
///     if usernames.is_empty() {
///         return Ok(false);
///     }
///
///     // Note: `repeat_vars` never returns anything attacker-controlled, so
///     // it's fine to use it in a dynamically-built SQL string.
///     let vars = repeat_vars(usernames.len());
///
///     let sql = format!(
///         // In practice this would probably be better as an `EXISTS` query.
///         "SELECT 1 FROM user WHERE is_active AND name IN ({}) LIMIT 1",
///         vars,
///     );
///     let mut stmt = conn.prepare(&sql)?;
///     stmt.exists(duckdb::params_from_iter(usernames))
/// }
///
/// // Helper function to return a comma-separated sequence of `?`.
/// // - `repeat_vars(0) => panic!(...)`
/// // - `repeat_vars(1) => "?"`
/// // - `repeat_vars(2) => "?,?"`
/// // - `repeat_vars(3) => "?,?,?"`
/// // - ...
/// fn repeat_vars(count: usize) -> String {
///     assert_ne!(count, 0);
///     let mut s = "?,".repeat(count);
///     // Remove trailing comma
///     s.pop();
///     s
/// }
/// ```
///
/// That is fairly complex, and even so would need even more work to be fully
/// production-ready:
///
/// - `repeat_vars` can be implemented in a way that avoids needing to allocate
///   a String.
///
/// - Etc...
///
/// This complexity reflects the fact that `ParamsFromIter` is mainly intended
/// for advanced use cases — most of the time you should know how many
/// parameters you have statically (and if you don't, you're either doing
/// something tricky, or should take a moment to think about the design).
#[derive(Clone, Debug)]
pub struct ParamsFromIter<I>(I);

/// Constructor function for a [`ParamsFromIter`]. See its documentation for
/// more.
#[inline]
pub fn params_from_iter<I>(iter: I) -> ParamsFromIter<I>
where
    I: IntoIterator,
    I::Item: ToSql,
{
    ParamsFromIter(iter)
}

impl<I> Sealed for ParamsFromIter<I>
where
    I: IntoIterator,
    I::Item: ToSql,
{
}

impl<I> Params for ParamsFromIter<I>
where
    I: IntoIterator,
    I::Item: ToSql,
{
    #[inline]
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        stmt.bind_parameters(self.0)
    }
}

fn reject_positional(name: &str) -> Result<()> {
    if !name.is_empty() && name.bytes().all(|byte| byte.is_ascii_digit()) {
        return Err(Error::InvalidParameterName(format!(
            "positional parameter {name} cannot be used with named parameters"
        )));
    }
    Ok(())
}

impl Sealed for &[(&str, &dyn ToSql)] {}

impl Params for &[(&str, &dyn ToSql)] {
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        let n = stmt.parameter_count();
        let mut seen: HashSet<&str> = HashSet::with_capacity(self.len());
        for (key, _) in self {
            if !seen.insert(*key) {
                return Err(Error::InvalidParameterName(format!("duplicate parameter name: {key}")));
            }
        }

        let mut params = Vec::with_capacity(n);
        let mut used = vec![false; self.len()];
        for i in 1..=n {
            let name = stmt.parameter_name(i)?;
            reject_positional(&name)?;
            let (idx, value) = self
                .iter()
                .enumerate()
                .find_map(|(idx, (key, value))| (*key == name).then_some((idx, *value)))
                .ok_or(Error::InvalidParameterName(name))?;
            used[idx] = true;
            params.push(value);
        }

        if let Some(idx) = used.iter().position(|u| !u) {
            return Err(Error::InvalidParameterName(self[idx].0.to_string()));
        }

        stmt.bind_parameters(params)
    }
}

impl<K, V, S> Sealed for &HashMap<K, V, S>
where
    K: Borrow<str> + Eq + Hash,
    V: ToSql,
    S: BuildHasher,
{
}

impl<K, V, S> Params for &HashMap<K, V, S>
where
    K: Borrow<str> + Eq + Hash,
    V: ToSql,
    S: BuildHasher,
{
    fn __bind_in(self, stmt: &mut Statement<'_>) -> Result<()> {
        let n = stmt.parameter_count();
        let mut params = Vec::with_capacity(n);
        let mut bound: HashSet<&str> = HashSet::with_capacity(n);
        for i in 1..=n {
            let name = stmt.parameter_name(i)?;
            reject_positional(&name)?;
            let (key, value) = self
                .get_key_value(name.as_str())
                .ok_or(Error::InvalidParameterName(name))?;
            bound.insert(key.borrow());
            params.push(value as &dyn ToSql);
        }

        // HashMap iteration order is arbitrary, so choose a stable extra key for diagnostics.
        if let Some(extra) = self
            .keys()
            .map(|key| -> &str { key.borrow() })
            .filter(|key| !bound.contains(*key))
            .min()
        {
            return Err(Error::InvalidParameterName(extra.to_string()));
        }

        stmt.bind_parameters(params)
    }
}