[SOLVED] Passing a _GET variable to Mysql query

[stackumhi]

I have a database with some business categories in it. I am trying to pass a variable from one page to another then run a mysql_query on that value. When the query runs I get a "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

 

I assume this is because I have some spaces between some of the values of the category field.

 

Example...Food & Dining, Home & Garden, etc...

 

I used urlencode($category) to remove the spaces on the first page but I am not sure how / or what to do with the mysql query. I have also tried single quotes versus double qoutes arond the value.

 

 

Thanks for any suggestions.

 

 

 

First page code (A list of all categories):

<?php

   $result = mysql_query("SELECT category, cat_id FROM CATEGORIES order by category");
   echo "<table id='cat-table' border=\"0\" cellpadding=\"3\" cellspacing=\"5\"><tr>";
   $i = 0;
   while($array = mysql_fetch_array($result)){//loop through
      
$i++;
$category = $array["category"];

      echo "<td width=\"303\">". "<img src='images/category-bullet.png' width=\"5\" height=\"15\" />".'  '. "<a href='view-category.php?category="  .urlencode($category). "'/>"   .$array['category']. "</td>";   


  if($i == 3){echo "</tr><tr>";$i=0;}
   } 

$num_rows = mysql_num_rows($result);
$find_colspan = 3 - ($num_rows%3);
for($i=0;$i<$find_colspan;$i++){
echo "</td>";
}
      echo "</tr></table>";
  echo "<br/>";
?>

 

 

Second page code(view-category.php):

 

 

   <?php
   $category = $_GET["category"];
   //$category = urlencode($category);
   
   //echo $category;
   
   $result2 = mysql_query("SELECT category FROM TEST WHERE category=$category");
   $row2 = mysql_fetch_array ($result2);
   echo "<strong>Results for the $row2[category] category are listed below.</strong>";
   echo "<br/><br/>";
   
   
   $result = mysql_query("SELECT * FROM TEST WHERE category=$category");
while($row = mysql_fetch_array($result))  {

	echo $row['business_name']. "<br/>";
	echo $row['address']. "<br/>";
	echo $row['city']. ', '. $row['state']. ' '.$row['zip'];		
	echo "<br/><br/>";
}
    
    ?>

 

 

 

[Zyx]

The real problem is that you are not using the MySQL error reporting functions, so you do not get any error messages from the database. mysql_query() usually returns false instead of the result resource, if there is a syntax error in the query, and if you pass it to mysql_fetch_array(), it will generate a PHP warning. This should tell you, what is wrong:

 

$result = mysql_query(' query here ');
if(mysql_errno())
{
  die('MySQL error: '.mysql_error());
}

 

Consider writing a new function - wrapper for mysql_query() that automatically checks for the errors and displays the appropriate message. Actually, in more professional scripts, the programmers never use bare database communication functions (unless we are talking about much smarter PDO ).

 

Another hint: NEVER put any data from the script input (GET, POST, COOKIE and anything else) without validation, like this:

 

   $category = $_GET["category"];  
   $result2 = mysql_query("SELECT category FROM TEST WHERE category=$category");

 

It's like an invitation to perform an SQL injection attack on this script. For example, if the category is a database identifier, it should be a number, so you could try this one:

 

if(!ctype_digit($_GET['category']))
{
   die('Data validation failed: not a number');
}
// Now you are sure that $_GET['category'] does not contain any dangerous SQL code
$result = mysql_query('SELECT ... WHERE category = '\'.$_GET['category'].'\'');

 

For strings, you must use mysql_real_escape_string() (and turn off magic quotes earlier).

[stackumhi]

Thanks Zyx.

 

I will take ANY security advice I can get.