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};
const SERVER_FILE_READERS: &[&str] = &["load_file", "loadfile"];
const SERVER_FILE_WRITERS: &[&str] = &["outfile", "dumpfile"];
const CLIENT_FILE_READER: &str = "infile";
#[derive(Debug, Clone)]
pub struct Admission {
analysis: Analysis,
stripped: Stripped,
}
impl Admission {
#[must_use]
pub const fn analysis(&self) -> &Analysis {
&self.analysis
}
#[must_use]
pub const fn classification(&self) -> Classification {
self.analysis.classification()
}
#[must_use]
pub const fn stripped(&self) -> &Stripped {
&self.stripped
}
}
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 })
}
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(())
}
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)
}
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(())
}
#[derive(Debug, Clone)]
struct Word {
text: String,
ends_at: usize,
}
impl Word {
fn opens_a_call(&self, text: &str) -> bool {
text[self.ends_at..]
.chars()
.find(|character| !character.is_whitespace())
== Some('(')
}
}
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
}
#[derive(Debug, Clone)]
enum Finding {
FileReader(String),
FileOutput(String),
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"),
}
}
}
#[derive(Debug)]
struct FilesystemFinder;
impl FilesystemFinder {
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 {
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"
);
}
#[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:?}"
);
}
}
#[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
);
}
#[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}`");
}
}
#[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:?}");
}
}
#[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"
);
}
#[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");
}
#[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
);
}
#[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());
}
}