Søger du en specifik kategori?

 



Oprettet tor. d. 19. juli 2012 kl. 00:26

olebole
olebole (219.518 point)
Guidens karaktér
1
2
3
4
5

Prepared Statements under MySQLI - kom igang

Det gamle (og forældede) MySQL-API er stadig udbredt i skræmmende grad, selvom der findes langt sikrere alternativer. Et af dem hedder MySQLI med Prepared Statements. Denne guide er beregnet for begyndere i MySQLI - og er på ingen måde uddybende.


Historik:
19.03-2012: Tilføjet UPDATE eksempel
19.07-2012: Opdatering om Zend's anbefalinger


Zend (som vedligeholder PHP) anbefaler nu direkte, at man fravælger MySQL-API'et til nyudvikling. I stedet anbefales MySQLI eller PDO.

MySQL
Det gamle MySQL-API, som rigtig mange stadig bruger, bygger på en højst usikker tilgang til databaseforespørgsler. Det bygger på konkatenering (sammenklistring) af strengstumper til en SQL-kommando, som efterfølgende afvikles direkte mod databasen - f.eks:

$user = $_POST['username'];
$sql = "SELECT `id`, `points` FROM `mytable` WHERE `user`='.$user.' LIMIT 1";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
echo 'Brugeren '.$user.' med id '.$row['id'].' har '.$row['points'];

Dette er i udgangspunktet en yderst uhensigtsmæssig fremgangsmåde, eftersom variabler - som ofte kommer udefra (altså noget, jeg som udvikler ikke selv har skrevet) - bliver sendt direkte ned i databasen og afviklet mod denne.

Man kan lappe på problemet ved at escape værdier med funktioner som mysql_real_escape_string, men det er - og vil altid være - lapperier på en i udgangspunktet forkert fremgangsmåde. Da mysql_real_escape_string kun kan bruges på strenge, står løsningen stadig åben for SQL-injection, hvis der bliver brugt tal i SQL'en.

Desuden virker mysql_real_escape_string ikke på strenge i multibyte tegnsæt - med undtagelse af utf-8. Sidst, men ikke mindst, er løsningen afhængig af, at udvikleren husker at bruge den hver eneste gang - hvilket med garanti ikke sker.

Meget ofte vil mysql_real_escape_string o.lign. 'hacks' ydermere resultere i uønskede dobbelt escapings med deraf følgende backslashes (\) i de gemte data, når disse udskrives.

Desværre ses brugen af dette API ofte i lærebøger og tutorials. Lærebøger om webkode forældes hurtigt, og tutorialforfattere har ofte lært fra ældre bøger eller andre tutorials uden at opdatere deres viden.

Endnu værre er det, at der stadig undervises i API'et på flere uddannelser på beskedent niveau. Hvad det skyldes, skal jeg ikke kunne sige, men det er i hvertfald overordentlig skidt, hvis meningen er, at eleverne skal lære at skrive tidssvarende og sikker kode.

Prepared Statements
En langt bedre tilgang er at bruge prepared statements (forberedte erklæringer). Her forgår forespørgslen i følgende trin:

1) Prepare: Databasens styringsmekanisme opretter en 'skabelon' på baggrund af en SQL-streng, hvor nogle værdier er erstattet af spørgsmålstegn:

SELECT `fornavn` FROM `tabel` WHERE `id` = ?

Databasen opretter en optimeret plan for forespørgslen og gemmer denne plan - uden at gennemføre forespørgslen.

2) Bind parameters: Til den eller de værdier, der var erstattet af spørgsmålstegn i skabelonen, bindes egentlige værdier i form af f.eks. strenge eller tal. I ovenstående eksempel kunne det være tallet 123.

3) Execute: Databasen udfører forespørgslen med de bundne værdier.

Ved forespørgsler, som skal returnere et sæt af resultater/rækker, anvendes desuden følgende to trin:

4) Bind result: De fundne data bindes til en eller flere variabler. I eksemplet ovenfor kunne denne variabel f.eks. kaldes $fornavn.

5) Fetch: De fundne rækker hentes én ad gangen - lidt, som det kendes fra 'det gamle API'. Når en række er hentet, vil $fornavn (variablen fra resultatbindingen) indeholde værdien af det søgte felt i rækken.

Så længe det pågældende statement (den optimerede plan) ikke lukkes, kan punkt 2, 3, 4 og 5 udføres så mange, man måtte ønske.

Fordelen ved denne fremgangsmåde er, at værdierne ikke sendes til databasen sammen med selve SQL-kommandoen, men sendes som parametre på et senere tidspunkt og ikke kommer i direkte forbindelse med resten af SQL-kommandoen. Databasen tager sig selv af den fornødne escaping af parametrene, hvorved SQL-injection forhindres.

