parameterized query

Overview

A parameterized query is a SQL statement that uses placeholders instead of directly embedding values into the query text. These placeholders get replaced with actual values when the query executes, making queries both safer and more reusable. This is a fundamental concept in preventing SQL injection attacks and improving query performance through plan caching.

DuckDB Implementation

In DuckDB, parameterized queries can be written using either named parameters prefixed with $ or positional parameters using ?. The named parameter style is generally preferred as it makes queries more readable and less prone to ordering errors.

Named parameters example:

Copy code
SELECT * FROM users 
WHERE age > $min_age 
AND country = $country;

Positional parameters example:

Copy code
SELECT * FROM users 
WHERE age > ? 
AND country = ?;

Usage in Practice

When using DuckDB through its Python API, you can pass parameters as a dictionary for named parameters or as a tuple/list for positional parameters:

Copy code
# Named parameters
conn.execute("SELECT * FROM users WHERE age > $min_age", 
            {"min_age": 21})

# Positional parameters
conn.execute("SELECT * FROM users WHERE age > ?", 
            (21,))

Benefits

Parameterized queries provide several key advantages:

• Protection against SQL injection by properly escaping and quoting values
• Better performance through query plan caching
• Cleaner and more maintainable code by separating the query logic from the data values
• Ability to reuse the same query structure with different parameter values

Differences from Other Databases

While most databases support parameterized queries, the syntax varies. DuckDB's $ prefix for named parameters is similar to PostgreSQL, while databases like MySQL use ? for all parameters and Microsoft SQL Server uses @ for named parameters. DuckDB's support for both styles makes it more flexible while maintaining compatibility with common patterns.