Prepared Statements: kuidas jõudluse optimeerimisest sai SQL-turvalisuse alustala

Tarkvaraarenduses on reegleid, mida korratakse peaaegu dogmaatiliselt: ära liida kasutaja sisendit otse SQL-päringu sõne sisse; kasuta parameetreid.

Enamik arendajaid teab, et parameetriseeritud päringud ja prepared statements aitavad vältida SQL-i süstimist (SQL injection). Vähem räägitakse sellest, et päringute ettevalmistamise algne väärtus oli eelkõige jõudluses: kui sama struktuuriga SQL-i käivitatakse korduvalt, pole mõistlik teha iga kord kogu ettevalmistustööd nullist.

Siin on aga oluline eristus: parameetrite sidumine, päringu ettevalmistamine ja päringuplaani korduskasutamine on omavahel seotud, kuid need ei ole üks ja sama asi. Just see seletab, miks turvakasu võib saada kohe, samal ajal kui maksimaalse jõudluskasu saavutamiseks peab mõnikord ka rakenduse kood olema teadlikult üles ehitatud põhimõttel prepare once, execute many.

1. Algusaastad: kui protsessori töö oli kallis

1970. aastate lõpus ja 1980. aastatel jõudsid relatsioonilised andmebaasid uurimisprojektidest järjest laiemasse kommertskasutusse. IBM-i System R-i tööle järgnesid SQL/DS ja hiljem DB2, samal ajal arenes Oracle'i relatsiooniline andmebaas.

SQL-päringu töötlemine ei tähenda ainult andmete lugemist. Lihtsustatult peab andmebaasimootor tegema mitu etappi:

1. Parsimine (parsing) -- SQL-teksti süntaktiline analüüs.
2. Semantiline analüüs -- kontroll, millistele tabelitele, veergudele ja objektidele päring viitab ning kas need on kasutatavad.
3. Optimeerimine ja päringuplaani koostamine -- otsustatakse näiteks indeksite, liitmiste (join) ja juurdepääsumeetodite kasutamine.
4. Käivitamine -- päringu tegelik täitmine.

Kui rakendus teeb tuhandeid sama struktuuriga päringuid ja muutub ainult väärtus, näiteks:

SELECT name FROM customers WHERE id = 1;
SELECT name FROM customers WHERE id = 2;
SELECT name FROM customers WHERE id = 3;

siis on loomulik küsida: miks peaks andmebaas iga variatsiooni käsitlema täiesti uue SQL-tekstina?

Parameetriseeritud kuju eraldab muutuvad väärtused päringu struktuurist:

SELECT name FROM customers WHERE id = ?;

või sõltuvalt andmebaasist näiteks:

SELECT name FROM customers WHERE id = :id;

Selline eraldamine võimaldab andmebaasil ja draiveril teha korduva töö efektiivsemalt.

2. Mis on prepared statement tegelikult?

Rakendus annab andmebaasiliidesele SQL-päringu struktuuri koos kohahoidjatega ning väärtused seotakse sellega eraldi. Natiivsete serveripoolsete prepared statement'ide korral saab andmebaas päringu ette valmistada ning korduvatel käivitustel kasutada sama ettevalmistatud struktuuri uute parameetritega.

Oluline on vältida liiga lihtsat universaalset seletust „SQL kompileeritakse ühe korra ja edaspidi saadetakse ainult mäluaadress ning väärtused". Konkreetne teostus sõltub andmebaasist ja draiverist. Mõnes süsteemis on nähtav serveripoolne statement'i identifikaator, mõnes mängib suuremat rolli execution plan cache, mõnes cursor ja shared pool ning mõni klienditeek võib prepared statement'e isegi emuleerida.

PostgreSQL

PostgreSQL-i PREPARE loob serveripoolse prepared statement'i. EXECUTE käivitab selle uute parameetritega. PostgreSQL võib kasutada kas custom plan'i, mis koostatakse konkreetseid parameetriväärtusi arvestades, või generic plan'i, mida saab erinevate väärtustega korduskasutada.

See on hea näide sellest, miks „prepared" ei tähenda tingimata, et täpselt sama execution plan kasutatakse alati. Kui parameetri väärtus mõjutab optimaalset plaani tugevalt, võib custom plan olla kiirem kui universaalse plaani korduskasutamine.

MySQL

MySQL toetab serveripoolseid prepared statement'e väga otseselt. Binaarprotokollis saadab klient COM_STMT_PREPARE käsu ja server tagastab statement ID. Hilisemal COM_STMT_EXECUTE käivitamisel kasutab klient seda ID-d ning saadab parameetrite väärtused.