Der er derfor ikke længere brug for at 'hacke' sig frem med mysql_real_escape_string el.lign.

En anden fordel er, at planen kan genbruges, hvilket i høj grad forbedrer performance ved gentagne ens forespørgsler med forskellige værdier. Det skal nævnes, at der ved en enkelt forespørgsel er tale om en marginalt dårligere performance, hvilket dog opvejes af den dramatisk forbedrede sikkerhed.

Kodeeksempler
For at åbne en forbindelse til databasen bruges følgende kode, som i høj grad ligner det, vi allerede kender. Dog defineres databasen med det samme, når forbindelsen etableres:

$mysqli = new mysqli("server", "brugernavn", "kodeord", "databasenavn");

/* Tjek, om der opstod en fejl */
if (mysqli_connect_errno()) {
    echo 'Der opstod en fejl ved forbindelsen: ' . mysqli_connect_error();
    exit();
}


Eksempel på en SELECT forespørgsel, hvor id er større en værdi, hentet fra dokumentets URL:

/* Opret et prepared statement */
if ($stmt = $mysqli->prepare('SELECT `fornavn`, `efternavn` FROM `tabel` WHERE `id` > ?')) {

    /* Bind parametre */
    $stmt->bind_param('i', $id);

    /* Sæt værdier på parametrene */
    $id = $_GET['id'];

    /* Eksekver forespørgslen */
    $stmt->execute();

    /* Bind resultatet */
    $stmt->bind_result($fornavn, $efternavn);

    /* Hent rækker og udskriv data */
    while ($stmt->fetch()) {
        echo $fornavn . ' ' . $efternavn . '<br>';
    }

    /* Luk statement */
    $stmt->close();

} else {
    /* Der er opstået en fejl */
    echo 'Der opstod en fejl i erklæringen: ' . $mysqli->error;
}

NB: Læg mærke til udtrykket:

$stmt->bind_param('i', $id);

I et kald til metoden bind_param består første argument altid af en streng med ligeså mange tegn, som der er efterfølgende parametre. I dette eksempel kun med ét: 'i' - men i det følgende eksempel med flere tegn. Disse tegn skal modsvare de pågældende variablers type - og kan antage følgende værdier:
*)  s: string
*)  i: integer
*)  d: double
*)  b: blob (binære data, sendt som pakker)

Eksempel på INSERT fra form med method="post":

/* Opret et prepared statement */
if ($stmt = $mysqli->prepare('INSERT INTO `tabel` (`fornavn`, `efternavn`, `stamp`) VALUES (?, ?, ?)')) {

    /* Bind parametre */
    $stmt->bind_param('ssi', $fornavn, $efternavn, $stamp_now);

    /* Sæt værdier på parametrene */
    $fornavn = $_POST['fornavn'];
    $efternavn = $_POST['efternavn'];
    $stamp_now = time();

    /* Eksekver forespørgslen */
    $stmt->execute();

    /* Luk statement */
    $stmt->close();

} else {
    /* Der er opstået en fejl */
    echo 'Der opstod en fejl i erklæringen: ' . $mysqli->error;
}

Eksempel på UPDATE fra form med method="post":

/* Opret et prepared statement */
if ($stmt = $mysqli->prepare('UPDATE `tabel` SET `fornavn`=? WHERE `id`=?')) {

    /* Bind parametre */
    $stmt->bind_param('si', $fornavn, $id);

    /* Sæt værdier på parametrene */
    $fornavn = $_POST['fornavn'];
    $id = $_POST['id'];

    /* Eksekver forespørgslen */
    $stmt->execute();

    /* Luk statement */
    $stmt->close();

} else {
    /* Der er opstået en fejl */
    echo 'Der opstod en fejl i erklæringen: ' . $mysqli->error;
}


Vigtige overvejelser
I de viste eksempler er brugt $_GET og $_POST variabler uden forudgående validering. Det er kun gjort for overskuelighedens skyld. Man bør altid først validere input 'udefra' - altså data, som ikke er skrevet ind i koden af udvikleren selv.

Mange tænker i denne forbindelse ikke på, at en $_COOKIE variabel også kommer udefra. Den stammer som bekendt fra en tekstfil, der er gemt på brugerens PC og kan derfor meget vel være manipuleret.

Hvis du forventer en variabel skal indeholde et tal, så tjek, om variablen faktisk indeholder et tal - og tjek også, om det er indenfor forventede minimums- og maksimumsværdier. Forventer du en streng, så tjek, om det er en streng, og om den passer med det format, du forventer, osv.

OBS: Hvad udskrivning af fejlmeddelelser angår, så er de her viste eksempler kun ment som eksempler, beregnet for læreprocessen. I virkeligheden udgør de i sig selv en sikkerhedsrisiko, hvis de får lov at ligge på en offentlig tilgængelig server. Jeg vil derfor varmt anbefale, at du læser min guide om sikrere håndtering af MySQL-fejl.

