mariadb-cli 0.1.1

One-shot agent-native command line client for MariaDB servers
Documentation
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
//! Structural refusals applied to a statement before it is sent.
//!
//! Each refusal here is driven by the syntax tree. Where the tree cannot carry
//! the answer, the fallback reads the text produced by [`mod@crate::sql::strip`]
//! and never the raw statement, which is the defect measured in both reference
//! implementations.

use crate::error::{Failure, Result};
use crate::sql::classify::{analyze, Analysis, Classification};
use crate::sql::parse::ParseLimits;
use crate::sql::strip::{strip, Stripped};
use core::ops::ControlFlow;
use sqlparser::ast::{Expr, ObjectName, Select, Statement, Visit, Visitor};

/// Server-side functions that read a file from the server's own filesystem.
///
/// Names are compared in lower case against the last part of the call, so a
/// schema qualifier does not hide the call.
const SERVER_FILE_READERS: &[&str] = &["load_file", "loadfile"];

/// Words that name a file the server would write, when they follow `into`.
const SERVER_FILE_WRITERS: &[&str] = &["outfile", "dumpfile"];

/// The word that turns a bulk load into a transfer from the caller's own disk,
/// when it follows `local`.
///
/// No handler for that transfer is ever registered, so the driver would refuse
/// it anyway. Refusing here as well names the real reason instead of leaving
/// the caller with a generic transport complaint.
const CLIENT_FILE_READER: &str = "infile";

/// A statement that passed every structural refusal.
#[derive(Debug, Clone)]
pub struct Admission {
    analysis: Analysis,
    stripped: Stripped,
}

impl Admission {
    /// What the statement does and the tree it was read from.
    #[must_use]
    pub const fn analysis(&self) -> &Analysis {
        &self.analysis
    }

    /// What the statement does.
    #[must_use]
    pub const fn classification(&self) -> Classification {
        self.analysis.classification()
    }

    /// The text with comments, literals and quoted identifiers blanked.
    #[must_use]
    pub const fn stripped(&self) -> &Stripped {
        &self.stripped
    }
}

/// Admits one statement, or says exactly why it is refused.
///
/// The order is fixed: blank the text, refuse an unterminated region, run the
/// text safety net, read the tree, then apply the tree refusals. Nothing
/// textual runs before the blanking.
///
/// The safety net runs before the parse on purpose. This parser does not know
/// the server's clause for dumping a result into a file, so parsing such a
/// statement fails and the caller would be told only that the text is
/// unreadable. Running the net first names the real reason.
///
/// # Errors
///
/// Returns a validation failure when the statement is unterminated, unreadable,
/// unrecognised, carries more than one command, or reaches the server's
/// filesystem.
pub fn admit(sql: &str, limits: ParseLimits) -> Result<Admission> {
    let stripped = strip(sql);
    stripped.refuse_if_unterminated()?;
    refuse_file_access_by_text(&stripped)?;

    let analysis = analyze(sql, limits)?;
    refuse_multiple_statements(&stripped, analysis.statements())?;
    refuse_server_filesystem_access(&stripped, analysis.statements())?;

    Ok(Admission { analysis, stripped })
}

/// Refuses a text that carries more than one command.
///
/// Two independent checks run, because either alone has a hole. The tree count
/// is authoritative; the separator count over the blanked text catches a shape
/// the parser folded into a single statement.
///
/// The reference implementation refuses a text that contains a separator and
/// does not end in one, so `SELECT 1; SELECT 2;` passes it. Ending in a
/// separator is not what makes a text single, and the tests below say so.
///
/// # Errors
///
/// Returns a validation failure when more than one command is present.
pub fn refuse_multiple_statements(stripped: &Stripped, statements: &[Statement]) -> Result<()> {
    let commands = stripped
        .text()
        .split(';')
        .filter(|segment| !segment.trim().is_empty())
        .count();

    if statements.len() > 1 || commands > 1 {
        return Err(Failure::validation(
            "sql.multiple_statements",
            "the text carries more than one statement",
        )
        .with_hint("send one statement per call"));
    }
    Ok(())
}

