Search Help

xamm · October 24, 2012

Forgive me if this is a simple issue but this is my first php + mysql project as I normally use html and .net exclusively. I have created a mysql database and a password protected page that I can login to add members to the database. That works fine. I have laso managed to script the below search form to connect to the database in order to populate the form with the only search criteria I will be allowing "category". However when I execute the search I get no results at all. I have poured over the script and changed it 1,000 times based on what I have seen in other threads.

Here is the script:

<!DOCTYPE  HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
   <meta  http-equiv="Content-Type" content="text/html;  charset=iso-8859-1">
   <title>Search  Contacts</title>
 </head>
 <body>
   <h3>Member Search</h3>
   <p>Choose a Category</p>
   <form  method="post" action="search.php?go"  id="searchform">
  <?php
  $db=mysql_connect ("mysqlserver",  "myusername", "mypassword") or die ('I cannot connect  to the database because: ' . mysql_error());
 $mydb=mysql_select_db("mydb");
$category_sql="SELECT distinct category FROM Members";
$category_Options="";
$category_result=mysql_query($category_sql) or die ('Error: '.mysql_error ());
while ($row=mysql_fetch_array($category_result)) {
   $category=$row["category"];
   $category_Options.="<OPTION VALUE=\"$category\">".$category.'</option>';
}
?>
  <p>
	  <SELECT NAME="category"><OPTION VALUE=0>Choose<?=$category_Options?></SELECT>
  </p>
<input id="submit" type="submit" value="submit" />
   </form>

<?php
 if(isset($_POST['submit'])){
 if(isset($_GET['go'])){
 $category1=$_POST['category'];
 $sql="SELECT category, company, address, city, state, zip, phone, url
 FROM Members
 WHERE category = '. $category1 .' ";
 $result=mysql_query($sql);
 while($row=mysql_fetch_array($result)){
    $category2=$row['category'];
    $company=$row['company'];
    $address=$row['address'];
    $city=$row['city'];
    $state=$row['state'];
    $zip=$row['zip'];
    $phone=$row['phone'];
    $url=$row['url'];
 echo "<ul>\n";
 echo "<li>".$category2 . "</li>\n";
 echo "<li>".$company . "</li>\n";
 echo "<li>".$address . "</li>\n";
 echo "<li>".$city . " "," ".$state. " ".$zip . "</li>\n";
 echo "<li>".$phone . "</li>\n";
 echo "</ul>";
}

 }
 else{
 echo  "<p>Please select a Category</p>";
 }
 }
mysql_close($db)
?>
 </body>
</html>

Any help would be greatly appreciated.

akphidelt2007 · October 24, 2012

Just a debugging tip. Whenever you have problems with queries, echo out the query to see how it looks. If you echo out your query I'm willing to bet you would quickly figure out what you are doing wrong.

But just cause I'm nice... you are not breaking your query. Your search looks like '.category.'. It should be '$category1' without the periods or if you want the periods '".$category1."'

Edited October 24, 2012 by akphidelt2007

xamm · October 24, 2012

Thanks for the response. I tried making the change and still have the same issue. If it would generate an error rather than doing nothing I'm sure I'd be fine but it seems to connect ok. Maybe I'm misunderstanding where the issue is as the code only has a different category variable in it 1000 times. You're saying to edit this line to say this:

$sql="SELECT category, company, address, city, state, zip, phone, url
 FROM Members
 WHERE category = '$category1' ";

As I am selecting the defined columns from my members table where category = "$category1" which was posted in the search.

Again I apologize for noobish understanding of this.

akphidelt2007 · October 24, 2012

Single quotes and periods within double quotes are read as a literal value. So for instance if $_POST['category'] = 'TestCategory' your query string from before would read

 
SELECT category, company, address, city, state, zip, phone, url   
FROM Members   
WHERE category = '. TestCategory .'"

If you use category = '$category1' and your still not coming up with any results, then make sure $_POST['category'] actually contains the proper value. And so people don't yell at me, you should clean your values before using them in a SQL statement. But aside from that, check $_POST['category'] and make sure you want to use = and not LIKE.

Christian F. · October 24, 2012

I notice that you don't have any validation or escaping in your script, which leaves you open for SQL injection attacks. If $_POST['category'] is an integer, you should be casting it to a proper int (with intval ()) when assigning it to $category1. By doing that you'll ensure that the value will always be an integer, which is not vulnerable for SQL injections.

You should also remove the single quotes from the SQL query, if the category is saved as an integer (which it should, btw. ).

