Skip to main content
Version: Next (v1.11)

Parameterized Queries

Parameterized queries separate SQL logic from data values, providing protection against SQL injection attacks and improving query performance through prepared statement caching.

Overview​

Instead of embedding values directly in SQL strings, parameterized queries use placeholders (e.g., $1, $2) that are bound to values at execution time. This approach:

  • Prevents SQL injection: Values are never interpreted as SQL code
  • Improves performance: Query plans can be cached and reused
  • Enhances code clarity: Query structure is separate from data

Placeholder Syntax​

Spice uses positional placeholders following the PostgreSQL convention:

SELECT * FROM users WHERE id = $1 AND status = $2

Parameters are bound in order: $1 receives the first parameter, $2 the second, and so on.

Using Parameterized Queries​

ADBC provides native support for parameterized queries through the FlightSQL driver:

from adbc_driver_flightsql.dbapi import connect

with connect("grpc://127.0.0.1:50051") as conn:
    with conn.cursor() as cur:
        # Single parameter
        cur.execute("SELECT * FROM users WHERE id = $1", parameters=(42,))
        table = cur.fetch_arrow_table()
        print(table)

        # Multiple parameters
        cur.execute(
            "SELECT * FROM orders WHERE customer_id = $1 AND total > $2",
            parameters=(100, 50.0)
        )
        table = cur.fetch_arrow_table()
        print(table)

Supported Parameter Types​

Spice supports a wide range of parameter types with automatic type inference:

Type CategoryExamplesArrow Type
Signed integersint, int8, int16, int32, int64Int8/16/32/64
Unsigned integersuint, uint8, uint16, uint32Uint8/16/32/64
Floating pointfloat, doubleFloat32/Float64
TextstringUtf8
Booleanbool, booleanBoolean
Binarybyte[], []byteBinary
Date/TimeLocalDate, LocalDateTime, time.TimeDate32/64, Timestamp
DecimalBigDecimal, decimalDecimal128/256
Nullnull, nilNull

Explicit Type Control​

For precise control over Arrow types, SDKs provide typed parameter constructors:

import "github.com/spiceai/gospice/v8"

reader, err := spice.SqlWithParams(
    ctx,
    "SELECT * FROM financial WHERE amount >= $1 AND timestamp > $2",
    gospice.Decimal128Param(amountBytes, 19, 4),            // Decimal with precision & scale
    gospice.TimestampParam(ts, arrow.Microsecond, "UTC"),   // Timestamp with unit & timezone
)

Available constructors: Int8Param, Int16Param, Int32Param, Int64Param, Uint8Param, Uint16Param, Uint32Param, Uint64Param, Float16Param, Float32Param, Float64Param, StringParam, LargeStringParam, BinaryParam, LargeBinaryParam, FixedSizeBinaryParam, BoolParam, Date32Param, Date64Param, Time32Param, Time64Param, TimestampParam, DurationParam, MonthIntervalParam, DayTimeIntervalParam, MonthDayNanoIntervalParam, Decimal128Param, Decimal256Param, NullParam

Security Benefits​

Parameterized queries protect against SQL injection by ensuring user input is never interpreted as SQL code.

// ❌ DANGEROUS: User input directly in SQL string
userId := getUserInput() // Could be: "1 OR 1=1"
sql := fmt.Sprintf("SELECT * FROM users WHERE id = %s", userId)
reader, err := spice.Sql(ctx, sql)

// ✅ SAFE: User input passed as parameter
userId := getUserInput()
reader, err := spice.SqlWithParams(ctx, "SELECT * FROM users WHERE id = $1", userId)

With parameterized queries, even if userId contains malicious SQL like 1 OR 1=1, it is treated as a literal string value, not as SQL code.

Performance Considerations​

Parameterized queries can improve performance in several ways:

  1. Query Plan Caching: The database can cache and reuse execution plans for parameterized queries
  2. Reduced Parsing: Parameter binding avoids repeated SQL parsing
  3. Batch Operations: Multiple executions with different parameters use the same prepared statement

SDK Support​

SDKVersionMethodStatus
gospice (Go)v8.0.0+SqlWithParams() with typed constructors✅ Full
spice-rs (Rust)v3.0.0+query_with_params() with RecordBatch✅ Full
spice-dotnet (Dotnet)v1.1.0+Query() with Dictionary<string, object>✅ Full
spice-java (Java)v0.5.0+queryWithParams() with Param class✅ Full
spice.js (JavaScript)-Coming soon❌ Not yet
spicepy (Python)-Coming soon (use ADBC)❌ Not yet
ADBC (Python)-cursor.execute() with parameters✅ Full
JDBC-PreparedStatement✅ Full
ODBC-Parameterized queries✅ Full

Examples​

Filtering with Multiple Conditions​

# Python with ADBC
cur.execute("""
    SELECT order_id, customer_name, total
    FROM orders
    WHERE status = $1
      AND order_date BETWEEN $2 AND $3
      AND total > $4
    ORDER BY order_date DESC
    LIMIT $5
""", parameters=("completed", "2024-01-01", "2024-12-31", 100.0, 50))

Batch Processing Pattern​

// Go: Process multiple customers with same query
customerIDs := []int{100, 200, 300, 400, 500}

for _, id := range customerIDs {
    reader, err := spice.SqlWithParams(
        ctx,
        "SELECT * FROM orders WHERE customer_id = $1",
        id,
    )
    if err != nil {
        log.Printf("Error querying customer %d: %v", id, err)
        continue
    }
    processOrders(reader)
    reader.Release()
}

Using with Aggregations​

# Calculate statistics for a specific time range
cur.execute("""
    SELECT
        DATE_TRUNC('day', order_date) as day,
        COUNT(*) as order_count,
        SUM(total) as daily_total
    FROM orders
    WHERE order_date >= $1 AND order_date < $2
    GROUP BY DATE_TRUNC('day', order_date)
    ORDER BY day
""", parameters=("2024-01-01", "2024-02-01"))

Troubleshooting​

Parameter Count Mismatch​

Ensure the number of parameters matches the number of placeholders:

# Error: 3 placeholders but only 2 parameters
cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2))

# Correct: 3 placeholders and 3 parameters
cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2, 3))

Type Mismatch​

If you encounter type errors, use explicit type constructors (Go) or ensure Python types match expected column types:

// If int is inferred as Int64 but column expects Int32
reader, err := spice.SqlWithParams(ctx, "SELECT $1", gospice.Int32Param(42))

Connection Issues​

For ADBC connections, verify the Spice runtime is running and accessible:

# Test connection
try:
    conn = connect("grpc://localhost:50051")
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    print("Connection successful")
except Exception as e:
    print(f"Connection failed: {e}")