Classified site.

bluebyyou · February 17, 2007

So I made a basic classified site, sort of like craigslist, only for a much smaller community. It works, and is fairly secure(i think). Does anyone have suggestions for better security and more elegant ways to code this? Forgive my variable names and such, they are a bit sloppy. Also I still need to add something on the classified_type.php page when there is not a listing for that category.

the working script is at www.rockypages.com/classified.php

Here it is...

<?php 
//classified.php

include("includes.php"); ?>
<?php include("header.php"); ?>
<?php
$query1 = "SELECT * FROM PostingType";
dbcon($query1);// <-- database connection function located on includes.php 
?>
<a href="insert.php">Post a New Ad</a><br />
<table bgcolor="#000000" cellpadding="2"><tr>
<?php
while ($row = mysql_fetch_array($result))// <-- result of function dbcon
{
extract($row);
?>
<td bgcolor="#FFFFFF" valign="top">
<div>
<h3><a href="classified_type.php?type=<?php echo $key; ?>"><?php echo $name;?></a></h3> 
<?php
$query2 = "SELECT * FROM PostingSubType WHERE ptype = $key";
    dbcon2($query2);// <-- database connection function located on includes.php 
while ($row2 = mysql_fetch_array($result2))
{
extract($row2); ?>
<a href="classified_type.php?subtype=<?php echo $psubtype; ?>"><?php echo $postingsubtype; ?></a><br />
<?php
}
?></div></td>
<?php } ?>
</tr>
</table>
</body>
</html>

<?php 
//classified_type.php

include("includes.php"); ?>
<?php
if (isset($_GET['type'])){
$query = "Select * FROM Posting WHERE type='$_GET[type]'";}
else {
$query = "Select * FROM Posting WHERE subtype='$_GET[subtype]'";}
dbcon($query); // <-- database connection function located on includes.php 
?>
<?php include("header.php"); ?>
<h3>
<?php if (isset($_GET['type'])){ posting_type($_GET['type']);}
       else { posting_subtype($_GET['subtype']); }?></h3>
<div>
<?php 
while ($row = mysql_fetch_array($result))// <-- result of function dbcon
{
extract($row); ?>
<?php echo $postdate; ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?></a> 
<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype);?></i> <?php } ?><br>
<?php } ?>
</div>
</body>
</html>

<?php 
//insert.php

