diff --git a/Cargo.lock b/Cargo.lock --- a/Cargo.lock +++ b/Cargo.lock @@ -377,6 +377,15 @@ dependencies = [ "syn 3.0.6", ] +[[package]] +name = "atoi" +version = "3.1.1" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "d12c37257553c19e9aee6cba77814fdc9e07844abfc8b3a2c377cd920ee5a993" +dependencies = [ + "num-traits", +] + [[package]] name = "atomic-waker" version = "1.1.2" @@ -2072,6 +2081,18 @@ dependencies = [ "gridcraft-core", ] +[[package]] +name = "gridcraft-odbc" +version = "0.12.1" +dependencies = [ + "chrono", + "odbc-api", + "serde", + "serde_json", + "sqlparser", + "thiserror 2.0.21", +] + [[package]] name = "gridcraft-pdf" version = "0.12.1" @@ -3400,6 +3421,25 @@ dependencies = [ "objc2-foundation 0.2.2", ] +[[package]] +name = "odbc-api" +version = "29.2.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "b8532ca21ccfef7f60246ce4f4d25d4a46d2aa501832dfaab17e2a27e621fb52" +dependencies = [ + "atoi", + "log", + "odbc-sys", + "thiserror 2.0.21", + "widestring", +] + +[[package]] +name = "odbc-sys" +version = "0.31.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "245cb4fe8236df4fd352ba96075d754233c6509d654d9f1c1482158b7d6c083d" + [[package]] name = "once_cell" version = "1.21.4" @@ -4830,6 +4870,15 @@ dependencies = [ "der", ] +[[package]] +name = "sqlparser" +version = "0.63.0" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "3679862809bd1f92e563cf6fd820e28f39135303668001c10331d5027183e8b4" +dependencies = [ + "log", +] + [[package]] name = "stable_deref_trait" version = "1.2.1" @@ -5928,6 +5977,12 @@ dependencies = [ "web-sys", ] +[[package]] +name = "widestring" +version = "1.2.1" +source = "registry+https://github.com/rust-lang/crates.io-index" +checksum = "72069c3113ab32ab29e5584db3c6ec55d416895e60715417b5b883a357c3e471" + [[package]] name = "winapi-util" version = "0.1.11" diff --git a/Cargo.toml b/Cargo.toml --- a/Cargo.toml +++ b/Cargo.toml @@ -24,6 +24,7 @@ gridcraft-engine = { path = "crates/engine" } gridcraft-ui-egui = { path = "crates/ui-egui" } gridcraft-mcp = { path = "crates/mcp" } gridcraft-pdf = { path = "crates/pdf" } +gridcraft-odbc = { path = "crates/odbc" } # Every craft-libs crate (craft-update and craft-window here, craft-ui in ui-egui, craft-print in # the app) comes from one storytold/craft-libs revision. craft-update = { git = "https://github.com/storytold/craft-libs", rev = "92d392cff132eaf53e13afff77f38b5c11b73aa0", version = "=0.1.0", features = ["egui"] } diff --git a/crates/odbc/Cargo.toml b/crates/odbc/Cargo.toml new file mode 100644 --- /dev/null +++ b/crates/odbc/Cargo.toml @@ -0,0 +1,21 @@ +[package] +name = "gridcraft-odbc" +version.workspace = true +edition.workspace = true +license.workspace = true +description = "Bounded, toolkit-independent ODBC data imports" + +[lints] +workspace = true + +[dependencies] +chrono = { workspace = true, features = ["serde"] } +serde = { workspace = true } +thiserror = { workspace = true } + +[target.'cfg(target_os = "windows")'.dependencies] +odbc-api = { version = "29.2", default-features = false, features = ["odbc_version_3_80", "wide"] } +sqlparser = { version = "0.63", default-features = false, features = ["std"] } + +[dev-dependencies] +serde_json = { workspace = true } diff --git a/crates/odbc/README.md b/crates/odbc/README.md new file mode 100644 --- /dev/null +++ b/crates/odbc/README.md @@ -0,0 +1,84 @@ +# gridcraft-odbc + +Bounded, read-only ODBC queries independent of the spreadsheet engine and UI. +The current native implementation supports Windows drivers for PostgreSQL, +SQL Server/Azure SQL, MySQL, MariaDB, Oracle and SQLite 3. Other targets build +the same API and return `OdbcError::Unavailable` for native operations. + +```rust,no_run +use gridcraft_odbc::{Backend, Connection, Limits, NativeBackend}; +use std::sync::atomic::AtomicBool; + +# fn main() -> Result<(), gridcraft_odbc::OdbcError> { +let connection = Connection { dsn: Some("Reporting".into()), ..Default::default() }; +let data = NativeBackend.query( + &connection, "SELECT * FROM customers", Limits::default(), false, + &AtomicBool::new(false), +)?; +for column in &data.columns { + println!("{}: {:?}", column.name, column.kind); +} +# Ok(()) +# } +``` + +## Contract + +`Backend::query` returns a `DataSet` containing column names/types, row values and +a preview truncation flag. NULL is `Value::Empty`. Numbers above spreadsheet +precision remain exact text. Text is never interpreted as a formula. Dates, +times and timestamps use Chrono types, retaining nanoseconds and explicit +offsets when supplied by ODBC metadata. No workbook epoch is chosen here. +Timestamp columns whose driver omits timezone information remain timezone-free. +Nonstandard temporal strings (including infinite dates, zero dates and duration +values outside a time of day) return `InvalidData`; explicitly cast them to text. +SQLite expressions with unknown metadata remain text rather than guessed dates. + +The result is materialized and bounded: 100,000 rows, 256 columns, 128 MiB +including container overhead, and 1 MiB per text cell. Preview reads at most +100 rows and may return `truncated`; full queries fail on overflow. Cancellation +is checked between native reads. A blocking driver call may finish first. +Arrow batches and unixODBC are follow-ups; this crate does not define saved +queries, refresh or an XLSX extension. See +[#431](https://github.com/storytold/gridcraft/issues/431) for that model. + +## Drivers and safety + +Install a matching 64-bit ODBC driver and configure a DSN, or supply a registered +driver connection string. Use separate user/password fields. Driver detection, +connection options, query guards and transaction behavior live in +`src/native/profile.rs`; values and bounded fetching live in `src/native.rs`. +Credentials and driver diagnostics are redacted from public errors and Debug. +Disable ODBC manager/vendor tracing separately. + +Queries accept a single SELECT/WITH/VALUES/TABLE statement. Write/control +keywords, executable comments and internal semicolons are rejected. Connections +roll back after reading. PostgreSQL/Oracle use a server read-only transaction; +MySQL/MariaDB start a read-only transaction; SQLite enables `query_only`. +SQL Server read intent is a routing hint: a SELECT-only account is required. +Query guards do not sandbox database functions or replace server permissions. + +## Validation and contributions + +Run `cargo test -p gridcraft-odbc`. Synthetic integration tests are ignored by +default and need dedicated local databases plus installed drivers: + +```powershell +$env:GRIDCRAFT_TEST_ODBC_DSN = 'GridCraftTest' +$env:GRIDCRAFT_TEST_ODBC_USER = 'reader' +$env:GRIDCRAFT_TEST_ODBC_PASSWORD = '' +cargo test -p gridcraft-odbc --test postgres -- --ignored +``` + +`GRIDCRAFT_TEST_ODBC_CONNECTION_STRING` replaces the DSN. Other databases use +`GRIDCRAFT_TEST_ODBC_{SQLSERVER,MYSQL,MARIADB,ORACLE,SQLITE}_{DSN,CONNECTION_STRING,USER,PASSWORD}` +and `cargo test -p gridcraft-odbc --test databases -- --ignored` (or a test-name +filter). SQLite requires an existing synthetic file. These tests create no +server objects. Add a driver profile and synthetic integration case when adding +a database family. Keep vendor settings out of engine/UI code and keep native +dependencies out of WASM. + +On 2026-10-27, all 17 unit/profile tests passed and the revised native tests +passed with local PostgreSQL and SQLite drivers. SQL Server, MySQL, MariaDB and +Oracle servers were unavailable for this revision; earlier validation of the +full implementation does not certify these revised temporal mappings. diff --git a/crates/odbc/src/lib.rs b/crates/odbc/src/lib.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/src/lib.rs @@ -0,0 +1,306 @@ +//! Bounded ODBC reads for spreadsheet imports. +#![forbid(unsafe_code)] +#![deny(clippy::unwrap_used, clippy::expect_used, clippy::panic)] +#![cfg_attr(test, allow(clippy::unwrap_used, clippy::expect_used, clippy::panic))] + +use std::{fmt, mem::size_of, sync::atomic::AtomicBool}; + +pub use chrono::{DateTime, Datelike, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, Timelike}; +use serde::{Deserialize, Serialize}; + +#[cfg(target_os = "windows")] +mod native; +#[cfg(test)] +mod tests; + +pub const MAX_ROWS: usize = 100_000; +pub const MAX_COLUMNS: usize = 256; +pub const MAX_BYTES: usize = 128 * 1024 * 1024; +pub const MAX_CELL_BYTES: usize = 1024 * 1024; + +#[derive(Clone, Default)] +pub struct Connection { + pub dsn: Option, + pub connection_string: Option, + pub user: String, + pub password: String, +} + +impl fmt::Debug for Connection { + fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { + f.debug_struct("Connection") + .field("dsn", &self.dsn) + .field("connection_string", &"[redacted]") + .field("user", &self.user) + .field("password", &"[redacted]") + .finish() + } +} + +impl Connection { + pub fn validate(&self) -> Result<(), OdbcError> { + match (&self.dsn, &self.connection_string) { + (Some(dsn), None) + if !dsn.trim().is_empty() + && dsn.trim() == dsn + && dsn.encode_utf16().count() <= 32 + && !dsn.chars().any(|c| c.is_control() || "[]{}(),;?*=!@\\".contains(c)) => {} + (None, Some(cs)) + if !cs.trim().is_empty() + && cs.len() <= 16_384 + && !cs.contains('\0') + && !cs.split(';').any(|part| part.split_once('=').is_some_and(|(key, _)| key.trim().eq_ignore_ascii_case("SAVEFILE"))) => {} + _ => return Err(OdbcError::InvalidConnection), + } + if self.user.len() > 512 || self.password.len() > 16_384 || self.user.contains('\0') || self.password.contains('\0') { + return Err(OdbcError::InvalidConnection); + } + Ok(()) + } +} + +#[derive(Clone, Copy, Debug, PartialEq, Eq, Serialize, Deserialize)] +#[serde(rename_all = "camelCase")] +pub struct Limits { + pub rows: usize, + pub columns: usize, + pub bytes: usize, + pub cell_bytes: usize, +} + +impl Default for Limits { + fn default() -> Self { + Self { rows: MAX_ROWS, columns: MAX_COLUMNS, bytes: MAX_BYTES, cell_bytes: MAX_CELL_BYTES } + } +} + +impl Limits { + pub fn validate(self) -> Result { + if self.rows == 0 + || self.rows > MAX_ROWS + || self.columns == 0 + || self.columns > MAX_COLUMNS + || self.bytes == 0 + || self.bytes > MAX_BYTES + || self.cell_bytes == 0 + || self.cell_bytes > MAX_CELL_BYTES + { + return Err(OdbcError::Limit("Requested limits are outside supported bounds.")); + } + Ok(self) + } +} + +#[derive(Clone, Copy, Debug, PartialEq, Eq, Serialize, Deserialize)] +#[serde(rename_all = "camelCase")] +pub enum ColumnKind { + Text, + Integer, + Decimal, + Float, + Boolean, + Date, + Time, + Timestamp, + TimestampWithOffset, +} + +/// Connector values are independent of workbook epochs, formats and formula semantics. +#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)] +pub enum Value { + Empty, + Bool(bool), + Number(f64), + Text(String), + Date(NaiveDate), + Time(NaiveTime), + Timestamp(NaiveDateTime), + TimestampWithOffset(DateTime), +} + +impl Value { + pub fn text(value: impl Into) -> Self { + Self::Text(value.into()) + } +} + +#[derive(Clone, Debug, PartialEq, Eq, Serialize, Deserialize)] +#[serde(rename_all = "camelCase")] +pub struct Column { + pub name: String, + pub kind: ColumnKind, +} + +#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)] +#[serde(rename_all = "camelCase")] +pub struct DataSet { + pub columns: Vec, + pub rows: Vec>, + pub truncated: bool, +} + +impl DataSet { + /// Validates the result and returns its bounded storage estimate. + pub fn validate(&self, limits: Limits) -> Result { + let limits = limits.validate()?; + if self.columns.is_empty() + || self.columns.len() > limits.columns + || self.rows.len() > limits.rows + || self.columns.iter().any(|c| c.name.len() > 1024 || c.name.contains('\0')) + { + return Err(OdbcError::Limit("The result exceeds the supported shape.")); + } + let mut bytes = self + .columns + .iter() + .try_fold(0usize, |n, c| n.checked_add(c.name.len() + size_of::())) + .ok_or(OdbcError::Limit("The result exceeds the supported size."))?; + for row in &self.rows { + if row.len() != self.columns.len() { + return Err(OdbcError::InvalidData); + } + bytes = bytes.checked_add(size_of::>()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?; + for value in row { + let size = match value { + Value::Empty | Value::Bool(_) | Value::Date(_) | Value::Time(_) | Value::Timestamp(_) | Value::TimestampWithOffset(_) => 0, + Value::Number(n) if n.is_finite() => 0, + Value::Text(s) if s.len() <= limits.cell_bytes => s.len() + 2 * size_of::(), + Value::Text(_) => return Err(OdbcError::Limit("A result cell exceeds the supported size.")), + _ => return Err(OdbcError::InvalidData), + }; + bytes = bytes.checked_add(size + size_of::()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?; + if bytes > limits.bytes { + return Err(OdbcError::Limit("The result exceeds the supported size.")); + } + } + } + if bytes > limits.bytes { + return Err(OdbcError::Limit("The result exceeds the supported size.")); + } + Ok(bytes) + } +} + +#[derive(Clone, Debug, PartialEq, Eq, Serialize, Deserialize)] +#[serde(rename_all = "camelCase")] +pub struct Sources { + pub data_sources: Vec, + pub drivers: Vec, +} + +#[derive(Clone, Debug, PartialEq, Eq, thiserror::Error)] +pub enum OdbcError { + #[error("ODBC is available only in the Windows desktop build.")] + Unavailable, + #[error("The connection settings are invalid.")] + InvalidConnection, + #[error("The ODBC connection could not be completed.")] + ConnectionFailed, + #[error("The ODBC query failed. Check the SQL and database permissions.")] + QueryFailed, + #[error("ODBC operation failed (SQLSTATE {0}). Check the driver, connection and query.")] + DriverState(String), + #[error("Use a supported PostgreSQL, SQL Server, MySQL, MariaDB, Oracle or SQLite3 ODBC driver.")] + UnsupportedDatabase, + #[error("Use one SELECT, WITH, VALUES or TABLE query without internal semicolons, write/control keywords or executable comments.")] + InvalidQuery, + #[error("{0}")] + Limit(&'static str), + #[error("The driver returned unsupported or malformed data.")] + InvalidData, + #[error("The operation was cancelled.")] + Cancelled, +} + +pub trait Backend: Send + Sync + 'static { + fn sources(&self) -> Result; + fn test(&self, connection: &Connection) -> Result<(), OdbcError>; + fn query(&self, connection: &Connection, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result; +} + +#[derive(Default)] +pub struct NativeBackend; + +impl Backend for NativeBackend { + fn sources(&self) -> Result { + native::sources() + } + fn test(&self, connection: &Connection) -> Result<(), OdbcError> { + native::test(connection) + } + fn query(&self, connection: &Connection, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result { + native::query(connection, sql, limits, preview, cancelled) + } +} + +pub const fn available() -> bool { + cfg!(target_os = "windows") +} + +pub fn validate_query(sql: &str) -> Result<(), OdbcError> { + let body = sql.trim().strip_suffix(';').unwrap_or(sql.trim()).trim(); + // shortcut: semicolons inside literals are refused; use a SQL parser if multi-statement-aware editing is added. + if body.is_empty() + || body.len() > 1_000_000 + || body.contains(';') + || body.chars().any(|c| c.is_control() && !matches!(c, '\r' | '\n' | '\t')) + || !body + .split(|c: char| !c.is_ascii_alphabetic() && c != '_') + .next() + .is_some_and(|word| ["select", "with", "values", "table"].iter().any(|keyword| word.eq_ignore_ascii_case(keyword))) + { + return Err(OdbcError::InvalidQuery); + } + Ok(()) +} + +pub fn decode(kind: ColumnKind, value: &str) -> Result { + let exact = || Value::text(value); + Ok(match kind { + ColumnKind::Text => exact(), + ColumnKind::Date => Value::Date(value.parse().map_err(|_| OdbcError::InvalidData)?), + ColumnKind::Time => Value::Time(value.parse().map_err(|_| OdbcError::InvalidData)?), + ColumnKind::Timestamp => Value::Timestamp(NaiveDateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f").map_err(|_| OdbcError::InvalidData)?), + ColumnKind::TimestampWithOffset => Value::TimestampWithOffset( + DateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f %:z") + .or_else(|_| DateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S%.f%:z")) + .map_err(|_| OdbcError::InvalidData)?, + ), + ColumnKind::Integer => { + let number = value.parse::().map_err(|_| OdbcError::InvalidData)?; + if number.unsigned_abs() < 1_000_000_000_000_000 { Value::Number(number as f64) } else { exact() } + } + ColumnKind::Decimal => { + let significant = value.trim_start_matches(['+', '-']).split(['e', 'E']).next().unwrap_or(value); + let digits = significant.chars().filter(|c| c.is_ascii_digit()).skip_while(|c| *c == '0').collect::().trim_end_matches('0').len(); + match value.parse::() { + Ok(n) if n.is_finite() && digits <= 15 && (n != 0.0 || digits == 0) => Value::Number(n), + Ok(_) => exact(), + Err(_) => return Err(OdbcError::InvalidData), + } + } + ColumnKind::Float => { + let number = value.parse::().map_err(|_| OdbcError::InvalidData)?; + if number.is_finite() { Value::Number(number) } else { exact() } + } + ColumnKind::Boolean => match value.to_ascii_lowercase().as_str() { + "1" | "-1" | "true" | "t" | "yes" => Value::Bool(true), + "0" | "false" | "f" | "no" => Value::Bool(false), + _ => return Err(OdbcError::InvalidData), + }, + }) +} + +#[cfg(not(target_os = "windows"))] +mod native { + use super::*; + pub fn sources() -> Result { + Err(OdbcError::Unavailable) + } + pub fn test(_: &Connection) -> Result<(), OdbcError> { + Err(OdbcError::Unavailable) + } + pub fn query(_: &Connection, _: &str, _: Limits, _: bool, _: &AtomicBool) -> Result { + Err(OdbcError::Unavailable) + } +} diff --git a/crates/odbc/src/native.rs b/crates/odbc/src/native.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/src/native.rs @@ -0,0 +1,221 @@ +use std::{ + mem::size_of, + sync::{ + OnceLock, + atomic::{AtomicBool, Ordering}, + }, +}; + +use odbc_api::{ + ConnectionOptions, Cursor, DataType, Environment, ResultSetMetadata, + buffers::Indicator, + parameter::{VarCell, WideText}, +}; + +use crate::{Column, ColumnKind, Connection as Settings, DataSet, Limits, OdbcError, Sources, Value, decode}; + +mod profile; +use profile::Driver; + +fn env() -> Result<&'static Environment, OdbcError> { + static ENV: OnceLock> = OnceLock::new(); + ENV.get_or_init(|| Environment::new().map_err(|_| OdbcError::Unavailable)).as_ref().map_err(Clone::clone) +} + +pub fn sources() -> Result { + let env = env()?; + let data_sources = env.data_sources().map_err(|_| OdbcError::Unavailable)?.into_iter().map(|s| s.server_name).collect(); + let drivers = env.drivers().map_err(|_| OdbcError::Unavailable)?.into_iter().map(|d| d.description).collect(); + Ok(Sources { data_sources, drivers }) +} + +fn connect<'a>(env: &'a Environment, settings: &Settings) -> Result<(odbc_api::Connection<'a>, Driver), OdbcError> { + let attrs = profile::attributes(settings)?; + let (key, name) = profile::selector(&attrs)?; + let description = if key == "DSN" { + env.data_sources() + .map_err(|_| OdbcError::Unavailable)? + .into_iter() + .find(|source| source.server_name.eq_ignore_ascii_case(name)) + .ok_or(OdbcError::ConnectionFailed)? + .driver + } else { + env.drivers() + .map_err(|_| OdbcError::Unavailable)? + .into_iter() + .find(|driver| driver.description.eq_ignore_ascii_case(name)) + .ok_or(OdbcError::ConnectionFailed)? + .description + }; + let driver = Driver::from_name(&description)?; + let options = ConnectionOptions { login_timeout_sec: Some(15), ..Default::default() }; + let connection_string = profile::connection_string(settings, &attrs, driver)?; + let connection = env.connect_with_connection_string(&connection_string, options).map_err(driver_error)?; + if !driver.accepts(&connection.database_management_system_name().map_err(driver_error)?) { + return Err(OdbcError::UnsupportedDatabase); + } + Ok((connection, driver)) +} + +fn driver_error(error: odbc_api::Error) -> OdbcError { + if let odbc_api::Error::Diagnostics { record, .. } = error + && record.state.0.iter().all(u8::is_ascii_alphanumeric) + && let Ok(state) = std::str::from_utf8(&record.state.0) + { + return OdbcError::DriverState(state.into()); + } + OdbcError::QueryFailed +} + +fn begin(connection: &odbc_api::Connection<'_>, driver: Driver) -> Result<(), OdbcError> { + connection.set_autocommit(false).map_err(driver_error)?; + if let Some(sql) = driver.begin_sql() { + connection.execute(sql, (), Some(60)).map_err(driver_error)?; + } + Ok(()) +} + +pub fn test(settings: &Settings) -> Result<(), OdbcError> { + let (connection, driver) = connect(env()?, settings)?; + begin(&connection, driver)?; + drop(connection.execute(driver.test_sql(), (), Some(15)).map_err(driver_error)?); + connection.rollback().map_err(driver_error) +} + +fn column_kind(driver: Driver, data_type: DataType) -> Result { + Ok(match data_type { + DataType::Integer | DataType::SmallInt | DataType::BigInt | DataType::TinyInt => ColumnKind::Integer, + DataType::Numeric { .. } | DataType::Decimal { .. } => ColumnKind::Decimal, + DataType::Float { .. } | DataType::Real | DataType::Double => ColumnKind::Float, + DataType::Bit => ColumnKind::Boolean, + DataType::Date => ColumnKind::Date, + DataType::Time { .. } => ColumnKind::Time, + DataType::Timestamp { .. } => ColumnKind::Timestamp, + DataType::Char { .. } + | DataType::WChar { .. } + | DataType::Varchar { .. } + | DataType::WVarchar { .. } + | DataType::LongVarchar { .. } + | DataType::WLongVarchar { .. } => ColumnKind::Text, + DataType::Other { data_type, .. } if data_type.0 == -11 => ColumnKind::Text, // SQL_GUID + DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -154 => ColumnKind::Time, + DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -155 => ColumnKind::TimestampWithOffset, + DataType::Other { data_type, .. } if driver == Driver::SqlServer && data_type.0 == -152 => ColumnKind::Text, // SQL_SS_XML + DataType::Unknown if driver == Driver::Sqlite => ColumnKind::Text, + _ => return Err(OdbcError::InvalidData), + }) +} + +pub fn query(settings: &Settings, sql: &str, limits: Limits, preview: bool, cancelled: &AtomicBool) -> Result { + let limits = limits.validate()?; + crate::validate_query(sql)?; + if cancelled.load(Ordering::Relaxed) { + return Err(OdbcError::Cancelled); + } + let (connection, driver) = connect(env()?, settings)?; + let sql = driver.query_sql(sql)?; + begin(&connection, driver)?; + let mut cursor = connection.execute(&sql, (), Some(60)).map_err(driver_error)?.ok_or(OdbcError::InvalidData)?; + let count = cursor.num_result_cols().map_err(|_| OdbcError::InvalidData)?; + if count <= 0 || count as usize > limits.columns { + return Err(OdbcError::Limit("The result has too many columns.")); + } + let mut columns = Vec::with_capacity(count as usize); + for i in 1..=count as u16 { + let kind = column_kind(driver, cursor.col_data_type(i).map_err(|_| OdbcError::InvalidData)?)?; + let name = cursor.col_name(i).map_err(|_| OdbcError::InvalidData)?; + if name.len() > 1024 || name.contains('\0') { + return Err(OdbcError::InvalidData); + } + columns.push(Column { name, kind }); + } + let mut rows = Vec::new(); + let max_rows = if preview { limits.rows.min(100) } else { limits.rows }; + let mut bytes = columns.iter().map(|c| c.name.len() + size_of::()).sum::(); + let mut truncated = false; + let mut buffer = vec![0u16; limits.cell_bytes.saturating_add(1)]; + while let Some(mut row) = cursor.next_row().map_err(|_| OdbcError::InvalidData)? { + if cancelled.load(Ordering::Relaxed) { + return Err(OdbcError::Cancelled); + } + if rows.len() == max_rows { + if !preview { + return Err(OdbcError::Limit("The result exceeds the row limit. Restrict the query or increase maxRows.")); + } + truncated = true; + break; + } + bytes = bytes + .checked_add(size_of::>() + columns.len() * size_of::()) + .ok_or(OdbcError::Limit("The result exceeds the supported size."))?; + if bytes > limits.bytes { + return Err(OdbcError::Limit("The result exceeds the supported size.")); + } + let mut values = Vec::with_capacity(columns.len()); + for (i, column) in columns.iter().enumerate() { + let mut cell = VarCell::<&mut [u16], WideText>::from_buffer(buffer.as_mut_slice(), Indicator::Null); + row.get_data((i + 1) as u16, &mut cell).map_err(|_| OdbcError::InvalidData)?; + if !cell.is_complete() { + return Err(OdbcError::Limit("A result cell exceeds the supported size.")); + } + let value = match cell.as_slice() { + None => Value::Empty, + Some(raw) => { + let text = String::from_utf16(raw).map_err(|_| OdbcError::InvalidData)?; + if text.len() > limits.cell_bytes { + return Err(OdbcError::Limit("A result cell exceeds the supported size.")); + } + decode(column.kind, &text)? + } + }; + if let Value::Text(text) = &value { + bytes = bytes.checked_add(text.len() + 2 * size_of::()).ok_or(OdbcError::Limit("The result exceeds the supported size."))?; + } + if bytes > limits.bytes { + return Err(OdbcError::Limit("The result exceeds the supported size.")); + } + values.push(value); + } + rows.push(values); + } + let dataset = DataSet { columns, rows, truncated }; + drop(cursor); + connection.rollback().map_err(|_| OdbcError::QueryFailed)?; + dataset.validate(limits)?; + Ok(dataset) +} + +#[cfg(test)] +mod tests { + use super::*; + #[test] + fn vendor_types_preserve_identifiers_offsets_and_binary_rejection() { + let custom = |code| DataType::Other { data_type: odbc_api::sys::SqlDataType(code), column_size: None, decimal_digits: 0 }; + assert_eq!(column_kind(Driver::SqlServer, custom(-11)).unwrap(), ColumnKind::Text); + assert_eq!(column_kind(Driver::SqlServer, custom(-154)).unwrap(), ColumnKind::Time); + assert_eq!(column_kind(Driver::SqlServer, custom(-155)).unwrap(), ColumnKind::TimestampWithOffset); + assert_eq!(column_kind(Driver::SqlServer, custom(-152)).unwrap(), ColumnKind::Text); + assert!(column_kind(Driver::Oracle, custom(-155)).is_err()); + assert_eq!(column_kind(Driver::Sqlite, DataType::Unknown).unwrap(), ColumnKind::Text); + assert!(column_kind(Driver::SqlServer, DataType::Unknown).is_err()); + for driver in [Driver::Postgres, Driver::SqlServer, Driver::MySql, Driver::MariaDb, Driver::Oracle, Driver::Sqlite] { + assert!(column_kind(driver, DataType::Binary { length: None }).is_err()); + assert_eq!(column_kind(driver, DataType::Numeric { precision: 38, scale: 20 }).unwrap(), ColumnKind::Decimal); + } + } + + #[test] + fn credentials_escape_braces_and_separators_without_quoting_the_dsn() { + let settings = Settings { dsn: Some("Postgres".into()), user: "reader".into(), password: "{pass};+".into(), connection_string: None }; + let value = profile::connection_string(&settings, &profile::attributes(&settings).unwrap(), Driver::Postgres).unwrap(); + assert!(value.contains("UID=reader;PWD={{pass}};+};DSN=Postgres;")); + assert!(value.starts_with("BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};")); + let unsafe_settings = Settings { + connection_string: Some("Driver={PostgreSQL Unicode};CX=ff;A0=0;B2=1;B3=1;A6={DELETE FROM x};B6=0;BI=8;D6=8".into()), + ..Default::default() + }; + let value = profile::connection_string(&unsafe_settings, &profile::attributes(&unsafe_settings).unwrap(), Driver::Postgres).unwrap(); + assert!(value.contains("D6=8;BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};")); + assert!(value.ends_with("UseDeclareFetch=1;Fetch=100;BI=-5;NumericAS=2;LFConversion=0;IgnoreTimeout=0;")); + } +} diff --git a/crates/odbc/src/native/profile.rs b/crates/odbc/src/native/profile.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/src/native/profile.rs @@ -0,0 +1,405 @@ +use crate::{Connection as Settings, OdbcError}; +use sqlparser::{ + dialect::{Dialect, MsSqlDialect, MySqlDialect, OracleDialect, PostgreSqlDialect, SQLiteDialect}, + tokenizer::{Token, Tokenizer}, +}; + +#[derive(Clone, Copy, Debug, PartialEq, Eq)] +pub(super) enum Driver { + Postgres, + SqlServer, + MySql, + MariaDb, + Oracle, + Sqlite, +} + +impl Driver { + pub fn from_name(name: &str) -> Result { + let name = name.to_ascii_lowercase(); + if name.contains("postgresql") { + Ok(Self::Postgres) + } else if name.contains("sql server") { + Ok(Self::SqlServer) + } else if name.contains("mariadb") { + Ok(Self::MariaDb) + } else if name.contains("mysql") { + Ok(Self::MySql) + } else if name.contains("oracle") { + Ok(Self::Oracle) + } else if name.contains("sqlite3") || name == "sqlite" { + Ok(Self::Sqlite) + } else { + Err(OdbcError::UnsupportedDatabase) + } + } + + pub fn accepts(self, dbms: &str) -> bool { + let dbms = dbms.to_ascii_lowercase(); + match self { + Self::Postgres => dbms.contains("postgresql"), + Self::SqlServer => dbms.contains("sql server"), + Self::MySql | Self::MariaDb => dbms.contains("mysql") || dbms.contains("mariadb"), + Self::Oracle => dbms.contains("oracle"), + Self::Sqlite => dbms.contains("sqlite"), + } + } + + fn options(self) -> &'static str { + match self { + Self::Postgres => { + // psqlODBC's read-only flag blocks SET TRANSACTION too; the server enforces our read-only transaction. + "BoolsAsChar=0;ReadOnly=0;CommLog=0;Debug=0;ConnSettings={};UseDeclareFetch=1;Fetch=100;BI=-5;NumericAS=2;LFConversion=0;IgnoreTimeout=0;" + } + Self::SqlServer => "ApplicationIntent=ReadOnly;MARS_Connection=No;", + Self::MySql => { + "OPTION=3145744;INITSTMT={};STMT={};NO_CACHE=1;FORWARD_CURSOR=1;MULTI_STATEMENTS=0;NO_TRANSACTIONS=0;AUTO_RECONNECT=0;LOG_QUERY=0;USE_MYCNF=0;CHARSET=utf8mb4;" + } + Self::MariaDb => "OPTION=3145744;OPTIONS=3145744;INITSTMT={};NO_CACHE=1;FORWARDONLY=1;AUTO_RECONNECT=0;USE_MYCNF=0;CHARSET=utf8mb4;", + // DBA=R starts a second read-only transaction in the driver, conflicting with SET TRANSACTION. + Self::Oracle => "DBA=W;QTO=T;FBS=65536;BNF=F;BTD=F;FEN=F;EXC=F;MTS=T;", + Self::Sqlite => "NoCreat=1;StepAPI=1;NoTXN=0;NoWCHAR=0;LoadExt=;Timeout=15000;", + } + } + + pub fn test_sql(self) -> &'static str { + if self == Self::Oracle { "SELECT 1 FROM DUAL" } else { "SELECT 1" } + } + + pub fn begin_sql(self) -> Option<&'static str> { + match self { + Self::Postgres | Self::Oracle => Some("SET TRANSACTION READ ONLY"), + Self::MySql | Self::MariaDb => Some("START TRANSACTION READ ONLY"), + Self::Sqlite => Some("PRAGMA query_only=ON"), + // SQL Server has no read-only transaction mode; server permissions must restrict writes. + Self::SqlServer => None, + } + } + + pub fn query_sql(self, sql: &str) -> Result { + crate::validate_query(sql)?; + let sql = sql.trim().strip_suffix(';').unwrap_or(sql.trim()); + if matches!(self, Self::MySql | Self::MariaDb) && (sql.contains("/*!") || sql.to_ascii_uppercase().contains("/*M!")) { + return Err(OdbcError::InvalidQuery); + } + let dialect: &dyn Dialect = match self { + Self::Postgres => &PostgreSqlDialect {}, + Self::SqlServer => &MsSqlDialect {}, + Self::MySql | Self::MariaDb => &MySqlDialect {}, + Self::Oracle => &OracleDialect {}, + Self::Sqlite => &SQLiteDialect {}, + }; + // A conservative token guard, not a SQL sandbox. Database permissions remain authoritative. + for token in Tokenizer::new(dialect, sql).tokenize().map_err(|_| OdbcError::InvalidQuery)? { + if let Token::Word(word) = token + && word.quote_style.is_none() + && matches!( + word.value.to_ascii_uppercase().as_str(), + "INSERT" + | "UPDATE" + | "DELETE" + | "MERGE" + | "INTO" + | "CREATE" + | "ALTER" + | "DROP" + | "TRUNCATE" + | "EXEC" + | "EXECUTE" + | "CALL" + | "GRANT" + | "REVOKE" + | "DENY" + | "SET" + | "RESET" + | "COMMIT" + | "ROLLBACK" + | "SAVEPOINT" + | "RELEASE" + | "BEGIN" + | "USE" + | "GO" + | "DBCC" + | "BACKUP" + | "RESTORE" + | "RECONFIGURE" + | "SHUTDOWN" + | "KILL" + | "WAITFOR" + | "ATTACH" + | "DETACH" + | "VACUUM" + | "PRAGMA" + | "COPY" + | "LOAD" + | "LOCK" + | "UNLOCK" + ) + { + return Err(OdbcError::InvalidQuery); + } + } + // psqlODBC bypasses cursor fetching for bare TABLE/VALUES; other dialects retain their syntax. + Ok(if self == Self::Postgres { format!("SELECT * FROM ({sql}\n) AS gridcraft_odbc_result") } else { sql.to_string() }) + } +} + +pub(super) fn attributes(settings: &Settings) -> Result, OdbcError> { + settings.validate()?; + if let Some(dsn) = &settings.dsn { + return Ok(vec![("DSN".into(), dsn.clone())]); + } + let mut chars = settings.connection_string.as_deref().ok_or(OdbcError::InvalidConnection)?.chars().peekable(); + let mut result = Vec::new(); + while chars.peek().is_some() { + while chars.peek().is_some_and(|c| c.is_whitespace() || *c == ';') { + chars.next(); + } + if chars.peek().is_none() { + break; + } + let mut key = String::new(); + loop { + match chars.next() { + Some('=') => break, + Some(c) if c.is_ascii_alphanumeric() || matches!(c, '_' | '-' | ' ') => key.push(c), + _ => return Err(OdbcError::InvalidConnection), + } + } + let key = key.trim().to_ascii_uppercase(); + if key.is_empty() || key.len() > 128 || matches!(key.as_str(), "SAVEFILE" | "FILEDSN") || result.len() == 128 { + return Err(OdbcError::InvalidConnection); + } + while chars.peek().is_some_and(|c| c.is_whitespace()) { + chars.next(); + } + let value = if chars.peek() == Some(&'{') { + chars.next(); + let mut value = String::new(); + loop { + match chars.next() { + Some('}') if chars.peek() == Some(&'}') => { + chars.next(); + value.push('}'); + } + Some('}') => break, + Some(c) => value.push(c), + None => return Err(OdbcError::InvalidConnection), + } + } + while chars.peek().is_some_and(|c| c.is_whitespace()) { + chars.next(); + } + if chars.peek().is_some_and(|c| *c != ';') { + return Err(OdbcError::InvalidConnection); + } + value + } else { + chars.by_ref().take_while(|c| *c != ';').collect::().trim().to_string() + }; + result.push((key, value)); + } + Ok(result) +} + +pub(super) fn selector(attrs: &[(String, String)]) -> Result<(&str, &str), OdbcError> { + let mut selectors = attrs.iter().filter(|(key, _)| matches!(key.as_str(), "DSN" | "DRIVER")); + let (key, value) = selectors.next().ok_or(OdbcError::InvalidConnection)?; + if value.is_empty() || selectors.next().is_some() { + return Err(OdbcError::InvalidConnection); + } + if key == "DSN" { + Settings { dsn: Some(value.clone()), ..Default::default() }.validate()?; + } + Ok((key, value)) +} + +pub(super) fn connection_string(settings: &Settings, attrs: &[(String, String)], driver: Driver) -> Result { + let escaped = |value: &str| { + if value.starts_with('{') || value.trim() != value { + format!("{{{}}}", value.replace('}', "}}")) + } else { + odbc_api::escape_attribute_value(value).into_owned() + } + }; + let safety = driver.options(); + let mut credentials = String::new(); + if !settings.user.is_empty() { + credentials.push_str(&format!("UID={};", escaped(&settings.user))); + } + if !settings.password.is_empty() { + credentials.push_str(&format!("PWD={};", escaped(&settings.password))); + } + // Cover both first- and last-occurrence resolution, including options read from a DSN. + let mut value = format!("{safety}{credentials}"); + for (key, entry) in attrs { + let overridden = safety.split(';').filter_map(|s| s.split_once('=')).any(|(option, _)| option.eq_ignore_ascii_case(key)); + if overridden + || (!settings.user.is_empty() && matches!(key.as_str(), "UID" | "USER")) + || (!settings.password.is_empty() && matches!(key.as_str(), "PWD" | "PASSWORD")) + { + continue; + } + if key == "DSN" { + value.push_str(&format!("DSN={entry};")); + } else if key == "DRIVER" { + value.push_str(&format!("DRIVER={{{}}};", entry.replace('}', "}}"))); + } else { + value.push_str(&format!("{key}={};", escaped(entry))); + } + } + value.push_str(&credentials); + value.push_str(safety); + // SQLDriverConnectW takes a signed 16-bit length; odbc-api panics if it cannot convert it. + if value.encode_utf16().count() > i16::MAX as usize { + return Err(OdbcError::InvalidConnection); + } + Ok(value) +} + +#[cfg(test)] +mod tests { + use super::*; + + #[test] + fn escaped_connections_fit_the_odbc_smallint_length() { + for password in ["x".repeat(16_384), "};".repeat(8192)] { + let settings = Settings { dsn: Some("Test".into()), password, ..Default::default() }; + assert!(connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).is_err()); + } + let settings = Settings { dsn: Some("Test".into()), password: "🦊".repeat(4096), ..Default::default() }; + let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap(); + assert!(value.len() > i16::MAX as usize); + assert!(value.encode_utf16().count() <= i16::MAX as usize); + } + + #[test] + fn credentials_keep_leading_braces_and_edge_spaces() { + for password in ["{pass}", " pass ", "{odbc};+test"] { + let settings = Settings { dsn: Some("Test".into()), password: password.into(), ..Default::default() }; + let raw = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap(); + let parsed = attributes(&Settings { connection_string: Some(raw), ..Default::default() }).unwrap(); + let passwords: Vec<_> = parsed.iter().filter(|(key, _)| key == "PWD").map(|(_, value)| value.as_str()).collect(); + assert_eq!(passwords, [password, password]); + } + } + + #[test] + fn ordinary_attributes_are_not_changed_into_braced_literals() { + let settings = Settings { + connection_string: Some("Driver={PostgreSQL Unicode(x64)};Servername=127.0.0.1;Port=55435;Database=postgres;SSLmode=disable".into()), + user: "gridcraft_reader".into(), + password: "test".into(), + ..Default::default() + }; + let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Postgres).unwrap(); + for expected in ["UID=gridcraft_reader;", "PWD=test;", "SERVERNAME=127.0.0.1;", "PORT=55435;", "DATABASE=postgres;", "SSLMODE=disable;"] { + assert!(value.contains(expected), "missing {expected}"); + } + assert!(value.contains("DRIVER={PostgreSQL Unicode(x64)};")); + } + + #[test] + fn sqlite_disables_extensions_with_an_unquoted_empty_value() { + assert!(Driver::Sqlite.options().contains("LoadExt=;")); + } + + #[test] + fn oracle_starts_read_only_transactions_without_a_conflicting_driver_mode() { + let settings = Settings { connection_string: Some("Driver={Oracle in instantclient_23_26};DBA=R".into()), ..Default::default() }; + let value = connection_string(&settings, &attributes(&settings).unwrap(), Driver::Oracle).unwrap(); + assert!(!value.contains("DBA=R;")); + assert!(value.starts_with("DBA=W;") && value.ends_with(Driver::Oracle.options())); + assert_eq!(Driver::Oracle.begin_sql(), Some("SET TRANSACTION READ ONLY")); + } + + #[test] + fn supported_drivers_have_database_specific_read_settings() { + for (name, driver, test_sql, begin_sql) in [ + ("PostgreSQL Unicode(x64)", Driver::Postgres, "SELECT 1", Some("SET TRANSACTION READ ONLY")), + ("ODBC Driver 18 for SQL Server", Driver::SqlServer, "SELECT 1", None), + ("MySQL ODBC 9.0 Unicode Driver", Driver::MySql, "SELECT 1", Some("START TRANSACTION READ ONLY")), + ("MariaDB ODBC 3.2 Driver", Driver::MariaDb, "SELECT 1", Some("START TRANSACTION READ ONLY")), + ("Oracle in instantclient_23_9", Driver::Oracle, "SELECT 1 FROM DUAL", Some("SET TRANSACTION READ ONLY")), + ("SQLite3 ODBC Driver", Driver::Sqlite, "SELECT 1", Some("PRAGMA query_only=ON")), + ] { + assert_eq!(Driver::from_name(name).unwrap(), driver); + assert_eq!(driver.test_sql(), test_sql); + assert_eq!(driver.begin_sql(), begin_sql); + } + assert!(Driver::from_name("Unknown ODBC Driver").is_err()); + assert!(Driver::MySql.accepts("MariaDB")); + assert!(Driver::MariaDb.accepts("MySQL")); + assert!(!Driver::Oracle.accepts("PostgreSQL")); + } + + #[test] + fn driver_options_are_isolated_and_credentials_override_aliases() { + let connection = Settings { + connection_string: Some( + "Driver={MySQL ODBC 9.0 Unicode Driver};PASSWORD=old;USER=old;INITSTMT={DELETE FROM t};OPTION=67108864;SSLCA={C:\\certs\\a;b.pem}" + .into(), + ), + user: "reader".into(), + password: "{pass};+".into(), + ..Default::default() + }; + let attrs = attributes(&connection).unwrap(); + assert_eq!(selector(&attrs).unwrap(), ("DRIVER", "MySQL ODBC 9.0 Unicode Driver")); + let value = connection_string(&connection, &attrs, Driver::MySql).unwrap(); + assert!(!value.contains("DELETE FROM t")); + assert!(!value.contains("PASSWORD={old}")); + assert!(value.contains("SSLCA={C:\\certs\\a;b.pem};")); + assert!(value.contains("PWD={{pass}};+};")); + assert!(value.contains("NO_CACHE=1;")); + assert!(!value.contains("BoolsAsChar")); + for driver in [Driver::SqlServer, Driver::Oracle, Driver::Sqlite, Driver::MariaDb] { + assert!(!connection_string(&connection, &attrs, driver).unwrap().contains("BoolsAsChar")); + } + } + + #[test] + fn ambiguous_or_malformed_connection_selectors_are_rejected() { + for raw in [ + "Driver={SQLite3 ODBC Driver};DSN=Other", + "DSN=One;dsn=Two", + "FILEDSN=hidden.dsn", + "Driver={unclosed", + "Driver={x}junk", + "Driver=x;SAVEFILE={hidden.dsn}", + ] { + let settings = Settings { connection_string: Some(raw.into()), ..Default::default() }; + assert!(attributes(&settings).and_then(|attrs| selector(&attrs).map(|_| ())).is_err(), "{raw}"); + } + let attrs = attributes(&Settings { dsn: Some("Reporting".into()), ..Default::default() }).unwrap(); + assert_eq!(selector(&attrs).unwrap(), ("DSN", "Reporting")); + } + + #[test] + fn query_guards_preserve_dialects_and_reject_writes() { + for (driver, sql) in [ + (Driver::SqlServer, "WITH t AS (SELECT 1 AS [value]) SELECT [value] FROM t ORDER BY [value]"), + (Driver::SqlServer, "SELECT CASE WHEN 1=1 THEN 'DELETE' ELSE 'INTO' END AS [update] /* DELETE */"), + (Driver::MySql, "SELECT `value`, 'delete' FROM example LIMIT 2"), + (Driver::Oracle, "SELECT q'[delete]' AS value FROM DUAL"), + (Driver::Sqlite, "WITH t AS (SELECT 1 AS value) SELECT value FROM t"), + (Driver::Postgres, "VALUES (1)"), + ] { + let prepared = driver.query_sql(sql).unwrap(); + if driver != Driver::Postgres { + assert_eq!(prepared, sql); + } + } + for (driver, sql) in [ + (Driver::SqlServer, "WITH t AS (SELECT 1 AS n) DELETE FROM example"), + (Driver::SqlServer, "SELECT 1 INTO copied"), + (Driver::SqlServer, "SELECT 1 EXEC('DELETE FROM example')"), + (Driver::MySql, "SELECT 1 INTO OUTFILE '/tmp/result'"), + (Driver::MariaDb, "SELECT 1 /*!50000 INTO OUTFILE '/tmp/result' */"), + (Driver::MySql, "SELECT 1 /*M! INTO OUTFILE '/tmp/result' */"), + (Driver::Sqlite, "WITH t AS (SELECT 1) UPDATE example SET n=1"), + ] { + assert!(driver.query_sql(sql).is_err(), "{sql}"); + } + } +} diff --git a/crates/odbc/src/tests.rs b/crates/odbc/src/tests.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/src/tests.rs @@ -0,0 +1,80 @@ +use super::*; + +#[test] +fn odbc_values_keep_text_null_boolean_and_numeric_types() { + assert_eq!(decode(ColumnKind::Text, "=SUM(A1:A2)").unwrap(), Value::text("=SUM(A1:A2)")); + assert_eq!(decode(ColumnKind::Text, "0012").unwrap(), Value::text("0012")); + assert_eq!(decode(ColumnKind::Integer, "42").unwrap(), Value::Number(42.0)); + assert_eq!(decode(ColumnKind::Boolean, "1").unwrap(), Value::Bool(true)); + assert_eq!(decode(ColumnKind::Boolean, "false").unwrap(), Value::Bool(false)); + assert!(decode(ColumnKind::Boolean, "not a boolean").is_err()); + for kind in [ColumnKind::Integer, ColumnKind::Decimal, ColumnKind::Float] { + assert!(decode(kind, "not a number").is_err()); + } +} + +#[test] +fn temporal_values_are_typed_and_preserve_fractional_seconds_and_offsets() { + let date = NaiveDate::from_ymd_opt(2024, 2, 29).unwrap(); + let time = NaiveTime::from_hms_nano_opt(12, 34, 56, 123_456_789).unwrap(); + assert_eq!(decode(ColumnKind::Date, "2024-02-29").unwrap(), Value::Date(date)); + assert_eq!(decode(ColumnKind::Time, "12:34:56.123456789").unwrap(), Value::Time(time)); + assert_eq!(decode(ColumnKind::Timestamp, "2024-02-29 12:34:56.123456789").unwrap(), Value::Timestamp(date.and_time(time))); + for text in ["2024-02-29 12:34:56.123456789 +03:00", "2024-02-29 12:34:56.123456789+03:00"] { + let Value::TimestampWithOffset(value) = decode(ColumnKind::TimestampWithOffset, text).unwrap() else { panic!("expected typed timestamp") }; + assert_eq!(value.naive_local(), date.and_time(time)); + assert_eq!(value.offset().local_minus_utc(), 10800); + } + for (kind, text) in [(ColumnKind::Date, "2023-02-29"), (ColumnKind::Time, "25:00:00"), (ColumnKind::Timestamp, "invalid")] { + assert!(decode(kind, text).is_err()); + } + let value = Value::Timestamp(date.and_time(time)); + assert_eq!(serde_json::from_str::(&serde_json::to_string(&value).unwrap()).unwrap(), value); +} + +#[test] +fn odbc_precision_is_not_silently_lost() { + for text in ["9007199254740993", "9223372036854775807"] { + assert_eq!(decode(ColumnKind::Integer, text).unwrap(), Value::text(text)); + } + assert_eq!(decode(ColumnKind::Decimal, "0.12345678901234567890").unwrap(), Value::text("0.12345678901234567890")); + assert_eq!(decode(ColumnKind::Decimal, "12.34000").unwrap(), Value::Number(12.34)); + assert_eq!(decode(ColumnKind::Float, "NaN").unwrap(), Value::text("NaN")); + assert_eq!(decode(ColumnKind::Decimal, "1e-400").unwrap(), Value::text("1e-400")); +} + +#[test] +fn queries_are_single_read_operations_and_result_memory_is_bounded() { + for sql in ["SELECT 1;", "WITH sample AS (SELECT 1) SELECT * FROM sample", "VALUES (1)"] { + assert!(validate_query(sql).is_ok()); + } + for sql in ["DELETE FROM sales", "SELECT 1; DELETE FROM sales", "COMMIT", "SELECT 1;;"] { + assert!(validate_query(sql).is_err()); + } + let dataset = + DataSet { columns: vec![Column { name: "n".into(), kind: ColumnKind::Integer }], rows: vec![vec![Value::Number(1.0)]], truncated: false }; + assert!(dataset.validate(Limits { bytes: 1, ..Default::default() }).is_err()); +} + +#[test] +fn odbc_limits_reject_invalid_and_oversized_data() { + assert!(Limits { rows: 0, ..Default::default() }.validate().is_err()); + assert!(Limits { rows: 100_001, ..Default::default() }.validate().is_err()); + let data = + DataSet { columns: vec![Column { name: "text".into(), kind: ColumnKind::Text }], rows: vec![vec![Value::text("=1")]], truncated: false }; + assert!(data.validate(Limits::default()).is_ok()); + assert!(data.validate(Limits { cell_bytes: 1, ..Default::default() }).is_err()); + let ragged = DataSet { rows: vec![vec![]], ..data }; + assert!(ragged.validate(Limits::default()).is_err()); +} + +#[test] +fn odbc_connections_validate_without_printing_credentials() { + let connection = + Connection { dsn: Some("Postgres".into()), connection_string: Some("PWD=hidden".into()), user: "reader".into(), password: "hidden".into() }; + assert!(connection.validate().is_err()); + assert!(!format!("{connection:?}").contains("hidden")); + assert!(Connection { dsn: Some("Postgres;PWD=hidden".into()), ..Default::default() }.validate().is_err()); + assert!(Connection { dsn: Some("a".repeat(33)), ..Default::default() }.validate().is_err()); + assert!(Connection { connection_string: Some("DSN=Postgres;SAVEFILE=credentials.dsn".into()), ..Default::default() }.validate().is_err()); +} diff --git a/crates/odbc/tests/databases.rs b/crates/odbc/tests/databases.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/tests/databases.rs @@ -0,0 +1,114 @@ +#![cfg(target_os = "windows")] +//! Each ignored test needs its own dedicated database/driver; no server objects are created. +use gridcraft_odbc::{Backend, ColumnKind, Connection, Limits, NativeBackend, OdbcError, Value, decode}; +use std::sync::atomic::AtomicBool; + +fn read_database(name: &str, sql: &str, oracle: bool) { + let prefix = format!("GRIDCRAFT_TEST_ODBC_{name}"); + let dsn = std::env::var(format!("{prefix}_DSN")).ok(); + let connection = Connection { + connection_string: if dsn.is_some() { + None + } else { + Some(std::env::var(format!("{prefix}_CONNECTION_STRING")).expect("dedicated test connection string")) + }, + dsn, + user: std::env::var(format!("{prefix}_USER")).unwrap_or_default(), + password: std::env::var(format!("{prefix}_PASSWORD")).unwrap_or_default(), + }; + let backend = NativeBackend; + backend.test(&connection).unwrap(); + let cancel = AtomicBool::new(false); + let data = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap(); + assert_eq!(data.rows.len(), 1); + let expected_number = + if name == "SQLITE" && data.columns[0].kind == gridcraft_odbc::ColumnKind::Text { Value::text("42") } else { Value::Number(42.0) }; + assert_eq!(data.rows[0][0], expected_number); + assert_eq!(data.rows[0][1], Value::text("=1")); + assert_eq!(data.rows[0][2], Value::text("ação 🦊")); + assert_eq!(data.rows[0][3], Value::text("9007199254740993")); + assert_eq!(data.rows[0][4], Value::Empty); + let temporal_sql = match name { + "SQLSERVER" => Some( + "SELECT CAST('2024-02-29' AS date), CAST('12:34:56.123456' AS time(6)), CAST('2024-02-29 12:34:56.123456' AS datetime2(6)), CAST('2024-02-29 12:34:56.123456 +03:00' AS datetimeoffset(6))", + ), + "MYSQL" | "MARIADB" => { + Some("SELECT CAST('2024-02-29' AS DATE), CAST('12:34:56.123456' AS TIME(6)), CAST('2024-02-29 12:34:56.123456' AS DATETIME(6))") + } + "ORACLE" => Some("SELECT TIMESTAMP '2024-02-29 12:34:56.123456' FROM DUAL"), + _ => None, + }; + if let Some(sql) = temporal_sql { + let dates = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap(); + let timestamp = decode(ColumnKind::Timestamp, "2024-02-29 12:34:56.123456").unwrap(); + if oracle { + assert_eq!(dates.rows[0][0], timestamp); + } else { + assert_eq!(dates.rows[0][0], decode(ColumnKind::Date, "2024-02-29").unwrap()); + assert_eq!(dates.rows[0][1], decode(ColumnKind::Time, "12:34:56.123456").unwrap()); + assert_eq!(dates.rows[0][2], timestamp); + if name == "SQLSERVER" { + assert_eq!(dates.rows[0][3], decode(ColumnKind::TimestampWithOffset, "2024-02-29 12:34:56.123456 +03:00").unwrap()); + } + } + } + let from = if oracle { " FROM DUAL" } else { "" }; + let two_rows = format!("SELECT 1 AS value{from} UNION ALL SELECT 2 AS value{from}"); + let preview = backend.query(&connection, &two_rows, Limits { rows: 1, ..Default::default() }, true, &cancel).unwrap(); + assert_eq!(preview.rows.len(), 1); + assert!(preview.truncated); + assert!(matches!(backend.query(&connection, &two_rows, Limits { rows: 1, ..Default::default() }, false, &cancel), Err(OdbcError::Limit(_)))); + let empty = format!("SELECT 1 AS value{from} WHERE 1=0"); + assert!(backend.query(&connection, &empty, Limits::default(), false, &cancel).unwrap().rows.is_empty()); + assert!(matches!(backend.query(&connection, sql, Limits::default(), false, &AtomicBool::new(true)), Err(OdbcError::Cancelled))); +} + +#[test] +#[ignore = "requires a dedicated SQLSERVER ODBC test connection"] +fn sql_server_reads_unicode_literals_and_exact_bigints() { + read_database( + "SQLSERVER", + "SELECT CAST(42 AS int) AS n, CAST('=1' AS nvarchar(10)) AS literal, N'ação 🦊' AS unicode_text, CAST(9007199254740993 AS bigint) AS identifier, CAST(NULL AS nvarchar(10)) AS optional_text", + false, + ); +} + +#[test] +#[ignore = "requires a dedicated MYSQL ODBC test connection"] +fn mysql_reads_unicode_literals_and_exact_bigints() { + read_database( + "MYSQL", + "SELECT CAST(42 AS SIGNED) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS SIGNED) AS identifier, NULL AS optional_text", + false, + ); +} + +#[test] +#[ignore = "requires a dedicated MARIADB ODBC test connection"] +fn mariadb_reads_unicode_literals_and_exact_bigints() { + read_database( + "MARIADB", + "SELECT CAST(42 AS SIGNED) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS SIGNED) AS identifier, NULL AS optional_text", + false, + ); +} + +#[test] +#[ignore = "requires a dedicated ORACLE ODBC test connection"] +fn oracle_reads_unicode_literals_and_exact_numbers() { + read_database( + "ORACLE", + "SELECT CAST(42 AS NUMBER(10)) AS n, '=1' AS literal, N'ação 🦊' AS unicode_text, CAST(9007199254740993 AS NUMBER(19)) AS identifier, CAST(NULL AS VARCHAR2(10)) AS optional_text FROM DUAL", + true, + ); +} + +#[test] +#[ignore = "requires a dedicated SQLITE ODBC test connection and existing database"] +fn sqlite_reads_unicode_literals_and_exact_integers() { + read_database( + "SQLITE", + "SELECT CAST(42 AS INTEGER) AS n, '=1' AS literal, 'ação 🦊' AS unicode_text, CAST(9007199254740993 AS INTEGER) AS identifier, CAST(NULL AS TEXT) AS optional_text", + false, + ); +} diff --git a/crates/odbc/tests/postgres.rs b/crates/odbc/tests/postgres.rs new file mode 100644 --- /dev/null +++ b/crates/odbc/tests/postgres.rs @@ -0,0 +1,60 @@ +#![cfg(target_os = "windows")] +//! Opt in with a dedicated test connection; all queries use synthetic expressions. +use gridcraft_odbc::{Backend, ColumnKind, Connection, Limits, NativeBackend, OdbcError, Value, decode}; +use std::sync::atomic::AtomicBool; + +#[test] +#[ignore = "requires GRIDCRAFT_TEST_ODBC_DSN or GRIDCRAFT_TEST_ODBC_CONNECTION_STRING"] +fn postgres_psqlodbc_reads_types_unicode_precision_and_read_only_transactions() { + let dsn = std::env::var("GRIDCRAFT_TEST_ODBC_DSN").ok(); + let connection = Connection { + connection_string: if dsn.is_some() { + None + } else { + Some(std::env::var("GRIDCRAFT_TEST_ODBC_CONNECTION_STRING").expect("test connection string")) + }, + dsn, + user: std::env::var("GRIDCRAFT_TEST_ODBC_USER").unwrap_or_default(), + password: std::env::var("GRIDCRAFT_TEST_ODBC_PASSWORD").unwrap_or_default(), + }; + let backend = NativeBackend; + backend.test(&connection).unwrap(); + if let Ok(connection_string) = std::env::var("GRIDCRAFT_TEST_ODBC_CONNECTION_STRING") { + let mut alternate = connection.clone(); + alternate.dsn = None; + alternate.connection_string = Some(connection_string); + backend.test(&alternate).unwrap(); + } + let cancel = AtomicBool::new(false); + let sql = "SELECT 9007199254740993::bigint AS identifier, 0.12345678901234567890::numeric(30,20) AS exact_amount, 12.34::numeric(10,2) AS amount, true AS active, '=1'::text AS literal, 'ação 🦊'::text AS unicode, timestamp '2026-10-14 12:34:56.123456' AS recorded, NULL::text AS optional_text, 42::integer AS count"; + let data = backend.query(&connection, sql, Limits::default(), false, &cancel).unwrap(); + let row = &data.rows[0]; + assert_eq!(row[0], Value::text("9007199254740993")); + assert_eq!(row[1], Value::text("0.12345678901234567890")); + assert_eq!(row[2], Value::Number(12.34)); + assert_eq!(row[3], Value::Bool(true)); + assert_eq!(row[4], Value::text("=1")); + assert_eq!(row[5], Value::text("ação 🦊")); + assert_eq!(row[6], decode(ColumnKind::Timestamp, "2026-10-14 12:34:56.123456").unwrap()); + assert_eq!(row[7], Value::Empty); + assert_eq!(row[8], Value::Number(42.0)); + let dates = backend.query(&connection, "SELECT date '2024-02-29', time '12:34:56.123456'", Limits::default(), false, &cancel).unwrap(); + assert_eq!(dates.rows[0][0], decode(ColumnKind::Date, "2024-02-29").unwrap()); + assert_eq!(dates.rows[0][1], decode(ColumnKind::Time, "12:34:56.123456").unwrap()); + let read_only = backend.query(&connection, "SELECT current_setting('transaction_read_only') AS mode", Limits::default(), false, &cancel).unwrap(); + assert_eq!(read_only.rows[0][0], Value::text("on")); + let preview = backend.query(&connection, "SELECT generate_series(1,101) AS value", Limits::default(), true, &cancel).unwrap(); + assert_eq!(preview.rows.len(), 100); + assert!(preview.truncated); + assert!(matches!( + backend.query(&connection, "SELECT generate_series(1,2) AS value", Limits { rows: 1, ..Default::default() }, false, &cancel), + Err(OdbcError::Limit(_)) + )); + let empty = backend.query(&connection, "SELECT 1 AS value WHERE false", Limits::default(), false, &cancel).unwrap(); + assert_eq!(empty.columns.len(), 1); + assert!(empty.rows.is_empty()); + assert!(backend.query(&connection, "SELECT decode('ff','hex') AS binary", Limits::default(), false, &cancel).is_err()); + assert!(matches!(backend.query(&connection, "SELECT 1; SELECT 2", Limits::default(), false, &cancel), Err(OdbcError::InvalidQuery))); + let cancelled = AtomicBool::new(true); + assert!(matches!(backend.query(&connection, "SELECT 1", Limits::default(), false, &cancelled), Err(OdbcError::Cancelled))); +} diff --git a/docs/architecture.md b/docs/architecture.md --- a/docs/architecture.md +++ b/docs/architecture.md @@ -1,6 +1,12 @@ # Architecture -> **Last reviewed:** 2026-10-10 · ** Last updated:** 2026-10-24 · **Change:** minor (engine building blocks for multi-command actions) · **Target:** Microsoft Excel (Microsoft 365) +`gridcraft-odbc` is an isolated L2 connector: connection settings and SQL produce +bounded typed tables, including calendar dates/times. It owns driver safety and +does not depend on the engine, UI or workbook persistence. See +[`crates/odbc/README.md`](../crates/odbc/README.md). Saved query/refresh design is +deferred to [#431](https://github.com/storytold/gridcraft/issues/431). + +> **Last reviewed:** 2026-10-10 · ** Last updated:** 2026-10-27 · **Change:** minor (standalone typed ODBC connector) · **Target:** Microsoft Excel (Microsoft 365) How GridCraft is built today. Rules for contributors are in [`AGENTS.md`](../AGENTS.md). @@ -148,6 +154,8 @@ rpm, tarball), FreeBSD, and web (WASM; WebGPU or WebGL2). Release workflows in ## Revision history +- 2026-10-27: Separate the typed ODBC connector from engine/UI and saved-query persistence pending #431. + | Date | Change | Summary | |---|---|---| | 2026-10-24 | minor | Engine building blocks for multi-command actions: undo groups, recording buffer, document trust, chunked hidden-name storage | diff --git a/docs/superpowers/plans/2026-10-27-odbc-split.md b/docs/superpowers/plans/2026-10-27-odbc-split.md new file mode 100644 --- /dev/null +++ b/docs/superpowers/plans/2026-10-27-odbc-split.md @@ -0,0 +1,53 @@ +# ODBC review split + +The complete implementation is preserved at `parked/odbc-saved-queries` +(`2b3d5c890f3d076a4f4b0cfba6cd6071ea4046bb`). This plan implements the PR +feedback without choosing the saved-query contract being discussed in #431. + +## 1. Standalone connector + +- Branch `feat/odbc-connector`, based on upstream main. +- Keep `crates/odbc`, workspace registration and layer registration only, with + contributor documentation. No engine, UI or workbook changes. +- Contract: `Backend::query(connection, sql, limits, preview, cancelled)` returns + a bounded typed `DataSet`. Replace spreadsheet cell values with connector values; + return dates, times and timestamps as Chrono types. Preserve precision, nulls, + Unicode, credentials redaction, vendor guards and rollback. +- Cover typed temporal results and existing six-driver behavior. Run connector + tests and repository CI before the English commit. + +## 2. One-shot import + +- Keep PR #1717 on `feat/odbc-postgresql`; merge the connector branch normally. +- Remove QuerySource, query profiles, refresh jobs, fingerprint/replace logic, + XLSX extensions and their tests. Restore existing pivot refresh behavior. +- Retain Test, Preview, Load, async jobs, cancellation, undo, CLI and MCP commands. +- Convert connector dates to the workbook date system and apply number formats. + Preserve offsets as text and dates outside spreadsheet limits as text. +- Update command docs, user docs and tests. Prove saved files contain ordinary + tables without SQL, connection settings or private query metadata. +- Run affected tests, format/layer/parity checks and repository CI, then commit + in English and push without rewriting published history. + +## 3. Review delivery + +- Open a connector-only upstream PR and describe the dependency in PR #1717. +- Link the preserved branch and #431 in the PR descriptions. +- Request a fresh local review of the combined change; fix material findings and + report actual validation, including unavailable environments. + +## Deferred + +Arrow record batches and unixODBC are optional follow-ups. Do not introduce a +Polars pipeline, saved transformations, refresh contract or workbook extension. + +## Execution notes + +- Connector: 17 unit/profile tests passed; revised PostgreSQL and SQLite native + integration tests passed on 2026-10-27. Other four local servers are absent. +- Connector CI: format and connector Clippy passed. Full CI is blocked by unchanged + upstream Windows issues: `engine/src/io/tests.rs:32` uses `std::os::unix`, and + `apps/gridcraft-cli/src/check.rs:281` triggers `only_used_in_recursion` with + Rust 1.99. Assets and layers passed when run separately. +- Fresh review found historical years must be checked before Excel DATE conversion; + importer now preserves pre-epoch dates/timestamps as text, with regression cases. diff --git a/xtask/src/layers.rs b/xtask/src/layers.rs --- a/xtask/src/layers.rs +++ b/xtask/src/layers.rs @@ -34,6 +34,7 @@ pub const TABLE: &[(&str, Class)] = &[ ("model", Class::Layer(2)), ("calc", Class::Layer(2)), ("xlsx", Class::Layer(2)), + ("odbc", Class::Layer(2)), ("chart", Class::Layer(3)), ("engine", Class::Layer(5)), ("ui-egui", Class::Layer(6)),