/// Refuses a statement that reads or writes a file on the server.
///
/// The call is found by walking the tree, so a call nested in a sub-query, a
/// join or an argument list is found just as the top-level one is, and a name
/// that only appears inside a text literal is not found at all.
///
/// Both sides also have a text net, and for different reasons. The write clause
/// is not one this parser knows, so a statement using it fails to parse and the
/// caller would be told only that the text is unreadable. The read call is one
/// the parser knows perfectly well, and the net exists because the tree was
/// once measured to be missing it anyway: a call written inside a conditional
/// comment reached the server and never reached the tree. That is fixed at the
/// source now, in the preparation the parse runs first, and the net stays
/// because a refusal that depends on one reading being right is a refusal one
/// mistake away from silence.
///
/// # Errors
///
/// Returns a validation failure naming the call or the clause that was found.
pub fn refuse_server_filesystem_access(
    stripped: &Stripped,
    statements: &[Statement],
) -> Result<()> {
    for statement in statements {
        let mut finder = FilesystemFinder;
        if let ControlFlow::Break(finding) = statement.visit(&mut finder) {
            return Err(finding.into_failure());
        }
    }
    refuse_file_access_by_text(stripped)
}

/// The safety net for both sides, run over the blanked text.
///
/// Working on the blanked text is what keeps `SELECT 'into outfile'` and
/// `SELECT 'load_file(1)'` untouched: a literal is data and data never refuses.
fn refuse_file_access_by_text(stripped: &Stripped) -> Result<()> {
    let words = ascii_words(stripped.text());
    for pair in words.windows(2) {
        if pair[0].text == "into" && SERVER_FILE_WRITERS.contains(&pair[1].text.as_str()) {
            return Err(Finding::FileOutput(pair[1].text.clone()).into_failure());
        }
        if pair[0].text == "local" && pair[1].text == CLIENT_FILE_READER {
            return Err(Finding::ClientFileRead.into_failure());
        }
    }
    for word in &words {
        if SERVER_FILE_READERS.contains(&word.text.as_str()) && word.opens_a_call(stripped.text()) {
            return Err(Finding::FileReader(word.text.clone()).into_failure());
        }
    }
    Ok(())
}

/// One run of word characters, and where it ended.
///
/// The end is carried because the name of a file-reading call is also a legal
/// column name, and only the parenthesis that follows tells the two apart.
#[derive(Debug, Clone)]
struct Word {
    text: String,
    ends_at: usize,
}

impl Word {
    /// Whether this word is being called rather than merely named.
    ///
    /// A call is the name followed by an opening parenthesis, with whitespace
    /// allowed between the two because this server allows it. A column named
    /// after the call is followed by anything else, so `SELECT load_file FROM t`
    /// stays a legitimate query.
    fn opens_a_call(&self, text: &str) -> bool {
        text[self.ends_at..]
            .chars()
            .find(|character| !character.is_whitespace())
            == Some('(')
    }
}

/// Splits text into lower-case runs of word characters.
fn ascii_words(text: &str) -> Vec<Word> {
    let mut words = Vec::new();
    let mut current = String::new();
    for (offset, character) in text.char_indices() {
        if character.is_alphanumeric() || character == '_' {
            current.extend(character.to_lowercase());
        } else if !current.is_empty() {
            words.push(Word {
                text: std::mem::take(&mut current),
                ends_at: offset,
            });
        }
    }
    if !current.is_empty() {
        words.push(Word {
            text: current,
            ends_at: text.len(),
        });
    }
    words
}

/// What the tree walk found, if anything.
#[derive(Debug, Clone)]
enum Finding {
    /// A call that reads a file on the server.
    FileReader(String),
    /// A clause that writes a file on the server.
    FileOutput(String),
    /// A clause that makes the server ask the caller for a local file.
    ClientFileRead,
}

