Programmeren in PHP/MySQL in PHP

MySQL verbinding in PHP

Nu we de basisfuncties kennen, kunnen we al een simpele website opzetten. Een hoop van deze websites zullen een manier nodig hebben om met de gebruiker te communiceren of om de website te personaliseren. Of om bijvoorbeeld een gastenboek te maken, zoals wij aan de hand van deze les, stap voor stap gaan doen.

Verbinding maken

Om verbinding te maken met een database hoor je drie dingen te weten. De gebruikersnaam, het wachtwoord en de plaats waar je database zich bevindt, de host.

PHP-code:

<?php
$_CONFIG["Username"]	= 'root';		// Gebruikersnaam
$_CONFIG["Password"]	= '';			// Wachtwoord
$_CONFIG["Host"]	= 'localhost';		// Host
?>

De conventie is om deze in een configuratiebestand te zetten. We hebben nog geen verbinding gemaakt, alleen de gegevens in een associatieve array gezet, zodat we ze makkelijk terug kunnen vinden. Om verbinding te maken met een database heeft PHP voor iedere database een hoop functies aan boord. Wij gaan die voor MySQL gebruiken, deze beginnen met "mysqli_" gevolgd door het doel, bijvoorbeeld mysqli_connect(). Omdat er natuurlijk het een en ander fout kan gaan met het maken van de verbinding moeten we ook zorgen voor een goede foutafhandeling, dat doen we met trigger_error(). Omdat die functie makkelijk aan te passen is en standaard een hoop extra informatie kan leveren over de foutmelding. Als je klaar bent met het gebruik van de database is het netjes om de verbinding te sluiten, PHP doet dat, als het niet door jou gebeurt, zelf. Toch is het niet netjes hier op te vertrouwen. Een verbinding sluiten doen we met mysqli_close().

PHP-code:

<?php
$_CONFIG["Username"] = 'root';
$_CONFIG["Password"] = '';
$_CONFIG["Host"]     = 'localhost';

$Verbinding = mysqli_connect($_CONFIG["Host"], $_CONFIG["Username"], $_CONFIG["Password"]);

if($Verbinding == false) // Verbinding is mislukt!
{
	trigger_error("Kan geen verbinding maken met de database");
}

mysqli_close($Verbinding);
?>

je kan ook dit gebruiken:

PHP-code:

<?php
$_CONFIG["Username"] = 'root';
$_CONFIG["Password"] = '';
$_CONFIG["Host"]     = 'localhost';

$Verbinding = mysqli_connect($_CONFIG["Host"], $_CONFIG["Username"], $_CONFIG["Password"]) or die("Kan geen verbinding maken met de database");
mysqli_close($Verbinding);
?>

Als het goed is levert het vorige programma geen tekst op als je het uitvoert, dat betekent dat de verbinding gelukt is. Als het niet lukt kan dit een aantal oorzaken hebben:

• De gegevens in $_CONFIG zijn fout, probeer dat eerst goed te krijgen.
• Je gebruikt een verouderde manier om verbinding met MySQL te maken, vraag aan de administrator van de database om een wachtwoord in te stellen met OLD_PASSWORD.

Database aanmaken

Als je met een database wilt werken, is het handig als je die hebt en als daar tabellen met gegevens in staan. Gelukkig kan je met SQL ook een database aanmaken. De syntax voor die query is een van de eenvoudigste. Namelijk

CREATE DATABASE db_name

Waarbij db_name de uiteindelijke naam voor de database is. Ook gaan we daarvoor een regel aan de $_CONFIG array toevoegen met daarin de te gebruiken database. Ook wordt dit onze eerste echte query in MySQL.

PHP-code:

<?php
$_CONFIG["Username"] = 'root';
$_CONFIG["Password"] = '';
$_CONFIG["Host"]     = 'localhost';
$_CONFIG["Database"] = 'gastenboek';

$Verbinding = mysqli_connect($_CONFIG["Host"], $_CONFIG["Username"], $_CONFIG["Password"]);

if($Verbinding == false) // Verbinding is mislukt!
{
	trigger_error("Kan geen verbinding maken met de database");
}

$Result = mysqli_query($Verbinding, "CREATE DATABASE " . mysqli_real_escape_string($Verbinding, $_CONFIG["Database"]));

