0
# SQL Composition
1

2
Safe SQL query construction with automatic quoting, parameter placeholders, and composable SQL elements to prevent SQL injection vulnerabilities while building dynamic queries programmatically.
3

4
## Capabilities
5

6
### SQL Building Classes
7

8
Composable SQL elements that can be combined to build complex queries safely with automatic escaping and quoting.
9

10
```python { .api }
11
class Composable:
12
    """Abstract base for SQL composition objects."""
13
    
14
    def __init__(self, wrapped):
15
        """Initialize with wrapped object."""
16
    
17
    def as_string(self, context):
18
        """Get string representation (abstract method)."""
19
    
20
    def __add__(self, other):
21
        """Concatenate with another Composable."""
22
    
23
    def __mul__(self, n):
24
        """Repeat n times."""
25
    
26
    def __eq__(self, other):
27
        """Equality comparison."""
28
    
29
    def __ne__(self, other):
30
        """Inequality comparison."""
31

32
class Composed(Composable):
33
    """Sequence of Composable objects."""
34
    
35
    def __init__(self, seq):
36
        """Initialize with sequence of Composables."""
37
    
38
    @property
39
    def seq(self):
40
        """Property returning content list."""
41
    
42
    def as_string(self, context):
43
        """Join all elements."""
44
    
45
    def __iter__(self):
46
        """Iterator over elements."""
47
    
48
    def __add__(self, other):
49
        """Concatenation."""
50
    
51
    def join(self, joiner):
52
        """
53
        Join elements with separator.
54
        
55
        Parameters:
56
        - joiner (Composable): Separator to join with
57
        
58
        Returns:
59
        Composed: New composed object with joined elements
60
        """
61

62
class SQL(Composable):
63
    """Raw SQL snippet."""
64
    
65
    def __init__(self, string):
66
        """
67
        Initialize with SQL string.
68
        
69
        Parameters:
70
        - string (str): Raw SQL text
71
        """
72
    
73
    @property
74
    def string(self):
75
        """Property returning wrapped string."""
76
    
77
    def as_string(self, context):
78
        """Return string as-is."""
79
    
80
    def format(self, *args, **kwargs):
81
        """
82
        Format with placeholders.
83
        
84
        Parameters:
85
        - *args: Positional Composable arguments
86
        - **kwargs: Named Composable arguments
87
        
88
        Returns:
89
        Composed: Formatted SQL composition
90
        """
91
    
92
    def join(self, seq):
93
        """
94
        Join sequence with SQL string.
95
        
96
        Parameters:
97
        - seq (sequence): Sequence of Composables to join
98
        
99
        Returns:
100
        Composed: Joined composition
101
        """
102

103
class Identifier(Composable):
104
    """SQL identifier (quoted)."""
105
    
106
    def __init__(self, *strings):
107
        """
108
        Initialize with identifier parts.
109
        
110
        Parameters:
111
        - *strings: Identifier components (schema, table, column, etc.)
112
        """
113
    
114
    @property
115
    def strings(self):
116
        """Property returning tuple of strings."""
117
    
118
    @property
119
    def string(self):
120
        """Property for single string (raises if multiple)."""
121
    
122
    def as_string(self, context):
123
        """Return quoted identifier."""
124

125
class Literal(Composable):
126
    """SQL literal value."""
127
    
128
    @property
129
    def wrapped(self):
130
        """Property returning wrapped object."""
131
    
132
    def as_string(self, context):
133
        """Return adapted/quoted value."""
134

135
class Placeholder(Composable):
136
    """Parameter placeholder."""
137
    
138
    def __init__(self, name=None):
139
        """
140
        Initialize with optional name.
141
        
142
        Parameters:
143
        - name (str, optional): Placeholder name for named parameters
144
        """
145
    
146
    @property
147
    def name(self):
148
        """Property returning placeholder name."""
149
    
150
    def as_string(self, context):
151
        """Return %s or %(name)s."""
152
```
153

