PHP - Prepared statements: mysqli.

'Prepared Statements' er en af 3 måder hvorpå PHP kan kommunikere med databasen MySQL. Ved at at benytte 'prepared statements' undgås risikoen for 'SQL injection'.

Et prepared statement forbliver aktivt indtil kommandoen 'close()'. Samtidigt kan kun et prepared statement være aktivt ad gangen.

'mysqli_real_escape_string() skal ikke indsættes. Den er allerede indarbejdet i den bagved liggende kode.

Ved kommunikationen anvendes en SQL-sætning og nogle få faste PHP-metoder. Hvilke varierer lidt mellem de fire CRUD-kommunikationsformer. Andre funktioner kan være optionelle. Anvendelsen af de faste funktioner kan ses i tabellen nedenfor.

Metoder etc.	CRUD				Kort besk.
Metoder etc.	select felter	insert	update	delete	Kort besk.
sql	x	x	x	x	Opbygning af SQL-sætning med ? som 'placeholdere'.
prepare()	x	x	x	x	Forbereder SQL-sætningen til udførelse .
bind_param()	x	x	x	x	Datatype og felter bag placeholdere for tilknytning til SQL-sætningen.
Feltliste		x	x		Tilknytning af eksterne kildedata til bind_param()-felterne i insert- eller update-felter.
execute()	x	x	x	x	Udfører databasejobbet
store_result()	x				Overfører udtrukne data til memory ('result').
bind_result()	x				Tilknytter brugerdefinerede variabelnavne til dataene i 'result'.
$num_rows	x				Optæller antal records i 'result'. Kan udelades.
while($query_fetch){}	x				Brug af uddata fra 'result'. Kan udelades.
close()	x	x	x	x	Lukker prepared statement og sletter result. - Skal ved 'select' placeres efter 'fetch'. Det er væsentligt at lukke, da kun et prepared statement kan være aktivt ad gangen.