if($Result == false)
{
	trigger_error("Kan de database niet aanmaken!");
}
else
{
	echo "De database is succesvol aangemaakt";
}

mysqli_close($Verbinding);
?>

Als het goed is, komt er nu "De database is succesvol aangemaakt" op je scherm te staan. Om de SQL-query te versturen gebruiken we mysqli_query, die als eerste argument de de verbinding wilt en als tweede argument de query, zodat hij weet waar de query heen gestuurd moet worden. mysqli_query geeft, als de query gelukt is een "identifier" terug, waar de opgehaalde rows in staan. Of false als de query mislukt is.

Zoals je wellicht gezien hebt, gebruiken we ook een andere functie. Namelijk mysqli_real_escape_string, deze zorgt er voor dat er geen "foute" tekens in je query terechtkomen en dat de query daardoor niet uitgevoerd wil worden. Ook lost deze functie in een later stadium een groot deel van de database beveiligingsproblemen op.

De tabel aanmaken

Nu we een database hebben aangemaakt, zullen we na moeten gaan denken over de informatie die we op willen slaan. We gaan nu kijken wat we op willen slaan.

• De naam van de gast
• Het e-mailadres van de gast
• Of we het e-mailadres al dan niet mogen weergeven
• De website van de gast
• Het bericht zelf
• De datum waarop het bericht gepost is

Als we dat bedacht hebben, zullen we een tabel moeten bedenken. Zoals er ook te zien is de relatie constant een-op-een. Dit houdt in dat we de alle gegevens mooi in één tabel kunnen zetten. Nu zullen we namen moeten bedenken voor de velden en aan moeten geven welk veld de Primary Key zal worden. De tabel zelf zal de velden "naam", "e-mail", "show_e-mail", "website", "datum" en "bericht" krijgen. Met "id" als Primary Key, zodat we daar op kunnen zoeken, later. Het SQL statement om een tabel te maken ziet er als volgt uit;

CREATE TABLE berichten
(
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`naam` VARCHAR(50),
	`e-mail` VARCHAR(80),
	`show_e-mail` TINYINT(1),
	`website` VARCHAR(50),
	`bericht` TINYTEXT,
	`datum` DATETIME
)

We zullen deze query op dezelfde manier uit voeren als bij het CREATE DATABASE statement, dus de code zal je bekend voorkomen.

Tabel.php

PHP-code:

<?php
$_CONFIG["Username"] = 'root';
$_CONFIG["Password"] = '';
$_CONFIG["Host"]     = 'localhost';
$_CONFIG["Database"] = 'gastenboek';

$Verbinding = mysqli_connect($_CONFIG["Host"], $_CONFIG["Username"], $_CONFIG["Password"], $_CONFIG["Database"]);

if($Verbinding == false) // Verbinding is mislukt!
{
	trigger_error("Kan geen verbinding maken met de database");
}

$Databasetabel="
CREATE TABLE berichten
(
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`naam` VARCHAR(50),
	`e-mail` VARCHAR(80),
	`show_e-mail` TINYINT(1),
	`website` VARCHAR(50),
	`bericht` TINYTEXT,
	`datum` DATETIME
)";
$Result = mysqli_query($Verbinding, mysqli_real_escape_string($Verbinding, $Databasetabel));

if($Result == false)
{
	trigger_error("Kan de tabel niet aanmaken! Waarschijnlijk bestaat de tabel al.");
}
else
{
	echo "De tabel is succesvol aangemaakt";
}

mysqli_close($Verbinding);
?>

De tabel vullen met test data

Omdat we nog geen gasten hebben die ons gastenboek schrijven zullen we zelf gegevens in de tabel moeten plaatsen om dadelijk de queries te kunnen testen om het gastenboek op te halen. Als we klaar zijn kunnen we deze gegevens er weer uit halen door middel van een TRUNCATE TABLE-query. Een query die ook in het administrator paneel komt. De query die we gaan gebruiken voor het vullen wordt een insert query. Zoals die hieronder. INSERT INTO `berichten` SET `naam` = !, `e-mail` = !, `show_email` = !, `website` = !, `bericht` = !, `datum` = NOW() Waarbij notie gemaakt moet worden van de ! tekens, die in dit voorbeeld als "place holder" dienen, daar komt later nog data te staan. Dan hebben we nog de NOW() functie, deze genereert aan het begin van de query een keer de datum en tijd en gebruikt die dan over de hele query, dus als je je query uitvoert op 23 maart, en het duurt 3 dagen om de query uit te voeren is het resultaat van NOW() nog steeds 23 maart. Nou is dat meestal niet relevant omdat het uitvoeren van de query meestal maar een tiende of een honderdste van een seconde duurt. Dus kunnen wij dat mooi gebruiken in ons gastenboek. Om de test gegevens makkelijk in te kunnen voeren gaan we eerst een array maken met de test data er in. Let wel op dat straks de gegevens andersom komen te staan als je het gastenboek opent. Dat komt omdat we dan de nieuwste gegevens bovenaan willen laten zien.