Seega vastab MySQL üsna hästi klassikalisele mudelile:

PREPARE SQL
    ↓
statement ID
    ↓
EXECUTE + parameetrid
    ↓
EXECUTE + uued parameetrid
    ↓
EXECUTE + uued parameetrid

Microsoft SQL Server

SQL Serveris on oluline roll execution plan cache'il. Parameetrite kasutamine võib suurendada tõenäosust, et varem koostatud execution plan leitakse ja kasutatakse uuesti.

SQL Server toetab ka sp_executesql mehhanismi, kus SQL-tekst ja parameetrid antakse eraldi. Seega ei pea SQL Serveri maailmas „prepared statement" rakenduse seisukohalt tähendama täpselt PostgreSQL-i PREPARE/EXECUTE mudelit.

Oracle Database

Oracle'i terminoloogias kohtab sageli mõisteid bind variables, cursor, shared pool, hard parse ja soft parse.

Kui SQL-i tekst koos literalidega pidevalt muutub, näiteks:

SELECT name FROM customers WHERE id = 1001;
SELECT name FROM customers WHERE id = 1002;

võib see takistada sama SQL-struktuuri efektiivset jagamist. Bind variable'iga:

SELECT name FROM customers WHERE id = :id;

saab Oracle olemasolevat cursor'it ja shared pool'is olevat tööd võimaluse korral korduskasutada. Oracle'i maailmas kirjeldab seda hästi põhimõte parse once, execute many.

3. Veebibuum ja SQL injection

Veebi kiire kasv 1990. aastate teisel poolel muutis andmebaasipõhiste rakenduste loomise väga lihtsaks. PHP sai üheks populaarsemaks serveripoolseks veebikeeleks ning SQL-päringuid koostati sageli sõnede liitmise teel.

1998. aastal avaldas rain.forest.puppy (rfp) ajakirja Phrack 54. numbris artikli "NT Web Technology Vulnerabilities", milles kirjeldati muu hulgas SQL-päringute manipuleerimise tehnikaid. Seda käsitletakse ühe varase avaliku SQL injection'i käsitlusena.

Kuidas SQL injection päringut muudab?

Vaatame teadlikult lihtsustatud sisselogimisvormi:

Kasutajanimi: [________________]
Parool:       [________________]

              [ Logi sisse ]

Vana rakendus võib kasutajanime ja parooli otse SQL-sõne sisse liita:

$username = $_POST['user'];
$password = $_POST['pass'];

$query = "SELECT id, username
          FROM users
          WHERE username = '$username'
          AND password = '$password'";

Märkus: parooli võrdlemine avatekstina on siin ainult SQL injection'i selgitamiseks mõeldud lihtsustus. Päris rakenduses tuleb paroole salvestada turvalise parooliräsina ning kontrollida sobiva parooliräsi API abil.

Tavaline sisend:

Kasutajanimi: mari
Parool:       salajane

annab päringu:

SELECT id, username
FROM users
WHERE username = 'mari'
  AND password = 'salajane';

Kasutaja leitakse ainult siis, kui mõlemad tingimused on tõesed.

Rünne 1: paroolikontrolli kommenteerimine

Oletame, et ründaja teab või oletab, et administraatori kasutajanimi on admin, ning sisestab:

Kasutajanimi: admin' --
Parool:       midagi

Rakendus teeb endiselt ainult stringide liitmist ja tulemuseks saab:

SELECT id, username
FROM users
WHERE username = 'admin' --'
  AND password = 'midagi';

SQL-is alustab -- rea kommentaari. Andmebaasi jaoks on päringu aktiivne osa sisuliselt:

SELECT id, username
FROM users
WHERE username = 'admin';

See osa:

AND password = 'midagi'

on nüüd kommentaar ja parooli enam ei kontrollita.

Ründaja ei arvanud parooli ära. Ta muutis SQL-päringu struktuuri nii, et parooli kontrolliv osa ei osale enam päringus.

Kommentaarisüntaksi täpsed reeglid erinevad andmebaaside vahel. Näide illustreerib põhimõtet, mitte universaalset kõigi SQL-mootorite jaoks identset sisendit.

Rünne 2: päringu loogika muutmine OR abil

Teine võimalus on proovida lisada päringusse tingimus, mis on alati tõene.

Ründaja sisestab kasutajanime väljale:

' OR '1'='1' --

ja parooliks näiteks:

midagi

Vorm näeks välja:

Kasutajanimi: ' OR '1'='1' --
Parool:       midagi

Rakendus liidab väärtused SQL-teksti:

