andrelashley/lab5 part2

## lab5 part2
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
	<title>Results</title>
</head>
<body>
	<?php
    /*
    Lab 5
    part2.php
    Andre Lashley (andre.lashley@gmail.com)
    February 5, 2012
    5 hours estimated completion time
    4 hours actual completion time
    This program connects to a database, runs queries against it and presents the results to the user.
    Visit http://deepblue.cs.camosun.bc.ca/~cst020/comp170/lab5/part2form.html to use it
    */

    // include our database credentials, it is above the public_html directory
    // so that the public cannot get our credentials and compromise the security
    // of the database.
    require('../../../comp170/db.php');

    // connect to the MySQL server using the required credentials
    $conn = mysql_connect("localhost", "170user", "phphasclass");

    // die if unable to connect and output and error message
    if(!$conn) {
        die('Could not connect: ' . mysql_error());
    }

    // choose the db and run a query.
    mysql_select_db("comp170", $conn);


    $fields = $_POST['f_name'];
    $table_name = strtolower($_POST['t_name']);
    $condition = $_POST['condition'];

    // validate the user's input
    if(empty($fields)){
        echo "<p>No fields were selected</p>";
        echo "<a href='part2form.html'>Enter another query</a>";
        exit;
    }

    if(empty($table_name)){
        echo "<p>You must select a tablename</p>";
        echo "<a href='part2form.html'>Enter another query</a>";
        exit;
    }

    $inputArr = array();

    $inputArr[] = $fields;
    $inputArr[] = $table_name;
    $inputArr[] = $condition;

    // sanitize the user's input
    $clean = array_map("strip_tags", $inputArr);
    $clean = array_map("trim", $clean);

    // remove SQL verbs or ';' if present
    for($i = 0; $i < count($clean); $i++){
        $clean[$i] = preg_replace("/\b(DELETE|SELECT|DROP|ALTER|CREATE|FROM|WHERE|INSERT|INTO|UPDATE|SET)\b/i", "", $clean[$i]);
        $clean[$i] = preg_replace("/;/", "", $clean[$i]);
    }

    $clean[2] = (empty($clean[2])) ? ";" : "WHERE ".$clean[2].";";

    // build the query
    $query = "SELECT $clean[0] FROM $clean[1] $clean[2]";

    // run the query
    $result = mysql_query($query, $conn);

    // get the number of rows
    $numfields = mysql_num_fields($result);

    // Display the results.
    echo "<h2>Results</h2>";
    if ($result) {
        echo "<table border='1'>";
        echo "<tr>";
        for($i = 0; $i < $numfields; $i++){
            echo '<th>'. mysql_field_name($result, $i) .'</th>';
        }
        echo "</tr>";
        while ($row=mysql_fetch_assoc($result)) {
             echo "<tr>";
            foreach($row as $k => $v){
                echo "<td>$v</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
        echo "<a href='part2form.html'>Enter another query</a>";
    } else {
        echo "<h2>No results to display</h2>";
        echo "<a href='part2form.html'>Enter another query</a>";
    }
    ?>
</body>
</html>
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
	<head>
	<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
	<title>Results</title>
	</head>
	<body>
	<?php
	/*
	Lab 5
	part2.php
	Andre Lashley (andre.lashley@gmail.com)
	February 5, 2012
	5 hours estimated completion time
	4 hours actual completion time
	This program connects to a database, runs queries against it and presents the results to the user.
	Visit http://deepblue.cs.camosun.bc.ca/~cst020/comp170/lab5/part2form.html to use it
	*/

	// include our database credentials, it is above the public_html directory
	// so that the public cannot get our credentials and compromise the security
	// of the database.
	require('../../../comp170/db.php');

	// connect to the MySQL server using the required credentials
	$conn = mysql_connect("localhost", "170user", "phphasclass");

	// die if unable to connect and output and error message
	if(!$conn) {
	die('Could not connect: ' . mysql_error());
	}

	// choose the db and run a query.
	mysql_select_db("comp170", $conn);


	$fields = $_POST['f_name'];
	$table_name = strtolower($_POST['t_name']);
	$condition = $_POST['condition'];

	// validate the user's input
	if(empty($fields)){
	echo "<p>No fields were selected</p>";
	echo "<a href='part2form.html'>Enter another query</a>";
	exit;
	}

	if(empty($table_name)){
	echo "<p>You must select a tablename</p>";
	echo "<a href='part2form.html'>Enter another query</a>";
	exit;
	}

	$inputArr = array();

	$inputArr[] = $fields;
	$inputArr[] = $table_name;
	$inputArr[] = $condition;

	// sanitize the user's input
	$clean = array_map("strip_tags", $inputArr);
	$clean = array_map("trim", $clean);

	// remove SQL verbs or ';' if present
	for($i = 0; $i < count($clean); $i++){
	$clean[$i] = preg_replace("/\b(DELETE\|SELECT\|DROP\|ALTER\|CREATE\|FROM\|WHERE\|INSERT\|INTO\|UPDATE\|SET)\b/i", "", $clean[$i]);
	$clean[$i] = preg_replace("/;/", "", $clean[$i]);
	}

	$clean[2] = (empty($clean[2])) ? ";" : "WHERE ".$clean[2].";";

	// build the query
	$query = "SELECT $clean[0] FROM $clean[1] $clean[2]";

	// run the query
	$result = mysql_query($query, $conn);

	// get the number of rows
	$numfields = mysql_num_fields($result);

	// Display the results.
	echo "<h2>Results</h2>";
	if ($result) {
	echo "<table border='1'>";
	echo "<tr>";
	for($i = 0; $i < $numfields; $i++){
	echo '<th>'. mysql_field_name($result, $i) .'</th>';
	}
	echo "</tr>";
	while ($row=mysql_fetch_assoc($result)) {
	echo "<tr>";
	foreach($row as $k => $v){
	echo "<td>$v</td>";
	}
	echo "</tr>";
	}
	echo "</table>";
	echo "<a href='part2form.html'>Enter another query</a>";
	} else {
	echo "<h2>No results to display</h2>";
	echo "<a href='part2form.html'>Enter another query</a>";
	}
	?>
	</body>
	</html>