Forslag til videre læsning
PHP-manualen om MySQLI
PHP-manualen om MySQLI klassens properties og metoder
PHP-manualen om statement klassens properties og metoder
PHP-manualen om result klassens properties og metoder

Skrevet tor. d. 01. marts 2012 kl. 12:00| #1

Dejlig letforståelig gennemgang, har allerede henvist til denne guide, og kommer med garanti at gøre det mange gange til. Er lettere at henvise hertil, end at skulle til at forklare hver gang.

Skrevet tor. d. 01. marts 2012 kl. 14:46| #2

olebole (219.518 point)
Tak Michael - og du rammer min egen begrundelse for at skrive den lige på sømmet!  *o)

Skrevet ons. d. 07. marts 2012 kl. 11:46| #3

jakobdo (194.075 point)
www.simitu.dk
Så forstod selv jeg MySQLi. :o)

Skrevet lør. d. 17. marts 2012 kl. 21:29| #4

Stefan1 (6.895 point)
Er det også muligt at bruge update i Prepared Statements?

Skrevet man. d. 19. marts 2012 kl. 13:39| #5

olebole (219.518 point)
@Stefan1: Jeg har tilføjet et UPDATE eksempel  =)

Skrevet man. d. 19. marts 2012 kl. 17:12| #6

Stefan1 (6.895 point)
@Ole Mange tak Ole.

Skrevet tir. d. 20. marts 2012 kl. 23:01| #7

h_thunbo (14.640 point)
Den bliver lige tilføjet til favoritter indtil det sidder på rygraden :-)

Skrevet tor. d. 29. marts 2012 kl. 15:38| #8

tobrukDk (5.080 point)
Hej

jeg forstår det ikke , er der nogle som kan forklare mig det, jeg har prøve frem og tilbage og jeg kan bare ikke få det til at virker overhovedet :O

Skrevet fre. d. 30. marts 2012 kl. 14:44| #9

#8
Øhhh, HVAD skal vi forklare? Den forklaring du har læst? Det er svært at forklare tydeligere end hvad der er skrevet. Opret et spørgsmål og forklar hvor i din kode, det er det går galt.

Skrevet man. d. 23. april 2012 kl. 14:33| #10

tobrukDk (5.080 point)
#9 efter som har snakke med olebole privat via PM så har han forklare mig det på en måde så jeg forstår det på :) Lækkert lækkert og det er pisse godt lavet ole! +1!!

Skrevet man. d. 21. maj 2012 kl. 21:01| #11

tobrukDk (5.080 point)
Jeg synes personligt at Mysqli det er langt bedre endnu det "gammel"

Skrevet fre. d. 15. juni 2012 kl. 09:00| #12

oomalkeoo (20.091 point)
Endnu en perfekt guide fra dig, mange tak!

Skrevet ons. d. 20. juni 2012 kl. 15:10| #13

tobrukDk (5.080 point)
#olebole. Du mangler også at lave en delete function/hvad man kalder det..

Skrevet fre. d. 20. juli 2012 kl. 09:39| #14

Jacobmoller (4.475 point)
Tak for denne letlæselige og forståelige guide.

Jeg har lige et spørgsmål - jeg vil gerne bruge den i forbindelse med at oprette en bruger, og der bruger jeg MD5 til password.

Hvad hedder det i bind_param? Er det bare en integer?
Altså
$stmt->bind_param('???', $password)

Skrevet fre. d. 20. juli 2012 kl. 13:07| #15

@Jacobmoller: Nej, det vil gå som en string. At du ikke bør benytte MD5, er en anden side af sagen. :)

Skrevet fre. d. 20. juli 2012 kl. 16:38| #16


Skrevet tir. d. 31. juli 2012 kl. 22:51| #17

Lækker skrevet guide.
Og bestemt noget jeg da skal til at begynde på (ikke mindst efter "advarslen" fra PHP mod at benytte mysql APIen).

Skrevet søn. d. 12. august 2012 kl. 03:12| #18

tobrukDk (5.080 point)
Nu komme jeg til at tænke på hvad er overhovedet bedste MD5 eller sha1? og hvorfor?

Skrevet tir. d. 14. august 2012 kl. 21:04| #19

arne_v (1.077.734 point)
MD5 har vaeret foraeldet i flere aar.

SHA1 er foraeldet idag.

SHA2 f.eks.SHA-256 er hvad du skal bruge.

Skrevet ons. d. 05. september 2012 kl. 08:06| #20

De guider her! De er godt nok perfekte.

Skrevet tir. d. 18. september 2012 kl. 16:57| #21

mccookie (29.077 point)
Har jo brugt det gamle API og en masse escapes mv....

Men det her er da egentligt rimeligt at gå til :-)