impl Finding {
    fn into_failure(self) -> Failure {
        match self {
            Self::FileReader(name) => Failure::validation(
                "sql.server_file_read",
                format!("the statement calls `{name}`, which reads a file on the server"),
            )
            .with_detail("detail", name)
            .with_hint("read the file where it lives instead of through the server"),
            Self::FileOutput(clause) => Failure::validation(
                "sql.server_file_write",
                format!("the statement uses `into {clause}`, which writes a file on the server"),
            )
            .with_detail("detail", format!("into {clause}"))
            .with_hint("take the result on the primary channel and write it yourself"),
            Self::ClientFileRead => Failure::validation(
                "sql.client_file_read",
                "the statement asks the server to read a file from this machine",
            )
            .with_hint("load the file with `data import`, which reads it here and never asks the server to"),
        }
    }
}

/// Walks a statement looking for anything that touches the server's filesystem.
#[derive(Debug)]
struct FilesystemFinder;

impl FilesystemFinder {
    /// Checks one called name against the file-reading calls.
    fn check_call(name: &ObjectName) -> ControlFlow<Finding> {
        let Some(last) = name
            .0
            .last()
            .and_then(sqlparser::ast::ObjectNamePart::as_ident)
        else {
            return ControlFlow::Continue(());
        };
        let lowered = last.value.to_lowercase();
        if SERVER_FILE_READERS.contains(&lowered.as_str()) {
            return ControlFlow::Break(Finding::FileReader(lowered));
        }
        ControlFlow::Continue(())
    }
}

impl Visitor for FilesystemFinder {
    type Break = Finding;

    fn pre_visit_expr(&mut self, expr: &Expr) -> ControlFlow<Self::Break> {
        match expr {
            Expr::Function(function) => Self::check_call(&function.name),
            _ => ControlFlow::Continue(()),
        }
    }

    fn pre_visit_statement(&mut self, statement: &Statement) -> ControlFlow<Self::Break> {
        match statement {
            // A procedure call carries its name outside any expression, so the
            // expression hook alone would not see it.
            Statement::Call(function) => Self::check_call(&function.name),
            Statement::Directory { .. } | Statement::Unload { .. } => {
                ControlFlow::Break(Finding::FileOutput("directory".to_owned()))
            }
            _ => ControlFlow::Continue(()),
        }
    }

