I have several URL's that display random data from a database. I would like to know a better (more secure) way to display the data. Right now it uses the real table and column names and I would like to disguise them somehow. Here is the syntax of the URL:
<a href="fetch_page.php?cat=table&subcat=column">Link</a>

The difficulty is that the select statement uses order by rand, so I am not sure of a way to pull the record by the id. Here is the PHP code:

$category = $_GET['cat'];
$subcategory = $_GET['subcat'];

if($category=="")
{
header("Location: index.php");
} else {

if($subcategory=="")
	$result=mysql_query("SELECT * FROM ".$category." ORDER BY RAND() LIMIT 1");
	else $result=mysql_query("SELECT * FROM ".$category." WHERE Category LIKE '".$subcategory."' ORDER BY RAND() LIMIT 1");

$row = mysql_fetch_assoc($result);
$data=$row['column'];
header("Location: ".$data);
echo $data;

if (!mysql_num_rows($result));
  echo "No records found";
}

Can someone please steer me in the right direction?

I think you're going to need to expand on what you mean by "more secure". You mean you don't want to just pass the variables as is? Only thing I'd suggest is to generate a unique key string (md5()) and caching them and their *actual* value so they can be retrieved and the SQL can continue as-is. Without knowing more about your script and what you're doing with it, that's about all I can suggest at this point.

If you are concerned about having the details on the command line, then you may want to put them into session variables (assuming that you are posting a program within the same domain).

Is it not a security issue to allow users to know the database details? I am just worried about injection or some other attack happening.

dont use category names in the posted urls http://bla.com/?1=something&2=somethingelse

$category = $_GET['cat'];
$subcategory = $_GET['subcat'];
if iset($1) { $category=$1; }
if($category=="") { header("Location: index.php"); } 
elseif ( iset($2)) {  $subcategory=$2; )
if($subcategory=="")
$result=mysql_query("SELECT * FROM ".$category." ORDER BY RAND() LIMIT 1");
else $result=mysql_query("SELECT * FROM ".$category." WHERE Category LIKE '".$subcategory."' ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_assoc($result);
$data=$row['column'];
header("Location: ".$data);
echo $data;
if (!mysql_num_rows($result));
  echo "No records found";
}

ignore syntax errors, just a thought process
Bob

You could simply set up a couple of associative (hash) arrays and use them to look up "coded" values:

$categories = array(
	'a' => 'cat',
	'b' => 'dog',
	'c' => 'orange',
	'd' => 'apple'
);

$subcategories = array(
	'a' => 'carpet',
	'b' => 'wardrobe',
	'c' => 'door',
	'd' => 'tv',
	'e' => 'radio'
);

if( isset($_GET['cat']) && isset($categories[$_GET['cat']]) )
{
	$category = isset($categories[$_GET['cat']];
}
else
{
	 $category = "";
}
if( isset($_GET['subcat']) && isset($subcategories[$_GET['subcat']]) )
{
	$subcategory = $_GET['subcat'];
}
else
{
	$subcategory = "";
}

With a small amount of imagination (see array_flip in the php manual), you could stick these lookups in an include file and use them in any number of pages to both encode (when building urls) as well as interpreting those urls (when they come back as requests).

Airshow

I like your idea Air. But how will this work since it is a random query? The category is the table, and the subcategory is a column in the specified table. So, this basically puts the data in an array then the query runs?

Tekkno,

First, please allow me to correct a couple of lines I posted above. I must have been more tired at the time than I realised.

if( isset($_GET['cat']) && isset($categories[$_GET['cat']]) )
{
	$category = $categories[$_GET['cat']];//corrected
}
else
{
	 $category = "";
}
if( isset($_GET['subcat']) && isset($subcategories[$_GET['subcat']]) )
{
	$subcategory = $subcategories[$_GET['subcat']];//corrected
}
else
{
	$subcategory = "";
}

To make the lookup hashes work, you will need to make sure that the $categories provides aliases (array keys) for all possible table names, and $subcategory provides aliases for all possible column names across all tables. Of course, there's an implicit assumption in this technique that all table and column names are known - which is generally the case but not always.

I should have said, my code only goes as far as creating $category and $subcategory so it does nothing more than replace the first two lines of the code in your original post.

As such, all it does is immunise the db query from code injection because any attempt to do so will result in $category="" and/or $subcategory="" .

You still need the rest of your code from if($category==""){ onwards to perform the query.

Airshow

No problem. I wish I could use excuses like I'm tired. My only excuse, is that I am a newbie :D I'll give your suggestion a try and let you know how it works out. Thanks very much.

I just have a few questions. Once the data is in the array, will PHP be able to distinguish which rows belong to which tables? And one more thing, some of the tables have the same subcategory, how will that work? Can I just add that particular parameter once or will I need to change them to unique names?

Tekkno,

From your question, I'm not completely sure I've explained it adequately - it's your phrase "Once the data is in the array", though maybe it's just me reading it wrongly.

The arrays $categories and $subcategories will be fixed - hard coded. At run time, there's no further data to put in them. They exist for one pupose only - to act as "lookup tables" for the coded 'cat' and 'subcat' that will be passed to the script in the request's querystring, and will appear in the associative array $_GET .

Here's a worked example using my silly cat/dog/carpet etc.:
Request : http://www.yourdomain.com/yourpage.php?cat=c&subcat=b.
$_GET : 'c'
$_GET : 'b'
$category = $categories[$_GET] : 'orange'
$subcategory = $subcategories[$_GET] : 'wardrobe'

So the parameters cat=c and subcat=b are translated into $category = 'orange' (actually the name of one of your tables) and $subcategory = 'wardrobe' (actually the name of one of your table columns).

The only place that you need to worry about which columns belong to which tables is where you compose urls with ?cat=..&subcat=.. querystrings. That is outside the scope of my code and beyond my understanding of your project.

The lookup process that is described and coded above is a translator not a validator. It doesn't have the means to verify whether any given cat|subcat pair is valid. It takes it on trust that the requested querystring was properly formed (when the requesting page was composed). If an invalid pair appears in the quesrystring, then an inappropriate SQL query will be made, unless validation code is added to the process.

I hope that helps

Airshow

Well it works great Air. Thanks so much. And with the data in an array, there is no fear of injection because the data is pulled from the array not directly from the database? That is if I understand correctly.

Very nearly Tekkno.

Data comes from the database. There is no fear of injection because the parameters that determine the SQL query pass through a lookup process that will only return good parametes or nulls.

Enjoy.

Airshow

I see, well thanks for your expertise. Take care.

use the variables to be the something in the row.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.