Skip to main content

mariadb_cli/exec/
routine.rs

1//! Calling a stored routine.
2//!
3//! # The name is quoted and the values are bound
4//!
5//! A routine name is not a value and cannot travel as a bound parameter, so it
6//! is pasted into the call. It goes through [`crate::sql::ident::quote`], the
7//! one sanctioned way to do that, which refuses a name it cannot quote instead
8//! of producing text that would change what the call means. Every argument
9//! travels bound, so an argument can never become part of the call.
10//!
11//! # Why every result set is kept apart
12//!
13//! A routine may answer with several result sets, and folding them into one
14//! loses which row came from which. Each set is therefore returned with its own
15//! position and its own columns.
16//!
17//! On the prepared protocol this server also returns the final values of the
18//! routine's output parameters, and it returns them as a result set of their
19//! own after the last one the routine produced. They are labelled as such, so a
20//! routine that answers only through its parameters still answers.
21
22use super::prepare::Gated;
23use super::read;
24use crate::conn::OrFailure;
25use crate::error::{Failure, Result};
26use crate::output::Row;
27use crate::sql::ident;
28use mysql_async::prelude::Queryable;
29use mysql_async::prelude::*;
30use mysql_async::Conn;
31use serde_json::{json, Value};
32
33/// One result set a routine produced.
34#[derive(Debug, Clone, PartialEq, Eq)]
35pub struct ResultSet {
36    /// Its position among the sets, counted from one.
37    pub index: usize,
38    /// The columns the server declared for it, in order.
39    pub columns: Vec<String>,
40    /// Its rows, in the order the server sent them.
41    pub rows: Vec<Row>,
42}
43
44impl ResultSet {
45    /// The answer this set carries.
46    #[must_use]
47    pub fn as_value(&self) -> Value {
48        json!({
49            "set": self.index,
50            "columns": self.columns,
51            "rows": self.rows,
52        })
53    }
54}
55
56/// Builds the call to one routine.
57///
58/// # Errors
59///
60/// Returns a validation failure when the routine name, or the database holding
61/// it, cannot be quoted safely.
62pub fn build(routine: &str, database: Option<&str>, arguments: usize) -> Result<String> {
63    let name = match database {
64        Some(database) => ident::quote_qualified(&[database, routine])?,
65        None => ident::quote(routine)?,
66    };
67    let markers = vec!["?"; arguments].join(", ");
68    Ok(format!("CALL {name}({markers})"))
69}
70
71/// Runs a call and keeps every result set it produced apart from the others.
72///
73/// # Errors
74///
75/// Returns the mapped driver failure when the routine does not exist, when the
76/// number of arguments does not match its signature, or when the channel is
77/// lost.
78pub async fn execute(
79    conn: &mut Conn,
80    gated: &Gated,
81    conversion: &crate::types::Conversion,
82) -> Result<Vec<ResultSet>> {
83    let mut result = conn
84        .exec_iter(gated.sql(), gated.params())
85        .await
86        .or_failure()?;
87
88    let mut sets = Vec::new();
89    while !result.is_empty() {
90        let columns = result.columns_ref().to_vec();
91        // One list for the whole set, used both by the rows and by the header
92        // reported beside them, so the two can never disagree.
93        let labels = read::column_names(&columns);
94        let mut rows = Vec::new();
95        while let Some(row) = result.next().await.or_failure()? {
96            rows.push(read::convert_row(row, &columns, &labels, conversion)?);
97        }
98        // A set with no columns at all is the server acknowledging the call and
99        // carries nothing for the caller, so it is not reported as an answer.
100        if !columns.is_empty() {
101            sets.push(ResultSet {
102                index: sets.len() + 1,
103                columns: labels,
104                rows,
105            });
106        }
107    }
108    Ok(sets)
109}
110
111/// Refuses a text asked to be a routine name that is not one.
112///
113/// A caller who writes a whole statement where a name belongs would otherwise
114/// have it quoted as a single strange name and rejected by the server with a
115/// message about an object that does not exist, which hides the real mistake.
116///
117/// # Errors
118///
119/// Returns a usage failure naming what was expected.
120pub fn refuse_statement_as_name(routine: &str) -> Result<()> {
121    if !routine.contains(char::is_whitespace) && !routine.contains('(') {
122        return Ok(());
123    }
124    Err(Failure::usage(
125        "usage.not_a_routine_name",
126        format!("`{routine}` is a statement, and this command takes the name of a routine"),
127    )
128    .with_detail("detail", routine)
129    .with_hint("give the routine name alone, and its arguments with --param"))
130}
131
132/// The routine's own parameters, as the catalogue declares them.
133const SIGNATURE: &str = "SELECT ORDINAL_POSITION, PARAMETER_NAME, PARAMETER_MODE \
134     FROM information_schema.PARAMETERS \
135     WHERE SPECIFIC_SCHEMA = COALESCE(:database, DATABASE()) \
136       AND SPECIFIC_NAME = :routine AND ORDINAL_POSITION > 0 \
137     ORDER BY ORDINAL_POSITION";
138
139/// One declared parameter, with where it sits and which way it carries.
140#[derive(Debug, Clone)]
141pub struct Parameter {
142    /// Its place in the call, counted from one.
143    pub position: usize,
144    /// The name the routine declares it under.
145    pub name: String,
146    /// Whether it carries in, out, or both.
147    pub mode: String,
148}
149
150impl Parameter {
151    /// Whether the routine writes a value back through this parameter.
152    #[must_use]
153    pub fn carries_out(&self) -> bool {
154        let mode = self.mode.to_uppercase();
155        mode == "OUT" || mode == "INOUT"
156    }
157}
158
159/// Reads the parameters a routine declares.
160///
161/// Position zero is skipped: a function keeps its return value there, and a
162/// return value is not a parameter anybody can name.
163///
164/// # Errors
165///
166/// Returns the mapped driver failure when the catalogue cannot be read.
167pub async fn signature(
168    conn: &mut Conn,
169    database: Option<&str>,
170    routine: &str,
171) -> Result<Vec<Parameter>> {
172    let rows: Vec<(u32, Option<String>, Option<String>)> = conn
173        .exec(
174            SIGNATURE,
175            mysql_async::params! {
176                "database" => database,
177                "routine" => routine,
178            },
179        )
180        .await
181        .or_failure()?;
182
183    Ok(rows
184        .into_iter()
185        .map(|(position, name, mode)| Parameter {
186            position: position as usize,
187            name: name.unwrap_or_default(),
188            mode: mode.unwrap_or_default(),
189        })
190        .collect())
191}
192
193/// The call to build once the caller has named some parameters as output.
194#[derive(Debug, Clone)]
195pub struct Planned {
196    /// The statement, with a session variable where each output sits.
197    pub sql: String,
198    /// The values still bound, in the order their markers appear.
199    pub bound: Vec<String>,
200    /// Each output, as the variable holding it and the name it answers under.
201    pub outputs: Vec<(String, String)>,
202}
203
204/// Places a session variable at each named output and a marker at the rest.
205///
206/// # Why the caller's values fill only the other positions
207///
208/// An output parameter carries nothing in, so a value written for it would have
209/// nowhere to go. The values therefore fill the positions that are not outputs,
210/// in the order they were written, and the manual says so.
211///
212/// # Why the variable is numbered and not named
213///
214/// The name comes from the caller and a name pasted into a statement is the one
215/// thing this module refuses to do. The position is the product's own number,
216/// so the variable cannot be steered from outside.
217///
218/// # Errors
219///
220/// Returns a validation failure naming a parameter the routine does not declare
221/// as an output, raised before anything is sent.
222pub fn plan(
223    routine: &str,
224    database: Option<&str>,
225    declared: &[Parameter],
226    asked: &[String],
227    values: &[String],
228) -> Result<Planned> {
229    let mut wanted = Vec::new();
230    for name in asked {
231        let found = declared
232            .iter()
233            .find(|parameter| parameter.name.eq_ignore_ascii_case(name) && parameter.carries_out());
234        let Some(parameter) = found else {
235            return Err(Failure::validation(
236                "sql.unknown_out_parameter",
237                format!("`{name}` is not an output parameter of this routine"),
238            )
239            .with_detail("detail", name.clone())
240            .with_hint(
241                "read the signature with `schema routines` and mark a parameter                  the routine declares as OUT or INOUT",
242            ));
243        };
244        wanted.push(parameter.clone());
245    }
246
247    let name = match database {
248        Some(database) => ident::quote_qualified(&[database, routine])?,
249        None => ident::quote(routine)?,
250    };
251    let mut pieces = Vec::new();
252    let mut outputs = Vec::new();
253    for parameter in declared {
254        match wanted.iter().find(|out| out.position == parameter.position) {
255            Some(out) => {
256                let variable = format!("@mariadb_cli_out_{}", out.position);
257                pieces.push(variable.clone());
258                outputs.push((variable, out.name.clone()));
259            }
260            None => pieces.push("?".to_owned()),
261        }
262    }
263
264    Ok(Planned {
265        sql: format!("CALL {name}({})", pieces.join(", ")),
266        bound: values.to_vec(),
267        outputs,
268    })
269}
270
271/// Reads the values the routine wrote into the session variables.
272///
273/// Read in the same invocation because the variables live in the session, and
274/// this product's session ends with the invocation.
275///
276/// # Errors
277///
278/// Returns the mapped driver failure when the reading cannot be made.
279async fn collected(
280    conn: &mut Conn,
281    outputs: &[(String, String)],
282    conversion: &crate::types::Conversion,
283) -> Result<Row> {
284    if outputs.is_empty() {
285        return Ok(Row::new());
286    }
287    let projection = outputs
288        .iter()
289        .map(|(variable, name)| ident::quote(name).map(|alias| format!("{variable} AS {alias}")))
290        .collect::<Result<Vec<_>>>()?
291        .join(", ");
292
293    let mut result = conn
294        .exec_iter(format!("SELECT {projection}"), mysql_async::Params::Empty)
295        .await
296        .or_failure()?;
297    let columns = result.columns_ref().to_vec();
298    let Some(row) = result.next().await.or_failure()? else {
299        return Ok(Row::new());
300    };
301    let labels = read::column_names(&columns);
302    read::convert_row(row, &columns, &labels, conversion)
303}
304
305/// Which routine is being called, and with what.
306///
307/// The three parts of the call itself travel together rather than as three
308/// parameters beside the four that describe the invocation it runs under. They
309/// are one thing — the call — and separating them made the signature read as a
310/// list rather than as two ideas.
311#[derive(Debug, Clone, Copy)]
312pub struct Call<'a> {
313    /// The routine's own name, never a statement.
314    pub routine: &'a str,
315    /// The database holding it, when the session is not already on it.
316    pub database: Option<&'a str>,
317    /// The values bound to its parameters, in order.
318    pub values: &'a [String],
319    /// The parameters the caller wants the final value of.
320    pub outputs: &'a [String],
321}
322
323/// Calls one routine, through the guards a call has to pass.
324///
325/// A routine may change data, and nothing outside it can say whether it does,
326/// so a call is admitted as a change rather than assumed harmless.
327///
328/// # Errors
329///
330/// Returns a usage failure when a statement was given where a name belongs, the
331/// refusal a change earns when it is not admitted, and the mapped driver
332/// failure when the routine does not exist or its signature does not match.
333pub async fn serve(
334    call: Call<'_>,
335    conn: &mut Conn,
336    policy: &super::Policy,
337    write_state: &mut super::WriteState,
338    server_read_only: bool,
339    conversion: &crate::types::Conversion,
340) -> Result<Value> {
341    refuse_statement_as_name(call.routine)?;
342    policy.admit_change("query call")?;
343    // A routine cannot be called halfway, so there is nothing to preview.
344    policy.admit_simulation("query call")?;
345    write_state.admit(server_read_only)?;
346
347    if call.outputs.is_empty() {
348        let request = build(call.routine, call.database, call.values.len())?;
349        let gated = super::prepare::gate(&request, call.values, policy)?;
350        let sets = execute(conn, &gated, conversion).await?;
351        return Ok(as_value(call.routine, call.database, &sets, &Row::new()));
352    }
353
354    // The signature is read, and the naming refused, before anything is sent,
355    // because a routine that ran and then had its output rejected has already
356    // done whatever it does.
357    let declared = signature(conn, call.database, call.routine).await?;
358    let planned = plan(
359        call.routine,
360        call.database,
361        &declared,
362        call.outputs,
363        call.values,
364    )?;
365    let gated = super::prepare::gate(&planned.sql, &planned.bound, policy)?;
366    let sets = execute(conn, &gated, conversion).await?;
367    let returned = collected(conn, &planned.outputs, conversion).await?;
368    Ok(as_value(call.routine, call.database, &sets, &returned))
369}
370
371/// The answer a call carries.
372#[must_use]
373pub fn as_value(
374    routine: &str,
375    database: Option<&str>,
376    sets: &[ResultSet],
377    returned: &Row,
378) -> Value {
379    json!({
380        // The database is declared because the routine name may carry one of
381        // its own, and then it is not the database the session sits on. An
382        // answer naming only the routine would leave the caller to assume the
383        // database they named, which is the one that did not apply.
384        "database": database,
385        "routine": routine,
386        "sets": sets.iter().map(ResultSet::as_value).collect::<Vec<_>>(),
387        "set_count": sets.len(),
388        "output_parameters": returned,
389    })
390}
391
392#[cfg(test)]
393mod tests {
394    use super::*;
395
396    #[test]
397    fn a_call_binds_one_marker_for_every_argument() {
398        assert_eq!(
399            build("recalcular", None, 0).expect("builds"),
400            "CALL `recalcular`()"
401        );
402        assert_eq!(
403            build("recalcular", None, 1).expect("builds"),
404            "CALL `recalcular`(?)"
405        );
406        assert_eq!(
407            build("recalcular", None, 3).expect("builds"),
408            "CALL `recalcular`(?, ?, ?)"
409        );
410    }
411
412    #[test]
413    fn the_database_holding_the_routine_is_quoted_part_by_part() {
414        assert_eq!(
415            build("recalcular", Some("loja"), 1).expect("builds"),
416            "CALL `loja`.`recalcular`(?)"
417        );
418    }
419
420    /// A name cannot travel as a bound value, so the one thing that must hold
421    /// is that it can never close its own quoting.
422    #[test]
423    fn a_routine_name_can_never_close_its_own_quoting() {
424        let built = build("p`(); DROP TABLE users; -- ", None, 0).expect("quotes");
425        assert_eq!(built, "CALL `p``(); DROP TABLE users; -- `()");
426        assert_eq!(
427            built.matches("``").count(),
428            1,
429            "the delimiter inside the name is doubled, not closed"
430        );
431    }
432
433    #[test]
434    fn a_name_that_cannot_be_quoted_is_refused_rather_than_pasted() {
435        assert!(build("", None, 0).is_err());
436        assert!(build("has\u{0}a control", None, 0).is_err());
437    }
438
439    #[test]
440    fn a_whole_statement_where_a_name_belongs_is_refused_with_the_real_reason() {
441        let failure = refuse_statement_as_name("CALL recalcular(1)").expect_err("must refuse");
442        assert_eq!(failure.code(), "usage.not_a_routine_name");
443        assert_eq!(failure.exit_number().code(), 2);
444    }
445
446    #[test]
447    fn a_plain_routine_name_passes() {
448        assert!(refuse_statement_as_name("recalcular").is_ok());
449        assert!(refuse_statement_as_name("loja.recalcular").is_ok());
450    }
451
452    /// A routine answering with two sets must show two, or the caller cannot
453    /// tell which row came from which.
454    #[test]
455    fn two_result_sets_reach_the_caller_as_two() {
456        let sets = vec![
457            ResultSet {
458                index: 1,
459                columns: vec!["a".to_owned()],
460                rows: vec![Row::new()],
461            },
462            ResultSet {
463                index: 2,
464                columns: vec!["b".to_owned()],
465                rows: Vec::new(),
466            },
467        ];
468        let value = as_value("recalcular", Some("loja"), &sets, &Row::new());
469        assert_eq!(
470            value["database"], "loja",
471            "the answer must say which database the routine ran in"
472        );
473        assert_eq!(value["set_count"], 2);
474        assert_eq!(value["sets"][0]["set"], 1);
475        assert_eq!(value["sets"][0]["columns"][0], "a");
476        assert_eq!(value["sets"][1]["set"], 2);
477        assert_eq!(value["sets"][1]["columns"][0], "b");
478    }
479
480    #[test]
481    fn a_routine_that_answers_with_nothing_says_so_without_inventing_a_set() {
482        let value = as_value("recalcular", None, &[], &Row::new());
483        assert_eq!(value["set_count"], 0);
484        assert_eq!(value["sets"].as_array().expect("array").len(), 0);
485    }
486}