    fn pre_visit_select(&mut self, select: &Select) -> ControlFlow<Self::Break> {
        let Some(into) = select.into.as_ref() else {
            return ControlFlow::Continue(());
        };
        let Some(last) = into
            .name
            .0
            .last()
            .and_then(sqlparser::ast::ObjectNamePart::as_ident)
        else {
            return ControlFlow::Continue(());
        };
        let lowered = last.value.to_lowercase();
        if SERVER_FILE_WRITERS.contains(&lowered.as_str()) {
            return ControlFlow::Break(Finding::FileOutput(lowered));
        }
        ControlFlow::Continue(())
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn refusal(sql: &str) -> Failure {
        admit(sql, ParseLimits::default()).expect_err("the statement must be refused")
    }

    fn admitted(sql: &str) -> Admission {
        admit(sql, ParseLimits::default()).expect("the statement must be admitted")
    }

    #[test]
    fn a_single_statement_is_admitted() {
        assert_eq!(admitted("SELECT 1").classification(), Classification::Read);
        assert_eq!(
            admitted("SELECT 1;").classification(),
            Classification::Read,
            "a trailing separator is not a second statement"
        );
    }

    /// The reference implementation lets this through, because it only refuses
    /// a text that contains a separator without ending in one.
    #[test]
    fn two_statements_are_refused_even_when_the_text_ends_in_a_separator() {
        for sql in [
            "SELECT 1; SELECT 2;",
            "SELECT 1; DROP TABLE users;",
            "SELECT 1;DELETE FROM users;",
        ] {
            let failure = refusal(sql);
            assert_eq!(failure.code(), "sql.multiple_statements", "input {sql:?}");
        }
    }

    #[test]
    fn two_statements_without_a_trailing_separator_are_refused_as_well() {
        assert_eq!(
            refusal("SELECT 1; SELECT 2").code(),
            "sql.multiple_statements"
        );
    }

    #[test]
    fn a_separator_inside_a_literal_is_not_a_second_statement() {
        assert_eq!(
            admitted("SELECT 'a;b' AS note").classification(),
            Classification::Read
        );
    }

    #[test]
    fn a_separator_inside_a_quoted_identifier_is_not_a_second_statement() {
        assert_eq!(
            admitted("SELECT 1 AS `a;b`").classification(),
            Classification::Read
        );
    }

    #[test]
    fn a_separator_inside_a_comment_is_not_a_second_statement() {
        assert_eq!(
            admitted("SELECT 1 -- ; SELECT 2\n").classification(),
            Classification::Read
        );
    }

    #[test]
    fn a_call_that_reads_a_server_file_is_refused() {
        assert_eq!(
            refusal("SELECT LOAD_FILE('/etc/passwd')").code(),
            "sql.server_file_read"
        );
    }

    #[test]
    fn a_call_that_reads_a_server_file_is_found_inside_a_sub_query() {
        for sql in [
            "SELECT * FROM (SELECT LOAD_FILE('/etc/passwd') AS c) AS s",
            "SELECT * FROM t WHERE a = (SELECT LOAD_FILE('/etc/passwd'))",
            "SELECT a FROM t JOIN u ON u.a = LOAD_FILE('/etc/passwd')",
            "SELECT CONCAT('x', LOAD_FILE('/etc/passwd'))",
        ] {
            assert_eq!(refusal(sql).code(), "sql.server_file_read", "input {sql:?}");
        }
    }

    #[test]
    fn a_schema_qualifier_does_not_hide_the_call() {
        assert_eq!(
            refusal("SELECT mysql.load_file('/etc/passwd')").code(),
            "sql.server_file_read"
        );
    }

    #[test]
    fn the_name_of_a_file_reading_call_inside_a_literal_does_not_refuse() {
        for sql in [
            "SELECT 'LOAD_FILE(/etc/passwd)' AS note",
            "SELECT * FROM t WHERE note = 'load_file'",
        ] {
            assert_eq!(
                admitted(sql).classification(),
                Classification::Read,
                "input {sql:?}"
            );
        }
    }

    #[test]
    fn a_column_merely_named_after_the_call_does_not_refuse() {
        assert_eq!(
            admitted("SELECT load_file FROM t").classification(),
            Classification::Read
        );
    }

    #[test]
    fn a_clause_that_writes_a_server_file_is_refused() {
        for sql in [
            "SELECT * FROM t INTO OUTFILE '/tmp/x'",
            "SELECT * FROM t INTO DUMPFILE '/tmp/x'",
            "select a from t into outfile '/tmp/x'",
            "SELECT a INTO OUTFILE '/tmp/x' FROM t",
            "SELECT a /* hide */ INTO OUTFILE '/tmp/x' FROM t",
        ] {
            assert_eq!(
                refusal(sql).code(),
                "sql.server_file_write",
                "input {sql:?}"
            );
        }
    }

    /// The safety net that catches the write clause must read the blanked text,
    /// or a legitimate query mentioning the clause is refused.
    #[test]
    fn the_write_clause_inside_a_literal_does_not_refuse() {
        for sql in [
            "SELECT 'INTO OUTFILE' AS note",
            "SELECT * FROM t WHERE note = 'into dumpfile /tmp/x'",
            "SELECT 1 -- INTO OUTFILE '/tmp/x'\n",
            "SELECT 1 /* INTO OUTFILE */",
        ] {
            assert_eq!(
                admitted(sql).classification(),
                Classification::Read,
                "input {sql:?}"
            );
        }
    }

    #[test]
    fn an_unterminated_region_is_refused_before_anything_textual_runs() {
        assert_eq!(
            refusal("SELECT 'never closed").code(),
            "sql.unterminated_region"
        );
    }

    #[test]
    fn a_conditional_comment_reaches_the_caller_as_code() {
        let admission = admitted("SELECT /*!40001 SQL_NO_CACHE */ 1");
        assert!(admission.stripped().has_conditional_comment());
        assert!(admission.stripped().text().contains("SQL_NO_CACHE"));
    }

    #[test]
    fn letter_case_does_not_change_a_refusal() {
        assert_eq!(
            refusal("select load_file('/etc/passwd')").code(),
            "sql.server_file_read"
        );
        assert_eq!(
            refusal("SELECT LoAd_FiLe('/etc/passwd')").code(),
            "sql.server_file_read"
        );
    }

    #[test]
    fn a_statement_hidden_behind_a_comment_still_reaches_its_classification() {
        assert_eq!(
            admitted("/* SELECT */ DELETE FROM users").classification(),
            Classification::Change
        );
    }

    /// The bulk load that makes the server ask this machine for a file is
    /// refused by name, so the caller is told the real reason instead of
    /// meeting a generic transport complaint.
    #[test]
    fn a_load_that_reads_a_file_from_this_machine_is_refused_by_name() {
        for statement in [
            "LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE t",
            "load data local infile '/etc/passwd' into table t",
            "LOAD DATA /* hide */ LOCAL INFILE '/etc/passwd' INTO TABLE t",
        ] {
            let failure = admit(statement, ParseLimits::default()).expect_err("must refuse");
            assert_eq!(failure.code(), "sql.client_file_read", "for `{statement}`");
        }
    }

    /// A conditional comment carries code, so the tree has to be read from what
    /// the server will run rather than from what the parser happens to keep.
    ///
    /// This is the shape the defect took. The blanking module declares the body
    /// of `/*M! ... */` executable and keeps it, which is right, because the
    /// server runs it. The parser crate expands only the introducer that starts
    /// with an exclamation mark and throws the server-specific one away as
    /// whitespace. Each side is defensible alone and the pair left a hole: the
    /// call is caught by walking the tree, and the call was not in the tree.
    ///
    /// The trailing text matters. Dropping the comment has to leave a statement
    /// that still reads, or the parser refuses it for being unreadable and the
    /// hole never opens.
    #[test]
    fn a_file_reading_call_hidden_in_a_conditional_comment_is_refused() {
        for sql in [
            "SELECT 1 /*M!100000 ,LOAD_FILE('/etc/passwd') */",
            "SELECT 1 /*!100000 ,LOAD_FILE('/etc/passwd') */",
            "SELECT 1 /*m!100000 ,LOAD_FILE('/etc/passwd') */",
            "SELECT 1 /*M! ,LOAD_FILE('/etc/passwd') */",
            "SELECT 1 /*M!100000 ,mysql.load_file('/etc/passwd') */",
        ] {
            assert_eq!(refusal(sql).code(), "sql.server_file_read", "input {sql:?}");
        }
    }

    /// The body of a conditional comment reaches the tree, so what the tree
    /// says about the statement is what the server will do with it.
    ///
    /// Without this the classification is computed over a different statement
    /// than the one that runs, and every decision downstream inherits the
    /// error, not just the filesystem refusal above.
    #[test]
    fn what_a_conditional_comment_adds_reaches_the_classification() {
        assert_eq!(
            admitted("SELECT 1 /*M!100000 ,2 */").classification(),
            Classification::Read
        );
        assert_eq!(
            refusal("SELECT 1 /*M!100000 */; DELETE FROM users").code(),
            "sql.multiple_statements"
        );
    }

    /// A conditional comment commented out is not code, and the text that
    /// wraps it is not a statement anywhere.
    ///
    /// This server does not nest block comments: the first closing delimiter
    /// ends the comment, so what follows is left over and the server refuses
    /// the text as unreadable. This product reaches the same answer by the same
    /// route, and the call never leaves this machine either way.
    #[test]
    fn a_conditional_comment_inside_a_plain_comment_carries_nothing_to_the_server() {
        let failure = refusal("SELECT 1 /* /*M!100000 ,LOAD_FILE('/etc/passwd') */ */");
        assert_eq!(failure.code(), "sql.unparsable");
    }

    /// The same text inside a literal is data, and data never refuses.
    #[test]
    fn a_conditional_comment_written_inside_a_literal_refuses_nothing() {
        assert_eq!(
            admitted("SELECT '/*M!100000 LOAD_FILE(1) */' AS note").classification(),
            Classification::Read
        );
    }

    /// The same words inside a literal are data, and data never refuses.
    #[test]
    fn the_words_of_a_local_load_inside_a_literal_do_not_refuse() {
        let admitted = admit("SELECT 'local infile' AS note", ParseLimits::default())
            .expect("a literal is not a clause");
        assert!(admitted.analysis().classification().is_read());
    }
}