God guide - Let forklarligt og lige ud ad landevejen....

Skrevet man. d. 15. oktober 2012 kl. 09:37| #22

Stefan1 (6.895 point)
Hvordan tilføjer man LIKE statements med wildcards hvis man vil sammenligne med to kolonner fra tabellen?

Skrevet ons. d. 17. oktober 2012 kl. 19:34| #23

olebole (219.518 point)
Hvis det er en variabel, du vil bruge, og du bruger prepared statements:

$stmt = $mysqli->prepare('SELECT `id` FROM `foobar` WHERE `foo` LIKE ?');
$parameter = '%' . $someVar . '%';
$stmt->bind_param('s', $parameter);

Hvis du vil sammenligne to felter (foo og bar):

$res = $db->query("SELECT `id` FROM `foobar` WHERE `foo` LIKE CONCAT('%', `bar`, '%')");

Skrevet ons. d. 17. oktober 2012 kl. 19:36| #24

olebole (219.518 point)
PS: I det andet eksempel, skal du passe på. Tegnet '%' i indholdet af feltet 'bar' vil skabe uventede resultater

Skrevet fre. d. 19. oktober 2012 kl. 09:07| #25

Stefan1 (6.895 point)
Tak ole :)

Skrevet lør. d. 02. februar 2013 kl. 21:47| #26

Super god guide. Nem at forstå, selv for en som ikke er så stærk til kodning (Læs, Mig) :-) Tak for hjælpen

Skrevet søn. d. 10. marts 2013 kl. 13:09| #27

riefart (3.260 point)
Syntes, at jeg lige havde fået en lille smule hold på det gamle API, og var lidt uvillig til at gå i gang med PS. Men for filen Ole, som du forklarer det, er det både logisk og enkelt.
Tak for en god guide.

Skrevet ons. d. 03. april 2013 kl. 09:01| #28

Jeg har besluttet mig for at gå fra det gamle mysql_ API  til mysqli, og jeg synes denne guide har hjulpet mig godt i gang - så tak for det! :-)

Jeg oplever dog denne fejl i min implementation, hvilket jeg undrer mig lidt over.

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes)

Er der nogen der har et bud på hvad det kan skyldes?

Skrevet man. d. 08. april 2013 kl. 21:42| #29

nemlig (6.606 point)
Super guide, som giver en god og grundlæggende indsigt i principperne. Jeg er hermed kommet godt i gang med at omkode mine scripts.

Skrevet tir. d. 23. april 2013 kl. 16:16| #30

olebole (219.518 point)
Tak for rosen til alle  =)

@Peter89: Den fejl må vist stamme fra noget andet

Skrevet fre. d. 01. august 2014 kl. 22:10| #31

kennethhede (885 point)
Super god guide! Har selv haft en del problemer med MySQL på Sportsway, men har fået det fikset nu :-)

Skriv en kommentar



Mest populære guides i Windows 8

Guidens karakter
!!!Karaktér: Mangler flere stemmer
Mangler stemmer
09/04 - 2014
Af: claes57

Værktøjslinjen Tablet PC Input Panel

Hvis man er på en alm pc uden touch, så kan den fjernes.
Guidens karakter
!!!Karaktér: Mangler flere stemmer
Mangler stemmer
07/04 - 2013
Af: poko1

Afstande mellem Ikoner På Windows 8 skrivebordet

Hvordan du kan ændre mellemrum så de ligner dine tidligere indstillinger!

Log ind

   


Seneste guides

Malwarebytes version 2.0.3.1025
Find ejeren af et vilkårligt domæne
Undgå reklamerne på iPad





Computerworld

Teaser billede

Nu er det slut med Java til NemID: Her er den nye løsning

Om få dage er det helt slut med at benytte Java til NemID. Læs her, hvad der kommer til at erstatte Java-appletten.

Comon

Teaser billede

5 tip: Sådan får du nye kontakter på 30 sekunder

Lær hvad du skal tale om, hvis folk ikke skal glemme dig, når du har været til konferencer. Og nej, folk gider ikke tale om vejret.

Channelworld

Teaser billede

Et nyt Microsoft rejser sig: Her er alle beviserne

Microsoft blev kørt over af Apple på mobilmarkedet, er bagud i ræset om søgemaskiner, og andre ejer de sociale medier. Men it-giganten er i gang med sin egen seje genrejsning. Og den er værd at...

White paper

Teaser billede

Forretningansvarlig it-arkitektur

Actionable enterprise architecture fra IBM kan hjælpe til at styre kompleksiteten og afstemme it-projekter og løsninger med forretningsmål.



Udgiver · © 2014 Computerworld A/S · Hørkær 18 · 2730 Herlev · Tlf.: 77 300 300 · Fax: 77 300 301 · Brug af personoplysninger