sqlx_core_oldapi/query_builder.rs
1//! Runtime query-builder API.
2
3use std::fmt::Display;
4use std::fmt::Write;
5use std::marker::PhantomData;
6
7use crate::arguments::Arguments;
8use crate::database::{Database, HasArguments};
9use crate::encode::Encode;
10use crate::from_row::FromRow;
11use crate::query::Query;
12use crate::query_as::QueryAs;
13use crate::types::Type;
14use crate::Either;
15
16/// A builder type for constructing queries at runtime.
17///
18/// See [`.push_values()`][Self::push_values] for an example of building a bulk `INSERT` statement.
19/// Note, however, that with Postgres you can get much better performance by using arrays
20/// and `UNNEST()`. [See our FAQ] for details.
21///
22/// [See our FAQ]: https://github.com/launchbadge/sqlx/blob/master/FAQ.md#how-can-i-bind-an-array-to-a-values-clause-how-can-i-do-bulk-inserts
23pub struct QueryBuilder<'args, DB>
24where
25 DB: Database,
26{
27 query: String,
28 init_len: usize,
29 arguments: Option<<DB as HasArguments<'args>>::Arguments>,
30}
31
32impl<'args, DB: Database> QueryBuilder<'args, DB>
33where
34 DB: Database,
35{
36 // `init` is provided because a query will almost always start with a constant fragment
37 // such as `INSERT INTO ...` or `SELECT ...`, etc.
38 /// Start building a query with an initial SQL fragment, which may be an empty string.
39 pub fn new(init: impl Into<String>) -> Self
40 where
41 <DB as HasArguments<'args>>::Arguments: Default,
42 {
43 let init = init.into();
44
45 QueryBuilder {
46 init_len: init.len(),
47 query: init,
48 arguments: Some(Default::default()),
49 }
50 }
51
52 #[inline]
53 fn sanity_check(&self) {
54 assert!(
55 self.arguments.is_some(),
56 "QueryBuilder must be reset before reuse after `.build()`"
57 );
58 }
59
60 /// Append a SQL fragment to the query.
61 ///
62 /// May be a string or anything that implements `Display`.
63 /// You can also use `format_args!()` here to push a formatted string without an intermediate
64 /// allocation.
65 ///
66 /// ### Warning: Beware SQL Injection Vulnerabilities and Untrusted Input!
67 /// You should *not* use this to insert input directly into the query from an untrusted user as
68 /// this can be used by an attacker to extract sensitive data or take over your database.
69 ///
70 /// Security breaches due to SQL injection can cost your organization a lot of money from
71 /// damage control and lost clients, betray the trust of your users in your system, and are just
72 /// plain embarrassing. If you are unfamiliar with the threat that SQL injection imposes, you
73 /// should take some time to learn more about it before proceeding:
74 ///
75 /// * [SQL Injection on OWASP.org](https://owasp.org/www-community/attacks/SQL_Injection)
76 /// * [SQL Injection on Wikipedia](https://en.wikipedia.org/wiki/SQL_injection)
77 /// * See "Examples" for notable instances of security breaches due to SQL injection.
78 ///
79 /// This method does *not* perform sanitization. Instead, you should use
80 /// [`.push_bind()`][Self::push_bind] which inserts a placeholder into the query and then
81 /// sends the possibly untrustworthy value separately (called a "bind argument") so that it
82 /// cannot be misinterpreted by the database server.
83 ///
84 /// Note that you should still at least have some sort of sanity checks on the values you're
85 /// sending as that's just good practice and prevent other types of attacks against your system,
86 /// e.g. check that strings aren't too long, numbers are within expected ranges, etc.
87 pub fn push(&mut self, sql: impl Display) -> &mut Self {
88 self.sanity_check();
89
90 write!(self.query, "{}", sql).expect("error formatting `sql`");
91
92 self
93 }
94
95 /// Push a bind argument placeholder (`?` or `$N` for Postgres) and bind a value to it.
96 ///
97 /// ### Note: Database-specific Limits
98 /// Note that every database has a practical limit on the number of bind parameters
99 /// you can add to a single query. This varies by database.
100 ///
101 /// While you should consult the manual of your specific database version and/or current
102 /// configuration for the exact value as it may be different than listed here,
103 /// the defaults for supported databases as of writing are as follows:
104 ///
105 /// * Postgres and MySQL: 65535
106 /// * You may find sources that state that Postgres has a limit of 32767,
107 /// but that is a misinterpretation of the specification by the JDBC driver implementation
108 /// as discussed in [this Github issue][postgres-limit-issue]. Postgres itself
109 /// asserts that the number of parameters is in the range `[0, 65535)`.
110 /// * SQLite: 32766 (configurable by [`SQLITE_LIMIT_VARIABLE_NUMBER`])
111 /// * SQLite prior to 3.32.0: 999
112 /// * MSSQL: 2100
113 ///
114 /// Exceeding these limits may panic (as a sanity check) or trigger a database error at runtime
115 /// depending on the implementation.
116 ///
117 /// [`SQLITE_LIMIT_VARIABLE_NUMBER`]: https://www.sqlite.org/limits.html#max_variable_number
118 /// [postgres-limit-issue]: https://github.com/launchbadge/sqlx/issues/671#issuecomment-687043510
119 pub fn push_bind<T>(&mut self, value: T) -> &mut Self
120 where
121 T: 'args + Encode<'args, DB> + Send + Type<DB>,
122 {
123 self.sanity_check();
124
125 let arguments = self
126 .arguments
127 .as_mut()
128 .expect("BUG: Arguments taken already");
129 arguments.add(value);
130
131 arguments
132 .format_placeholder(&mut self.query)
133 .expect("error in format_placeholder");
134
135 self
136 }
137
138 /// Start a list separated by `separator`.
139 ///
140 /// The returned type exposes identical [`.push()`][Separated::push] and
141 /// [`.push_bind()`][Separated::push_bind] methods which push `separator` to the query
142 /// before their normal behavior. [`.push_unseparated()`][Separated::push_unseparated] and [`.push_bind_unseparated()`][Separated::push_bind_unseparated] are also
143 /// provided to push a SQL fragment without the separator.
144 ///
145 /// ```rust
146 /// # #[cfg(feature = "mysql")] {
147 /// use sqlx::{Execute, MySql, QueryBuilder};
148 /// let foods = vec!["pizza".to_string(), "chips".to_string()];
149 /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
150 /// "SELECT * from food where name in ("
151 /// );
152 /// // One element vector is handled correctly but an empty vector
153 /// // would cause a sql syntax error
154 /// let mut separated = query_builder.separated(", ");
155 /// for value_type in foods.iter() {
156 /// separated.push_bind(value_type);
157 /// }
158 /// separated.push_unseparated(") ");
159 ///
160 /// let mut query = query_builder.build();
161 /// let sql = query.sql();
162 /// assert!(sql.ends_with("in (?, ?) "));
163 /// # }
164 /// ```
165 pub fn separated<'qb, Sep>(&'qb mut self, separator: Sep) -> Separated<'qb, 'args, DB, Sep>
166 where
167 'args: 'qb,
168 Sep: Display,
169 {
170 self.sanity_check();
171
172 Separated {
173 query_builder: self,
174 separator,
175 push_separator: false,
176 }
177 }
178
179 // Most of the `QueryBuilder` API is purposefully very low-level but this was a commonly
180 // requested use-case so it made sense to support.
181 /// Push a `VALUES` clause where each item in `tuples` represents a tuple/row in the clause.
182 ///
183 /// This can be used to construct a bulk `INSERT` statement, although keep in mind that all
184 /// databases have some practical limit on the number of bind arguments in a single query.
185 /// See [`.push_bind()`][Self::push_bind] for details.
186 ///
187 /// To be safe, you can do `tuples.into_iter().take(N)` where `N` is the limit for your database
188 /// divided by the number of fields in each tuple; since integer division always rounds down,
189 /// this will ensure that you don't exceed the limit.
190 ///
191 /// ### Notes
192 ///
193 /// If `tuples` is empty, this will likely produce a syntactically invalid query as `VALUES`
194 /// generally expects to be followed by at least 1 tuple.
195 ///
196 /// If `tuples` can have many different lengths, you may want to call
197 /// [`.persistent(false)`][Query::persistent] after [`.build()`][Self::build] to avoid
198 /// filling up the connection's prepared statement cache.
199 ///
200 /// Because the `Arguments` API has a lifetime that must live longer than `Self`, you cannot
201 /// bind by-reference from an iterator unless that iterator yields references that live
202 /// longer than `Self`, even if the specific `Arguments` implementation doesn't actually
203 /// borrow the values (like `MySqlArguments` and `PgArguments` immediately encode the arguments
204 /// and don't borrow them past the `.add()` call).
205 ///
206 /// So basically, if you want to bind by-reference you need an iterator that yields references,
207 /// e.g. if you have values in a `Vec` you can do `.iter()` instead of `.into_iter()`. The
208 /// example below uses an iterator that creates values on the fly
209 /// and so cannot bind by-reference.
210 ///
211 /// ### Example (MySQL)
212 ///
213 /// ```rust
214 /// # #[cfg(feature = "mysql")]
215 /// # {
216 /// use sqlx::{Execute, MySql, QueryBuilder};
217 ///
218 /// struct User {
219 /// id: i32,
220 /// username: String,
221 /// email: String,
222 /// password: String,
223 /// }
224 ///
225 /// // The number of parameters in MySQL must fit in a `u16`.
226 /// const BIND_LIMIT: usize = 65535;
227 ///
228 /// // This would normally produce values forever!
229 /// let users = (0..).map(|i| User {
230 /// id: i,
231 /// username: format!("test_user_{}", i),
232 /// email: format!("test-user-{}@example.com", i),
233 /// password: format!("Test!User@Password#{}", i),
234 /// });
235 ///
236 /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
237 /// // Note the trailing space; most calls to `QueryBuilder` don't automatically insert
238 /// // spaces as that might interfere with identifiers or quoted strings where exact
239 /// // values may matter.
240 /// "INSERT INTO users(id, username, email, password) "
241 /// );
242 ///
243 /// // Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
244 /// query_builder.push_values(users.take(BIND_LIMIT / 4), |mut b, user| {
245 /// // If you wanted to bind these by-reference instead of by-value,
246 /// // you'd need an iterator that yields references that live as long as `query_builder`,
247 /// // e.g. collect it to a `Vec` first.
248 /// b.push_bind(user.id)
249 /// .push_bind(user.username)
250 /// .push_bind(user.email)
251 /// .push_bind(user.password);
252 /// });
253 ///
254 /// let mut query = query_builder.build();
255 ///
256 /// // You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
257 /// // For the sake of demonstration though, we're just going to assert the contents
258 /// // of the query.
259 ///
260 /// // These are methods of the `Execute` trait, not normally meant to be called in user code.
261 /// let sql = query.sql();
262 /// let arguments = query.take_arguments().unwrap();
263 ///
264 /// assert!(sql.starts_with(
265 /// "INSERT INTO users(id, username, email, password) VALUES (?, ?, ?, ?), (?, ?, ?, ?)"
266 /// ));
267 ///
268 /// assert!(sql.ends_with("(?, ?, ?, ?)"));
269 ///
270 /// // Not a normally exposed function, only used for this doctest.
271 /// // 65535 / 4 = 16383 (rounded down)
272 /// // 16383 * 4 = 65532
273 /// assert_eq!(arguments.len(), 65532);
274 /// # }
275 /// ```
276 pub fn push_values<I, F>(&mut self, tuples: I, mut push_tuple: F) -> &mut Self
277 where
278 I: IntoIterator,
279 F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),
280 {
281 self.sanity_check();
282
283 self.push("VALUES ");
284
285 let mut separated = self.separated(", ");
286
287 for tuple in tuples {
288 separated.push("(");
289
290 // use a `Separated` with a separate (hah) internal state
291 push_tuple(separated.query_builder.separated(", "), tuple);
292
293 separated.push_unseparated(")");
294 }
295
296 separated.query_builder
297 }
298
299 /// Creates `((a, b), (..)` statements, from `tuples`.
300 ///
301 /// This can be used to construct a bulk `SELECT` statement like this:
302 /// ```sql
303 /// SELECT * FROM users WHERE (id, username) IN ((1, "test_user_1"), (2, "test_user_2"))
304 /// ```
305 ///
306 /// Although keep in mind that all
307 /// databases have some practical limit on the number of bind arguments in a single query.
308 /// See [`.push_bind()`][Self::push_bind] for details.
309 ///
310 /// To be safe, you can do `tuples.into_iter().take(N)` where `N` is the limit for your database
311 /// divided by the number of fields in each tuple; since integer division always rounds down,
312 /// this will ensure that you don't exceed the limit.
313 ///
314 /// ### Notes
315 ///
316 /// If `tuples` is empty, this will likely produce a syntactically invalid query
317 ///
318 /// ### Example (MySQL)
319 ///
320 /// ```rust
321 /// # #[cfg(feature = "mysql")]
322 /// # {
323 /// use sqlx::{Execute, MySql, QueryBuilder};
324 ///
325 /// struct User {
326 /// id: i32,
327 /// username: String,
328 /// email: String,
329 /// password: String,
330 /// }
331 ///
332 /// // The number of parameters in MySQL must fit in a `u16`.
333 /// const BIND_LIMIT: usize = 65535;
334 ///
335 /// // This would normally produce values forever!
336 /// let users = (0..).map(|i| User {
337 /// id: i,
338 /// username: format!("test_user_{}", i),
339 /// email: format!("test-user-{}@example.com", i),
340 /// password: format!("Test!User@Password#{}", i),
341 /// });
342 ///
343 /// let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
344 /// // Note the trailing space; most calls to `QueryBuilder` don't automatically insert
345 /// // spaces as that might interfere with identifiers or quoted strings where exact
346 /// // values may matter.
347 /// "SELECT * FROM users WHERE (id, username, email, password) in"
348 /// );
349 ///
350 /// // Note that `.into_iter()` wasn't needed here since `users` is already an iterator.
351 /// query_builder.push_tuples(users.take(BIND_LIMIT / 4), |mut b, user| {
352 /// // If you wanted to bind these by-reference instead of by-value,
353 /// // you'd need an iterator that yields references that live as long as `query_builder`,
354 /// // e.g. collect it to a `Vec` first.
355 /// b.push_bind(user.id)
356 /// .push_bind(user.username)
357 /// .push_bind(user.email)
358 /// .push_bind(user.password);
359 /// });
360 ///
361 /// let mut query = query_builder.build();
362 ///
363 /// // You can then call `query.execute()`, `.fetch_one()`, `.fetch_all()`, etc.
364 /// // For the sake of demonstration though, we're just going to assert the contents
365 /// // of the query.
366 ///
367 /// // These are methods of the `Execute` trait, not normally meant to be called in user code.
368 /// let sql = query.sql();
369 /// let arguments = query.take_arguments().unwrap();
370 ///
371 /// assert!(sql.starts_with(
372 /// "SELECT * FROM users WHERE (id, username, email, password) in ((?, ?, ?, ?), (?, ?, ?, ?), "
373 /// ));
374 ///
375 /// assert!(sql.ends_with("(?, ?, ?, ?)) "));
376 ///
377 /// // Not a normally exposed function, only used for this doctest.
378 /// // 65535 / 4 = 16383 (rounded down)
379 /// // 16383 * 4 = 65532
380 /// assert_eq!(arguments.len(), 65532);
381 /// }
382 /// ```
383 pub fn push_tuples<I, F>(&mut self, tuples: I, mut push_tuple: F) -> &mut Self
384 where
385 I: IntoIterator,
386 F: FnMut(Separated<'_, 'args, DB, &'static str>, I::Item),
387 {
388 self.sanity_check();
389
390 self.push(" (");
391
392 let mut separated = self.separated(", ");
393
394 for tuple in tuples {
395 separated.push("(");
396
397 push_tuple(separated.query_builder.separated(", "), tuple);
398
399 separated.push_unseparated(")");
400 }
401 separated.push_unseparated(") ");
402
403 separated.query_builder
404 }
405
406 /// Produce an executable query from this builder.
407 ///
408 /// ### Note: Query is not Checked
409 /// It is your responsibility to ensure that you produce a syntactically correct query here,
410 /// this API has no way to check it for you.
411 ///
412 /// ### Note: Reuse
413 /// You can reuse this builder afterwards to amortize the allocation overhead of the query
414 /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
415 /// to the state it was in immediately after [`new()`][Self::new].
416 ///
417 /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
418 pub fn build(&mut self) -> Query<'_, DB, <DB as HasArguments<'args>>::Arguments> {
419 self.sanity_check();
420
421 Query {
422 statement: Either::Left(&self.query),
423 arguments: self.arguments.take(),
424 database: PhantomData,
425 persistent: true,
426 }
427 }
428
429 /// Produce an executable query from this builder.
430 ///
431 /// ### Note: Query is not Checked
432 /// It is your responsibility to ensure that you produce a syntactically correct query here,
433 /// this API has no way to check it for you.
434 ///
435 /// ### Note: Reuse
436 /// You can reuse this builder afterwards to amortize the allocation overhead of the query
437 /// string, however you must call [`.reset()`][Self::reset] first, which returns `Self`
438 /// to the state it was in immediately after [`new()`][Self::new].
439 ///
440 /// Calling any other method but `.reset()` after `.build()` will panic for sanity reasons.
441 pub fn build_query_as<'q, T: FromRow<'q, DB::Row>>(
442 &'q mut self,
443 ) -> QueryAs<'q, DB, T, <DB as HasArguments<'args>>::Arguments> {
444 QueryAs {
445 inner: self.build(),
446 output: PhantomData,
447 }
448 }
449
450 /// Reset this `QueryBuilder` back to its initial state.
451 ///
452 /// The query is truncated to the initial fragment provided to [`new()`][Self::new] and
453 /// the bind arguments are reset.
454 pub fn reset(&mut self) -> &mut Self {
455 self.query.truncate(self.init_len);
456 self.arguments = Some(Default::default());
457
458 self
459 }
460
461 /// Get the current build SQL; **note**: may not be syntactically correct.
462 pub fn sql(&self) -> &str {
463 &self.query
464 }
465
466 /// Deconstruct this `QueryBuilder`, returning the built SQL. May not be syntactically correct.
467 pub fn into_sql(self) -> String {
468 self.query
469 }
470}
471
472/// A wrapper around `QueryBuilder` for creating comma(or other token)-separated lists.
473///
474/// See [`QueryBuilder::separated()`] for details.
475#[allow(explicit_outlives_requirements)]
476pub struct Separated<'qb, 'args: 'qb, DB, Sep>
477where
478 DB: Database,
479{
480 query_builder: &'qb mut QueryBuilder<'args, DB>,
481 separator: Sep,
482 push_separator: bool,
483}
484
485impl<'qb, 'args: 'qb, DB, Sep> Separated<'qb, 'args, DB, Sep>
486where
487 DB: Database,
488 Sep: Display,
489{
490 /// Push the separator if applicable, and then the given SQL fragment.
491 ///
492 /// See [`QueryBuilder::push()`] for details.
493 pub fn push(&mut self, sql: impl Display) -> &mut Self {
494 if self.push_separator {
495 self.query_builder
496 .push(format_args!("{}{}", self.separator, sql));
497 } else {
498 self.query_builder.push(sql);
499 self.push_separator = true;
500 }
501
502 self
503 }
504
505 /// Push a SQL fragment without a separator.
506 ///
507 /// Simply calls [`QueryBuilder::push()`] directly.
508 pub fn push_unseparated(&mut self, sql: impl Display) -> &mut Self {
509 self.query_builder.push(sql);
510 self
511 }
512
513 /// Push the separator if applicable, then append a bind argument.
514 ///
515 /// See [`QueryBuilder::push_bind()`] for details.
516 pub fn push_bind<T>(&mut self, value: T) -> &mut Self
517 where
518 T: 'args + Encode<'args, DB> + Send + Type<DB>,
519 {
520 if self.push_separator {
521 self.query_builder.push(&self.separator);
522 }
523
524 self.query_builder.push_bind(value);
525 self.push_separator = true;
526
527 self
528 }
529
530 /// Push a bind argument placeholder (`?` or `$N` for Postgres) and bind a value to it
531 /// without a separator.
532 ///
533 /// Simply calls [`QueryBuilder::push_bind()`] directly.
534 pub fn push_bind_unseparated<T>(&mut self, value: T) -> &mut Self
535 where
536 T: 'args + Encode<'args, DB> + Send + Type<DB>,
537 {
538 self.query_builder.push_bind(value);
539 self
540 }
541}
542
543#[cfg(test)]
544mod test {
545 use crate::postgres::Postgres;
546
547 use super::*;
548
549 #[test]
550 fn test_new() {
551 let qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("SELECT * FROM users");
552 assert_eq!(qb.query, "SELECT * FROM users");
553 }
554
555 #[test]
556 fn test_push() {
557 let mut qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("SELECT * FROM users");
558 let second_line = " WHERE last_name LIKE '[A-N]%;";
559 qb.push(second_line);
560
561 assert_eq!(
562 qb.query,
563 "SELECT * FROM users WHERE last_name LIKE '[A-N]%;".to_string(),
564 );
565 }
566
567 #[test]
568 #[should_panic]
569 fn test_push_panics_when_no_arguments() {
570 let mut qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("SELECT * FROM users;");
571 qb.arguments = None;
572
573 qb.push("SELECT * FROM users;");
574 }
575
576 #[test]
577 fn test_push_bind() {
578 let mut qb: QueryBuilder<'_, Postgres> =
579 QueryBuilder::new("SELECT * FROM users WHERE id = ");
580
581 qb.push_bind(42i32)
582 .push(" OR membership_level = ")
583 .push_bind(3i32);
584
585 assert_eq!(
586 qb.query,
587 "SELECT * FROM users WHERE id = $1 OR membership_level = $2"
588 );
589 }
590
591 #[test]
592 fn test_build() {
593 let mut qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("SELECT * FROM users");
594
595 qb.push(" WHERE id = ").push_bind(42i32);
596 let query = qb.build();
597
598 assert_eq!(
599 query.statement.unwrap_left(),
600 "SELECT * FROM users WHERE id = $1"
601 );
602 assert!(query.persistent);
603 }
604
605 #[test]
606 fn test_reset() {
607 let mut qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("");
608
609 let _query = qb
610 .push("SELECT * FROM users WHERE id = ")
611 .push_bind(42i32)
612 .build();
613
614 qb.reset();
615
616 assert_eq!(qb.query, "");
617 }
618
619 #[test]
620 fn test_query_builder_reuse() {
621 let mut qb: QueryBuilder<'_, Postgres> = QueryBuilder::new("");
622
623 let _query = qb
624 .push("SELECT * FROM users WHERE id = ")
625 .push_bind(42i32)
626 .build();
627
628 qb.reset();
629
630 let query = qb.push("SELECT * FROM users WHERE id = 99").build();
631
632 assert_eq!(
633 query.statement.unwrap_left(),
634 "SELECT * FROM users WHERE id = 99"
635 );
636 }
637}