SELECT id, username
FROM users
WHERE username = '' OR '1'='1' --'
  AND password = 'midagi';

-- tõttu muutub ülejäänud rida kommentaariks. Aktiivseks loogikaks jääb sisuliselt:

SELECT id, username
FROM users
WHERE username = ''
   OR '1' = '1';

Vaatame tingimusi eraldi.

username = ''

on enamiku ridade puhul väär.

Kuid:

'1' = '1'

on alati tõene.

Seega:

FALSE OR TRUE

annab:

TRUE

Päring võib seetõttu tagastada palju või kõik users tabeli read. Kui halvasti kirjutatud autentimisloogika võtab lihtsalt esimese tagastatud rea ja loeb selle kasutaja autendituks, võib tulemuseks olla autentimisest möödapääsemine.

Siin tegi sisend korraga kaks asja:

1. OR '1'='1' lisas päringusse alati tõese tingimuse;
2. -- muutis ülejäänud SQL-i, sealhulgas paroolikontrolli, kommentaariks.

See näitab SQL injection'i olemust hästi: probleem pole lihtsalt „ohtlikes tähemärkides". Probleem on selles, et andmed said võimaluse muuta SQL-i grammatilist struktuuri ja loogikat.

4. Mis juhtub sama sisendiga prepared statement'i korral?

Kirjutame päringu parameetritega:

SELECT id, username
FROM users
WHERE username = @username
  AND password = @password;

Rakendus annab väärtused eraldi:

@username = "' OR '1'='1' --"
@password = "midagi"

Andmebaas ei moodusta sellest SQL-i:

WHERE username = '' OR '1'='1' --'

SQL-i struktuur jääb:

WHERE username = @username
  AND password = @password

ning esimese parameetri väärtus on tervikuna tekst:

' OR '1'='1' --

Andmebaas otsib seega kasutajat, kelle kasutajanimi oleks sõna-sõnalt:

' OR '1'='1' --

ja kelle parool vastaks teisele parameetrile.

Kui sellist kasutajat pole, on tavapärane tulemus:

0 rows

Rakenduse tasemel võib see tähendada näiteks:

user == null

Kas prepared statement annab ründajale SQL-veateate?

Tavaliselt ei anna.

See on prepared statement'i juures väga oluline omadus.

Sisend:

' OR '1'='1' --

on parameetri sees täiesti korrektne tekstiväärtus. Ülakoma, sõna OR, võrdusmärgid ja -- ei ole selles kontekstis SQL-süntaks.

Seetõttu ei ole tüüpiline tulemus:

SQL syntax error

vaid näiteks:

0 rows

või kasutajale kuvatav üldine teade:

Vale kasutajanimi või parool.

Andmebaas ei pea isegi teadma, et keegi proovis SQL injection'it.

Tema jaoks otsis rakendus lihtsalt väga kummalise nimega kasutajat.

See on oluline põhimõte:

Prepared statement ei pea SQL injection'i ära tundma. Parameetrina antud sisendil lihtsalt puudub võimalus muutuda SQL-koodiks.

Seetõttu ei ole vaja keelata kõiki ülakomasid, OR-sõnu või muid SQL-is tähendust omavaid sümboleid. Need võivad olla täiesti legitiimsete andmete osa.

5. Miks parameetrite sidumine SQL injection'i peatab?

Parameetriseeritud päringu puhul on SQL-i struktuur ja väärtused loogiliselt eraldatud.

Lihtsustatud serveripoolse prepared statement'i korral võib suhtlus välja näha nii:

Rakendus                                  Andmebaas
   |                                         |
   |  PREPARE:                               |
   |  SELECT ... WHERE username = ?  ------> |
   |                                         |  SQL struktuuri
   |                                         |  analüüs
   |  <-------------------------- statement  |
   |                                         |
   |  EXECUTE                                |
   |  value = "admin' --"  ----------------> |
   |                                         |  väärtus jääb
   |                                         |  väärtuseks

Oluline pole see, mitu võrgupaketti täpselt liigub. Oluline on koodi ja andmete semantiline eraldamine.

Kui väärtus admin' -- antakse korrektselt seotud parameetrina, käsitletakse seda väärtusena, mitte uue SQL-süntaksina.

OWASP soovitab prepared statement'e koos parameetriseeritud päringutega ühe esmase kaitsemeetodina SQL injection'i vastu.

6. Turvalisus ja jõudlus: sama idee kaks erinevat kasu

Turvakasu

Kui kasutaja kontrollitavad andmeväärtused jõuavad päringusse korrektselt seotud parameetritena, ei saa need stringide liitmise kombel muuta SQL-päringu struktuuri.

