pub trait Params: Sealed { }Expand description
Trait used for sets of parameter passed into SQL statements/queries.
Note: Currently, this trait can only be implemented inside this crate.
Additionally, it’s 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 SQLite
Many functions in this library let you pass parameters to SQLite. 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:
-
For small lists of parameters up to 16 items, they may alternatively be passed as a tuple, as in
thing.query((1, "foo")).This is somewhat inconvenient for a single item, since you need a weird-looking trailing comma:
thing.query(("example",)). That case is perhaps more cleanly expressed asthing.query(["example"]). -
Using the
rusqlite::params!macro, e.g.thing.query(rusqlite::params![1, "foo", bar]). This is mostly useful for heterogeneous lists where the number of parameters greater than 16, or homogenous lists of parameters where the number of parameters exceeds 32. -
For small homogeneous lists of parameters, they can either be passed as:
-
an array, as in
thing.query([1i32, 2, 3, 4])orthing.query(["foo", "bar", "baz"]). -
a reference to an array of references, as in
thing.query(&["foo", "bar", "baz"])orthing.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
rusqlite::params!)
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 useparams!, an array literal, a&[&dyn ToSql]or if none of those work,ParamsFromIter. -
-
As a slice of
ToSqltrait object references, e.g.&[&dyn ToSql]. This is mostly useful for passing parameter lists around as arguments without having every function take a genericP: Params.
Example (positional)
fn update_rows(conn: &Connection) -> Result<()> {
let mut stmt = conn.prepare("INSERT INTO test (a, b) VALUES (?1, ?2)")?;
// Using a tuple:
stmt.execute((0, "foobar"))?;
// Using `rusqlite::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"])?;
// However, this doesn't work (see above):
// stmt.execute(&[1i32, 2i32])?;
Ok(())
}Named parameters
SQLite lets you name parameters using a number of conventions (“:foo”, “@foo”, “$foo”). You can pass named parameters in to SQLite using rusqlite in a few ways:
-
Using the
rusqlite::named_params!macro, as instmt.execute(named_params!{ ":name": "foo", ":age": 99 }). Similar to theparamsmacro, this is most useful for heterogeneous lists of parameters, or lists where the number of parameters exceeds 32. -
As a slice of
&[(&str, &dyn ToSql)]. This is what essentially all of these boil down to in the end, conceptually at least. In theory you can pass this asstmt. -
As array references, similar to the positional params. This looks like
thing.query(&[(":foo", &1i32), (":bar", &2i32)])orthing.query(&[(":foo", "abc"), (":bar", "def")]).
Note: Unbound named parameters will be left to the value they previously
were bound with, falling back to NULL for parameters which have never been
bound.
Example (named)
fn insert(conn: &Connection) -> Result<()> {
let mut stmt = conn.prepare("INSERT INTO test (key, value) VALUES (:key, :value)")?;
// Using `rusqlite::params!`:
stmt.execute(named_params! { ":key": "one", ":val": 2 })?;
// Alternatively:
stmt.execute(&[(":key", "three"), (":val", "four")])?;
// Or:
stmt.execute(&[(":key", &100), (":val", &200)])?;
Ok(())
}No parameters
You can just use an empty tuple or the empty array literal to run a query that accepts no parameters.
Example (no parameters)
The empty tuple:
fn delete_all_users(conn: &Connection) -> Result<()> {
// You may also use `()`.
conn.execute("DELETE FROM users", ())?;
Ok(())
}The empty array:
fn delete_all_users(conn: &Connection) -> Result<()> {
// Just use an empty array (e.g. `[]`) for no params.
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]. This is often annoying to construct if you don’t already have this type on-hand. - Use the
ParamsFromItertype. This essentially lets you wrap an iterator someT: ToSqlwith something that implementsParams. The usage of this looks likerusqlite::params_from_iter(something).
A lot of the considerations here are similar either way, so you should see
the ParamsFromIter documentation for more info / examples.