include("includes.php"); ?>
<?php include("header.php"); ?>
<?php
switch ($do)
{
case "insert":



	/* Set up array of field labels */
	$label_array = array ("price" => "Price","title" => "Title","description" => "Description","email" => "E-Mail","vemail" => "Verify E-Mail");
	foreach ($_POST as $field => $value)
	{
		/* Check for blank fields */
		if ($value == "")
		{

			if ($field == "title" or $field == "price" or $field == "description" or $field == "email")
			{
				$blank_array[$field] = "1";
			}
		}
		elseif ($field == "email")
		{
			if(!ereg(".+\@.+\..+",$_POST[$field]))
			{
				{
					$bad_format[$field] =  "2";
				}
			}

		}
	}
	if ($_POST['email'] != $_POST['vemail'])
	{
		$match_email = "3";
	}

	/* if any fields were not ok, display error message and form */
	if (@sizeof($blank_array) > 0 or @sizeof($bad_format) > 0 or $match_email > 0)
	{
		if (@sizeof($blank_array) > 0)
		{
			/*display message for missing information*/
			echo "You didnt fill out one or more required fields. You must enter:<br>";
			/*display list of missing information*/
			foreach ($blank_array as $field => $value)
			{
				echo "    $label_array[$field]<br>";
			}
		}
		if (@sizeof($bad_format) > 0)
		{
			/*display message for bad information*/
			echo "One or more fields have information that appears incorrect. Correct the format for:<br>";
			/*display list of bad information*/
			foreach ($bad_format as $field => $value)
			{
				echo "    $label_array[$field]<br>";
			}
		}
		if ($match_email > 0)
		{
			/*display message for email's not matching*/
			echo "You must verify your E-Mail.";
		}



	exit();
	}

	$price = strip_tags(trim($_POST['price']));
	$title = strip_tags(trim($_POST['title']));
	$description = strip_tags(trim($_POST['description']));

	$today = date("Y-m-d");
	$query = sprintf("INSERT INTO Posting (postdate,type,subtype,price,title,description,email,reply) 
                  VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')",
			  $today, $_POST['type'],$_POST['subtype'],$price,$title,$description,
                  $_POST['email'],$_POST['reply']);

	dbcon($query); // <-- database connection function located on includes.php
echo "upload success.";
break;
case "form":
	unset($_GET['do']); 
	echo "Category: "; posting_type($_POST['type']); echo "<br>";
	echo "Sub Category: ";  posting_subtype($_POST['subtype']);  echo "<br><br>"; ?>
	<form  action="<?php echo $_SERVER['PHP_SELF']; ?>?do=insert" method="post">
	<input name="type" type="hidden" value="<?php echo $_POST['type'];?>" />
	<input name="subtype" type="hidden" value="<?php echo $_POST['subtype'];?>" />
	Title<input name="title" type="text"><br>

	<?php
	switch ($_POST['type'])
	{
		case "1":
			if ($_POST['subtype'] == 1 or $_POST['subtype'] == 6){$displayprice = 0;}
			else {$displayprice = 1;}
			break;
		case "2":
			$displayprice = 1;
			break;
		case "3":
			$displayprice = 0;
			break;
		case "4":
			$displayprice = 1;
			break;
		case "5":
			$displayprice = 1;
			break;
	} 

	if ($displayprice == 0){ echo "<font color='#CCCCCC'>Price</font><input name='price' type='text' disabled><br><br>";}
					  else { echo "Price<input name='price' type='text'><br><br>";} ?>


	Description<br>
	<textarea name="description" cols="40" rows="4"></textarea><br><br>

	Email<input name="email" type="text"><br>
	Verify<input name="vemail" type="text"><br><br>

	<input name="reply" type="radio" value="0" checked>Reply to my E-Mail<br>
	<input name="reply" type="radio" value="1">Do not show my e-mail<br><br>

	Upload image  (Coming Soon..)<br>
	<input name="h" type="text" disabled><input name="browse" type="button" disabled value="Browse..."><br><br>
	<input name="submit" type="submit" value="Submit">
	</form>
	<?php
break;
case "sbtype":
	unset($_GET['do']); ?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>?do=form" method="post">
	Sub Category: <select name="subtype" >
	<option value="0">Select One..</option>
	<?php 
	$query2 = "Select * FROM PostingSubType WHERE ptype= '$_POST[type]'";
	dbcon($query2); 
	while ($row = mysql_fetch_array($result))
	{
	extract($row); ?>
	<option value="<?php echo $psubtype; ?>"><?php echo $postingsubtype; ?></option>
	<?php } ?>
	</select>
	<input name="type" type="hidden" value="<?php echo $_POST['type'];?>" />
	<input name='submit' type='submit' value='submit'>
	</form>	<?php
break;
default; ?>
	<form action="<?php echo $_SERVER['PHP_SELF']; ?>?do=sbtype" method="post">
	Category: <select name="type" >
	<option value="0">Select One..</option>
	<?php 
	$query = "Select * FROM PostingType";
	dbcon($query);

	while ($row = mysql_fetch_array($result))
	{
	extract($row); ?>
	<option value="<?php echo $key; ?>"><?php echo $name; ?></option>
	<?php } ?>
	</select>
	<input name="submit" type="submit" value="submit">
	</form>
	<?php
break;
	}?>

<?php
//includes.php

/* function dbcon stores the database connection information and
   the connection functions */

/* $result must be handled on each individual page.
   for example: $row = mysql_fetch_array($result) .. */
function dbcon($sql) 
{
$host = "*****";
$user = "*****";
$password = "*****";
$database = "*****";

global $result;

$connection = mysql_connect($host,$user,$password) or die ("couldnt connect to rockypages server.");
$db = mysql_select_db($database, $connection) or die ("couldnt select rockypages database.");
$result = mysql_query($sql) or die ("Couldn't execute query.");

return $result;
}

function dbcon2($sql2) 
{
$host = "mysql.*.com";
$user = "*********";
$password = "*********";
$database = "*********";

global $result2;

$connection = mysql_connect($host,$user,$password) or die ("couldnt connect to rockypages server.");
$db = mysql_select_db($database, $connection) or die ("couldnt select rockypages database.");
$result2 = mysql_query($sql2) or die ("Couldn't execute query.");

return $result2;
}

/* This Function takes the numerica data from the PostingType Table 
   and converts it to the corresponding text. */ 
   
/* $input is the $key field in the database */
   
function posting_type($input)
{
$switch = $input;
switch ($switch)
{
case "1":
	$output = "For Sale";
	break;
case "2":
	$output = "Jobs";
	break;
case "3":
	$output = "Community";
	break;
case "4":
	$output = "Housing";
	break;
case "5":
	$output = "Services";
	break;
}
echo $output;
}

