Prepared statement

En sistemes de base de dades, prepared statement o sentència parametritzada és una característica utilitzada per executar sentències a base de dades similars i repetidament amb una eficiència alta. S'utilitza principalment en sentències SQL del tipus consulta (select) o actualització (update) on la declaració parametritzada pren la forma d'una plantilla en la qual se substitueixen certs valors constants durant cada execució.

El workflow habitual en l'ús de les prepared statement és el següent:

1. Prepare: La sentència plantilla és creada per l'aplicació i és enviada al sistema de gestió de base de dades (SGBD). Els valors que queden sense especificar s'anomenen paràmetres, placeholders o bind variables (etiquetats com un "?"):
   • INSERT INTO PRODUCT (name, price) VALUES (?, ?)
2. El SGBD parseja, compila i realitza una optimització de la consulta en la sentència plantilla i emmagatzema el resultat sense executar-lo.
3. Executar: en darrer lloc, l'aplicació substitueix (o bind; 'lliga') els valors pels paràmetres i llavors, el SGBD executa la sentència (possiblement retornant un resultat). L'aplicació pot executar l'statement tantes vegades com valor diferents es tinguin. En aquest exemple, es substitueix 'Pa' pel primer paràmetre i '1.00' pel segon paràmetre.

Si es comparen amb l'execució de les sentències SQL directament, les parametritzades ofereixen dos avantatges principals:

• El cost de compilar i optimitzar de la sentència plantilla només es succeirà un cop, encara que la declaració s'executi diverses vegades. Tanmateix, no tota optimització pot ser efectuada durant la compilació del prepared statement per dues raons: la millor opció depèn dels valors específics dels paràmetres i la millor opció pot canviar si les taules com els índexs canvien amb el temps.
• Les sentències parametritzades són resistents contra injecció SQL, perquè els valors parametritzats, que són transmesos després utilitzant un protocol diferent, no necessiten ser escapats. Si la plantilla de la sentència original no prové d'una entrada externa, no es pot donar la injecció SQL.

D'altra banda, si una consulta és executada només una vegada, les prepared statement en el costat del servidor podrien ser més lentes perquè requereixen un temps addicional d'anada i tornada al servidor. Les limitacions d'implementació també poden donar lloc a penalitzacions de rendiment: algunes versions de MySQL no guarden el resultat en memòria cau en el cas de consultes parametritzades i alguns SGBDs com PostgreSQL no apliquen optimitzacions addicionals a les consultes durant execució.

Exemples

Java JDBC

Aquest exemple està programat en Java i utilitza l'API JDBC:

java.sql.PreparedStatement stmt = connection.prepareStatement(
 "SELECT * FROM usuaris WHERE NOM = ? AND HABITACIO = ?");
stmt.setString(1, nomUsuari);
stmt.setInt(2, numeroHabitacio);
stmt.executeQuery();

Java PreparedStatement proveeix "setters" (setInt(int), setString(String), setDouble(double), etc.) pels principals tipus de dades incorporats.

Python DB-API

Aquest exemple usa Python DB-API amb SQLite i paramstyle='qmark':

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()

_users = [('mare', 'vermell'),
 ('pare', 'verd'),
 ('jo', 'blau')]
c.executemany('INSERT INTO users VALUES (?,?)', _users)

params = ('sister', 'yellow')
c.execute('SELECT * FROM users WHERE username=? AND room=?', params)
c.fetchone()