Learning PHP and MySQL
Chapter 12 Code
Monday, September 25, 2006
Cross-Site Scripting Attacks
To guard against these attacks, you should pass any strings that came from a user through the htmlentities function. It takes the format:
htmlentities(string_to_clean)
For example:
print "The title of the book is: " .
htmlentities($_POST['title']);
Here’s an example of what htmlentities does to the string:
<?php
$sample = "A sample is <i>italics</i>";
echo htmlentities($sample);
?>
When executed, this returns:
A sample is <i>italics</i>
Here’s a script to display the title table with the htmlentities functionality added:
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
// Dislplay the table
$query = "SELECT * FROM `books`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>Title</th><th>Pages</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo htmlentities($result_row["title"]) . '</td><td>';
echo htmlentities($result_row["pages"]) . '</td></tr>';
}
echo "</table>";
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 11:11 AM
Chapter 12 Code • (5) Comments • (75) Trackbacks • Permalink
Chapter 12 Code • (5) Comments • (75) Trackbacks • Permalink
Example 12-1 Creating a table from a PHP page in create_table.php
<?php include('db_login.php'); require_once( 'DB.php' );
$connection = DB::connect( "mysql://$db_username:$db_password@$db_host/
$db_database");
if (!$connection)
{
die ("Could not connect to the database: <br>". DB::errorMessage());
};
$query = '
CREATE TABLE `purchases` (
`purchase_id` int(11) NOT NULL auto_increment,
`user_id` varchar(10) NOT NULL,
`title_id` int(11) NOT NULL,
`purchased` timestamp NOT NULL, PRIMARY KEY (`purchase_id`)
)
';
echo ("Table created successfully!");
$result = $connection->query($query);
if (DB::isError($result))
{
die ("Could not query the database: <br>". $query. " ".DB::errorMessage($result));
}
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 10:50 AM
Chapter 12 Code • (3) Comments • (75) Trackbacks • Permalink
Chapter 12 Code • (3) Comments • (75) Trackbacks • Permalink
Example 12-10 The delete.php code for performing a delete
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$purchase_id = $_GET["purchase_id"];
$query = "DELETE FROM `purchases` WHERE `purchase_id`=$purchase_id";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
?>
<html>
<head>
<title>Item deleted!</title>
<meta http-equiv="refresh" content="4; url=deletion_link.php">
</head>
<body>
Item deleted!<br />
<?php
$query = "SELECT * FROM `purchases` NATURAL JOIN `books` NATURAL JOIN `authors`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>User</th><th>Title</th><th>Pages</th>";
echo "<th>Author</th><th>Purchased</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo $result_row["user_id"] . '</td><td>'; echo $result_row["title"] . '</td><td>'; echo $result_row["pages"] . '</td><td>'; echo $result_row["author"] . "</td><td>";
echo $result_row["purchased"] . "</td></tr>";
}
echo "</table>";
$connection->disconnect();
?>
</body>
</html>
Posted by krautgrrl on 09/25 at 11:18 AM
Chapter 12 Code • (4) Comments • (136) Trackbacks • Permalink
Chapter 12 Code • (4) Comments • (136) Trackbacks • Permalink
Example 12-11 Using mysql_insert_id to link up an author to a title
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "INSERT INTO `books` VALUES (NULL,'Python in a Nutshell',600)";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
$last_value = mysql_insert_id();
echo "The id that was created is: $last_value<br />";
$query = "INSERT INTO `authors` VALUES (NULL,$last_value,'Alex Martelli')";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo "Inserted successfully!";
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 11:18 AM
Chapter 12 Code • (26) Comments • (1) Trackbacks • Permalink
Chapter 12 Code • (26) Comments • (1) Trackbacks • Permalink
Example 12-12 Displaying the authors in a list
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
// Display the table
$query = "SELECT * FROM `books`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>Title</th><th>Pages</th><th>Authors</th></tr>"; while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) { echo "<tr><td>";
echo htmlentities($result_row["title"]) . '</td><td>';
echo htmlentities($result_row["pages"]) . '</td><td>';
$author_query = "SELECT * FROM `authors` WHERE `title_id`=".$result_row["title_id"];
$author_result = $connection->query($author_query);
if (DB::isError($author_result)){
die("Could not query the database: <br />".$author_query."
".DB::errorMessage($author_result));
}
$author_count = $author_result->numRows();
if (0 == $author_count) {
echo 'none';
}
$counter = 0;
while ($author_result_row = $author_result->fetchRow(DB_FETCHMODE_ASSOC)) {
$counter++;
echo htmlentities($author_result_row["author"]);
if ($counter != $author_count) {
echo ', ';
}
}
echo '</td></tr>';
}
echo '</table>';
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 11:19 AM
Chapter 12 Code • (5) Comments • (319) Trackbacks • Permalink
Chapter 12 Code • (5) Comments • (319) Trackbacks • Permalink
Example 12-2 Dropping the purchases table in drop.php
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "DROP TABLE `purchases`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo "Table dropped successfully!";
$connection->disconnect();
?>
Example 12-2 returns:
Table dropped successfully!
Posted by krautgrrl on 09/25 at 10:51 AM
Chapter 12 Code • (0) Comments • (2754) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (2754) Trackbacks • Permalink
Example 12-3 Using a predefined INSERT statement in insert.php
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "INSERT INTO `purchases` VALUES (NULL,'mdavis',2,NULL)";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo "Inserted successfully!";
$connection->disconnect();
?>
When you call up insert.php, in your browser, you get:
Inserted successfully!
Posted by krautgrrl on 09/25 at 10:53 AM
Chapter 12 Code • (2) Comments • (179) Trackbacks • Permalink
Chapter 12 Code • (2) Comments • (179) Trackbacks • Permalink
Example 12-4 Using embedded links to provide a purchase button in pear_purchase_example.php
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "SELECT * FROM `books`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>Title</th><th>Pages</th><th>Buy</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo $result_row["title"] . '</td><td>';
echo $result_row["pages"] . '</td><td>';
echo '<a href="purchase.php?title_id='.$result_row["title_id"].'">Click to purchase</a></td></tr>';
}
echo "</table>";
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 10:54 AM
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Example 12-5 The file purchase.php processes the user action based on the title_id parameter
<?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$title_id = $_GET["title_id"];
$user_id = 'mdavis';
$query = "INSERT INTO `purchases` VALUES (NULL,'$user_id',$title_id,NULL)";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
?>
<html>
<head>
<title>Thanks for your purchase!</title>
<meta http-equiv="refresh" content="4; url=pear_purchase_example.php">
</head>
<body>
Thanks for your purchase!<br />
<?php
$query = "SELECT * FROM purchases NATURAL JOIN books NATURAL JOIN authors";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>User</th><th>Title</th><th>Pages</th>";
echo "<th>Author</th><th>Purchased</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo $result_row["user_id"] . '</td><td>';
echo $result_row["title"] . '</td><td>';
echo $result_row["pages"] . '</td><td>';
echo $result_row["author"] . "</td><td>";
echo $result_row["purchased"] . "</td></tr>";
}
echo "</table>";
$connection->disconnect();
?>
</body>
</html>
Posted by krautgrrl on 09/25 at 10:56 AM
Chapter 12 Code • (2) Comments • (3) Trackbacks • Permalink
Chapter 12 Code • (2) Comments • (3) Trackbacks • Permalink
Example 12-6 Using input from a form to add a title
<?php
// Define a function to perform the database insert and display the titles function insert_db($title, $pages){
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
// The query includes the form sumbission values that were passed to the function
$query = "INSERT INTO `books` VALUES (NULL,'$title','$pages')";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo "Inserted OK.<br />";
// Display the table
$query = "SELECT * FROM `books`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>Title</th><th>Pages</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo $result_row["title"] . '</td><td>';
echo $result_row["pages"] . '</td></tr>';
}
echo "</table>";
$connection->disconnect();
}
?>
<html>
<head>
<title>Inserting From a Form</title>
</head>
<body>
<?php
// Retrieve the variable from the form submission
$title = $_GET["title"];
$pages = $_GET["pages"];
if (($title != NULL ) && ($pages != NULL)){
insert_db($title,$pages);
}
else {
// Display the form echo '
<h1>Enter a new title:</h1>
<form action="'.$_SERVER["PHP_SELF"].'" method="GET">
<label> Title:
<input type="text" name="title" id="title" />
</label>
<label> Pages:
<input type="text" name="pages" id="pages" />
</label>
<input type="submit" value="Go!" />
</form>';
}
?>
</body>
</html>
Posted by krautgrrl on 09/25 at 11:07 AM
Chapter 12 Code • (15) Comments • (0) Trackbacks • Permalink
Chapter 12 Code • (15) Comments • (0) Trackbacks • Permalink
Example 12-7 Checking for magic quotes
<?php
if (get_magic_quotes_gpc()) {
echo "Magic quotes are enabled.";
} else {
echo "Magic quotes are disabled.";
}
?>
The script should return:
Magic quotes are enabled.
Posted by krautgrrl on 09/25 at 11:10 AM
Chapter 12 Code • (0) Comments • (233) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (233) Trackbacks • Permalink
Example 12-8 Updating a field
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "UPDATE `books` SET `pages`=558 WHERE `title_id`=2";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo "Updated successfully!";
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 11:16 AM
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Example 12-9 Providing a link to delete a purchase in deletion_link.php
<?php require_once('db_login.php'); require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
}
$query = "SELECT * FROM `purchases` NATURAL JOIN `books`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />".$query." ".DB::errorMessage($result));
}
echo '<table border="1">';
echo "<tr><th>User</th><th>Title</th><th>Purchased</th><th>Remove</th></tr>";
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "<tr><td>";
echo $result_row["user_id"] . '</td><td>';
echo $result_row["title"] . '</td><td>';
echo $result_row["purchased"] . '</td><td>';
echo '<a href="delete.php?purchase_id='.$result_row["purchase_id"].'">Click to remove from purchases</a></td></tr>';
}
echo '</table>';
$connection->disconnect();
?>
Posted by krautgrrl on 09/25 at 11:17 AM
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
SQL Injection
1,1);drop table users;. When this query is added to a query like this:
$query = "INSERT INTO ‘books’ VALUES (NULL,$title,$pages)";
Here’s what could happen:
$query = "INSERT INTO ‘books’ VALUES (NULL,1,1);drop table users; ,$pages)";
Posted by krautgrrl on 09/25 at 11:08 AM
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Chapter 12 Code • (0) Comments • (0) Trackbacks • Permalink
Statistics
This page has been viewed 271394 times
Page rendered in 0.2061 seconds
Total Entries: 224
Total Comments: 16
Total Trackbacks: 307338
Most Recent Entry: 09/27/2006 12:39 pm
Most Recent Comment on: 10/26/2007 10:00 am
Total Members: 2
Total Logged in members: 0
Total guests: 10
Total anonymous users: 0
Most Recent Visitor on: 07/30/2010 05:52 am
The most visitors ever was 1103 on 11/20/2007 12:50 pm