function posting_subtype($input)
{
$switch = $input;
switch ($switch)
{
case "1":
	$output = "Barter";
	break;
case "2":
	$output = "Bikes";
	break;
case "3":
	$output = "Cars/Trucks/Motorcycles";
	break;
case "4":
	$output = "Books";
	break;
case "5":
	$output = "CD/DVD/VHS";
	break;
case "6":
	$output = "Free";
	break;
case "7":
	$output = "Clothing & Accessories";
	break;
case "8":
	$output = "Furniture";
	break;
case "9":
	$output = "General";
	break;
case "10":
	$output = "Computer";
	break;
case "11":
	$output = "Jewelry";
	break;
case "12":
	$output = "Electronics";
	break;
case "13":
	$output = "Sporting";
	break;
case "14":
	$output = "Household";
	break;
case "15":
	$output = "Tools";
	break;
case "16":
	$output = "Musical Instruments";
	break;
case "17":
	$output = "Wanted";
	break;
case "18":
	$output = "Accounting/Finance";
	break;
case "19":
	$output = "Administrative/Office";
	break;
case "20":
	$output = "Art/Design";
	break;
case "21":
	$output = "Buisness/Management";
	break;
case "22":
	$output = "Education";
	break;
case "23":
	$output = "Government";
	break;
case "24":
	$output = "Medical/Health";
	break;
case "25":
	$output = "Non-Profit";
	break;
case "26":
	$output = "Retail/Food/Hospitality";
	break;
case "27":
	$output = "Sales/Customer Service";
	break;
case "28":
	$output = "Skilled Trade/Craft";
	break;
case "29":
	$output = "IT/Web Design";
	break;
case "30":
	$output = "TV/Film/Video";
	break;
case "31":
	$output = "Writing/Editing";
	break;
case "32":
	$output = "Other";
	break;
case "33":
	$output = "Lost & Found";
	break;
case "34":
	$output = "Rideshare";
	break;
case "35":
	$output = "Pets";
	break;
case "36":
	$output = "Volunteers";
	break;
case "37":
	$output = "Events";
	break;
case "38":
	$output = "Apartments/Housing";
	break;
case "39":
	$output = "Rooms/Shared";
	break;
case "40":
	$output = "Sublets/Temporary";
	break;
case "41":
	$output = "Housing Wanted";
	break;
case "42":
	$output = "Automotive";
	break;
case "43":
	$output = "Computer";
	break;
case "44":
	$output = "Creative";
	break;
case "45":
	$output = "Skilled Trade/Craft";
	break;
case "46":
	$output = "Financial";
	break;
case "47":
	$output = "Lessons";
	break;
case "48":
	$output = "Musicians";
	break;
}
echo $output;
}
?>

bluebyyou · February 17, 2007

forgot this..


<?php include("includes.php");
//post.php
$query = "Select * FROM Posting WHERE number='$_GET[id]'";
dbcon($query); // <-- database connection function located on includes.php 
?>
<?php include("header.php"); ?>
<?php 
$row = mysql_fetch_array($result); // <-- result of function dbcon
extract($row);
?>
<table border="1">
<tr><td><?php echo $title; ?></td><?php if($price > 0){ ?><td>$<?php echo $price; ?></td><?php } ?></tr>
<tr><td <?php if($price > 0){ ?>colspan="2"<?php } ?>><?php echo $description; ?></td></tr>
<tr><td <?php if($price > 0){ ?>colspan="2"<?php } ?>><?php if ($reply == 0){ echo $email;} else {echo "This poster has chosen to keep thier contact information confidential";} ?></td></tr>
</table>
</body>
</html>

ted_chou12 · February 17, 2007

No, this is not secure, you havnt delete your mysql db information in your 3rd piece of coding, i dont think that should be posted on a public forum :-\.

Ted

ted_chou12 · February 17, 2007

Not bad, I like it, the idea is cool, and everything was easy to navigate, maybe a search would be good, and the layout could be improved.

Ted

bluebyyou · February 17, 2007

Well I changed my passwords, and db names, if a moderator comes across this please edit my sensitive info out.

A search will definately show up, and yes obviously the layout. I was more concerned about getting it to work before making it pretty. I was mainly concerned that I may have thrown too much code at some simple problems.

redbullmarky · February 17, 2007

with code like this:

<?php
if (isset($_GET['type'])){
$query = "Select * FROM Posting WHERE type='$_GET[type]'";}
else {
$query = "Select * FROM Posting WHERE subtype='$_GET[subtype]'";}
?>

you're asking for trouble. basically you're taking input directly from the URI and directly throwing it into a query.