xamm · October 25, 2012

I'm not sure how vulnerable the script is really since there is no option to search it with any information other than that which is already in the database. Injection attacks are usually form initiated attacks where users can input code instead of search information, correct?

I'll try those changes though. I've changed the code a number of times more with still no results showing. I know the database connection is working because the select field is populating correctly.

MDCode · October 25, 2012

Simple explanation of sql injection (unlike your script)

// not too good of a query but oh well
$sql = "SELECT * FROM `table` WHERE `thing` LIKE search";

if a user inserts: (not giving real examples so you dont go around hacking )

DELETE FROM `table`

INSERT INTO `table`

etc. it would affect you greatly (deleting other tables, inserting into tables, displaying data from tables, etc.)

One of the things most people don't realize. I can go on my site make a simple form like:

<form action="http://yoursite.com/blah">
<input type="text" name="what you're checking for">
</form>

Edited October 25, 2012 by ExtremeGaming

Christian F. · October 25, 2012

You don't even have to make a new page on your own server, Opera (and most other web developer tools in browsers) allow you to edit the HTML on a live site. Extremely easy to create a text input of the text box, and type in whatever you like. Or even just editing the values for the options themselves.

Sverre H. Huseby's book "Innocent Code" is something I can warmly recommend a read of, as it gives a very nice intro on how to secure your web applications. For all levels of experience, and all languages. It lists a number of rules to remember as well, to help you solidify the knowledge.

Psycho · October 25, 2012

Thanks for the response. I tried making the change and still have the same issue. If it would generate an error rather than doing nothing I'm sure I'd be fine but it seems to connect ok. Maybe I'm misunderstanding where the issue is as the code only has a different category variable in it 1000 times. You're saying to edit this line to say this:
$sql="SELECT category, company, address, city, state, zip, phone, url
FROM Members
WHERE category = '$category1' ";
As I am selecting the defined columns from my members table where category = "$category1" which was posted in the search.

Again I apologize for noobish understanding of this.

There doesn't seem to be anything obviously wrong with that query from what I can tell, but as previously stated a good debugging tip is to echo out the query when there are problems to ensure it contains what you *think* it should. Especially when there are variables - it could be that $_POST['category'] has a typo between the field name and $category1 isn't set like you think it is. But, I'll take the debugging of queries a step further.

1. Start by running the queries directly in the DB (in this case you can use PHPMyAdmin). If you can't get them to run there with expected results, don't even think about trying to put them into code.

2. When implementing the query in your code to be created dynamically, always echo it out first to verify the contents.

3. Once you do implement the script add some logic to handle errors. Ideally you want a way to handle the errors differently in a test environment vs. a production environment. In a test environment you want the full error, but in production you don't want to expose information that could give someone information about your structure. But, for now just use an or die() clause to display the error if the query fails.

  $result=mysql_query($sql) or die("Query: $sql<br>Error: " . mysql_error());

If there is an error that will tell you what it is. But, what IF there wasn't an error and there were just no results? The query could be successfully run but legitimately have no results. I think that is probably the case here since you aren't getting errors from the mysql_fetch_array() function. I always like to check for empty results as well.

if(!mysql_num_rows($result)
{
   echo "There were no results found."
   //echo "Query: $sql";  //Uncomment this line to validate the query
}
else
{
   while($row=mysql_fetch_array($result))
   {
    //Display the results
   }
}

PFMaBiSmAd · October 25, 2012

The overall problem preventing your code from working is your submit button doesn't have a name='submit' attribute, so your if(isset($_POST['submit'])){ logic is never true.

xamm · October 25, 2012

Wow! I thought it was a simple solution. name="submit" Thanks!

Now I just need to figure out the most efficient way to deal with the SQL injection attacks since it's more vulnerable than I thought.

PFMaBiSmAd · October 25, 2012

The short-answer to preventing sql injection is to escape string data, i.e.data that is put inside of single-quotes in the query statement, and cast numerical data as the appropriate numerical data type, i.e. use intval for integers, before putting it into your query statement.

You can also use prepared query statements (available using the mysqli extension or the PDO extension), where the actual query statement has place holders for the data values and any data you supply is treated ONLY as data and cannot alter the actual sql statement.

xamm · October 25, 2012

So in my case changing '$category' to category (which still works from the previous definition of it) should protect me sufficiently.

xamm · October 25, 2012

Spoke too soon category doesn't work. It pulls everything.

Sign In

Search Help

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

Join the conversation

Important Information