nomoreide_core/db/
mod.rs

1//! Read-only access to the databases a user has registered.
2//!
3//! The read-safe half of the database layer, and the one an agent reaches. It
4//! can list connections, read a catalog, and sample rows; it cannot change
5//! anything. Everything that can is in `nomoreide-actions`, behind a per
6//! connection unlock and an affected-rows preview a human has to look at.
7//!
8//! Two things here are load-bearing rather than cosmetic. A connection URL is
9//! masked before it leaves this module, because a registered database's URL
10//! carries its password and the agent asking for the list has no business
11//! reading it. And a column whose *name* suggests a secret is replaced with
12//! bullets in a sample, so a peek at a users table does not hand back everyone's
13//! password hash.
14//!
15//! Moved out of the Tauri command module; the desktop app now calls in here.
16
17mod catalog;
18mod details;
19mod engine;
20mod export;
21mod peek;
22mod rows;
23mod sql;
24mod types;
25
26pub use catalog::{columns_for, objects_for, resolve_object, schemas_for};
27pub use details::object_details;
28pub use engine::{driver_message, hex_bytes, list_db_tables, lossless_json_integer, run_query};
29pub use export::{
30    content_disposition, csv_cell, export_filename, export_sql, ExportFormat, ExportWriter,
31};
32pub use peek::{
33    connection as peek_connection, details as peek_details, is_read_statement,
34    objects as peek_objects, query as peek_query, run_capped_query, sample as peek_sample,
35    schemas as peek_schemas, tables as peek_tables, write_staging_guidance, QueryOutcome, TableRef,
36    DEFAULT_ROW_LIMIT,
37};
38pub use rows::{row_browse_clauses, sample_object};
39pub use sql::{first_statement, is_sensitive_preview_column, quote_identifier};
40pub use types::{
41    CatalogCapabilities, CatalogObject, ColumnInfo, NamedDefinition, ObjectDetails, ObjectRows,
42    QueryResult, RowBrowseQuery, RowFilter, RowSort,
43};
44
45use crate::config::{Config, DatabaseDef};
46use serde_json::{json, Value};
47
48/// The connection a name refers to, or a refusal naming it.
49pub fn connection<'a>(config: &'a Config, name: &str) -> Result<&'a DatabaseDef, String> {
50    config
51        .databases
52        .iter()
53        .find(|database| database.name == name)
54        .ok_or_else(|| format!("Database '{name}' not found"))
55}
56
57/// Every registered connection, with its URL masked.
58pub fn list_connections(config: &Config) -> Vec<Value> {
59    config.databases.iter().map(public_connection).collect()
60}
61
62/// One connection as every surface reports it: no raw URL, and no key at all
63/// for a project path the connection does not have. An absent field and a null
64/// one read differently to whatever is on the other end.
65pub fn public_connection(database: &DatabaseDef) -> Value {
66    let mut entry = json!({
67        "name": database.name,
68        "engine": database.engine,
69        "url": mask_url(&database.engine, &database.url),
70        "writeUnlocked": database.write_unlocked.unwrap_or(false),
71    });
72    if let Some(path) = &database.project_path {
73        entry["projectPath"] = json!(path);
74    }
75    entry
76}
77
78/// One connection string found in a service's `.env`.
79#[derive(Debug, Clone, serde::Serialize)]
80#[serde(rename_all = "camelCase")]
81pub struct DetectedConnection {
82    pub service: String,
83    pub cwd: String,
84    pub key: String,
85    pub engine: String,
86    /// **Unmasked, on purpose.** This is the one place a raw connection string
87    /// leaves the server: the client has just been told a connection exists and
88    /// needs the real value to register it, and has nowhere else to get it.
89    pub url: String,
90    pub masked_url: String,
91}
92
93/// Scan registered services' `.env` files for anything that looks like a
94/// connection string.
95///
96/// Deduplicated by engine *and* value, so the same database named twice in one
97/// file -- or shared between two services -- is offered once. The first
98/// sighting wins, which keeps the result in the order the services are
99/// registered rather than in whichever order the filesystem answered.
100pub async fn detect_from_env(config: &Config) -> Vec<DetectedConnection> {
101    let mut found = Vec::new();
102    let mut seen = std::collections::HashSet::new();
103    for service in &config.services {
104        let Some(cwd) = service.cwd.as_deref() else {
105            continue;
106        };
107        let Ok(Some(lines)) = crate::env_file::read(std::path::Path::new(cwd).join(".env")).await
108        else {
109            continue;
110        };
111        for entry in crate::env_file::entries(&lines) {
112            let Some(engine) = engine_from_url(&entry.value) else {
113                continue;
114            };
115            if !seen.insert(format!("{engine}:{}", entry.value)) {
116                continue;
117            }
118            found.push(DetectedConnection {
119                service: service.name.clone(),
120                cwd: cwd.to_string(),
121                key: entry.key,
122                engine: engine.to_string(),
123                masked_url: mask_url(engine, &entry.value),
124                url: entry.value,
125            });
126        }
127    }
128    found
129}
130
131/// Check that a connection can be reached, without saying anything about it.
132pub async fn test_connection(engine: &str, url: &str) -> Result<(), String> {
133    let sql = match engine {
134        "postgres" | "mysql" | "sqlite" => "SELECT 1",
135        _ => return Err(format!("Unsupported engine: {engine}")),
136    };
137    run_query(engine, url, sql).await.map(|_| ())
138}
139
140/// Which object kinds and table details this engine can be asked about.
141pub fn capabilities(engine: &str) -> Result<CatalogCapabilities, String> {
142    let object_kinds = match engine {
143        "postgres" => vec![
144            "table",
145            "view",
146            "materializedView",
147            "function",
148            "procedure",
149            "sequence",
150        ],
151        "mysql" => vec!["table", "view", "function", "procedure"],
152        "sqlite" => vec!["table", "view"],
153        other => return Err(format!("Unsupported engine: {other}")),
154    };
155    Ok(CatalogCapabilities {
156        object_kinds: object_kinds.into_iter().map(str::to_string).collect(),
157        table_details: ["columns", "indexes", "constraints", "triggers"]
158            .into_iter()
159            .map(str::to_string)
160            .collect(),
161    })
162}
163
164/// A connection URL with its password removed.
165///
166/// A URL that parses keeps every part a person needs to recognise it — scheme,
167/// user, host, port, database — and loses only the secret. One that does not
168/// parse cannot be edited that precisely, so it is blanked down to its first
169/// and last few characters: enough to tell two connections apart, not enough to
170/// reconstruct either. Anything short enough that those two ends would be most
171/// of it is replaced outright.
172///
173/// SQLite is left alone. Its "URL" is a path on the user's own disk and carries
174/// no credential, and masking it would hide which file a connection reads.
175pub fn mask_url(engine: &str, url: &str) -> String {
176    if engine == "sqlite" {
177        return url.to_string();
178    }
179    const KEPT_EDGE: usize = 4;
180    match url::Url::parse(url) {
181        Ok(mut parsed) => {
182            if parsed
183                .password()
184                .is_some_and(|password| !password.is_empty())
185            {
186                let _ = parsed.set_password(Some("****"));
187            }
188            mask_sensitive_query(&mut parsed);
189            parsed.to_string()
190        }
191        Err(_) => {
192            let characters: Vec<char> = url.chars().collect();
193            if characters.len() <= KEPT_EDGE * 2 {
194                return "****".to_string();
195            }
196            let head: String = characters[..KEPT_EDGE].iter().collect();
197            let tail: String = characters[characters.len() - KEPT_EDGE..].iter().collect();
198            format!("{head}****{tail}")
199        }
200    }
201}
202
203/// Query-string fields that must never leave the machine in the clear.
204///
205/// The password is not always in the password slot. A connection string can
206/// carry a second credential in its query -- `?password=`, `?token=`,
207/// `?api_key=` -- and a mask that only rewrites the userinfo section hands that
208/// one straight to the client. The pattern matches the reference's:
209/// `password|passwd|secret|token|api[_-]?key`, case-insensitively, anywhere in
210/// the key, so `apiKey` and `X-API-KEY` are both caught.
211pub fn is_sensitive_connection_parameter(key: &str) -> bool {
212    let lower = key.to_ascii_lowercase();
213    if lower.contains("password")
214        || lower.contains("passwd")
215        || lower.contains("secret")
216        || lower.contains("token")
217    {
218        return true;
219    }
220    // `api key`, with an optional single `_` or `-` between the halves.
221    let bytes = lower.as_bytes();
222    for (index, _) in lower.match_indices("api") {
223        let rest = &bytes[index + 3..];
224        let rest = match rest.first() {
225            Some(b'_') | Some(b'-') => &rest[1..],
226            _ => rest,
227        };
228        if rest.starts_with(b"key") {
229            return true;
230        }
231    }
232    false
233}
234
235fn mask_sensitive_query(parsed: &mut url::Url) {
236    let masked: Vec<(String, String)> = parsed
237        .query_pairs()
238        .map(|(key, value)| {
239            let key = key.into_owned();
240            let value = if is_sensitive_connection_parameter(&key) {
241                "****".to_string()
242            } else {
243                value.into_owned()
244            };
245            (key, value)
246        })
247        .collect();
248    if masked.is_empty() {
249        return;
250    }
251    // Rebuilt wholesale rather than edited in place: the reference sets each
252    // sensitive value through the same URLSearchParams object, which re-encodes
253    // the whole query, so a value that arrived oddly encoded comes back
254    // normalised on both sides.
255    let mut serializer = parsed.query_pairs_mut();
256    serializer.clear();
257    for (key, value) in &masked {
258        serializer.append_pair(key, value);
259    }
260    drop(serializer);
261}
262
263/// Guess an engine from a connection string or a bare file path.
264pub fn engine_from_url(value: &str) -> Option<&'static str> {
265    let trimmed = value.trim();
266    let lower = trimmed.to_ascii_lowercase();
267    if lower.starts_with("postgres://") || lower.starts_with("postgresql://") {
268        return Some("postgres");
269    }
270    if lower.starts_with("mysql://") || lower.starts_with("mariadb://") {
271        return Some("mysql");
272    }
273    if lower.starts_with("sqlite://") {
274        return Some("sqlite");
275    }
276    // `file:` followed by at least one character and then a database suffix
277    // *anywhere* after it -- the reference does not anchor this one to the end,
278    // so `file:./app.db?mode=ro` still counts.
279    if let Some(rest) = lower.strip_prefix("file:") {
280        if !rest.is_empty()
281            && [".db", ".sqlite", ".sqlite3"]
282                .iter()
283                .any(|ext| rest.contains(ext))
284        {
285            return Some("sqlite");
286        }
287    }
288    if [".db", ".sqlite", ".sqlite3"]
289        .iter()
290        .any(|ext| lower.ends_with(ext))
291    {
292        return Some("sqlite");
293    }
294    None
295}
296
297/// Put the stored password back into an edited connection string.
298///
299/// The client only ever holds the masked URL, so an edit that did not change
300/// the password arrives without one. Taking that at face value would silently
301/// wipe the credential. A password that *is* supplied always wins, SQLite has
302/// none to carry, and a string that does not parse cannot be spliced -- it is
303/// returned untouched rather than guessed at.
304pub fn merge_stored_password(engine: &str, next_url: &str, existing_url: &str) -> String {
305    if engine == "sqlite" {
306        return next_url.to_string();
307    }
308    let (Ok(mut next), Ok(existing)) = (url::Url::parse(next_url), url::Url::parse(existing_url))
309    else {
310        return next_url.to_string();
311    };
312    if next.password().is_some_and(|value| !value.is_empty()) {
313        return next_url.to_string();
314    }
315    let Some(password) = existing.password().filter(|value| !value.is_empty()) else {
316        return next_url.to_string();
317    };
318    let decoded = percent_decode(password);
319    if next.set_password(Some(&decoded)).is_err() {
320        return next_url.to_string();
321    }
322    next.to_string()
323}
324
325/// Take the connection string, and any password inside it, out of an error.
326///
327/// Drivers put the URL they failed to open into their message, so the error a
328/// user sees would otherwise carry the credential the mask exists to hide.
329pub fn redact_database_error(engine: &str, url: &str, message: &str) -> String {
330    let mut message = message.replace(url, &mask_url(engine, url));
331    if engine == "sqlite" {
332        return message;
333    }
334    if let Ok(parsed) = url::Url::parse(url) {
335        if let Some(password) = parsed.password().filter(|value| !value.is_empty()) {
336            // Both spellings: a driver may quote the password as it appeared in
337            // the URL (encoded) or as it used it (decoded).
338            let decoded = percent_decode(password);
339            if !decoded.is_empty() {
340                message = message.replace(&decoded, "****");
341            }
342            message = message.replace(password, "****");
343        }
344    }
345    message
346}
347
348/// `decodeURIComponent`, near enough: `urlencoding` also leaves `+` alone,
349/// where a form decoder would turn it into a space.
350fn percent_decode(value: &str) -> String {
351    urlencoding::decode(value)
352        .map(|decoded| decoded.into_owned())
353        .unwrap_or_else(|_| value.to_string())
354}
355
356#[cfg(test)]
357mod tests {
358    use super::*;
359    use crate::config::DatabaseDef;
360
361    fn sqlite_fixture_url() -> (std::path::PathBuf, String) {
362        let path =
363            std::env::temp_dir().join(format!("nomoreide-catalog-{}.db", uuid::Uuid::new_v4()));
364        std::fs::File::create(&path).unwrap();
365        let url = format!("sqlite://{}", path.display());
366        (path, url)
367    }
368
369    /// Set a fixture up without borrowing the write-capable crate: these tests
370    /// are about reading a catalog, and depending on `nomoreide-actions` to
371    /// build one would invert the dependency the split exists to enforce.
372    async fn seed(url: &str, statements: &[&str]) {
373        let pool = sqlx::sqlite::SqlitePoolOptions::new()
374            .connect(url)
375            .await
376            .unwrap();
377        for statement in statements {
378            sqlx::query(statement).execute(&pool).await.unwrap();
379        }
380        pool.close().await;
381    }
382
383    #[tokio::test]
384    async fn sqlite_catalog_uses_opaque_live_keys() {
385        let (path, url) = sqlite_fixture_url();
386        seed(
387            &url,
388            &["CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)"],
389        )
390        .await;
391        let database = DatabaseDef {
392            name: "app".into(),
393            engine: "sqlite".into(),
394            url: url.clone(),
395            write_unlocked: None,
396            project_path: Some("/workspace/app".into()),
397        };
398        let objects = objects_for(&database, "main").await.unwrap();
399        let users = objects
400            .iter()
401            .find(|object| object.name == "users")
402            .unwrap();
403        assert!(!users.key.contains("users"));
404        assert_eq!(resolve_object(&database, &users.key).await.unwrap(), *users);
405        assert!(resolve_object(&database, "dXNlcnM").await.is_err());
406        let _ = std::fs::remove_file(path);
407    }
408
409    #[tokio::test]
410    async fn sqlite_columns_preserve_type_and_primary_key_metadata() {
411        let (path, url) = sqlite_fixture_url();
412        seed(&url, &["CREATE TABLE memberships (team_id INTEGER NOT NULL, user_id TEXT NOT NULL, label TEXT, PRIMARY KEY (team_id, user_id))"]).await;
413        seed(
414            &url,
415            &["INSERT INTO memberships (team_id, user_id) VALUES (9007199254740993, 'one')"],
416        )
417        .await;
418        let database = DatabaseDef {
419            name: "app".into(),
420            engine: "sqlite".into(),
421            url,
422            write_unlocked: Some(true),
423            project_path: None,
424        };
425        let object = objects_for(&database, "main")
426            .await
427            .unwrap()
428            .into_iter()
429            .find(|object| object.name == "memberships")
430            .unwrap();
431        let columns = columns_for(&database, &object).await.unwrap();
432        assert_eq!(columns[0].data_type, "INTEGER");
433        assert!(columns[0].primary_key);
434        assert!(columns[1].primary_key);
435        assert!(!columns[2].primary_key);
436        assert!(columns[2].nullable);
437        // A primary key past 2^53 comes back as the integer it is, and reaches
438        // JSON without passing through a float. The reference cannot do this at
439        // all -- `node:sqlite` refuses the row rather than lose the precision --
440        // so it is a fix rather than a divergence, and it is why the projection
441        // is the column itself and not a cast to text.
442        let projection = columns
443            .iter()
444            .map(|column| quote_identifier(&column.name, "sqlite"))
445            .collect::<Vec<_>>()
446            .join(", ");
447        let sampled = run_query(
448            "sqlite",
449            &database.url,
450            &format!("SELECT {projection} FROM memberships"),
451        )
452        .await
453        .unwrap();
454        assert_eq!(sampled.rows[0][0], serde_json::json!(9007199254740993i64));
455        let _ = std::fs::remove_file(path);
456    }
457
458    #[test]
459    fn browser_filters_validate_columns_and_escape_like_wildcards() {
460        let columns = vec![
461            ColumnInfo {
462                name: "id".into(),
463                data_type: "INTEGER".into(),
464                nullable: false,
465                primary_key: true,
466            },
467            ColumnInfo {
468                name: "email".into(),
469                data_type: "TEXT".into(),
470                nullable: false,
471                primary_key: false,
472            },
473        ];
474        let (where_sql, order_sql) = row_browse_clauses(
475            "sqlite",
476            &columns,
477            RowBrowseQuery {
478                filters: vec![RowFilter {
479                    column: "email".into(),
480                    operator: "contains".into(),
481                    value: Some("a%b_!".into()),
482                }],
483                sort: Some(RowSort {
484                    column: "email".into(),
485                    direction: "desc".into(),
486                }),
487            },
488        )
489        .unwrap();
490        assert_eq!(where_sql, " WHERE \"email\" LIKE '%a!%b!_!!%' ESCAPE '!'");
491        assert_eq!(order_sql, " ORDER BY \"email\" DESC, \"id\" ASC");
492        assert!(row_browse_clauses(
493            "sqlite",
494            &columns,
495            RowBrowseQuery {
496                filters: vec![RowFilter {
497                    column: "email; DROP TABLE users".into(),
498                    operator: "eq".into(),
499                    value: Some("x".into()),
500                }],
501                sort: None,
502            },
503        )
504        .is_err());
505    }
506}
nomoreide_core/db/mod.rs

nomoreide_core/db/
mod.rs