take a look at mysql_real_escape_string and Google up on "SQL Injection"

<?php
if (isset($_GET['type']))
{
   $type = mysql_real_escape_string($_GET['type'];
   $query = "SELECT * FROM Posting WHERE type='$type'";
   ... etc...
}
else
{
   $subtype = mysql_real_escape_string($_GET['subtype']);
   ... etc ...
}
?>

bluebyyou · February 17, 2007

maybe im not understanding that very well but isnt that basically the same thing?

as long as I use the GET method for that it wont be very secure correct?

redbullmarky · February 17, 2007

no.

imagine you have a query like this:

<?php
$query = "SELECT * FROM users WHERE username = '{$_POST['username']}' AND password = MD5('{$_POST['password']}')";
?>

now - imagine if someone put something in the username field that a) caused the first condition (the username check) to be true, and b) the password check to be ignored, so you'd end up with a query such as:

<?php
$query = "SELECT * FROM users WHERE username = 'admin'; # everything after here is ignored";
?>

and voila - you're in!

more experienced people could even get your query into something like:

SELECT * FROM Posting WHERE type = ''; DELETE * FROM Users

what mysql_real_escape_string will do is to 'escape' stuff like quotes, etc, by prepending backslashes to them, so they can't "affect" your queries in ways that you would prefer not to

have.

there's a few rules of thumb, in my book at least:

1) NEVER trust input from a user, be it $_GET, $_POST or $_COOKIE. assume EVERYONE is trying to get into your site with nasty input.

2) always validate the input to make sure it meets the criteria.

3) ALWAYS clean up $_GET / $_POST / $_COOKIE data before either using them to interrogate a database or displaying them to the screen.

the manual entry for mysql_real_escape_string does go into some detail about stuff like this, and a simple Google search as i pointed out before will open your eyes to prevent all sorts of stuff that you dont want.

I've been on the recieving end. Trust me, it's not nice.

bluebyyou · February 18, 2007

So i took the advice, and made it so my query doesnt take the statement right from GET. My problem now is that users can still put any input in the URL they want. It wont mess anything up except create a mysql error.

So now i need to make it so if someone puts something i dont want in, it will recognize that and instead of creating a mysql error it will say "This page doesnt exist" or something like that.

Im guessing i need to do something about that before it checks if $_GET['type'] is set. suggestions?

Ive also added breadcrumbs to the pages....

<?php 
//classified_type.php

include("includes.php"); ?>
<?php

if (isset($_GET['type']))
{
$gettype = mysql_escape_string($_GET['type']);
$query = "SELECT * FROM PostingType WHERE `key` = '$gettype'";
dbcon($query);// <-- database connection function located on includes.php 
$row = mysql_fetch_array($result);
extract($row);

$breadcrumb = "<a href='classified.php'>Classified</a> > $row[name]";


$query2 = "SELECT * FROM Posting WHERE type = '$gettype'"; 
   
}
if (isset($_GET['subtype']))
{
   $getsubtype = mysql_escape_string($_GET['subtype']);
   	$query = "SELECT * FROM PostingSubType WHERE psubtype = '$getsubtype'";
dbcon($query);// <-- database connection function located on includes.php 
$row = mysql_fetch_array($result);
extract($row);
$ptype = posting_type($row['ptype']);
$psubtype = $row['postingsubtype'];
$breadcrumb = "<a href='classified.php'>Classified</a> > <a href='classified_type.php?type=$row[ptype]'>$ptype</a> > $psubtype ";

   $query2 = "SELECT * FROM Posting WHERE subtype='$getsubtype'";
}

dbcon2($query2); // <-- database connection function located on includes.php 


?>
<?php include("header.php"); ?>
<h3> <?php echo $breadcrumb; ?></h3>
<div>
<?php 
if (mysql_num_rows($result2) > 0)
{
while ($row2 = mysql_fetch_array($result2))// <-- result of function dbcon
{
extract($row2); ?>
<?php echo $postdate; ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?><?php if ($price != ""){ ?> - $ <?php echo $price; } ?></a> 
<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype); echo $posting_subtype ?></i> <?php } ?><br>
<?php 
} 
}
else
{
echo "Nothing Here Yet.";
}
?>

</div>
</body>
</html>

bluebyyou · February 18, 2007

I think i figured it out. Here is the code. Are there other security holes I need to account for still, if I did in fact solve the problem?

The site again: www.rockypages.com/classified.php

