sqlx_build_trust_core/
query.rs

1use std::marker::PhantomData;
2
3use either::Either;
4use futures_core::stream::BoxStream;
5use futures_util::{future, StreamExt, TryFutureExt, TryStreamExt};
6
7use crate::arguments::{Arguments, IntoArguments};
8use crate::database::{Database, HasArguments, HasStatement, HasStatementCache};
9use crate::encode::Encode;
10use crate::error::Error;
11use crate::executor::{Execute, Executor};
12use crate::statement::Statement;
13use crate::types::Type;
14
15/// A single SQL query as a prepared statement. Returned by [`query()`].
16#[must_use = "query must be executed to affect database"]
17pub struct Query<'q, DB: Database, A> {
18    pub(crate) statement: Either<&'q str, &'q <DB as HasStatement<'q>>::Statement>,
19    pub(crate) arguments: Option<A>,
20    pub(crate) database: PhantomData<DB>,
21    pub(crate) persistent: bool,
22}
23
24/// A single SQL query that will map its results to an owned Rust type.
25///
26/// Executes as a prepared statement.
27///
28/// Returned by [`Query::try_map`], `query!()`, etc. Has most of the same methods as [`Query`] but
29/// the return types are changed to reflect the mapping. However, there is no equivalent of
30/// [`Query::execute`] as it doesn't make sense to map the result type and then ignore it.
31///
32/// [`Query::bind`] is also omitted; stylistically we recommend placing your `.bind()` calls
33/// before `.try_map()`. This is also to prevent adding superfluous binds to the result of
34/// `query!()` et al.
35#[must_use = "query must be executed to affect database"]
36pub struct Map<'q, DB: Database, F, A> {
37    inner: Query<'q, DB, A>,
38    mapper: F,
39}
40
41impl<'q, DB, A> Execute<'q, DB> for Query<'q, DB, A>
42where
43    DB: Database,
44    A: Send + IntoArguments<'q, DB>,
45{
46    #[inline]
47    fn sql(&self) -> &'q str {
48        match self.statement {
49            Either::Right(ref statement) => statement.sql(),
50            Either::Left(sql) => sql,
51        }
52    }
53
54    fn statement(&self) -> Option<&<DB as HasStatement<'q>>::Statement> {
55        match self.statement {
56            Either::Right(ref statement) => Some(&statement),
57            Either::Left(_) => None,
58        }
59    }
60
61    #[inline]
62    fn take_arguments(&mut self) -> Option<<DB as HasArguments<'q>>::Arguments> {
63        self.arguments.take().map(IntoArguments::into_arguments)
64    }
65
66    #[inline]
67    fn persistent(&self) -> bool {
68        self.persistent
69    }
70}
71
72impl<'q, DB: Database> Query<'q, DB, <DB as HasArguments<'q>>::Arguments> {
73    /// Bind a value for use with this SQL query.
74    ///
75    /// If the number of times this is called does not match the number of bind parameters that
76    /// appear in the query (`?` for most SQL flavors, `$1 .. $N` for Postgres) then an error
77    /// will be returned when this query is executed.
78    ///
79    /// There is no validation that the value is of the type expected by the query. Most SQL
80    /// flavors will perform type coercion (Postgres will return a database error).
81    pub fn bind<T: 'q + Send + Encode<'q, DB> + Type<DB>>(mut self, value: T) -> Self {
82        if let Some(arguments) = &mut self.arguments {
83            arguments.add(value);
84        }
85
86        self
87    }
88}
89
90impl<'q, DB, A> Query<'q, DB, A>
91where
92    DB: Database + HasStatementCache,
93{
94    /// If `true`, the statement will get prepared once and cached to the
95    /// connection's statement cache.
96    ///
97    /// If queried once with the flag set to `true`, all subsequent queries
98    /// matching the one with the flag will use the cached statement until the
99    /// cache is cleared.
100    ///
101    /// If `false`, the prepared statement will be closed after execution.
102    ///
103    /// Default: `true`.
104    pub fn persistent(mut self, value: bool) -> Self {
105        self.persistent = value;
106        self
107    }
108}
109
110impl<'q, DB, A: Send> Query<'q, DB, A>
111where
112    DB: Database,
113    A: 'q + IntoArguments<'q, DB>,
114{
115    /// Map each row in the result to another type.
116    ///
117    /// See [`try_map`](Query::try_map) for a fallible version of this method.
118    ///
119    /// The [`query_as`](super::query_as::query_as) method will construct a mapped query using
120    /// a [`FromRow`](super::from_row::FromRow) implementation.
121    #[inline]
122    pub fn map<F, O>(
123        self,
124        mut f: F,
125    ) -> Map<'q, DB, impl FnMut(DB::Row) -> Result<O, Error> + Send, A>
126    where
127        F: FnMut(DB::Row) -> O + Send,
128        O: Unpin,
129    {
130        self.try_map(move |row| Ok(f(row)))
131    }
132
133    /// Map each row in the result to another type.
134    ///
135    /// The [`query_as`](super::query_as::query_as) method will construct a mapped query using
136    /// a [`FromRow`](super::from_row::FromRow) implementation.
137    #[inline]
138    pub fn try_map<F, O>(self, f: F) -> Map<'q, DB, F, A>
139    where
140        F: FnMut(DB::Row) -> Result<O, Error> + Send,
141        O: Unpin,
142    {
143        Map {
144            inner: self,
145            mapper: f,
146        }
147    }
148
149    /// Execute the query and return the total number of rows affected.
150    #[inline]
151    pub async fn execute<'e, 'c: 'e, E>(self, executor: E) -> Result<DB::QueryResult, Error>
152    where
153        'q: 'e,
154        A: 'e,
155        E: Executor<'c, Database = DB>,
156    {
157        executor.execute(self).await
158    }
159
160    /// Execute multiple queries and return the rows affected from each query, in a stream.
161    #[inline]
162    #[deprecated = "Only the SQLite driver supports multiple statements in one prepared statement and that behavior is deprecated. Use `sqlx::raw_sql()` instead. See https://github.com/launchbadge/sqlx/issues/3108 for discussion."]
163    pub async fn execute_many<'e, 'c: 'e, E>(
164        self,
165        executor: E,
166    ) -> BoxStream<'e, Result<DB::QueryResult, Error>>
167    where
168        'q: 'e,
169        A: 'e,
170        E: Executor<'c, Database = DB>,
171    {
172        executor.execute_many(self)
173    }
174
175    /// Execute the query and return the generated results as a stream.
176    #[inline]
177    pub fn fetch<'e, 'c: 'e, E>(self, executor: E) -> BoxStream<'e, Result<DB::Row, Error>>
178    where
179        'q: 'e,
180        A: 'e,
181        E: Executor<'c, Database = DB>,
182    {
183        executor.fetch(self)
184    }
185
186    /// Execute multiple queries and return the generated results as a stream.
187    ///
188    /// For each query in the stream, any generated rows are returned first,
189    /// then the `QueryResult` with the number of rows affected.
190    #[inline]
191    #[deprecated = "Only the SQLite driver supports multiple statements in one prepared statement and that behavior is deprecated. Use `sqlx::raw_sql()` instead. See https://github.com/launchbadge/sqlx/issues/3108 for discussion."]
192    // TODO: we'll probably still want a way to get the `DB::QueryResult` at the end of a `fetch()` stream.
193    pub fn fetch_many<'e, 'c: 'e, E>(
194        self,
195        executor: E,
196    ) -> BoxStream<'e, Result<Either<DB::QueryResult, DB::Row>, Error>>
197    where
198        'q: 'e,
199        A: 'e,
200        E: Executor<'c, Database = DB>,
201    {
202        executor.fetch_many(self)
203    }
204
205    /// Execute the query and return all the resulting rows collected into a [`Vec`].
206    ///
207    /// ### Note: beware result set size.
208    /// This will attempt to collect the full result set of the query into memory.
209    ///
210    /// To avoid exhausting available memory, ensure the result set has a known upper bound,
211    /// e.g. using `LIMIT`.
212    #[inline]
213    pub async fn fetch_all<'e, 'c: 'e, E>(self, executor: E) -> Result<Vec<DB::Row>, Error>
214    where
215        'q: 'e,
216        A: 'e,
217        E: Executor<'c, Database = DB>,
218    {
219        executor.fetch_all(self).await
220    }
221
222    /// Execute the query, returning the first row or [`Error::RowNotFound`] otherwise.
223    ///
224    /// ### Note: for best performance, ensure the query returns at most one row.
225    /// Depending on the driver implementation, if your query can return more than one row,
226    /// it may lead to wasted CPU time and bandwidth on the database server.
227    ///
228    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
229    /// can result in a more optimal query plan.
230    ///
231    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
232    ///
233    /// Otherwise, you might want to add `LIMIT 1` to your query.
234    #[inline]
235    pub async fn fetch_one<'e, 'c: 'e, E>(self, executor: E) -> Result<DB::Row, Error>
236    where
237        'q: 'e,
238        A: 'e,
239        E: Executor<'c, Database = DB>,
240    {
241        executor.fetch_one(self).await
242    }
243
244    /// Execute the query, returning the first row or `None` otherwise.
245    ///
246    /// ### Note: for best performance, ensure the query returns at most one row.
247    /// Depending on the driver implementation, if your query can return more than one row,
248    /// it may lead to wasted CPU time and bandwidth on the database server.
249    ///
250    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
251    /// can result in a more optimal query plan.
252    ///
253    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
254    ///
255    /// Otherwise, you might want to add `LIMIT 1` to your query.
256    #[inline]
257    pub async fn fetch_optional<'e, 'c: 'e, E>(self, executor: E) -> Result<Option<DB::Row>, Error>
258    where
259        'q: 'e,
260        A: 'e,
261        E: Executor<'c, Database = DB>,
262    {
263        executor.fetch_optional(self).await
264    }
265}
266
267impl<'q, DB, F: Send, A: Send> Execute<'q, DB> for Map<'q, DB, F, A>
268where
269    DB: Database,
270    A: IntoArguments<'q, DB>,
271{
272    #[inline]
273    fn sql(&self) -> &'q str {
274        self.inner.sql()
275    }
276
277    #[inline]
278    fn statement(&self) -> Option<&<DB as HasStatement<'q>>::Statement> {
279        self.inner.statement()
280    }
281
282    #[inline]
283    fn take_arguments(&mut self) -> Option<<DB as HasArguments<'q>>::Arguments> {
284        self.inner.take_arguments()
285    }
286
287    #[inline]
288    fn persistent(&self) -> bool {
289        self.inner.arguments.is_some()
290    }
291}
292
293impl<'q, DB, F, O, A> Map<'q, DB, F, A>
294where
295    DB: Database,
296    F: FnMut(DB::Row) -> Result<O, Error> + Send,
297    O: Send + Unpin,
298    A: 'q + Send + IntoArguments<'q, DB>,
299{
300    /// Map each row in the result to another type.
301    ///
302    /// See [`try_map`](Map::try_map) for a fallible version of this method.
303    ///
304    /// The [`query_as`](super::query_as::query_as) method will construct a mapped query using
305    /// a [`FromRow`](super::from_row::FromRow) implementation.
306    #[inline]
307    pub fn map<G, P>(
308        self,
309        mut g: G,
310    ) -> Map<'q, DB, impl FnMut(DB::Row) -> Result<P, Error> + Send, A>
311    where
312        G: FnMut(O) -> P + Send,
313        P: Unpin,
314    {
315        self.try_map(move |data| Ok(g(data)))
316    }
317
318    /// Map each row in the result to another type.
319    ///
320    /// The [`query_as`](super::query_as::query_as) method will construct a mapped query using
321    /// a [`FromRow`](super::from_row::FromRow) implementation.
322    #[inline]
323    pub fn try_map<G, P>(
324        self,
325        mut g: G,
326    ) -> Map<'q, DB, impl FnMut(DB::Row) -> Result<P, Error> + Send, A>
327    where
328        G: FnMut(O) -> Result<P, Error> + Send,
329        P: Unpin,
330    {
331        let mut f = self.mapper;
332        Map {
333            inner: self.inner,
334            mapper: move |row| f(row).and_then(|o| g(o)),
335        }
336    }
337
338    /// Execute the query and return the generated results as a stream.
339    pub fn fetch<'e, 'c: 'e, E>(self, executor: E) -> BoxStream<'e, Result<O, Error>>
340    where
341        'q: 'e,
342        E: 'e + Executor<'c, Database = DB>,
343        DB: 'e,
344        F: 'e,
345        O: 'e,
346    {
347        // FIXME: this should have used `executor.fetch()` but that's a breaking change
348        // because this technically allows multiple statements in one query string.
349        #[allow(deprecated)]
350        self.fetch_many(executor)
351            .try_filter_map(|step| async move {
352                Ok(match step {
353                    Either::Left(_) => None,
354                    Either::Right(o) => Some(o),
355                })
356            })
357            .boxed()
358    }
359
360    /// Execute multiple queries and return the generated results as a stream
361    /// from each query, in a stream.
362    #[deprecated = "Only the SQLite driver supports multiple statements in one prepared statement and that behavior is deprecated. Use `sqlx::raw_sql()` instead."]
363    pub fn fetch_many<'e, 'c: 'e, E>(
364        mut self,
365        executor: E,
366    ) -> BoxStream<'e, Result<Either<DB::QueryResult, O>, Error>>
367    where
368        'q: 'e,
369        E: 'e + Executor<'c, Database = DB>,
370        DB: 'e,
371        F: 'e,
372        O: 'e,
373    {
374        Box::pin(try_stream! {
375            let mut s = executor.fetch_many(self.inner);
376
377            while let Some(v) = s.try_next().await? {
378                r#yield!(match v {
379                    Either::Left(v) => Either::Left(v),
380                    Either::Right(row) => {
381                        Either::Right((self.mapper)(row)?)
382                    }
383                });
384            }
385
386            Ok(())
387        })
388    }
389
390    /// Execute the query and return all the resulting rows collected into a [`Vec`].
391    ///
392    /// ### Note: beware result set size.
393    /// This will attempt to collect the full result set of the query into memory.
394    ///
395    /// To avoid exhausting available memory, ensure the result set has a known upper bound,
396    /// e.g. using `LIMIT`.
397    pub async fn fetch_all<'e, 'c: 'e, E>(self, executor: E) -> Result<Vec<O>, Error>
398    where
399        'q: 'e,
400        E: 'e + Executor<'c, Database = DB>,
401        DB: 'e,
402        F: 'e,
403        O: 'e,
404    {
405        self.fetch(executor).try_collect().await
406    }
407
408    /// Execute the query, returning the first row or [`Error::RowNotFound`] otherwise.
409    ///
410    /// ### Note: for best performance, ensure the query returns at most one row.
411    /// Depending on the driver implementation, if your query can return more than one row,
412    /// it may lead to wasted CPU time and bandwidth on the database server.
413    ///
414    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
415    /// can result in a more optimal query plan.
416    ///
417    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
418    ///
419    /// Otherwise, you might want to add `LIMIT 1` to your query.
420    pub async fn fetch_one<'e, 'c: 'e, E>(self, executor: E) -> Result<O, Error>
421    where
422        'q: 'e,
423        E: 'e + Executor<'c, Database = DB>,
424        DB: 'e,
425        F: 'e,
426        O: 'e,
427    {
428        self.fetch_optional(executor)
429            .and_then(|row| match row {
430                Some(row) => future::ok(row),
431                None => future::err(Error::RowNotFound),
432            })
433            .await
434    }
435
436    /// Execute the query, returning the first row or `None` otherwise.
437    ///
438    /// ### Note: for best performance, ensure the query returns at most one row.
439    /// Depending on the driver implementation, if your query can return more than one row,
440    /// it may lead to wasted CPU time and bandwidth on the database server.
441    ///
442    /// Even when the driver implementation takes this into account, ensuring the query returns at most one row
443    /// can result in a more optimal query plan.
444    ///
445    /// If your query has a `WHERE` clause filtering a unique column by a single value, you're good.
446    ///
447    /// Otherwise, you might want to add `LIMIT 1` to your query.
448    pub async fn fetch_optional<'e, 'c: 'e, E>(mut self, executor: E) -> Result<Option<O>, Error>
449    where
450        'q: 'e,
451        E: 'e + Executor<'c, Database = DB>,
452        DB: 'e,
453        F: 'e,
454        O: 'e,
455    {
456        let row = executor.fetch_optional(self.inner).await?;
457
458        if let Some(row) = row {
459            (self.mapper)(row).map(Some)
460        } else {
461            Ok(None)
462        }
463    }
464}
465
466/// Execute a single SQL query as a prepared statement (explicitly created).
467pub fn query_statement<'q, DB>(
468    statement: &'q <DB as HasStatement<'q>>::Statement,
469) -> Query<'q, DB, <DB as HasArguments<'_>>::Arguments>
470where
471    DB: Database,
472{
473    Query {
474        database: PhantomData,
475        arguments: Some(Default::default()),
476        statement: Either::Right(statement),
477        persistent: true,
478    }
479}
480
481/// Execute a single SQL query as a prepared statement (explicitly created), with the given arguments.
482pub fn query_statement_with<'q, DB, A>(
483    statement: &'q <DB as HasStatement<'q>>::Statement,
484    arguments: A,
485) -> Query<'q, DB, A>
486where
487    DB: Database,
488    A: IntoArguments<'q, DB>,
489{
490    Query {
491        database: PhantomData,
492        arguments: Some(arguments),
493        statement: Either::Right(statement),
494        persistent: true,
495    }
496}
497
498/// Execute a single SQL query as a prepared statement (transparently cached).
499///
500/// The query string may only contain a single DML statement: `SELECT`, `INSERT`, `UPDATE`, `DELETE` and variants.
501/// The SQLite driver does not currently follow this restriction, but that behavior is deprecated.
502///
503/// The connection will transparently prepare and cache the statement, which means it only needs to be parsed once
504/// in the connection's lifetime, and any generated query plans can be retained.
505/// Thus, the overhead of executing the statement is amortized.
506///
507/// Some third-party databases that speak a supported protocol, e.g. CockroachDB or PGBouncer that speak Postgres,
508/// may have issues with the transparent caching of prepared statements. If you are having trouble,
509/// try setting [`.persistent(false)`][Query::persistent].
510///
511/// See the [`Query`] type for the methods you may call.
512///
513/// ### Dynamic Input: Use Query Parameters (Prevents SQL Injection)
514/// At some point, you'll likely want to include some form of dynamic input in your query, possibly from the user.
515///
516/// Your first instinct might be to do something like this:
517/// ```rust,no_run
518/// # async fn example() -> sqlx::Result<()> {
519/// # let mut conn: sqlx::PgConnection = unimplemented!();
520/// // Imagine this is input from the user, e.g. a search form on a website.
521/// let user_input = "possibly untrustworthy input!";
522///
523/// // DO NOT DO THIS unless you're ABSOLUTELY CERTAIN it's what you need!
524/// let query = format!("SELECT * FROM articles WHERE content LIKE '%{user_input}%'");
525/// // where `conn` is `PgConnection` or `MySqlConnection`
526/// // or some other type that implements `Executor`.
527/// let results = sqlx::query(&query).fetch_all(&mut conn).await?;
528/// # }
529/// ```
530///
531/// The example above showcases a **SQL injection vulnerability**, because it's trivial for a malicious user to craft
532/// an input that can "break out" of the string literal.
533///
534/// For example, if they send the input `foo'; DELETE FROM articles; --`
535/// then your application would send the following to the database server (line breaks added for clarity):
536///
537/// ```sql
538/// SELECT * FROM articles WHERE content LIKE '%foo';
539/// DELETE FROM articles;
540/// --%'
541/// ```
542///
543/// In this case, because this interface *always* uses prepared statements, you would likely be fine because prepared
544/// statements _generally_ (see above) are only allowed to contain a single query. This would simply return an error.
545///
546/// However, it would also break on legitimate user input.
547/// What if someone wanted to search for the string `Alice's Apples`? It would also return an error because
548/// the database would receive a query with a broken string literal (line breaks added for clarity):
549///
550/// ```sql
551/// SELECT * FROM articles WHERE content LIKE '%Alice'
552/// s Apples%'
553/// ```
554///
555/// Of course, it's possible to make this syntactically valid by escaping the apostrophe, but there's a better way.
556///
557/// ##### You should always prefer query parameters for dynamic input.
558///
559/// When using query parameters, you add placeholders to your query where a value
560/// should be substituted at execution time, then call [`.bind()`][Query::bind] with that value.
561///
562/// The syntax for placeholders is unfortunately not standardized and depends on the database:
563///
564/// * Postgres and SQLite: use `$1`, `$2`, `$3`, etc.
565///     * The number is the Nth bound value, starting from one.
566///     * The same placeholder can be used arbitrarily many times to refer to the same bound value.
567///     * SQLite technically supports MySQL's syntax as well as others, but we recommend using this syntax
568///       as SQLx's SQLite driver is written with it in mind.
569/// * MySQL and MariaDB: use `?`.
570///     * Placeholders are purely positional, similar to `println!("{}, {}", foo, bar)`.
571///     * The order of bindings must match the order of placeholders in the query.
572///     * To use a value in multiple places, you must bind it multiple times.
573///
574/// In both cases, the placeholder syntax acts as a variable expression representing the bound value:
575///
576/// ```rust,no_run
577/// # async fn example2() -> sqlx::Result<()> {
578/// # let mut conn: sqlx::PgConnection = unimplemented!();
579/// let user_input = "Alice's Apples";
580///
581/// // Postgres and SQLite
582/// let results = sqlx::query(
583///     // Notice how we only have to bind the argument once and we can use it multiple times:
584///     "SELECT * FROM articles
585///      WHERE title LIKE '%' || $1 || '%'
586///      OR content LIKE '%' || $1 || '%'"
587/// )
588///     .bind(user_input)
589///     .fetch_all(&mut conn)
590///     .await?;
591///
592/// // MySQL and MariaDB
593/// let results = sqlx::query(
594///     "SELECT * FROM articles
595///      WHERE title LIKE CONCAT('%', ?, '%')
596///      OR content LIKE CONCAT('%', ?, '%')"
597/// )
598///     // If we want to reference the same value multiple times, we have to bind it multiple times:
599///     .bind(user_input)
600///     .bind(user_input)
601///     .fetch_all(&mut conn)
602///     .await?;
603/// # Ok(())
604/// # }
605/// ```
606/// ##### The value bound to a query parameter is entirely separate from the query and does not affect its syntax.
607/// Thus, SQL injection is impossible (barring shenanigans like calling a SQL function that lets you execute a string
608/// as a statement) and *all* strings are valid.
609///
610/// This also means you cannot use query parameters to add conditional SQL fragments.
611///
612/// **SQLx does not substitute placeholders on the client side**. It is done by the database server itself.
613///
614/// ##### SQLx supports many different types for parameter binding, not just strings.
615/// Any type that implements [`Encode<DB>`][Encode] and [`Type<DB>`] can be bound as a parameter.
616///
617/// See [the `types` module][crate::types] (links to `sqlx_core::types` but you should use `sqlx::types`) for details.
618///
619/// As an additional benefit, query parameters are usually sent in a compact binary encoding instead of a human-readable
620/// text encoding, which saves bandwidth.
621pub fn query<DB>(sql: &str) -> Query<'_, DB, <DB as HasArguments<'_>>::Arguments>
622where
623    DB: Database,
624{
625    Query {
626        database: PhantomData,
627        arguments: Some(Default::default()),
628        statement: Either::Left(sql),
629        persistent: true,
630    }
631}
632
633/// Execute a SQL query as a prepared statement (transparently cached), with the given arguments.
634///
635/// See [`query()`][query] for details, such as supported syntax.
636pub fn query_with<'q, DB, A>(sql: &'q str, arguments: A) -> Query<'q, DB, A>
637where
638    DB: Database,
639    A: IntoArguments<'q, DB>,
640{
641    Query {
642        database: PhantomData,
643        arguments: Some(arguments),
644        statement: Either::Left(sql),
645        persistent: true,
646    }
647}
sqlx_build_trust_core/query.rs

sqlx_build_trust_core/
query.rs
