1---
2title: 'Parameterized Queries'
3sidebar_label: 'Parameterized Queries'
4description: 'Learn how to use prepared statements and parameterized queries in Spice for improved security and performance.'
5sidebar_position: 1
6pagination_prev: null
7pagination_next: null
8tags:
9  - query
10  - sql
11  - security
12  - features
13  - adbc
14---
15
16import Tabs from '@theme/Tabs'
17import TabItem from '@theme/TabItem'
18
19Parameterized queries separate SQL logic from data values, providing protection against SQL injection attacks and improving query performance through prepared statement caching.
20
21## Overview
22
23Instead 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:
24
25- **Prevents SQL injection**: Values are never interpreted as SQL code
26- **Improves performance**: Query plans can be cached and reused
27- **Enhances code clarity**: Query structure is separate from data
28
29## Placeholder Syntax
30
31Spice uses positional placeholders following the PostgreSQL convention:
32
33```sql
34SELECT * FROM users WHERE id = $1 AND status = $2
35```
36
37Parameters are bound in order: `$1` receives the first parameter, `$2` the second, and so on.
38
39## Using Parameterized Queries
40
41<Tabs groupId="sdk">
42  <TabItem value="python" label="Python (ADBC)" default>
43
44ADBC provides native support for parameterized queries through the FlightSQL driver:
45
46```python
47from adbc_driver_flightsql.dbapi import connect
48
49with connect("grpc://127.0.0.1:50051") as conn:
50    with conn.cursor() as cur:
51        # Single parameter
52        cur.execute("SELECT * FROM users WHERE id = $1", parameters=(42,))
53        table = cur.fetch_arrow_table()
54        print(table)
55
56        # Multiple parameters
57        cur.execute(
58            "SELECT * FROM orders WHERE customer_id = $1 AND total > $2",
59            parameters=(100, 50.0)
60        )
61        table = cur.fetch_arrow_table()
62        print(table)
63```
64
65  </TabItem>
66  <TabItem value="go" label="Go">
67
68The [gospice](https://github.com/spiceai/gospice) SDK (v8+) provides `SqlWithParams()` for parameterized queries:
69
70```go
71import "github.com/spiceai/gospice/v8"
72
73spice := gospice.NewSpiceClient()
74defer spice.Close()
75
76if err := spice.Init(); err != nil {
77    panic(err)
78}
79
80// Query with parameters
81reader, err := spice.SqlWithParams(
82    context.Background(),
83    "SELECT * FROM customers WHERE c_custkey > $1 LIMIT 10",
84    100,
85)
86if err != nil {
87    panic(err)
88}
89defer reader.Release()
90
91for reader.Next() {
92    record := reader.RecordBatch()
93    defer record.Release()
94    fmt.Println(record)
95}
96```
97
98Multiple parameters with different types:
99
100```go
101reader, err := spice.SqlWithParams(
102    context.Background(),
103    "SELECT * FROM orders WHERE customer_id = $1 AND order_date > $2 AND total > $3",
104    42,           // int
105    "2024-01-01", // string
106    100.50,       // float64
107)
108```
109
110  </TabItem>
111  <TabItem value="java" label="Java">
112
113The [spice-java](https://github.com/spiceai/spice-java) SDK (v0.5.0+) provides `queryWithParams()` for parameterized queries:
114
115```java
116import org.apache.arrow.vector.VectorSchemaRoot;
117import org.apache.arrow.vector.ipc.ArrowReader;
118import ai.spice.SpiceClient;
119
120public class Example {
121    public static void main(String[] args) {
122        try (SpiceClient client = SpiceClient.builder().build()) {
123
124            // Query with automatic type inference
125            ArrowReader reader = client.queryWithParams(
126                "SELECT * FROM taxi_trips WHERE trip_distance > $1 LIMIT 10",
127                5.0); // Double is inferred as Float64
128
129            while (reader.loadNextBatch()) {
130                VectorSchemaRoot root = reader.getVectorSchemaRoot();
131                System.out.println(root.contentToTSVString());
132            }
133            reader.close();
134
135        } catch (Exception e) {
136            System.err.println("Error: " + e.getMessage());
137        }
138    }
139}
140```
141
142Multiple parameters:
143
144```java
145ArrowReader reader = client.queryWithParams(
146    "SELECT * FROM taxi_trips WHERE trip_distance > $1 AND fare_amount > $2 LIMIT 10",
147    5.0, 20.0);
148```
149
150  </TabItem>
151  <TabItem value="rust" label="Rust">
152
153The [spice-rs](https://github.com/spiceai/spice-rs) SDK (v3.0.0+) provides `query_with_params()`:
154
155```rust
156use spice_rs::Client;
157use arrow::record_batch::RecordBatch;
158
159let client = Client::new("http://localhost:50051").await?;
160
161// Create parameter batch
162let params = RecordBatch::try_new(
163    Arc::new(Schema::new(vec![
164        Field::new("$1", DataType::Float64, false),
165    ])),
166    vec![Arc::new(Float64Array::from(vec![5.0]))],
167)?;
168
169let batches = client.query_with_params(
170    "SELECT * FROM taxi_trips WHERE trip_distance > $1 LIMIT 10",
171    params
172).await?;
173```
174
175  </TabItem>
176  <TabItem value="dotnet" label="Dotnet">
177
178The [spice-dotnet](https://github.com/spiceai/spice-dotnet) SDK (v1.1.0+) provides `Query()` with dictionary parameters:
179
180```csharp
181using Spice;
182
183var client = new SpiceClientBuilder().Build();
184
185var parameters = new Dictionary<string, object>
186{
187    { "min_distance", 5.0 },
188    { "min_fare", 20.0 }
189};
190
191var data = await client.Query(
192    "SELECT * FROM taxi_trips WHERE trip_distance > :min_distance AND fare_amount > :min_fare",
193    parameters
194);
195```
196
197  </TabItem>
198  <TabItem value="http" label="HTTP API">
199
200The HTTP API supports parameterized queries through the `/v1/sql` endpoint:
201
202```bash
203curl -X POST http://localhost:8090/v1/sql \
204  -H "Content-Type: application/json" \
205  -d '{
206    "sql": "SELECT * FROM users WHERE id = $1 AND status = $2",
207    "parameters": [42, "active"]
208  }'
209```
210
211  </TabItem>
212</Tabs>
213
214## Supported Parameter Types
215
216Spice supports a wide range of parameter types with automatic type inference:
217
218| Type Category     | Examples                                  | Arrow Type           |
219| ----------------- | ----------------------------------------- | -------------------- |
220| Signed integers   | `int`, `int8`, `int16`, `int32`, `int64`  | Int8/16/32/64        |
221| Unsigned integers | `uint`, `uint8`, `uint16`, `uint32`       | Uint8/16/32/64       |
222| Floating point    | `float`, `double`                         | Float32/Float64      |
223| Text              | `string`                                  | Utf8                 |
224| Boolean           | `bool`, `boolean`                         | Boolean              |
225| Binary            | `byte[]`, `[]byte`                        | Binary               |
226| Date/Time         | `LocalDate`, `LocalDateTime`, `time.Time` | Date32/64, Timestamp |
227| Decimal           | `BigDecimal`, `decimal`                   | Decimal128/256       |
228| Null              | `null`, `nil`                             | Null                 |
229
230## Explicit Type Control
231
232For precise control over Arrow types, SDKs provide typed parameter constructors:
233
234<Tabs groupId="sdk">
235  <TabItem value="go" label="Go" default>
236
237```go
238import "github.com/spiceai/gospice/v8"
239
240reader, err := spice.SqlWithParams(
241    ctx,
242    "SELECT * FROM financial WHERE amount >= $1 AND timestamp > $2",
243    gospice.Decimal128Param(amountBytes, 19, 4),            // Decimal with precision & scale
244    gospice.TimestampParam(ts, arrow.Microsecond, "UTC"),   // Timestamp with unit & timezone
245)
246```
247
248Available 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`
249
250  </TabItem>
251  <TabItem value="java" label="Java">
252
253```java
254import ai.spice.Param;
255
256ArrowReader reader = client.queryWithParams(
257    "SELECT * FROM orders WHERE order_id = $1 AND amount >= $2",
258    Param.int64(12345),
259    Param.decimal128(new BigDecimal("99.99"), 10, 2));
260```
261
262Available constructors: `int8`, `int16`, `int32`, `int64`, `uint8`, `uint16`, `uint32`, `uint64`, `float16`, `float32`, `float64`, `string`, `largeString`, `binary`, `largeBinary`, `fixedSizeBinary`, `bool`, `date32`, `date64`, `time32`, `time64`, `timestamp`, `duration`, `decimal128`, `decimal256`, `nullValue`
263
264You can also use generic constructors: `Param.of(value)` for automatic type inference, or `Param.of(value, arrowType)` for explicit Arrow type.
265
266  </TabItem>
267  <TabItem value="rust" label="Rust">
268
269Rust uses Arrow `RecordBatch` for parameters, giving full control over the Arrow schema:
270
271```rust
272use arrow::datatypes::{DataType, Field, Schema, TimeUnit};
273use arrow::array::{TimestampMicrosecondArray, Decimal128Array};
274
275let params = RecordBatch::try_new(
276    Arc::new(Schema::new(vec![
277        Field::new("$1", DataType::Decimal128(19, 4), false),
278        Field::new("$2", DataType::Timestamp(TimeUnit::Microsecond, Some("UTC".into())), false),
279    ])),
280    vec![
281        Arc::new(Decimal128Array::from(vec![Some(9999)])),
282        Arc::new(TimestampMicrosecondArray::from(vec![Some(1704067200000000)])),
283    ],
284)?;
285```
286
287  </TabItem>
288</Tabs>
289
290## Security Benefits
291
292Parameterized queries protect against SQL injection by ensuring user input is never interpreted as SQL code.
293
294<Tabs groupId="sdk">
295  <TabItem value="go" label="Go" default>
296
297```go
298// ❌ DANGEROUS: User input directly in SQL string
299userId := getUserInput() // Could be: "1 OR 1=1"
300sql := fmt.Sprintf("SELECT * FROM users WHERE id = %s", userId)
301reader, err := spice.Sql(ctx, sql)
302
303// ✅ SAFE: User input passed as parameter
304userId := getUserInput()
305reader, err := spice.SqlWithParams(ctx, "SELECT * FROM users WHERE id = $1", userId)
306```
307
308  </TabItem>
309  <TabItem value="java" label="Java">
310
311```java
312// ❌ DANGEROUS: User input directly in SQL string
313String userId = getUserInput(); // Could be: "1 OR 1=1"
314String sql = "SELECT * FROM users WHERE id = " + userId;
315FlightStream stream = client.query(sql);
316
317// ✅ SAFE: User input passed as parameter
318ArrowReader reader = client.queryWithParams(
319    "SELECT * FROM users WHERE id = $1",
320    userId);
321```
322
323  </TabItem>
324  <TabItem value="python" label="Python (ADBC)">
325
326```python
327# ❌ DANGEROUS: User input directly in SQL string
328user_id = get_user_input()  # Could be: "1 OR 1=1"
329sql = f"SELECT * FROM users WHERE id = {user_id}"
330cur.execute(sql)
331
332# ✅ SAFE: User input passed as parameter
333cur.execute("SELECT * FROM users WHERE id = $1", parameters=(user_id,))
334```
335
336  </TabItem>
337</Tabs>
338
339With 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.
340
341## Performance Considerations
342
343Parameterized queries can improve performance in several ways:
344
3451. **Query Plan Caching**: The database can cache and reuse execution plans for parameterized queries
3462. **Reduced Parsing**: Parameter binding avoids repeated SQL parsing
3473. **Batch Operations**: Multiple executions with different parameters use the same prepared statement
348
349## SDK Support
350
351| SDK                                                              | Version | Method                                      | Status     |
352| ---------------------------------------------------------------- | ------- | ------------------------------------------- | ---------- |
353| [gospice](https://github.com/spiceai/gospice) (Go)               | v8.0.0+ | `SqlWithParams()` with typed constructors   | ✅ Full    |
354| [spice-rs](https://github.com/spiceai/spice-rs) (Rust)           | v3.0.0+ | `query_with_params()` with `RecordBatch`    | ✅ Full    |
355| [spice-dotnet](https://github.com/spiceai/spice-dotnet) (Dotnet) | v1.1.0+ | `Query()` with `Dictionary<string, object>` | ✅ Full    |
356| [spice-java](https://github.com/spiceai/spice-java) (Java)       | v0.5.0+ | `queryWithParams()` with `Param` class      | ✅ Full    |
357| [spice.js](https://github.com/spiceai/spice.js) (JavaScript)     | -       | Coming soon                                 | ❌ Not yet |
358| [spicepy](https://github.com/spiceai/spicepy) (Python)           | -       | Coming soon (use ADBC)                      | ❌ Not yet |
359| ADBC (Python)                                                    | -       | `cursor.execute()` with `parameters`        | ✅ Full    |
360| JDBC                                                             | -       | `PreparedStatement`                         | ✅ Full    |
361| ODBC                                                             | -       | Parameterized queries                       | ✅ Full    |
362
363## Examples
364
365### Filtering with Multiple Conditions
366
367```python
368# Python with ADBC
369cur.execute("""
370    SELECT order_id, customer_name, total
371    FROM orders
372    WHERE status = $1
373      AND order_date BETWEEN $2 AND $3
374      AND total > $4
375    ORDER BY order_date DESC
376    LIMIT $5
377""", parameters=("completed", "2024-01-01", "2024-12-31", 100.0, 50))
378```
379
380### Batch Processing Pattern
381
382```go
383// Go: Process multiple customers with same query
384customerIDs := []int{100, 200, 300, 400, 500}
385
386for _, id := range customerIDs {
387    reader, err := spice.SqlWithParams(
388        ctx,
389        "SELECT * FROM orders WHERE customer_id = $1",
390        id,
391    )
392    if err != nil {
393        log.Printf("Error querying customer %d: %v", id, err)
394        continue
395    }
396    processOrders(reader)
397    reader.Release()
398}
399```
400
401### Using with Aggregations
402
403```python
404# Calculate statistics for a specific time range
405cur.execute("""
406    SELECT
407        DATE_TRUNC('day', order_date) as day,
408        COUNT(*) as order_count,
409        SUM(total) as daily_total
410    FROM orders
411    WHERE order_date >= $1 AND order_date < $2
412    GROUP BY DATE_TRUNC('day', order_date)
413    ORDER BY day
414""", parameters=("2024-01-01", "2024-02-01"))
415```
416
417## Troubleshooting
418
419### Parameter Count Mismatch
420
421Ensure the number of parameters matches the number of placeholders:
422
423```python
424# Error: 3 placeholders but only 2 parameters
425cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2))
426
427# Correct: 3 placeholders and 3 parameters
428cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2, 3))
429```
430
431### Type Mismatch
432
433If you encounter type errors, use explicit type constructors (Go) or ensure Python types match expected column types:
434
435```go
436// If int is inferred as Int64 but column expects Int32
437reader, err := spice.SqlWithParams(ctx, "SELECT $1", gospice.Int32Param(42))
438```
439
440### Connection Issues
441
442For ADBC connections, verify the Spice runtime is running and accessible:
443
444```python
445# Test connection
446try:
447    conn = connect("grpc://localhost:50051")
448    cursor = conn.cursor()
449    cursor.execute("SELECT 1")
450    print("Connection successful")
451except Exception as e:
452    print(f"Connection failed: {e}")
453```
454
455## Related Topics
456
457- [ADBC API](../../api/adbc) - Arrow Database Connectivity documentation
458- [Arrow Flight SQL API](../../api/arrow-flight-sql) - Flight SQL protocol details
459- [Go SDK](../../sdks/golang) - gospice SDK documentation
460- [Python SDK](../../sdks/python) - spicepy SDK documentation
461- [Results Caching](../caching) - Query result caching
462

1---
2title: 'Parameterized Queries'
3sidebar_label: 'Parameterized Queries'
4description: 'Learn how to use prepared statements and parameterized queries in Spice for improved security and performance.'
5sidebar_position: 1
6pagination_prev: null
7pagination_next: null
8tags:
9  - query
10  - sql
11  - security
12  - features
13  - adbc
14---
15
16import Tabs from '@theme/Tabs'
17import TabItem from '@theme/TabItem'
18
19Parameterized queries separate SQL logic from data values, providing protection against SQL injection attacks and improving query performance through prepared statement caching.
20
21## Overview
22
23Instead 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:
24
25- **Prevents SQL injection**: Values are never interpreted as SQL code
26- **Improves performance**: Query plans can be cached and reused
27- **Enhances code clarity**: Query structure is separate from data
28
29## Placeholder Syntax
30
31Spice uses positional placeholders following the PostgreSQL convention:
32
33```sql
34SELECT * FROM users WHERE id = $1 AND status = $2
35```
36
37Parameters are bound in order: `$1` receives the first parameter, `$2` the second, and so on.
38
39## Using Parameterized Queries
40
41<Tabs groupId="sdk">
42  <TabItem value="python" label="Python (ADBC)" default>
43
44ADBC provides native support for parameterized queries through the FlightSQL driver:
45
46```python
47from adbc_driver_flightsql.dbapi import connect
48
49with connect("grpc://127.0.0.1:50051") as conn:
50    with conn.cursor() as cur:
51        # Single parameter
52        cur.execute("SELECT * FROM users WHERE id = $1", parameters=(42,))
53        table = cur.fetch_arrow_table()
54        print(table)
55
56        # Multiple parameters
57        cur.execute(
58            "SELECT * FROM orders WHERE customer_id = $1 AND total > $2",
59            parameters=(100, 50.0)
60        )
61        table = cur.fetch_arrow_table()
62        print(table)
63```
64
65  </TabItem>
66  <TabItem value="go" label="Go">
67
68The [gospice](https://github.com/spiceai/gospice) SDK (v8+) provides `SqlWithParams()` for parameterized queries:
69
70```go
71import "github.com/spiceai/gospice/v8"
72
73spice := gospice.NewSpiceClient()
74defer spice.Close()
75
76if err := spice.Init(); err != nil {
77    panic(err)
78}
79
80// Query with parameters
81reader, err := spice.SqlWithParams(
82    context.Background(),
83    "SELECT * FROM customers WHERE c_custkey > $1 LIMIT 10",
84    100,
85)
86if err != nil {
87    panic(err)
88}
89defer reader.Release()
90
91for reader.Next() {
92    record := reader.RecordBatch()
93    defer record.Release()
94    fmt.Println(record)
95}
96```
97
98Multiple parameters with different types:
99
100```go
101reader, err := spice.SqlWithParams(
102    context.Background(),
103    "SELECT * FROM orders WHERE customer_id = $1 AND order_date > $2 AND total > $3",
104    42,           // int
105    "2024-01-01", // string
106    100.50,       // float64
107)
108```
109
110  </TabItem>
111  <TabItem value="java" label="Java">
112
113The [spice-java](https://github.com/spiceai/spice-java) SDK (v0.5.0+) provides `queryWithParams()` for parameterized queries:
114
115```java
116import org.apache.arrow.vector.VectorSchemaRoot;
117import org.apache.arrow.vector.ipc.ArrowReader;
118import ai.spice.SpiceClient;
119
120public class Example {
121    public static void main(String[] args) {
122        try (SpiceClient client = SpiceClient.builder().build()) {
123
124            // Query with automatic type inference
125            ArrowReader reader = client.queryWithParams(
126                "SELECT * FROM taxi_trips WHERE trip_distance > $1 LIMIT 10",
127                5.0); // Double is inferred as Float64
128
129            while (reader.loadNextBatch()) {
130                VectorSchemaRoot root = reader.getVectorSchemaRoot();
131                System.out.println(root.contentToTSVString());
132            }
133            reader.close();
134
135        } catch (Exception e) {
136            System.err.println("Error: " + e.getMessage());
137        }
138    }
139}
140```
141
142Multiple parameters:
143
144```java
145ArrowReader reader = client.queryWithParams(
146    "SELECT * FROM taxi_trips WHERE trip_distance > $1 AND fare_amount > $2 LIMIT 10",
147    5.0, 20.0);
148```
149
150  </TabItem>
151  <TabItem value="rust" label="Rust">
152
153The [spice-rs](https://github.com/spiceai/spice-rs) SDK (v3.0.0+) provides `query_with_params()`:
154
155```rust
156use spice_rs::Client;
157use arrow::record_batch::RecordBatch;
158
159let client = Client::new("http://localhost:50051").await?;
160
161// Create parameter batch
162let params = RecordBatch::try_new(
163    Arc::new(Schema::new(vec![
164        Field::new("$1", DataType::Float64, false),
165    ])),
166    vec![Arc::new(Float64Array::from(vec![5.0]))],
167)?;
168
169let batches = client.query_with_params(
170    "SELECT * FROM taxi_trips WHERE trip_distance > $1 LIMIT 10",
171    params
172).await?;
173```
174
175  </TabItem>
176  <TabItem value="dotnet" label="Dotnet">
177
178The [spice-dotnet](https://github.com/spiceai/spice-dotnet) SDK (v1.1.0+) provides `Query()` with dictionary parameters:
179
180```csharp
181using Spice;
182
183var client = new SpiceClientBuilder().Build();
184
185var parameters = new Dictionary<string, object>
186{
187    { "min_distance", 5.0 },
188    { "min_fare", 20.0 }
189};
190
191var data = await client.Query(
192    "SELECT * FROM taxi_trips WHERE trip_distance > :min_distance AND fare_amount > :min_fare",
193    parameters
194);
195```
196
197  </TabItem>
198  <TabItem value="http" label="HTTP API">
199
200The HTTP API supports parameterized queries through the `/v1/sql` endpoint:
201
202```bash
203curl -X POST http://localhost:8090/v1/sql \
204  -H "Content-Type: application/json" \
205  -d '{
206    "sql": "SELECT * FROM users WHERE id = $1 AND status = $2",
207    "parameters": [42, "active"]
208  }'
209```
210
211  </TabItem>
212</Tabs>
213
214## Supported Parameter Types
215
216Spice supports a wide range of parameter types with automatic type inference:
217
218| Type Category     | Examples                                  | Arrow Type           |
219| ----------------- | ----------------------------------------- | -------------------- |
220| Signed integers   | `int`, `int8`, `int16`, `int32`, `int64`  | Int8/16/32/64        |
221| Unsigned integers | `uint`, `uint8`, `uint16`, `uint32`       | Uint8/16/32/64       |
222| Floating point    | `float`, `double`                         | Float32/Float64      |
223| Text              | `string`                                  | Utf8                 |
224| Boolean           | `bool`, `boolean`                         | Boolean              |
225| Binary            | `byte[]`, `[]byte`                        | Binary               |
226| Date/Time         | `LocalDate`, `LocalDateTime`, `time.Time` | Date32/64, Timestamp |
227| Decimal           | `BigDecimal`, `decimal`                   | Decimal128/256       |
228| Null              | `null`, `nil`                             | Null                 |
229
230## Explicit Type Control
231
232For precise control over Arrow types, SDKs provide typed parameter constructors:
233
234<Tabs groupId="sdk">
235  <TabItem value="go" label="Go" default>
236
237```go
238import "github.com/spiceai/gospice/v8"
239
240reader, err := spice.SqlWithParams(
241    ctx,
242    "SELECT * FROM financial WHERE amount >= $1 AND timestamp > $2",
243    gospice.Decimal128Param(amountBytes, 19, 4),            // Decimal with precision & scale
244    gospice.TimestampParam(ts, arrow.Microsecond, "UTC"),   // Timestamp with unit & timezone
245)
246```
247
248Available 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`
249
250  </TabItem>
251  <TabItem value="java" label="Java">
252
253```java
254import ai.spice.Param;
255
256ArrowReader reader = client.queryWithParams(
257    "SELECT * FROM orders WHERE order_id = $1 AND amount >= $2",
258    Param.int64(12345),
259    Param.decimal128(new BigDecimal("99.99"), 10, 2));
260```
261
262Available constructors: `int8`, `int16`, `int32`, `int64`, `uint8`, `uint16`, `uint32`, `uint64`, `float16`, `float32`, `float64`, `string`, `largeString`, `binary`, `largeBinary`, `fixedSizeBinary`, `bool`, `date32`, `date64`, `time32`, `time64`, `timestamp`, `duration`, `decimal128`, `decimal256`, `nullValue`
263
264You can also use generic constructors: `Param.of(value)` for automatic type inference, or `Param.of(value, arrowType)` for explicit Arrow type.
265
266  </TabItem>
267  <TabItem value="rust" label="Rust">
268
269Rust uses Arrow `RecordBatch` for parameters, giving full control over the Arrow schema:
270
271```rust
272use arrow::datatypes::{DataType, Field, Schema, TimeUnit};
273use arrow::array::{TimestampMicrosecondArray, Decimal128Array};
274
275let params = RecordBatch::try_new(
276    Arc::new(Schema::new(vec![
277        Field::new("$1", DataType::Decimal128(19, 4), false),
278        Field::new("$2", DataType::Timestamp(TimeUnit::Microsecond, Some("UTC".into())), false),
279    ])),
280    vec![
281        Arc::new(Decimal128Array::from(vec![Some(9999)])),
282        Arc::new(TimestampMicrosecondArray::from(vec![Some(1704067200000000)])),
283    ],
284)?;
285```
286
287  </TabItem>
288</Tabs>
289
290## Security Benefits
291
292Parameterized queries protect against SQL injection by ensuring user input is never interpreted as SQL code.
293
294<Tabs groupId="sdk">
295  <TabItem value="go" label="Go" default>
296
297```go
298// ❌ DANGEROUS: User input directly in SQL string
299userId := getUserInput() // Could be: "1 OR 1=1"
300sql := fmt.Sprintf("SELECT * FROM users WHERE id = %s", userId)
301reader, err := spice.Sql(ctx, sql)
302
303// ✅ SAFE: User input passed as parameter
304userId := getUserInput()
305reader, err := spice.SqlWithParams(ctx, "SELECT * FROM users WHERE id = $1", userId)
306```
307
308  </TabItem>
309  <TabItem value="java" label="Java">
310
311```java
312// ❌ DANGEROUS: User input directly in SQL string
313String userId = getUserInput(); // Could be: "1 OR 1=1"
314String sql = "SELECT * FROM users WHERE id = " + userId;
315FlightStream stream = client.query(sql);
316
317// ✅ SAFE: User input passed as parameter
318ArrowReader reader = client.queryWithParams(
319    "SELECT * FROM users WHERE id = $1",
320    userId);
321```
322
323  </TabItem>
324  <TabItem value="python" label="Python (ADBC)">
325
326```python
327# ❌ DANGEROUS: User input directly in SQL string
328user_id = get_user_input()  # Could be: "1 OR 1=1"
329sql = f"SELECT * FROM users WHERE id = {user_id}"
330cur.execute(sql)
331
332# ✅ SAFE: User input passed as parameter
333cur.execute("SELECT * FROM users WHERE id = $1", parameters=(user_id,))
334```
335
336  </TabItem>
337</Tabs>
338
339With 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.
340
341## Performance Considerations
342
343Parameterized queries can improve performance in several ways:
344
3451. **Query Plan Caching**: The database can cache and reuse execution plans for parameterized queries
3462. **Reduced Parsing**: Parameter binding avoids repeated SQL parsing
3473. **Batch Operations**: Multiple executions with different parameters use the same prepared statement
348
349## SDK Support
350
351| SDK                                                              | Version | Method                                      | Status     |
352| ---------------------------------------------------------------- | ------- | ------------------------------------------- | ---------- |
353| [gospice](https://github.com/spiceai/gospice) (Go)               | v8.0.0+ | `SqlWithParams()` with typed constructors   | ✅ Full    |
354| [spice-rs](https://github.com/spiceai/spice-rs) (Rust)           | v3.0.0+ | `query_with_params()` with `RecordBatch`    | ✅ Full    |
355| [spice-dotnet](https://github.com/spiceai/spice-dotnet) (Dotnet) | v1.1.0+ | `Query()` with `Dictionary<string, object>` | ✅ Full    |
356| [spice-java](https://github.com/spiceai/spice-java) (Java)       | v0.5.0+ | `queryWithParams()` with `Param` class      | ✅ Full    |
357| [spice.js](https://github.com/spiceai/spice.js) (JavaScript)     | -       | Coming soon                                 | ❌ Not yet |
358| [spicepy](https://github.com/spiceai/spicepy) (Python)           | -       | Coming soon (use ADBC)                      | ❌ Not yet |
359| ADBC (Python)                                                    | -       | `cursor.execute()` with `parameters`        | ✅ Full    |
360| JDBC                                                             | -       | `PreparedStatement`                         | ✅ Full    |
361| ODBC                                                             | -       | Parameterized queries                       | ✅ Full    |
362
363## Examples
364
365### Filtering with Multiple Conditions
366
367```python
368# Python with ADBC
369cur.execute("""
370    SELECT order_id, customer_name, total
371    FROM orders
372    WHERE status = $1
373      AND order_date BETWEEN $2 AND $3
374      AND total > $4
375    ORDER BY order_date DESC
376    LIMIT $5
377""", parameters=("completed", "2024-01-01", "2024-12-31", 100.0, 50))
378```
379
380### Batch Processing Pattern
381
382```go
383// Go: Process multiple customers with same query
384customerIDs := []int{100, 200, 300, 400, 500}
385
386for _, id := range customerIDs {
387    reader, err := spice.SqlWithParams(
388        ctx,
389        "SELECT * FROM orders WHERE customer_id = $1",
390        id,
391    )
392    if err != nil {
393        log.Printf("Error querying customer %d: %v", id, err)
394        continue
395    }
396    processOrders(reader)
397    reader.Release()
398}
399```
400
401### Using with Aggregations
402
403```python
404# Calculate statistics for a specific time range
405cur.execute("""
406    SELECT
407        DATE_TRUNC('day', order_date) as day,
408        COUNT(*) as order_count,
409        SUM(total) as daily_total
410    FROM orders
411    WHERE order_date >= $1 AND order_date < $2
412    GROUP BY DATE_TRUNC('day', order_date)
413    ORDER BY day
414""", parameters=("2024-01-01", "2024-02-01"))
415```
416
417## Troubleshooting
418
419### Parameter Count Mismatch
420
421Ensure the number of parameters matches the number of placeholders:
422
423```python
424# Error: 3 placeholders but only 2 parameters
425cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2))
426
427# Correct: 3 placeholders and 3 parameters
428cur.execute("SELECT * FROM t WHERE a = $1 AND b = $2 AND c = $3", parameters=(1, 2, 3))
429```
430
431### Type Mismatch
432
433If you encounter type errors, use explicit type constructors (Go) or ensure Python types match expected column types:
434
435```go
436// If int is inferred as Int64 but column expects Int32
437reader, err := spice.SqlWithParams(ctx, "SELECT $1", gospice.Int32Param(42))
438```
439
440### Connection Issues
441
442For ADBC connections, verify the Spice runtime is running and accessible:
443
444```python
445# Test connection
446try:
447    conn = connect("grpc://localhost:50051")
448    cursor = conn.cursor()
449    cursor.execute("SELECT 1")
450    print("Connection successful")
451except Exception as e:
452    print(f"Connection failed: {e}")
453```
454
455## Related Topics
456
457- [ADBC API](../../api/adbc) - Arrow Database Connectivity documentation
458- [Arrow Flight SQL API](../../api/arrow-flight-sql) - Flight SQL protocol details
459- [Go SDK](../../sdks/golang) - gospice SDK documentation
460- [Python SDK](../../sdks/python) - spicepy SDK documentation
461- [Results Caching](../caching) - Query result caching
462