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 playgroundFix¶
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].