TestData.php:

PHP-code:

<?php
include_once('verbinding.php');

$TestData = array();
$TestData[] = array('Henk', 'henk@example.com', false, 'www.example.com', 
	"He! Leuk idee zo''n gastenboek op je website!");
$TestData[] = array('Xavier', 'xavier@example.net', true, '', "Test bericht");
$TestData[] = array('Peter Gulutzan', 'peter@mysql.com', false, "http://mysql.com", 
	"Ik kom van de mysql-foundation even een kijkje nemen in je gastenboek, leuk gedaan");

$Query = "
INSERT INTO
	`berichten` 
SET
	`naam` = '%s', 
	`e-mail` = '%s', 
	`show_e-mail` = %u, 
	`website` = '%s', 
	`bericht` = '%s', 
	`datum` = NOW()
";

foreach ($TestData as $Data)
{
	mysqli_query($Verbinding, vsprintf($Query, $Data)) 
		or trigger_error('Kan de query niet uitvoeren!');
}

mysqli_close($Verbinding);
?>

Verbinding.php:

PHP-code:

<?php
$_CONFIG["Username"] = 'root';
$_CONFIG["Password"] = '';
$_CONFIG["Host"]     = 'localhost';
$_CONFIG["Database"] = 'gastenboek';

$Verbinding = mysqli_connect($_CONFIG["Host"], $_CONFIG["Username"], $_CONFIG["Password"], $_CONFIG["Database"]);

if($Verbinding == false) // Verbinding is mislukt!
{
	trigger_error("Kan geen verbinding maken met de database");
}
?>

Deze pagina slaan we zo op omdat we hem nog vaak nodig zullen hebben. Sla het op als Verbinding.php.

Gebruikersinvoer

Voor het invoeren van de berichten gebruiken we een HTML pagina met een formuliertje waar de gebruiker een aantal waarden in kan vullen. Deze gegevens komen dus uiteindelijk in het gastenboek te staan. Het formulier is opgemaakt met behulp van CSS. Om het formulier te verwerken en in de database te zetten versturen we het bericht naar verwerk_bericht.php.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>Bericht invoeren</title>
		<style type="text/css">
		<!--
		label { float: left; clear: left; text-align: right; width: 140px; }
		input, textarea { float: right; clear: right; }
		form { width: 340px; margin-left: auto; margin-right: auto; 
			border: 1px solid black; padding: 10px; }
		h1 { text-align: center; font-size: 24px; }
		input, textarea { width: 175px; }
		input { height: 15px; }
		label { height: 20px; }
		textarea { height: 150px; margin-top: 5px; }
		#submit { height: 22px; }
		* { font-family: sans-serif; font-size: 12px; margin-top: 2px; }
		-->
		</style>
	</head>
	<body>
		<h1>Gastenboek</h1>
		<form method="post" action="verwerk_bericht.php">
			<label for="naam">Naam</label>
				<input type="text" id="naam" name="Naam"/>
			<label for="e-mail">e-mailadres</label>
				<input type="text" id="e-mail" name="email"/>
			<label for="display_e-mail">e-mailadres weergeven</label>
				<input type="checkbox" name="d_email" id="display_e-mail"/>
			<label for="website">Website</label>
				<input type="text" value="http://" name="website" id="website"/>
			<label for="bericht">Bericht</label>
				<textarea id="bericht" name="bericht"></textarea>
			<label></label>
				<input type="submit" id="submit" value="Verstuur bericht"/>
		</form>
	</body>
</html>