154
**Usage Example:**
155

156
```python
157
from psycopg2 import sql
158

159
# Basic SQL composition
160
query = sql.SQL("SELECT {fields} FROM {table} WHERE {condition}").format(
161
    fields=sql.SQL(', ').join([
162
        sql.Identifier('id'),
163
        sql.Identifier('name'),
164
        sql.Identifier('email')
165
    ]),
166
    table=sql.Identifier('users'),
167
    condition=sql.SQL("{} > {}").format(
168
        sql.Identifier('age'),
169
        sql.Literal(25)
170
    )
171
)
172

173
print(query.as_string(conn))
174
# SELECT "id", "name", "email" FROM "users" WHERE "age" > 25
175

176
# Dynamic WHERE clauses
177
conditions = []
178
params = []
179

180
if name_filter:
181
    conditions.append(sql.SQL("{} ILIKE {}").format(
182
        sql.Identifier('name'),
183
        sql.Placeholder()
184
    ))
185
    params.append(f"%{name_filter}%")
186

187
if age_min:
188
    conditions.append(sql.SQL("{} >= {}").format(
189
        sql.Identifier('age'),
190
        sql.Placeholder()
191
    ))
192
    params.append(age_min)
193

194
if conditions:
195
    where_clause = sql.SQL(" WHERE ").join([
196
        sql.SQL(""),
197
        sql.SQL(" AND ").join(conditions)
198
    ])
199
else:
200
    where_clause = sql.SQL("")
201

202
query = sql.SQL("SELECT * FROM {table}{where}").format(
203
    table=sql.Identifier('users'),
204
    where=where_clause
205
)
206

207
# Execute with parameters
208
cur.execute(query, params)
209
```
210

211
### SQL Constants
212

213
Pre-defined SQL constants for common values.
214

215
```python { .api }
216
NULL = SQL("NULL")      # SQL NULL constant
217
DEFAULT = SQL("DEFAULT")  # SQL DEFAULT constant
218
```
219

220
**Usage Example:**
221

222
```python
223
from psycopg2 import sql
224

225
# Using SQL constants
226
insert_query = sql.SQL("INSERT INTO {table} ({columns}) VALUES ({values})").format(
227
    table=sql.Identifier('users'),
228
    columns=sql.SQL(', ').join([
229
        sql.Identifier('name'),
230
        sql.Identifier('email'),
231
        sql.Identifier('created_at')
232
    ]),
233
    values=sql.SQL(', ').join([
234
        sql.Placeholder('name'),
235
        sql.Placeholder('email'), 
236
        sql.DEFAULT  # Use DEFAULT for created_at timestamp
237
    ])
238
)
239

240
cur.execute(insert_query, {'name': 'Alice', 'email': 'alice@example.com'})
241
```
242

243
### Safe Dynamic Queries
244

245
Building queries dynamically while preventing SQL injection attacks.
246

247
**Usage Example:**
248

