sqlpage/
filesystem.rs

1use crate::webserver::ErrorWithStatus;
2use crate::webserver::{make_placeholder, Database};
3use crate::{AppState, TEMPLATES_DIR};
4use anyhow::Context;
5use chrono::{DateTime, Utc};
6use sqlx::any::{AnyKind, AnyStatement, AnyTypeInfo};
7use sqlx::postgres::types::PgTimeTz;
8use sqlx::{Postgres, Statement, Type};
9use std::io::ErrorKind;
10use std::path::{Component, Path, PathBuf};
11
12pub(crate) struct FileSystem {
13    local_root: PathBuf,
14    db_fs_queries: Option<DbFsQueries>,
15}
16
17impl FileSystem {
18    pub async fn init(local_root: impl Into<PathBuf>, db: &Database) -> Self {
19        Self {
20            local_root: local_root.into(),
21            db_fs_queries: match DbFsQueries::init(db).await {
22                Ok(q) => Some(q),
23                Err(e) => {
24                    log::debug!(
25                        "Using local filesystem only, could not initialize on-database filesystem. \
26                        You can host sql files directly in your database by creating the following table: \n\
27                        {} \n\
28                        The error while trying to use the database file system is: {e:#}",
29                        DbFsQueries::get_create_table_sql(db.connection.any_kind())
30                    );
31                    None
32                }
33            },
34        }
35    }
36
37    pub async fn modified_since(
38        &self,
39        app_state: &AppState,
40        path: &Path,
41        since: DateTime<Utc>,
42        priviledged: bool,
43    ) -> anyhow::Result<bool> {
44        let local_path = self.safe_local_path(app_state, path, priviledged)?;
45        let local_result = file_modified_since_local(&local_path, since).await;
46        match (local_result, &self.db_fs_queries) {
47            (Ok(modified), _) => Ok(modified),
48            (Err(e), Some(db_fs)) if e.kind() == ErrorKind::NotFound => {
49                // no local file, try the database
50                db_fs
51                    .file_modified_since_in_db(app_state, path, since)
52                    .await
53            }
54            (Err(e), _) => Err(e).with_context(|| {
55                format!("Unable to read local file metadata for {}", path.display())
56            }),
57        }
58    }
59
60    pub async fn read_to_string(
61        &self,
62        app_state: &AppState,
63        path: &Path,
64        priviledged: bool,
65    ) -> anyhow::Result<String> {
66        let bytes = self.read_file(app_state, path, priviledged).await?;
67        String::from_utf8(bytes).with_context(|| {
68            format!(
69                "The file at {} contains invalid UTF8 characters",
70                path.display()
71            )
72        })
73    }
74
75    /**
76     * Priviledged files are the ones that are in sqlpage's config directory.
77     */
78    pub async fn read_file(
79        &self,
80        app_state: &AppState,
81        path: &Path,
82        priviledged: bool,
83    ) -> anyhow::Result<Vec<u8>> {
84        let local_path = self.safe_local_path(app_state, path, priviledged)?;
85        log::debug!(
86            "Reading file {} from {}",
87            path.display(),
88            local_path.display()
89        );
90        let local_result = tokio::fs::read(&local_path).await;
91        match (local_result, &self.db_fs_queries) {
92            (Ok(f), _) => Ok(f),
93            (Err(e), Some(db_fs)) if e.kind() == ErrorKind::NotFound => {
94                // no local file, try the database
95                db_fs.read_file(app_state, path.as_ref()).await
96            }
97            (Err(e), None) if e.kind() == ErrorKind::NotFound => Err(ErrorWithStatus {
98                status: actix_web::http::StatusCode::NOT_FOUND,
99            }
100            .into()),
101            (Err(e), _) => {
102                Err(e).with_context(|| format!("Unable to read local file {}", path.display()))
103            }
104        }
105    }
106
107    fn safe_local_path(
108        &self,
109        app_state: &AppState,
110        path: &Path,
111        priviledged: bool,
112    ) -> anyhow::Result<PathBuf> {
113        if priviledged {
114            // Templates requests are always made to the static TEMPLATES_DIR, because this is where they are stored in the database
115            // but when serving them from the filesystem, we need to serve them from the `SQLPAGE_CONFIGURATION_DIRECTORY/templates` directory
116            if let Ok(template_path) = path.strip_prefix(TEMPLATES_DIR) {
117                let normalized = app_state
118                    .config
119                    .configuration_directory
120                    .join("templates")
121                    .join(template_path);
122                log::trace!(
123                    "Normalizing template path {} to {}",
124                    path.display(),
125                    normalized.display()
126                );
127                return Ok(normalized);
128            }
129        } else {
130            for (i, component) in path.components().enumerate() {
131                if let Component::Normal(c) = component {
132                    if i == 0 && c.eq_ignore_ascii_case("sqlpage") {
133                        anyhow::bail!(ErrorWithStatus {
134                            status: actix_web::http::StatusCode::FORBIDDEN,
135                        });
136                    }
137                    if c.as_encoded_bytes().starts_with(b".") {
138                        anyhow::bail!(ErrorWithStatus {
139                            status: actix_web::http::StatusCode::FORBIDDEN,
140                        });
141                    }
142                } else {
143                    anyhow::bail!(
144                    "Unsupported path: {path:?}. Path component '{component:?}' is not allowed."
145                );
146                }
147            }
148        }
149        Ok(self.local_root.join(path))
150    }
151
152    pub(crate) async fn file_exists(
153        &self,
154        app_state: &AppState,
155        path: &Path,
156    ) -> anyhow::Result<bool> {
157        let local_exists = match self.safe_local_path(app_state, path, false) {
158            Ok(safe_path) => tokio::fs::try_exists(safe_path).await?,
159            Err(e) => return Err(e),
160        };
161
162        // If not in local fs and we have db_fs, check database
163        if !local_exists {
164            log::debug!(
165                "File {} not found in local filesystem, checking database",
166                path.display()
167            );
168            if let Some(db_fs) = &self.db_fs_queries {
169                return db_fs.file_exists(app_state, path).await;
170            }
171        }
172        Ok(local_exists)
173    }
174}
175
176async fn file_modified_since_local(path: &Path, since: DateTime<Utc>) -> tokio::io::Result<bool> {
177    tokio::fs::metadata(path)
178        .await
179        .and_then(|m| m.modified())
180        .map(|modified_at| DateTime::<Utc>::from(modified_at) > since)
181}
182
183pub struct DbFsQueries {
184    was_modified: AnyStatement<'static>,
185    read_file: AnyStatement<'static>,
186    exists: AnyStatement<'static>,
187}
188
189impl DbFsQueries {
190    #[must_use]
191    pub fn get_create_table_sql(db_kind: AnyKind) -> &'static str {
192        match db_kind {
193            AnyKind::Mssql => "CREATE TABLE sqlpage_files(path NVARCHAR(255) NOT NULL PRIMARY KEY, contents VARBINARY(MAX), last_modified DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);",
194            AnyKind::Postgres => "CREATE TABLE IF NOT EXISTS sqlpage_files(path VARCHAR(255) NOT NULL PRIMARY KEY, contents BYTEA, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);",
195            _ => "CREATE TABLE IF NOT EXISTS sqlpage_files(path VARCHAR(255) NOT NULL PRIMARY KEY, contents BLOB, last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);",
196        }
197    }
198
199    async fn init(db: &Database) -> anyhow::Result<Self> {
200        log::debug!("Initializing database filesystem queries");
201        let db_kind = db.connection.any_kind();
202        Ok(Self {
203            was_modified: Self::make_was_modified_query(db, db_kind).await?,
204            read_file: Self::make_read_file_query(db, db_kind).await?,
205            exists: Self::make_exists_query(db, db_kind).await?,
206        })
207    }
208
209    async fn make_was_modified_query(
210        db: &Database,
211        db_kind: AnyKind,
212    ) -> anyhow::Result<AnyStatement<'static>> {
213        let was_modified_query = format!(
214            "SELECT 1 from sqlpage_files WHERE last_modified >= {} AND path = {}",
215            make_placeholder(db_kind, 1),
216            make_placeholder(db_kind, 2)
217        );
218        let param_types: &[AnyTypeInfo; 2] = &[
219            PgTimeTz::type_info().into(),
220            <str as Type<Postgres>>::type_info().into(),
221        ];
222        log::debug!("Preparing the database filesystem was_modified_query: {was_modified_query}");
223        db.prepare_with(&was_modified_query, param_types).await
224    }
225
226    async fn make_read_file_query(
227        db: &Database,
228        db_kind: AnyKind,
229    ) -> anyhow::Result<AnyStatement<'static>> {
230        let read_file_query = format!(
231            "SELECT contents from sqlpage_files WHERE path = {}",
232            make_placeholder(db_kind, 1),
233        );
234        let param_types: &[AnyTypeInfo; 1] = &[<str as Type<Postgres>>::type_info().into()];
235        log::debug!("Preparing the database filesystem read_file_query: {read_file_query}");
236        db.prepare_with(&read_file_query, param_types).await
237    }
238
239    async fn make_exists_query(
240        db: &Database,
241        db_kind: AnyKind,
242    ) -> anyhow::Result<AnyStatement<'static>> {
243        let exists_query = format!(
244            "SELECT 1 from sqlpage_files WHERE path = {}",
245            make_placeholder(db_kind, 1),
246        );
247        let param_types: &[AnyTypeInfo; 1] = &[<str as Type<Postgres>>::type_info().into()];
248        db.prepare_with(&exists_query, param_types).await
249    }
250
251    async fn file_modified_since_in_db(
252        &self,
253        app_state: &AppState,
254        path: &Path,
255        since: DateTime<Utc>,
256    ) -> anyhow::Result<bool> {
257        let query = self
258            .was_modified
259            .query_as::<(i32,)>()
260            .bind(since)
261            .bind(path.display().to_string());
262        log::trace!(
263            "Checking if file {} was modified since {} by executing query: \n\
264            {}\n\
265            with parameters: {:?}",
266            path.display(),
267            since,
268            self.was_modified.sql(),
269            (since, path)
270        );
271        query
272            .fetch_optional(&app_state.db.connection)
273            .await
274            .map(|modified| modified == Some((1,)))
275            .with_context(|| {
276                format!(
277                    "Unable to check when {} was last modified in the database",
278                    path.display()
279                )
280            })
281    }
282
283    async fn read_file(&self, app_state: &AppState, path: &Path) -> anyhow::Result<Vec<u8>> {
284        log::debug!("Reading file {} from the database", path.display());
285        self.read_file
286            .query_as::<(Vec<u8>,)>()
287            .bind(path.display().to_string())
288            .fetch_optional(&app_state.db.connection)
289            .await
290            .map_err(anyhow::Error::from)
291            .and_then(|modified| {
292                if let Some((modified,)) = modified {
293                    Ok(modified)
294                } else {
295                    Err(ErrorWithStatus {
296                        status: actix_web::http::StatusCode::NOT_FOUND,
297                    }
298                    .into())
299                }
300            })
301            .with_context(|| format!("Unable to read {} from the database", path.display()))
302    }
303
304    async fn file_exists(&self, app_state: &AppState, path: &Path) -> anyhow::Result<bool> {
305        let query = self
306            .exists
307            .query_as::<(i32,)>()
308            .bind(path.display().to_string());
309        log::trace!(
310            "Checking if file {} exists by executing query: \n\
311            {}\n\
312            with parameters: {:?}",
313            path.display(),
314            self.exists.sql(),
315            (path,)
316        );
317        let result = query.fetch_optional(&app_state.db.connection).await;
318        log::debug!("DB File exists result: {result:?}");
319        result.map(|result| result.is_some()).with_context(|| {
320            format!(
321                "Unable to check if {} exists in the database",
322                path.display()
323            )
324        })
325    }
326}
327
328#[actix_web::test]
329async fn test_sql_file_read_utf8() -> anyhow::Result<()> {
330    use crate::app_config;
331    use sqlx::Executor;
332    let config = app_config::tests::test_config();
333    let state = AppState::init(&config).await?;
334    let create_table_sql = DbFsQueries::get_create_table_sql(state.db.connection.any_kind());
335    state
336        .db
337        .connection
338        .execute(format!("DROP TABLE IF EXISTS sqlpage_files; {create_table_sql}").as_str())
339        .await?;
340
341    let db_kind = state.db.connection.any_kind();
342    let insert_sql = format!(
343        "INSERT INTO sqlpage_files(path, contents) VALUES ({}, {})",
344        make_placeholder(db_kind, 1),
345        make_placeholder(db_kind, 2)
346    );
347    sqlx::query(&insert_sql)
348        .bind("unit test file.txt")
349        .bind("Héllö world! 😀".as_bytes())
350        .execute(&state.db.connection)
351        .await?;
352
353    let fs = FileSystem::init("/", &state.db).await;
354    let actual = fs
355        .read_to_string(&state, "unit test file.txt".as_ref(), false)
356        .await?;
357    assert_eq!(actual, "Héllö world! 😀");
358
359    let one_hour_ago = Utc::now() - chrono::Duration::hours(1);
360    let one_hour_future = Utc::now() + chrono::Duration::hours(1);
361
362    let was_modified = fs
363        .modified_since(&state, "unit test file.txt".as_ref(), one_hour_ago, false)
364        .await?;
365    assert!(was_modified, "File should be modified since one hour ago");
366
367    let was_modified = fs
368        .modified_since(
369            &state,
370            "unit test file.txt".as_ref(),
371            one_hour_future,
372            false,
373        )
374        .await?;
375    assert!(
376        !was_modified,
377        "File should not be modified since one hour in the future"
378    );
379
380    Ok(())
381}