Er zijn een aantal zaken waar we rekening mee moeten houden tijdens het verwerken van het gastenboek, namelijk beveiliging en of de gegevens valide zijn (het e-mailadres moet een echt e-mailadres zijn en de website een echte website) Om dat te kunnen gaan we gebruik maken van reguliere expressies. Ik ga hier niet uitleggen hoe die werken, maar ik geef je wel twee reguliere expressies om de twee verplichte gegevens te valideren. Ook moeten we rekening houden met SQL injection.

#email adressen valideren
;^([a-z0-9-_]+)(.[a-z0-9-_]+)*@([a-z0-9-]+)(.[a-z0-9-]+)*.[a-z]{2,4}$;i

Een email-adres moet dus bestaan uit 1 of 2 groepjes alfanumerieke waarden (A tot Z, 0 tot 9 en _) met een punt tussenin, gevolgd door een apestaartje en 1 of 2 groepjes alfanumerieken, afgesloten door 2 tot 4 letters.

En om URL's (adressen van websites) te valideren:

;^http\:\/\/[a-z0-9-]+.([a-z0-9-]+.)?[a-z]+;i

Omdat we die reguliere expressies niet zomaar in PHP kunnen gebruiken, op een manier als we in perl zouden doen, maken we gebruik van de preg_match() functie die standaard in PHP zit. Daar schrijven we een mooie functie omheen zodat we de acties vaker kunnen gebruiken.

PHP-code:

function is_email($email)
{
	return (bool)preg_match(';^([a-z0-9-_]+)(.[a-z0-9-_]+)*@([a-z0-9-]+)(.[a-z0-9-]+)*.[a-z]{2,4}$;i', $email);
}

function is_url($url)
{
	return (bool)preg_match(';^http\:\/\/[a-z0-9-]+.([a-z0-9-]+.)?[a-z]+;i', $url);
}

SQL Injection

Aangezien we niet willen dat er database-aanvallen worden uitgevoerd zullen we dit eerst moeten voorkomen, dat zien we dadelijk. Het idee achter een database-aanval is dat er malafide data in de echte SQL query wordt geïnjecteerd. Neem bijvoorbeeld de volgende query:

$query = "SELECT * FROM `table` WHERE `x` = " . $waarde;