249
```python
250
from psycopg2 import sql
251
import psycopg2
252

253
def build_select_query(table, columns=None, conditions=None, order_by=None, limit=None):
254
    """Build a safe SELECT query dynamically."""
255
    
256
    # Default to all columns
257
    if columns:
258
        fields = sql.SQL(', ').join(sql.Identifier(col) for col in columns)
259
    else:
260
        fields = sql.SQL('*')
261
    
262
    # Base query
263
    query_parts = [
264
        sql.SQL("SELECT"),
265
        fields,
266
        sql.SQL("FROM"),
267
        sql.Identifier(table)
268
    ]
269
    params = []
270
    
271
    # Add WHERE clause
272
    if conditions:
273
        where_conditions = []
274
        for column, operator, value in conditions:
275
            where_conditions.append(
276
                sql.SQL("{} {} {}").format(
277
                    sql.Identifier(column),
278
                    sql.SQL(operator),
279
                    sql.Placeholder()
280
                )
281
            )
282
            params.append(value)
283
        
284
        query_parts.extend([
285
            sql.SQL("WHERE"),
286
            sql.SQL(" AND ").join(where_conditions)
287
        ])
288
    
289
    # Add ORDER BY
290
    if order_by:
291
        order_fields = []
292
        for col_spec in order_by:
293
            if isinstance(col_spec, tuple):
294
                col, direction = col_spec
295
                order_fields.append(
296
                    sql.SQL("{} {}").format(
297
                        sql.Identifier(col),
298
                        sql.SQL(direction)
299
                    )
300
                )
301
            else:
302
                order_fields.append(sql.Identifier(col_spec))
303
        
304
        query_parts.extend([
305
            sql.SQL("ORDER BY"),
306
            sql.SQL(", ").join(order_fields)
307
        ])
308
    
309
    # Add LIMIT
310
    if limit:
311
        query_parts.extend([
312
            sql.SQL("LIMIT"),
313
            sql.Placeholder()
314
        ])
315
        params.append(limit)
316
    
317
    return sql.SQL(" ").join(query_parts), params
318

319
# Usage
320
conn = psycopg2.connect("host=localhost dbname=mydb user=myuser")
321

322
query, params = build_select_query(
323
    table='users',
324
    columns=['id', 'name', 'email'],
325
    conditions=[
326
        ('age', '>=', 18),
327
        ('status', '=', 'active')
328
    ],
329
    order_by=[('name', 'ASC'), ('id', 'DESC')],
330
    limit=50
331
)
332

333
with conn.cursor() as cur:
334
    cur.execute(query, params)
335
    results = cur.fetchall()
336

337
conn.close()
338
```
339

340
### Table and Column Name Safety
341

342
Safe handling of dynamic table and column names that come from user input or configuration.
343

344
**Usage Example:**
345

346
```python
347
from psycopg2 import sql
348

349
def safe_table_query(table_name, user_columns, user_input_table):
350
    """Safely handle user-provided table and column names."""
351
    
352
    # Validate table name (you should have your own validation)
353
    allowed_tables = ['users', 'products', 'orders']
354
    if user_input_table not in allowed_tables:
355
        raise ValueError(f"Table {user_input_table} not allowed")
356
    
357
    # Validate column names
358
    allowed_columns = ['id', 'name', 'email', 'created_at']
359
    for col in user_columns:
360
        if col not in allowed_columns:
361
            raise ValueError(f"Column {col} not allowed")
362
    
363
    # Build safe query
364
    query = sql.SQL("SELECT {columns} FROM {table}").format(
365
        columns=sql.SQL(', ').join(sql.Identifier(col) for col in user_columns),
366
        table=sql.Identifier(user_input_table)
367
    )
368
    
369
    return query
370

371
# Usage with validation
372
try:
373
    query = safe_table_query('users', ['id', 'name'], 'users')
374
    cur.execute(query)
375
    results = cur.fetchall()
376
except ValueError as e:
377
    print(f"Security error: {e}")
378
```
379

380
### Advanced Composition Patterns
381

382
Complex query building patterns for advanced use cases.
383

384
**Usage Example:**
385

