Skip to content

param-type-mismatch

Default severity: error

What it does

Checks the arguments a call passes for its query's placeholders against the type each placeholder takes from its context: the column it is compared with or stored in, a cast, LIMIT, an IN list, = ANY(%s) (a list). It applies to calls of a marked helper whose parameters are spelled out: a params argument holding a tuple, list or dict literal, *args, or **params.

Driver calls are checked too: execute and executemany (each row) on a psycopg, sqlite3 or aiosqlite connection or cursor, execute_fetchall and execute_insert on an aiosqlite connection, and execute, fetch, fetchrow, fetchval, executemany and fetchmany (each row) on an asyncpg connection, when the file binds the receiver to psycopg.connect(...), sqlite3.connect(...), aiosqlite.connect(...), asyncpg.connect(...), <connection>.cursor() or a driver class annotation, and the execute and scalar helpers of sqlty.drivers.psycopg. The query is a literal, SQL[T]("...") of one, or a name assigned once to one. A single value passed as a driver call's parameters, execute(query, user_id), is reported too: the driver raises TypeError and wants a sequence, (user_id,). sqlite3 (and so aiosqlite) reads a str or bytes as a sequence, so those are left alone there.

The fields of a --sql t-string are checked as well: psycopg sends each {value} as a parameter. A t-string with a field that changes the SQL, such as {part:q}, is not.

An argument is judged only where the source states its type: a literal, a variable bound to a literal, annotated or assigned a class, a constant imported from a checked module, a call of a known class (date(...), UUID(...)). An argument of unknown type is never reported, and neither is a str, which the server coerces to any type. int, float and Decimal are interchangeable. On PostgreSQL a bool is not a number and an int is not a boolean, since the server has no implicit cast between them; MySQL takes either. asyncpg encodes each argument as its parameter's type: it takes a bool for a number, but not a str for a number, a date or time, bytes or a list. A parameter with an explicit cast (%s::text) takes anything. None is reported only for a parameter stored in a NOT NULL column. SQLite is dynamically typed: with dialect = "sqlite" only None is checked.

Why is this bad?

The database rejects the value, or compares it in a way that never matches, at run time and only on the path that runs the query.

Example

[tool.sqlty]
schema = "schema.sql"

schema.sql:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, born DATE);
from datetime import date
from typing import Annotated, LiteralString

from sqlty import SQL


def execute(query: Annotated[LiteralString, SQL], params: tuple[object, ...]) -> None: ...


execute("SELECT name FROM users WHERE id = %s AND born > %s", (1, date(2000, 1, 1)))
# error: [param-type-mismatch] "parameter 1 expects int, got datetime.date"
execute("SELECT name FROM users WHERE id = %s", (date(2000, 1, 1),))
# error: [param-type-mismatch] "parameter 2 is not nullable, got None"
execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, None))
# error: [param-type-mismatch] "parameter 'id' expects int, got bytes"
execute("DELETE FROM users WHERE id = %(id)s", {"id": b"1"})
# error: [param-type-mismatch] "parameter 1 expects int, got bytes"
execute("SELECT name FROM users LIMIT %s", (b"1",))
Open in playground

Fix

Pass a value of the type the query expects, or change the query. Turn the rule off with param-type-mismatch = "ignore" in [tool.sqlty.rules].