Hier kun je alles in de variabele $waarde zetten, ook bijvoorbeeld de string " 1 OR 1=1" die er voor zorgt dat alle gegevens uit de hele tabel worden opgevraagd en niet alleen de gegevens die voldoen aan de voorwaarde die wij in gedachten hadden. Nou is dat niet zo'n ramp in dit geval, omdat het geen gegevens verwijdert uit onze query. Daar steekt MySQL zelf namelijk een stokje voor. Normaal zou je namelijk een query 'koppelen' door het ; teken, als je $waarde dan in "1; DELETE * FROM table" verandert, zie je de bui al hangen? Maar, omdat MySQL niet twee query's kan verwerken die gescheiden zijn door een ; werkt dit niet. Om toch een groot deel van de fouten te voorkomen, meest voorkomende is het injecteren van een apostrof (') in de query, om simpelweg te kijken of MySQL over z'n nek gaat, gebruiken we de functie mysqli_real_escape_string, omdat we alle gegevens uit $_POST in de SQL query gaan gebruiken. De aanroep wordt dan dus:

PHP-code:

<?php
mysqli_real_escape_string($Verbinding, $_POST);

Nu is de post data klaar om in de query te stoppen.

Gegevens in de tabel stoppen

Om de gevevens uit het formulier in de tabel te stoppen moet je de volgende code uitvoeren:

PHP-code:

<?PHP 
function is_email($email)
{	return((bool)preg_match(';^([a-z0-9-_]+)(.[a-z0-9-_]+)*@([a-z0-9-]+)(.[a-z0-9-]+)*.[a-z]{2,4}$;i', $email));
}
function is_url($url)
{	return((bool)preg_match(';^http\:\/\/[a-z0-9-]+.([a-z0-9-]+.)?[a-z]+;i', $url));
}
$Query = "
	INSERT INTO
		`berichten` 
	SET
		`naam` = '%s', 
		`e-mail` = '%s', 
		`show_e-mail` = %u, 
		`website` = '%s', 
		`bericht` = '%s', 
		`datum` = NOW()
	";
if(is_email($_POST['email']))
{	// Verwijder de url als hij niet bestaat.
	if(!is_url($_POST['website']))
	{	$_POST['website'] = '';
	}
	
	
	// Dit moet eerst, mysqli_real_escape_string() geeft anders een foutmelding.
	include_once('verbinding.php'); 
	 
	mysqli_real_escape_string($Verbinding, $_POST);
	
	
	if(mysqli_query($Verbinding, vsprintf($Query,$data)))
	{	// Het is gelukt.
		echo('Gegevens zijn toegevoegd.');
	}
	else
	{	// Het is mislukt.
		echo('Er is een error opgetreden.');
	}
	
	mysqli_close($Verbinding);
}
?>

Gegevens uit de tabel halen

Je hebt niks aan gegevens in een database als je ze er niet uit kan halen. Je gebruik de sql 'SELECT * FROM `table`' als je gegevens wilt opvragen. Hier een voorbeeld als je gegevens uit een gastenboek wilt halen. (Deze gebruikt het document 'verbinding.php' om te verbinden met de database.)

PHP-code: Voorbeeld: gegevens uit een tabel halen

<html>
    <head>
        <title>gegevens uit een tabel halen</title>
    </head>
    <body>
<?php
include_once('verbinding.php');
$data = array();
$i = 0;
$result = mysqli_query($Verbinding, "SELECT * FROM `berichten`");
while ($lijst = mysqli_fetch_assoc($result))
{	foreach ($lijst as $sleutel => $waarde)
	{	// Met htmlspecialchars() voorkom je dat html wordt uitgevoerd.
		//   Dus gebruiken we het op alle gegevens behalve op 'show_e-mail'.
		if($sleutel != 'show_e-mail')
		{	$waarde = htmlspecialchars($waarde);
		}
		
		
		/*-------------------------------*
		 * Zet alle berichten gesorteerd *
		 *  op id in $data              *
		 *-------------------------------*/
		$data[$i][$sleutel] = $waarde;
	}
	$i++;
}
mysqli_free_result($result);
mysqli_close($Verbinding);  	// Sluit de verbinding.
				//   We hebben hem nu niet meer nodig.

$data = array_reverse($data);   // Zet de nieuwste berichten bovenaan
				//   in plaats van onderaan.


$cnt = count($data);
for($i = 0; $i < $cnt; ++$i)
{	$bericht = $data[$i];
	
	echo('<table>');	// Zet de gegevens netjes in een tabel.
	echo('<tr>');
	echo('<th>');
	echo('naam: ');
	echo('</th><td>');
	
	
	// Lees de gegevens uit de associatieve array.
	
	echo($bericht['naam']);    	// Zo makkelijk is het vanaf hier.
	echo('</td></tr>');
	if($bericht['show_e-mail'])  	// controleer of de e-mail weergegeven 
	{	echo('<tr><th>');    	//   mag worden
		echo('e-mail: ');
		echo('</th><td>');
		echo($bericht['e-mail']);
		echo('</td></tr>');
	}
	echo('<tr><th>');
	echo('website: ');
	echo('</th><td>');
	echo('<a href="' . $bericht['website'] . '">' . $bericht['website'] . '</a>');
	echo('</td></tr><tr><th colspan="2">');
	echo('Bericht:');
	echo('</th></tr><tr><td colspan="2">');
	echo(nl2br($bericht['bericht']));		// Met nl2br() worden alle enters in het 
							//  bericht omgezet naar <br/>.
	echo('</td></tr><tr><td colspan="2">');
	echo('<b>Gepost op: </b>');
	echo($bericht['datum']);
	echo('</td></tr></table>');
}

?>
    </body>
</html>

Databasebescherming van mailinglists en ledenlijsten

Een heel eenvoudig codering- / decoderingsschema is het volgende:

1. Gebruik een one-way hash functie voor de index. De uitkomst gebruikt u als de cipher-key bij het coderen van de datavelden.

2. Gebruik een symmetrische codering voor de datavelden. Bij symmetrische codering wordt zowel voor de codering als de decodering dezelfde cipher-key gebruikt.

De database doorzoeken:

Doorzoek de index met de hash-waarde, gebruik dezelfde hash-functie als bij stap 1. Voor elke record-match decodeert u de andere datavelden door de cipher-key van het indexveld te gebruiken.

Zie ook

• MySQL