386
```python
387
from psycopg2 import sql
388

389
def build_upsert_query(table, conflict_columns, update_columns, insert_columns=None):
390
    """Build PostgreSQL UPSERT query using ON CONFLICT."""
391
    
392
    if insert_columns is None:
393
        insert_columns = update_columns
394
    
395
    # Build INSERT part
396
    insert_query = sql.SQL("INSERT INTO {table} ({columns}) VALUES ({placeholders})").format(
397
        table=sql.Identifier(table),
398
        columns=sql.SQL(', ').join(sql.Identifier(col) for col in insert_columns),
399
        placeholders=sql.SQL(', ').join(sql.Placeholder() for _ in insert_columns)
400
    )
401
    
402
    # Build ON CONFLICT part
403
    conflict_clause = sql.SQL("ON CONFLICT ({conflict_cols}) DO UPDATE SET {updates}").format(
404
        conflict_cols=sql.SQL(', ').join(sql.Identifier(col) for col in conflict_columns),
405
        updates=sql.SQL(', ').join(
406
            sql.SQL("{col} = EXCLUDED.{col}").format(col=sql.Identifier(col))
407
            for col in update_columns
408
        )
409
    )
410
    
411
    # Combine
412
    return sql.SQL(" ").join([insert_query, conflict_clause])
413

414
# Usage
415
upsert_query = build_upsert_query(
416
    table='users',
417
    conflict_columns=['email'],
418
    update_columns=['name', 'updated_at'],
419
    insert_columns=['email', 'name', 'created_at', 'updated_at']
420
)
421

422
params = ['user@example.com', 'Updated Name', '2023-01-01', '2023-01-02']
423
cur.execute(upsert_query, params)
424

425
# Complex joins with dynamic conditions
426
def build_join_query(base_table, joins, conditions=None):
427
    """Build query with multiple joins."""
428
    
429
    query_parts = [
430
        sql.SQL("SELECT * FROM"),
431
        sql.Identifier(base_table)
432
    ]
433
    
434
    # Add joins
435
    for join_type, join_table, on_condition in joins:
436
        query_parts.extend([
437
            sql.SQL(join_type),
438
            sql.Identifier(join_table),
439
            sql.SQL("ON"),
440
            sql.SQL(on_condition)
441
        ])
442
    
443
    # Add WHERE conditions
444
    if conditions:
445
        query_parts.extend([
446
            sql.SQL("WHERE"),
447
            sql.SQL(" AND ").join(sql.SQL(cond) for cond in conditions)
448
        ])
449
    
450
    return sql.SQL(" ").join(query_parts)
451

452
# Usage
453
join_query = build_join_query(
454
    base_table='users',
455
    joins=[
456
        ('LEFT JOIN', 'profiles', 'users.id = profiles.user_id'),
457
        ('INNER JOIN', 'organizations', 'users.org_id = organizations.id')
458
    ],
459
    conditions=['users.active = true', 'organizations.status = \'active\'']
460
)
461
```
462

463
## Types
464

465
### Composable Base Types
466

467
```python { .api }
468
class Composable:
469
    """Base class for all SQL composition objects."""
470
    
471
    def as_string(self, context) -> str:
472
        """Get SQL string representation."""
473
    
474
    def __add__(self, other: 'Composable') -> 'Composed':
475
        """Concatenate composables."""
476
    
477
    def __mul__(self, n: int) -> 'Composed':
478
        """Repeat composable n times."""
479
    
480
    def __eq__(self, other) -> bool:
481
        """Equality comparison."""
482

483
class Composed(Composable):
484
    """Sequence of multiple composables."""
485
    
486
    seq: list  # List of composable elements
487
    
488
    def join(self, joiner: Composable) -> 'Composed':
489
        """Join elements with separator."""
490

491
class SQL(Composable):
492
    """Raw SQL text."""
493
    
494
    string: str  # The SQL string
495
    
496
    def format(self, *args, **kwargs) -> 'Composed':
497
        """Format with placeholders."""
498
    
499
    def join(self, seq) -> 'Composed':
500
        """Join sequence with this SQL."""
501

502
class Identifier(Composable):
503
    """Quoted SQL identifier."""
504
    
505
    strings: tuple[str, ...]  # Identifier components
506
    string: str  # Single string (if only one component)
507

508
class Literal(Composable):
509
    """SQL literal value."""
510
    
511
    wrapped: Any  # The wrapped Python value
512

513
class Placeholder(Composable):
514
    """Parameter placeholder."""
515
    
516
    name: str | None  # Placeholder name (None for positional)
517
```
518

519
### SQL Constants
520

521
```python { .api }
522
NULL: SQL       # SQL("NULL")
523
DEFAULT: SQL    # SQL("DEFAULT")
524
```