<?php 
//classified_type.php
include("includes.php"); ?>
<?php
if (isset($_GET['type']))
{
$gettype = mysql_escape_string($_GET['type']);
$query = "SELECT * FROM PostingType WHERE `key` = '$gettype'";
dbcon($query);
if (mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_array($result);
	extract($row);
	$breadcrumb = "<a href='classified.php'>Classified</a> > $row[name]";
	$query2 = "SELECT * FROM Posting WHERE type = '$gettype'"; 
	dbcon2($query2);
}
else
{
	echo "You tried some funny business!";
	exit();
}
}
if (isset($_GET['subtype']))
{
   $getsubtype = mysql_escape_string($_GET['subtype']);
   	$query = "SELECT * FROM PostingSubType WHERE psubtype = '$getsubtype'";
dbcon($query);
if (mysql_num_rows($result) > 0)
{
	$row = mysql_fetch_array($result);
	extract($row);
	$ptype = posting_type($row['ptype']);
	$psubtype = $row['postingsubtype'];
	$breadcrumb = "<a href='classified.php'>Classified</a> > <a href='classified_type.php?type=$row[ptype]'>$ptype</a> > $psubtype ";
	$query2 = "SELECT * FROM Posting WHERE subtype='$getsubtype'";
	dbcon2($query2);
}
	else
{
	echo "You tried some funny business!";
	exit();
}
}
?>
<?php include("header.php"); ?>
<h3> <?php echo $breadcrumb; ?></h3>
<div>
<?php 
if (mysql_num_rows($result2) > 0)
{
while ($row2 = mysql_fetch_array($result2))
{
extract($row2); ?>
<?php echo $postdate; ?> <a href="post.php?id=<?php echo $number; ?>"><?php echo $title; ?><?php if ($price != ""){ ?> - $ <?php echo $price; } ?></a> 
<?php if (isset($_GET['type'])){ ?><i> <?php posting_subtype($subtype); echo $posting_subtype ?></i> <?php } ?><br>
<?php 
} 
}
else
{
echo "Nothing Here Yet.";
}
?>
</div>
</body>
</html>

<?php include("includes.php");
//post.php
if (isset($_GET['id']))
{
$getid = mysql_escape_string($_GET['id']);
$query = "Select * FROM Posting WHERE number='$getid'";
dbcon($query);
if (mysql_num_rows($result) > 0)
{
	?>
	<?php include("header.php"); ?>
	<?php 
	$row = mysql_fetch_array($result);
	extract($row);
	?>
	<a href="classified.php">Classified</a> > <a href="classified_type?type=<?php echo $type; ?>"><?php posting_type($type); echo $posting_type; ?></a> > 
	<a href="classified_type?subtype=<?php echo $subtype; ?>"><?php posting_subtype($subtype); echo $posting_subtype; ?></a> 
	<h3><?php echo $title; ?><?php if($price > 0){ ?> - $<?php echo $price; ?><?php } ?></h3>
	<hr width="700" align="left" />
	reply to: <?php if ($reply == 0){ echo $email;} else {echo "This poster has chosen to keep thier contact information confidential";} ?><br /><br />
	<?php echo $description;
} 
else
{
	echo "You tried some funny business!";
	exit();
}
}?>
</body>
</html>

corbin · February 18, 2007

I don't feel like reading through a lot of code, and I tested out the site a little. When you go to add a classified and you select a category, it says "Category:" on the next page, but doesn't tell you what you selected.

I generally do something like the following to make sure variables are clean.

foreach($_POST as $k => $v) {
$_POST[$k] = addslashes($v); //or if a db conn is established i would use mysql_escape_string()
}
//repeat for get
//repeat for cookies

bluebyyou · February 18, 2007

that is odd, because it does show the category for me... I dont know what would be causing that for you.

bluebyyou · February 25, 2007

Search function added.. and I think it is better protected from the mysql injection. Please test and let me know how you think it is working so far.

Layout and design still in the works..so ignore that part for now.

http://www.rockypages.com/classified.php

midmented · April 4, 2007

I tested your site this morning. Everything seemed to work fine.

I've thought of writing something along the same lines but as like many of us, I don't have the time.

Good job. I'm a noobie to php so a lot of examples are very helpful to me. Please keep posting your updates!

agentsteal · April 4, 2007

SQL Injection:

http://www.rockypages.com/classified_type?type=1 AND 1=1

http://www.rockypages.com/classified_type?type=1 AND 1=2

calabiyau · April 5, 2007

Just a small usability point here...on the jobs section, you click on a link only to find out there is nothing posted in that category yet. That's minorly annoying to people. Might be better to do a number of rows query on the main page and display that information on the main page next to each item, so people can know before they click that there are no postings in that category yet.

Sign In

Classified site.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Important Information