$query->bind_param('xx..x', ...

Forrest i parameterfeltet i 'bind_param' indsættes en kode med et bogstav pr felt med datatypen for de substituerede felters '?'-felter. Der skal være en kode pr substitution. Koderne skal anføres i samme rækkefølge som substitutionerne. Der kan vælges blandt 4 koder:

s	streng	Alle felttyper beregnet til tekst
i	tal	Alle felttyper beregnet til tal.
d	double	Tegn, der kræver 2 bytes. Kinesisk, japansk etc.
b	blob

'Gnyffer'

Der skal i prepared statements kun anvendes gnyffer (anførselstegn) omkring SQL-sætningen og omkring strengen med datatypekoder.

INSERT

Model for INSERT-query:

Eksterne data tilføres via value-delen af 'Feltliste'.

$sql = "INSERT INTO tabelnavn (feltnavn01, feltnavn02, feltnavn03) VALUES (?, ?, ?)";
$query = $db_link->prepare($sql);
$query->bind_param('ssi', $feltnavn01, $feltnavn02, $feltnavn03);
   $feltnavn01 = $feltnavn01;
   $feltnavn02 = $feltnavn02;
   $feltnavn03 = time();
$query->execute();
$query->close();
header ( "Location: programnavn.php");
exit;

sql
Opbyg normal SQL-sætning. I stedet for at indsætte datanavne (variabler / funktioner) som VALUES, indsættes et (kommasepareret) spørgsmålstegn ? som substitution for hver enkelt value.
prepare()
Klargør sql-sætningen til udførsel i den database, der er linket til.
bind_param()
Her indsættes først typekoder (en for hvert felt) som anført øverst på siden. Derefter indsættes bindparametre: variabelnavn knyttet til hvert af de felter, der i 'prepare' blev substituteret med '?'. Variabelnavnene skal indsættes i samme rækkefølge som feltnavnene i 'prepare'. Der skal også anføres variabelnavne for de metoder etc., der skal tilknyttes tabellens felter.
Feltliste
Efter 'bind_param' indsættes en liste, der som selvstændige kommandoer for hver 'bind_param'-variabelnavn tilknytter parameteret dets eksterne 'value' (variabel, metode, $_POST etc.). Felterne skal placeres i samme rækkefølge, som er benyttet i 'bind_param'.
execute()
Funktionen har ingen parametre, men sørger for at queryen udføres: at indhold i databasen ændres.
close()
Sletter queryen og dens brug af memory.

Insert i mange / mange tabel

I en mange / mange tabel er grundprincippet at alle records - baseret på den primære tabeldel - først slettes. Derefter indsættes alle nye records med INSERT. Der foretages aldrig UPDATE.

INSERT-delen vil være karakteriseret ved indsættelse af en eller flere records med samme struktur. Opbygningen af prepared statements muliggør, at SQL-sætningen kun opbygges en gang og genbruges efter behov.

Den samlede model for mange / mange kan se sådan ud:

DELETE:

$sql = "DELETE FROM doktags WHERE dok_id = ?";
$query = $db_link->prepare($sql);
$query->bind_param('i', $dok_id);
$query->execute();
$query->close();

INSERT (Her baseret på array)

$sql = "INSERT INTO doktags (dok_id, tag_id) VALUES (?, ?)";
foreach($tags as $tag_id)
    {
    $query = $db_link->prepare($sql);
    $query->bind_param('ii', $dok_id, $tag_id);
       $dok_id = $dok_id;
       $tag_id = $tag_id;
    $query->execute();
    $query->close();
    }

UPDATE

Model for UPDATE-query:

Eksterne data tilføres via value-delen af 'Feltliste'.

$sql = "UPDATE tabelnavn SET feltnavn01=?, feltnavn02=?, feltnavn03=? WHERE xx_id=?";
$query = $db_link->prepare($sql);
$query->bind_param('sssi', $feltnavn01, $feltnavn02, $feltnavn03, $xx_id);
    $feltnavn01 = $feltnavn01;
    $feltnavn02 = $feltnavn02;
    $feltnavn03 = $feltnavn03;
    $xx_id = $xx_id;
$query->execute();
$query->close();

sql
Opbyg normal SQL-sætning. I stedet for at indsætte datanavne (variabler, metoder etc.) for SET-identer og efter WHERE indsættes ? som substitution for hver enkelt VALUE.
prepare()
Klargør sql-sætningen til udførsel i den database, der er linket til.
bind()
Her indsættes først typekoder (en for hvert felt) som anført øverst på siden. Derefter indsættes bindparametre: variabelnavn knyttet til hvert af de felter, der i 'prepare' blev substituteret med '?'. Variabelnavnene skal indsættes i samme rækkefølge som feltnavnene i 'prepare'. Der skal også anføres variabelnavne for de metoder etc., der skal tilknyttes tabellens felter.
Feltliste
Efter 'bind_param' indsættes en liste, der som selvstændige kommandoer for hver 'bind_param'-variabelnavn tilknytter parameteret dets eksterne 'value' (variabel, metode, $_POST etc.). Felterne skal placeres i samme rækkefølge, som er benyttet i 'bind_param'.
execute()
Funktionen har ingen parametre, men sørger for at queryen udføress: at indhold i databasen ændres.
close()
Sletter queryen og dens brug af memory.

Delete

Model for DELETE-query:

$sql = "DELETE FROM tabelnavn WHERE xx_id = ?";
$query = $db_link->prepare($sql);
$query->bind_param('i', $xx_id);
$query->execute();
$query->close();

sql
Opbyg normal SQL-sætning. I stedet for hvert af de feltnavne der skal styre, hvorfra / hvordan data skal hentes, indsættes et (kommasepareret) spørgsmålstegn ?
prepare()
Klargør sql-sætningen til udførsel i den database, der er linket til.
bind()
Her indsættes først typekoder (en for hvert felt) som anført øverst på siden. Derefter indsættes bindparametre: variabelnavn knyttet til hvert af de felter, der i 'prepare' blev substituteret med '?'. Variabelnavnene skal indsættes i samme rækkefølge som feltnavnene i 'prepare'. Der skal også anføres variabelnavne for de metoder etc., der skal tilknyttes tabellens felter.
execute()
Funktionen har ingen parametre, men sørger for at queryen udføres: at indhold i databasen ændres.
close()
Sletter queryen og dens brug af memory.

Select - Dataudtræk

Alt efter opgaven kan et prepared statement for SELECT opbygges på 3 måder:
('$antal' er optionel i dem alle)

I alle situationer kan '$query->bind_result'-variablerne generelt resp. i while-løkken bruges som almindelige variabler.

Modeller for SELECT-query:

Ved udtræk af alle records fra tabel:

$sql = "SELECT feltnavn01, feltnavn02, feltnavn03 FROM tabel order by feltnavnx asc";
$query = $db_link->prepare($sql);
$query->execute();
$query->store_result();
$query->bind_result($feltnavn01, $feltnavn02);
$antal = $query->num_rows;
while($query->fetch()) {
...
Anvendelse af de selekterede data. Identerne fra $query->bind_result(xxx) anvendes til at angive uddata.
...
}
$query->close();

Ved udtræk af flere records fra tabel:

$sql = "SELECT feltnavn01, feltnavn02, feltnavn03 FROM tabel WHERE feltnavn0x x ? and feltnavn0y x ?";
$query = $db_link->prepare($sql);
$query->bind_param('xx', $valuevariabel0x, $valuevariabel0y );
$query->execute();
$query->store_result();
$query->bind_result($feltnavn01, $feltnavn02);
$antal = $query->num_rows;
while($query->fetch()) {
...
Anvendelse af de selekterede data. Identerne fra $query->bind_result(xxx) anvendes til at angive uddata.
...
}
$query->close();

Ved udtræk af enkelt record fra tabel:

$sql = "SELECT feltnavn01, feltnavn02, feltnavn03 FROM tabel WHERE feltnavn0x = ? or feltnavn0y = ?";
$query = $db_link->prepare($sql);
$query->bind_param('xx', $valuevariabel0x, $valuevariabel0y );
$query->execute();
$query->store_result();
$query->bind_result($feltnavn01, $feltnavn02);
$antal = $query->num_rows;
$query->fetch();
$query->close();

sql
Opbyg normal SQL-sætning. I stedet for hvert af de feltnavne der skal styre, hvorfra / hvordan data skal hentes, indsættes et (kommasepareret) spørgsmålstegn ?
prepare()
Klargør sql-sætningen til udførsel i den database, der er linket til.
bind_param()
Undlades når der ingen ? er indsat i SQL-sætningen.
Her indsættes først typekoder - 'i' for talfelt og 's' for tekstfelt. En kode for hvert '?' i sql-sætningen. Derefter indsættes bindparametre: variabelnavn knyttet til hvert af de felter, der i sql-sætningen blev substituteret med '?'. Variabelnavnene skal indsættes i samme rækkefølge som feltnavnene i sql-sætningen. Der skal også anføres variabelnavne for de metoder etc., der skal tilknyttes tabellens felter.
execute()
Funktionen har ingen parametre, men sørger for at queryen udføres.
store_result()
Overfører udtrukne data fra MySQL til menory.
bind_result()
Tilknytter brugerdefinerede variabelnavne til de udtrukne data. Identerne tilknyttes i den rækkefølge, der er angivet i 'select ...'.
$antal = $query->num:rows;
Optæller antal records, der er i 'result'. Kan udelades når ikke relevant.
while($query->fetch){}
Ved udtræk af flere resultater hentes en record ad gangen fra 'result'. Hvordan recordens data skal anvendes mellem '{}' vil afhænge af den aktuelle situation.
De enkelte felter defineres ved deres navn fra 'bind_result()'.
Kan udelades når ikke relevant.
$query->fetch()
Ved udtræk af enkelt resultat stilles uddata til rådighed med de i 'bind_result()' anførte identer.
close()
Sletter queryen og dens brug af memory - incl. 'result'. Skal placeres efter 'while'-løkke..

Select fra array

Udtrækket placeres inde i en 'foreach'-løkke. Derved udtrækkes record for sig og der kan ikke anvendes 'order by' eller mange andre parametre / metoder på udtrækket.

$tags = ($_POST['tags']);

$sql = "SELECT dok_id FROM doktags WHERE tag_id = ?";

foreach($tags as $tag_id)
{
    $query = $db_link->prepare($sql);
    $query->bind_param('i', $tag_id );
    $query->execute();
    $query->store_result();
    $query->bind_result($dok_id);
    $antal = $query->num_rows;
    while($query->fetch()) {
       echo "<br />".$dok_id;
    }
$query->close();
}

Select fra mange / mange tabel

For at kunne anvende hele spektret af 'order by' etc. er det nødvendigt at anvende den 'almindelige' sekventielle select-model og gøre den dynamisk overfor antallet af elementer i arrayen. Det kræver yderligere kode og og at der 3 steder dynamisk skal foretages tilpasning til antallet af elementer i arrayen:: WHERE: Antallet af parametre - der skal være separeret med 'or'.; bind->param: Antallet af type-definitioner.; bind->param: Antallet af variabler.

$arr_elem
Optælling af antal elementer i arrayen
$tags2
Konverter arrayen til en kommasepareret liste.
$bind_var
Opbygning af streng med dynamisk antal variabler

Select - Valideringer

Select anvendes ofte i forbindelse med validering til at kontrollere 'findes' / 'findes ikke'.

Den anvendes lidt forskelligt alt efter den primære CRUD-aktivitet det pågældende program udfører.

Der vil altid være knyttet en if-sætning til at styre om sand eller falsk.

MINIMUM SELECT:
Så længe man alene vil tælle antal uden at give noget andet feedback end evt. antal, kan SELECT (også i eksemplerne nedenfor) begrænses til denne model:

$sql = "SELECT felt_id FROM tabelnavn";
    $query = $db_link->prepare($sql);
    $query->xecute();
    $query->store_result();
    $antal = $query->num_rows;
   if($antal x nn){
       .....
   }

INSERT:
Kontrol af at felt_navn er unikt før recorden indsættes i tabellen.

$sql = "SELECT felt_navn FROM tabelnavn where felt_navn = ?";
    $query = $db_link->prepare($sql);
    $query->bind_param('s', $felt_navn);
    $query->execute();
    $query->store_result();
    $query->bind_result($felt_navn);
    $antal = $query->num_rows;
    $query->close();
       if ($antal > 0)
       {
          $fejlbeskeder .= "Feltnavnet $felt_navn er allerede brugt.<br />";
       }

UPDATE:
Kontrol af at felt_navn er unikt før recorden opdateres i tabellen.
Der skal her tages højde for at den kontrollerede ident måske allerede - korrekt - er til stede i den aktuelle record. Recorden skal derfor ikke indgå i valireringen.

$sql = "SELECT felt_navn FROM tabelnavn where felt_navn = ? and felt_id != ?";
    $query = $db_link->prepare($sql);
    $query->bind_param('si', $felt_navn, $felt_id);
    $query->execute();
    $query->store_result();
    $query->bind_result($felt_navn);
    $antal = $query->num_rows;
    $query->close();
       if ($antal > 0)
       {
          $fejlbeskeder .= "Feltnavnet $felt_navn er allerede brugt.<br />";
       }

DELETE:
Kontrol af om identen stadig bruges i anden (primær) tabel.

$sql = "SELECT felt_id FROM tabelnavn WHERE felt_id = ?";
    $query = $db_link->prepare($sql);
    $query->bind_param('i', $felt_id );
    $query->execute();
    $query->store_result();
    $query->bind_result($ret_id);
v     $antal = $query->num_rows;
    if($antal > 0){
    $fejlbeskeder .= "xxx er stadig knyttet til en eller flere yyy.<br />";
    }

Drop down menuer

I drop down menuer anvendes prepared statements (SELECT) på helt samme måde som andetsteds.

Eksempler senere.