retrieving informaiton with random id

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
maverickman55
Forum Newbie
Posts: 15
Joined: Sat Dec 14, 2002 10:22 pm

retrieving informaiton with random id

Post by maverickman55 »

hey guys, i've seen topics posted on how to retrieve table data when there is a ID, normally auto_increment. My question is how to select data when there is a randomly generated id? Background of my project to help understand: i am making an ecard system where a user enters information into a form, that information is stored into a table, with a random id being created for each row. An email is then sent to the recipiant with a link that include that ID at the end i.e. viewcard.php?ID=N4OC4Fn4WnH83ng2 When the user clicks on that link i need the page to "grab" that ID and retrieve the information from that row and display it. Does anyone know how to do that? Thanks in advance for the help. Oh yea, here is the code ihave so far for that page, but no information is displayed

Code: Select all

<?
include ("dbconnect.php");
mysql_select_db("maverick") or die(mysql_error());
$id = $_GET&#1111;'id'];
$query = "SELECT * FROM information WHERE id = '$id' ";
$result = mysql_query($query) or die (mysql_error());  
$row = mysql_fetch_array($result);
echo "To: $row&#1111;recipiant]<br />";
echo "From: $row&#1111;sender]<br />";
echo "Email: $row&#1111;email]<br />";
?>
User avatar
TheTestUser
Forum Newbie
Posts: 9
Joined: Sat Dec 14, 2002 9:25 pm

Post by TheTestUser »

I'm not sure how you have your tables setup, but I would make sure there is a primary key in that table for the sake of distinction.

If not for ensuring a distinct and unique row, then do it based on speed. If you have hundreds of thousands of rows that contain those IDs, then searching by a random ID is not a good way to search since it doesn't provide a very good index. I could be wrong about that but it passes my own internal sniff test.

So instead of making the user only search by a random series of numbers and letters, I would also have them provide an actual ID to speed up the search.

Code: Select all

CREATE TABLE ECards (
  CardID int(10) unsigned NOT NULL auto_increment,
  CardKey varchar(20) NOT NULL default '',
  Stuff varchar(30) NOT NULL default '',
  PRIMARY KEY  (CardID),
  KEY CardKey (CardKey)
);
I am assuming that the ecard is being sent by email, and the recipient must click a link to get that card. Well, it won't matter to that user whether they are sending one variable or two. ie. viewcard.php?CardID=15&CardKey=N4OC4Fn4WnH83ng2

Remember to follow the basics of taking user provided variables by cleaning out those variables of all bad content. For integers such as CardID it's easy to do by using:

Code: Select all

$CardID = (isset($CardID)) ? intval($CardID) : 0;
What that basically does is check to see if the variable $CardID is set, if so, then set it to the integer value of that variable. If it is not set, then set it to 0.
Then you do a check later to see if that CardID is set to 0. If it is, then produce an error message saying that the user had provided invalid information or did not set $CardID.

Since you had chosen to use a random alphanumeric for verification, then it sort of complicates things. I would make a simple regular expression to filter out all the characters from that variable that I would not expect.

Say you would only allow numbers and letter in that variable and you would filter out anything that is NOT a number and letter. That would make the regular expression:

Code: Select all

$CardKey = (isset($CardKey)) ? preg_replace('/[^a-z0-9]/i','',trim($CardKey)) : '';
You would then, again, put an if statement to test out if that variable is of the correct length (if you are providing fixed length keys) or format and if not produce an error message.
Again, I must remind you that nothing is to be taken into your script without making damn sure that it's in the format you expect and is 'safe'. Failing to do so would mean leaving your database/webserver to a host of attacks
PHP is easy to code, but it takes a while to understand that security lies squarely on the shoulders of the programmer in charge of the project and that the scripts you write are as dangerous and as safe as you make them.

OK, now that you've cleaned out your variables, you now need to use them in your query.

Code: Select all

$query = "SELECT Stuff FROM ECards";
$query.= " WHERE CardID='".$CardID."' AND CardKey='".$CardKey."'";
$query.= "LIMIT 1";
This is just a quick example of what you need to keep in mind. For more in-depth support, you need to pay me. :mrgreen:
maverickman55
Forum Newbie
Posts: 15
Joined: Sat Dec 14, 2002 10:22 pm

Post by maverickman55 »

ok thanks, i figured out my problem. i feel so stupid, when i send the email, it says ...viewcard.php?ID NOT id
it was just a stupid case error that i ddin't even look for, i was just looking to make sure the variable names were right
my bad
Post Reply