Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Consultas parametrizadas são essenciais para:
- Segurança: Prevenção de ataques de injeção SQL
- Desempenho: Ativação da reutilização de planos de consulta
- Correção: Tratamento adequado de caracteres especiais e tipos de dados
O controlador mssql-python utiliza, por defeito, o estilo de parâmetros pyformat com marcadores de posição %(name)s, mas é igualmente compatível com outros estilos de parâmetros, caso prefira esse formato.
Consultas parametrizadas básicas
Parâmetros nomeados
Use marcadores nomeados para passar parâmetros às consultas:
import mssql_python
conn = mssql_python.connect(
"Server=<server>.database.windows.net;"
"Database=<database>;"
"Authentication=ActiveDirectoryDefault;"
"Encrypt=yes"
)
cursor = conn.cursor()
# Single parameter
cursor.execute(
"SELECT * FROM Production.Product WHERE ProductSubcategoryID = %(category)s",
{"category": 5}
)
# Multiple parameters
cursor.execute(
"SELECT * FROM Production.Product WHERE ProductSubcategoryID = %(cat)s AND ListPrice > %(price)s",
{"cat": 5, "price": 10.00}
)
Reutilização de parâmetros
Pode consultar o mesmo parâmetro várias vezes:
cursor.execute("""
SELECT * FROM Production.Product
WHERE (Name LIKE %(search)s OR ProductNumber LIKE %(search)s)
AND ProductSubcategoryID = %(cat)s
""", {"search": "%Road%", "cat": 2})
Quando passa parâmetros como dicionário, o driver substitui cada %(name)s marcador de posição por um parâmetro posicional. Não substitui texto de marcador de posição dentro de literais de cadeia, identificadores entre aspas ou entre parênteses retos, ou comentários. Para obter um literal % no texto da declaração, escreva %%, incluindo dentro de literais de cadeia e comentários. Não precisa de escapar % nos valores dos parâmetros.
Tipos de dados em parâmetros
Parâmetros de cadeia de caracteres
As cadeias são automaticamente citadas e os caracteres especiais escapam em segurança:
# Strings are automatically quoted
cursor.execute(
"SELECT * FROM Person.EmailAddress WHERE EmailAddress = %(email)s",
{"email": "ken0@adventure-works.com"}
)
# Special characters are escaped
cursor.execute(
"SELECT * FROM Person.Person WHERE LastName = %(name)s",
{"name": "O'Brien"} # Apostrophe handled safely
)
Parâmetros numéricos
Passe os valores numéricos como inteiros, decimais ou flutuantes, dependendo da precisão necessária:
from decimal import Decimal
# Integer
cursor.execute("SELECT * FROM Production.Product WHERE ProductID = %(id)s", {"id": 42})
# Decimal for financial precision
cursor.execute(
"SELECT * FROM Production.Product WHERE ListPrice >= %(min)s AND ListPrice <= %(max)s",
{"min": Decimal("10.00"), "max": Decimal("100.00")}
)
# Float
cursor.execute(
"SELECT * FROM Production.Product WHERE Weight > %(threshold)s",
{"threshold": 15.0}
)
Parâmetros data/hora
Use o módulo do datetime Python para passar valores de data, data, hora e hora:
from datetime import date, datetime, time
# Date
cursor.execute(
"SELECT * FROM Sales.SalesOrderHeader WHERE OrderDate >= %(date)s AND OrderDate < DATEADD(day, 1, %(date)s)",
{"date": date(2014, 3, 15)}
)
# Datetime
cursor.execute(
"SELECT * FROM Sales.SalesOrderHeader WHERE ModifiedDate >= %(start)s AND ModifiedDate < %(end)s",
{"start": datetime(2014, 3, 1), "end": datetime(2014, 4, 1)}
)
# Time
cursor.execute(
"SELECT * FROM HumanResources.Shift WHERE StartTime >= %(time)s",
{"time": time(9, 0, 0)}
)
Nenhum para NULL
Passe None para inserir ou atualizar valores NULL na base de dados:
# Insert NULL
cursor.execute("""
CREATE TABLE #NullDemo (ID INT IDENTITY, Name NVARCHAR(50), Email NVARCHAR(100))
""")
cursor.execute(
"INSERT INTO #NullDemo (Name, Email) VALUES (%(name)s, %(email)s)",
{"name": "Guest", "email": None}
)
# Query with NULL
cursor.execute(
"UPDATE #NullDemo SET Email = %(email)s WHERE ID = %(id)s",
{"email": None, "id": 1}
)
Parâmetros binários
Inserir dados binários como objetos bytes:
# Binary data
hash_value = b'\x00\x01\x02\x03'
cursor.execute("""
CREATE TABLE #HashDemo (ID INT IDENTITY, DocumentHash VARBINARY(256))
""")
cursor.execute(
"INSERT INTO #HashDemo (DocumentHash) VALUES (%(hash)s)",
{"hash": hash_value}
)
Construir consultas dinâmicas
Cláusulas condicionais WHERE
Construa as cláusulas WHERE dinamicamente com base em critérios opcionais de pesquisa:
def search_products(cursor, name: str | None = None,
category: int | None = None,
min_price: float | None = None) -> list:
"""Build query with optional conditions."""
conditions = []
params = {}
if name:
conditions.append("Name LIKE %(name)s")
params["name"] = f"%{name}%"
if category:
conditions.append("ProductSubcategoryID = %(category)s")
params["category"] = category
if min_price is not None:
conditions.append("ListPrice >= %(min_price)s")
params["min_price"] = min_price
query = "SELECT TOP 10 * FROM Production.Product"
if conditions:
query += " WHERE " + " AND ".join(conditions)
cursor.execute(query, params)
return cursor.fetchall()
# Usage
products = search_products(cursor, name="Road", min_price=10.0)
Cláusula IN com múltiplos valores
Constrói a IN cláusula dinamicamente com um marcador para cada valor. Nunca use formatação de strings para injetar valores diretamente:
def get_products_by_ids(cursor, product_ids: list[int]) -> list:
"""Query with IN clause using qmark (?) placeholders."""
if not product_ids:
return []
placeholders = ", ".join("?" for _ in product_ids)
query = f"SELECT * FROM Production.Product WHERE ProductID IN ({placeholders})"
cursor.execute(query, tuple(product_ids))
return cursor.fetchall()
# Usage
products = get_products_by_ids(cursor, [1, 5, 10, 15])
O mesmo padrão funciona com os marcadores de posição pyformat (%(name)s):
def get_products_by_ids(cursor, product_ids: list[int]) -> list:
"""Query with IN clause using pyformat placeholders."""
if not product_ids:
return []
# Create named parameters for each ID
params = {f"id{i}": id for i, id in enumerate(product_ids)}
placeholders = ", ".join(f"%(id{i})s" for i in range(len(product_ids)))
query = f"SELECT * FROM Production.Product WHERE ProductID IN ({placeholders})"
cursor.execute(query, params)
return cursor.fetchall()
# Usage
products = get_products_by_ids(cursor, [1, 5, 10, 15])
Seleção dinâmica de colunas
Use uma lista de permitir para validar colunas antes de construir dinamicamente listas SELECT, mantendo os valores dos filtros como parâmetros:
def get_employee(cursor, employee_id: int, columns: list[str] | None = None) -> dict:
"""Get employee with specified columns."""
# Allow list of permitted columns
allowed = {"BusinessEntityID", "LoginID", "JobTitle", "HireDate", "SalariedFlag"}
if columns:
# Validate columns against allow list
safe_columns = [c for c in columns if c in allowed]
if not safe_columns:
raise ValueError("No valid columns specified")
column_list = ", ".join(safe_columns)
else:
column_list = "*"
# ID is always a parameter, never interpolated
query = f"SELECT {column_list} FROM HumanResources.Employee WHERE BusinessEntityID = %(id)s"
cursor.execute(query, {"id": employee_id})
return cursor.fetchone()
Sequência de ordenação
Use uma lista de permissão para validar colunas de ordenação antes da interpolação:
def get_products_sorted(cursor, sort_by: str = "Name",
descending: bool = False) -> list:
"""Get products with validated sort order."""
# Allow list of permitted sort columns
allowed_sorts = {"Name", "ListPrice", "SellStartDate", "ProductID"}
if sort_by not in allowed_sorts:
sort_by = "Name" # Default
direction = "DESC" if descending else "ASC"
# sort_by and direction are validated, safe to interpolate
query = f"SELECT TOP 10 * FROM Production.Product ORDER BY {sort_by} {direction}"
cursor.execute(query)
return cursor.fetchall()
INSERT operações
Inserção única
Insira uma única linha com valores parametrizados:
cursor.execute("""
CREATE TABLE #ParamInsert (ID INT IDENTITY, Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)
""")
cursor.execute("""
INSERT INTO #ParamInsert (Name, Price, CategoryID)
VALUES (%(name)s, %(price)s, %(category)s)
""", {"name": "New Widget", "price": 29.99, "category": 5})
conn.commit()
Inserir com retorno de identidade
Use OUTPUT para recuperar o valor de identidade gerado após inserir uma nova linha:
cursor.execute("""
CREATE TABLE #IdentDemo (ProductID INT IDENTITY, Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)
""")
cursor.execute("""
INSERT INTO #IdentDemo (Name, Price, CategoryID)
OUTPUT INSERTED.ProductID
VALUES (%(name)s, %(price)s, %(category)s)
""", {"name": "New Widget", "price": 29.99, "category": 5})
new_id = cursor.fetchval()
conn.commit()
print(f"Created product with ID: {new_id}")
Inserção em lote com executemany
Use executemany() para inserir várias linhas de forma eficiente com uma única instrução parametrizada:
Sugestão
Para volumes grandes, bulkcopy() é mais rápido do que executemany() porque utiliza o protocolo de inserção em massa em vez de instruções individuais INSERT . Consulte cópia em lote.
cursor.execute("""
CREATE TABLE #BatchDemo (ID INT IDENTITY, Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)
""")
products = [
{"name": "Widget A", "price": 19.99, "cat": 1},
{"name": "Widget B", "price": 29.99, "cat": 1},
{"name": "Widget C", "price": 39.99, "cat": 2},
]
cursor.executemany("""
INSERT INTO #BatchDemo (Name, Price, CategoryID)
VALUES (%(name)s, %(price)s, %(cat)s)
""", products)
conn.commit()
UPDATE operações de cópia em massa
Atualizar uma única linha ou várias linhas com base em condições utilizando cláusulas WHERE parametrizadas:
# Create temp table with sample data
cursor.execute("""
CREATE TABLE #UpdDemo (
ID INT IDENTITY, Name NVARCHAR(50),
Price DECIMAL(10,2), CategoryID INT, ModifiedAt DATETIME
)
""")
cursor.execute("""
INSERT INTO #UpdDemo (Name, Price, CategoryID)
VALUES ('Widget X', 25.00, 5), ('Widget Y', 30.00, 5), ('Gadget Z', 50.00, 3)
""")
# Single row update
cursor.execute("""
UPDATE #UpdDemo
SET Price = %(price)s, ModifiedAt = %(modified)s
WHERE ID = %(id)s
""", {"price": 34.99, "modified": datetime.now(), "id": 1})
# Conditional update
cursor.execute("""
UPDATE #UpdDemo
SET Price = Price * %(multiplier)s
WHERE CategoryID = %(category)s
""", {"multiplier": 1.1, "category": 5})
conn.commit()
DELETE operações
Apagar linhas das tabelas com base nas condições parametrizadas do filtro:
# Create temp table with sample data
cursor.execute("""
CREATE TABLE #DelDemo (
ID INT IDENTITY, Name NVARCHAR(50), Status NVARCHAR(20), OrderDate DATE
)
""")
cursor.execute("""
INSERT INTO #DelDemo (Name, Status, OrderDate)
VALUES ('Order1', 'Active', '2024-06-01'), ('Order2', 'Cancelled', '2022-05-01'),
('Order3', 'Cancelled', '2022-11-01')
""")
# Delete single row
cursor.execute(
"DELETE FROM #DelDemo WHERE ID = %(id)s",
{"id": 1}
)
# Delete with conditions
cursor.execute("""
DELETE FROM #DelDemo
WHERE Status = %(status)s AND OrderDate < %(date)s
""", {"status": "Cancelled", "date": date(2023, 1, 1)})
conn.commit()
Considerações de segurança
Nunca interpole a entrada do utilizador
Use sempre parâmetros para escapar da entrada do utilizador de forma segura:
# DANGEROUS - SQL injection vulnerability!
user_input = "'; DROP TABLE Users;--"
query = f"SELECT * FROM Person.Person WHERE LastName = '{user_input}'" # DON'T DO THIS
# SAFE - always use parameters
cursor.execute(
"SELECT * FROM Person.Person WHERE LastName = %(name)s",
{"name": user_input} # Input is safely escaped
)
Validar nomes de tabelas e colunas
Use listas de permissão para validar identificadores de tabelas e colunas que não possam ser parametrizados:
def query_table(cursor, table: str, columns: list[str]):
"""Query with validated table and column names."""
# Allow list of permitted tables
allowed_tables = {"Person.Person", "Production.Product", "Sales.SalesOrderHeader"}
if table not in allowed_tables:
raise ValueError(f"Invalid table: {table}")
# Allow list of permitted columns per table
allowed_columns = {
"Person.Person": {"BusinessEntityID", "FirstName", "LastName"},
"Production.Product": {"ProductID", "Name", "ListPrice"},
"Sales.SalesOrderHeader": {"SalesOrderID", "CustomerID", "TotalDue"},
}
safe_columns = [c for c in columns if c in allowed_columns.get(table, set())]
if not safe_columns:
raise ValueError("No valid columns")
# Safe to interpolate after validation
query = f"SELECT TOP 5 {', '.join(safe_columns)} FROM {table}"
cursor.execute(query)
return cursor.fetchall()
Utilizar procedimentos armazenados para operações complexas
Os procedimentos armazenados adicionam outra camada de proteção e permitem a execução de lógica de negócio complexa do lado do servidor:
cursor.execute("""
EXECUTE dbo.uspGetEmployeeManagers @BusinessEntityID = %(id)s
""", {"id": 5})
rows = cursor.fetchall()
Benefícios de desempenho
Armazenamento em cache do plano de consulta
Quando usas consultas parametrizadas, o SQL Server reutiliza o mesmo plano de execução através de diferentes valores de parâmetro, em vez de compilar um novo plano para cada consulta.
for product_id in range(1, 100):
cursor.execute(
"SELECT * FROM Production.Product WHERE ProductID = %(id)s",
{"id": product_id}
)
Declarações preparadas
Use instruções preparadas para consultas que faz com frequência. O driver prepara instruções automaticamente. Quando executa novamente a mesma instrução no mesmo cursor, e cada parâmetro tem o mesmo tipo e tamanho da execução anterior, o driver reutiliza a instrução preparada em vez de a preparar novamente. O driver também armazena em cache o texto convertido das pyformat instruções, por isso não volta a converter os marcadores de posição em cada execução.
query = "SELECT Name, ListPrice FROM Production.Product WHERE ProductSubcategoryID = %(cat)s"
for category in [1, 2, 3, 4, 5]:
cursor.execute(query, {"cat": category})
products = cursor.fetchall()