Turvalisuse seisukohalt on põhireegel:

Parameterize values.

Jõudluskasu

Jõudluse puhul on olukord keerulisem. Parameetrite kasutamine võib aidata andmebaasil või draiveril olemasolevat tööd korduskasutada, kuid maksimaalne kasu sõltub muu hulgas:

• andmebaasimootorist;
• draiverist ja selle seadistusest;
• ühenduse ja statement'i elueast;
• connection pooling'ust;
• execution plan cache'ist;
• päringu korduste arvust;
• sellest, kas optimaalne execution plan sõltub tugevalt parameetri väärtusest.

Seetõttu on jõudluse puhul kasulik teine põhimõte:

Prepare once, execute many — kui konkreetse andmebaasi ja draiveri tööviisist on näha, et see annab kasu.

Näide C#-is

Järgmine kood kasutab parameetrit, kuid loob käsu iga iteratsiooni ajal uuesti:

foreach (var customer in customers)
{
    using var cmd = connection.CreateCommand();

    cmd.CommandText =
        "SELECT Name FROM Customers WHERE Id = @id";

    var p = cmd.CreateParameter();
    p.ParameterName = "@id";
    p.Value = customer.Id;
    cmd.Parameters.Add(p);

    var name = cmd.ExecuteScalar();
}

See võib olla SQL injection'i seisukohalt täiesti korrektne. Jõudluse seisukohalt ei tähenda see aga tingimata, et rakendus kasutab ära kõik konkreetse provider'i explicit prepare'i võimalused.

Kui provider toetab sellest kasu saavat explicit prepare'i ja sama statement'i täidetakse palju kordi, võib struktuur olla:

using var cmd = connection.CreateCommand();

cmd.CommandText =
    "SELECT Name FROM Customers WHERE Id = @id";

var p = cmd.CreateParameter();
p.ParameterName = "@id";
cmd.Parameters.Add(p);

cmd.Prepare();

foreach (var customer in customers)
{
    p.Value = customer.Id;
    var name = cmd.ExecuteScalar();
}

See väljendab mudelit:

prepare once
     ↓
execute(value 1)
     ↓
execute(value 2)
     ↓
execute(value 3)

See ei tähenda, et esimene variant sunniks näiteks SQL Serverit iga kord nullist uut execution plan'i koostama. Serveri plan cache ja draiver võivad teha osa korduskasutusest automaatselt.

Jõudluse väiteid tuleb seetõttu kontrollida konkreetse provider'i ja andmebaasi puhul ning vajadusel mõõta.

7. Kuidas seda tänapäeva keeltes tehakse?

PHP ja PDO

$stmt = $pdo->prepare(
    'SELECT id, username
     FROM users
     WHERE username = :username'
);

$stmt->execute([
    'username' => $userInput
]);

$user = $stmt->fetch();

PDO puhul tasub teada, et prepared statement võib sõltuvalt draiverist olla native või emulated. Mõnel draiveril saab emulatsiooni seadistada PDO::ATTR_EMULATE_PREPARES abil.

Turvalisuse põhiidee jääb samaks: väärtust ei ehitata käsitsi SQL-teksti sisse.

C# ja Dapper

var user = connection.QueryFirstOrDefault<User>(
    @"SELECT Id, Username
      FROM Users
      WHERE Username = @username",
    new { username = userInput }
);

Dapper loob anonüümse objekti väärtusest andmebaasikäsu parameetri. Arendaja ei pea ise jutumärke ega escape'imist konstrueerima.

C++ ja SOCI

std::string userInput = "admin' OR '1'='1";
int id;
std::string name;

sql << "SELECT id, name "
       "FROM people "
       "WHERE username = :name",
       soci::use(userInput),
       soci::into(id),
       soci::into(name);

userInput seotakse parameetrina, mitte ei liideta SQL-sõne sisse.

8. Mida prepared statements ei lahenda?

Parameeter saab üldjuhul esindada andmeväärtust, mitte suvalist SQL-i struktuuri.

Näiteks ei saa tavaliselt teha nii:

SELECT * FROM users ORDER BY :column :direction;

ja eeldada, et :column asendab turvaliselt veeru nime ning :direction märksõna ASC või DESC.

Kui kasutaja saab valida sorteerimisveeru, tabeli või SQL-i muu struktuurse osa, tuleks valik teisendada rakenduses lubatud variantideks ehk kasutada allow-list'i:

var orderBy = requestedColumn switch
{
    "name" => "Name",
    "created" => "CreatedAt",
    _ => "Name"
};

