Jak składać zapytania SQL

Wystarczy zrobić coś takiego (konsola interaktywna Pythona, polecam iPython):

import sqlite3

con = sqlite3.connection(":memory:") 
cur = con.cursor()

To taki wstęp, dalej będziemy bawić się obiektem cur. Sqlite3 nie ma (pseudo)tabeli dual, ale to nie szkodzi, i bez tego można zadawać proste zapytania sql bez tworzenia tabel. Do tej demonstracji to wystarczy ma przykład:

cur.execute("SELECT 1").fetchall()

Zwróci coś takiego:

[(1,)]

Tak samo zresztą nieco bardziej złożone zapytanie:

cur.execute("SELECT 1 WHERE 1=1").fetchall() 
[(1,)]

To teraz dodajmy tutaj porównanie z parametrem kontrolowanym przez użytkownika:

param = "1" 
cur.execute("SELECT 1 WHERE 1="+param).fetchall() 
[(1,)]

Działa? Aż za dobrze. Co się stanie, gdy w parametrze param pojawi się coś innego?

param = "1 AND 1=0 --" 
cur.execute("SELECT 1 WHERE 1="+param).fetchall() 
[]

Albo już bardziej realizstyczne:

param = "1 AND 1=(SELECT 0) --" 
cur.execute("SELECT 1 WHERE 1="+param).fetchall() 
[]

W zapytaniu, które tutaj jest postaci (SELECT 0) można umieścić bardziej złożoną logikę, co w rezultacie pozwoli na atak typu blind sql-injection.

Jeśli zamiast sklejania stringów będą format stringi, to nadal to nie pomoże (do końca), choć akurat w omawianym przypadku pomóc może, ale tylko pod warunkiem, gdy string formatujący będzie postaci SELECT 1 WHERE 1=%d, a to dlatego, że jakby nie patrzeć 1 AND 1=(SELECT 0) -- nie jest wartością liczbową. Jeśli jednak będzie to SELECT 1 WHERE 1=%s, to zadziała to dokładnie tak samo:

param = "1 AND 1=(SELECT 0) --" 
cur.execute("SELECT 1 WHERE 1=%s" % param).fetchall() 
[]

A co się stanie, jeśli będzie to mechanizm bind variables?

param = "1 AND 1=(SELECT 0) --" 
cur.execute("SELECT 1 WHERE 1=?", (param,)).fetchall() 
[]

To gdzie ta różnica??? Różnica jest w tym, że w drugim przypadku, czyli:

param = "1 AND 1=(SELECT 1) --" 
cur.execute("SELECT 1 WHERE 1=?", (param,)).fetchall() 
[]

...zapytanie też nic nie zwraca. Dlaczego? Bo parametrem jest string, a więc powstaje porównanie typu 1="jakis string", który siłą rzeczy nie zwróci True. Gdy było to "sklejanie stringów", to wartość przekazana przez klienta była po prostu wstawiana do zapytania SQL stając się jego częścią.

Można to przećwiczyć na przykładzie:

cur.execute("SELECT 1 WHERE 1=?", (1,)).fetchall()
[(1,)] 
cur.execute("SELECT 1 WHERE 1=?", ("1",)).fetchall() 
[]

Tak samo zadziała to dla stringów, czyli:

cur.execute("SELECT 1 WHERE '1'=?", (1,)).fetchall() 
[] 
cur.execute("SELECT 1 WHERE '1'=?", ("1",)).fetchall() 
[(1,)]

A tu porównanie sklejania stringów i bind variables:

cur.execute("SELECT 1 WHERE '1'=?", ("'1' OR 1=1 --",)).fetchall() 
[] 
cur.execute("SELECT 1 WHERE '1'='%s'" % "1' OR 1=1 --" ).fetchall() 
[(1,)] 
cur.execute("SELECT 1 WHERE '1'=?", ("'0' OR 1=1 --",)).fetchall() 
[] 
cur.execute("SELECT 1 WHERE '1'='%s'" % "0' OR 1=1 --").fetchall() 
[(1,)]

Po prostu to "środowisko" zadba o to, by parametr przekazywany (bindowany) pod określoną zmienną w zapytaniu (oznaczoną w tym przypadku znakiem ?) został wstawiony prawidłowo (czyli jako wartość, a nie fragment zapytania SQL).

...gdyby programiści stosowali to rozwiązanie, to problem sql-injection byłby zdecydowanie mniej istotny, niż dziś... Ale wówczas byłoby nudno...

Przykłady, które zaprezentowałem wyżej są trywialne, ale chyba dość dobrze pokazują istotną różnicę między tymi dwoma technikami. I dlatego sklejanie stringów nie powinno być wykorzystywane. Nawet, jeśli samo zapytanie SQL jest tworzone dynamicznie, to nadal można zastosować mechanizm bind variables. Fakt, że w zależności od wybranych przez użytkownika opcji zapytanie wygląda raz tak: SELECT * FROM tabela, a raz tak: SELECT * FROM tabela WHERE id=21, wcale nie znaczy, że nie można najpierw skonstruować zapytania postaci SELECT * FROM tabela WHERE id=?, a następnie przez mechanizm bind variables wstawić to przykładowe 21...