Sama kehtib sorteerimissuuna kohta: kasutaja sisendist ei tasu SQL-i teksti kopeerida, vaid valida näiteks kahe rakenduses fikseeritud väärtuse ASC ja DESC vahel.

Prepared statements ei asenda ka:

• sisendi valideerimist;
• vähimate õiguste põhimõtet;
• autentimist ja autoriseerimist;
• turvalist paroolide räsimist;
• korrektset veakäsitlust;
• rakenduse ülejäänud turvameetmeid.

Nende konkreetne ülesanne SQL injection'i kontekstis on tagada, et andmeväärtust ei tõlgendataks SQL-koodina.

9. Kolm mõistet, mida tasub lahus hoida

Praktilises arenduses kipuvad kolm ideed ühe termini alla kokku sulama.

Mõiste	Põhiküsimus	Peamine kasu
Parameterization	Kas väärtused on SQL-struktuurist eraldi?	Turvalisus
Preparation	Kas statement valmistatakse ette ja seda saab uuesti käivitada?	Võimalik jõudluskasu
Plan reuse	Kas varem tehtud optimeerimis-/kompileerimistööd kasutatakse uuesti?	Jõudlus ja skaleeruvus

Need võivad esineda koos, kuid ei ole sünonüümid.

Hea praktiline rusikareegel on:

Turvalisuse jaoks mõtle „parameterize". Jõudluse jaoks mõtle lisaks „prepare once, execute many" ning tea, kuidas sinu konkreetne andmebaas ja draiver päringuid korduskasutavad.

Kokkuvõte: hea arhitektuuriline eraldatus kestab kaua

Prepared statements on hea näide sellest, kuidas jõudluse ja tarkvaraarhitektuuri jaoks kasulik põhimõte osutus hiljem ka väga tugevaks turvamehhanismiks.

Varastes andmebaasisüsteemides oli oluline vähendada korduvat parsimist, planeerimist ja muud ettevalmistustööd. Veebirakenduste ajastul muutus sama arhitektuuriline eraldatus — SQL-i struktuur ühel pool, andmeväärtused teisel pool — üheks peamiseks kaitseks SQL injection'i vastu.

Samas ei tasu turvalisust ja jõudlust segamini ajada. Korrektselt parameetriseeritud päring annab turvakasu ka siis, kui server ei korduskasuta täpselt sama execution plan'i. Jõudluskasu sõltub rohkem konkreetsest andmebaasimootorist, draiverist, cache'idest, cursor'itest ja rakenduse töövoost.

Seetõttu võib prepared statement'i põhimõtte võtta kokku kahe reaga:

Turvalisus: ära lase andmetel muutuda koodiks.
Jõudlus: ära tee sama ettevalmistustööd asjatult uuesti.

Viited ja edasilugemist

1. OWASP Foundation. SQL Injection Prevention Cheat Sheet.
   https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

2. OWASP Foundation. Query Parameterization Cheat Sheet.
   https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html

3. rain.forest.puppy (1998). NT Web Technology Vulnerabilities. Phrack Magazine, Volume 8, Issue 54, Article 08.
   https://phrack.org/issues/54/8

4. PostgreSQL Documentation. PREPARE.
   https://www.postgresql.org/docs/current/sql-prepare.html

5. PostgreSQL Documentation. Query Planning / plan_cache_mode.
   https://www.postgresql.org/docs/current/runtime-config-query.html

6. MySQL Reference Manual. Prepared Statements.
   https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html

7. MySQL Server Documentation. COM_STMT_PREPARE.
   https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_stmt_prepare.html

8. Microsoft Learn. Query Processing Architecture Guide.
   https://learn.microsoft.com/sql/relational-databases/query-processing-architecture-guide

9. Microsoft Learn. sp_executesql (Transact-SQL).
   https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

10. Oracle Database Documentation. Designing and Developing for Performance.
    https://docs.oracle.com/en/database/oracle/oracle-database/26/tgdba/designing-and-developing-for-performance.html

11. Oracle Database Documentation. Shared Pool.
    https://docs.oracle.com/en/database/oracle/oracle-database/26/dbiad/db_sharedpool.html

12. PHP Manual. PDO::prepare.
    https://www.php.net/manual/en/pdo.prepare.php

---

Artikli näited on teadlikult lihtsustatud, et selgitada parameetrite sidumise, prepared statement'ide ja päringuplaanide korduskasutamise põhimõtteid. Konkreetne käitumine sõltub andmebaasi, draiveri ja nende versioonide seadistusest.