...

View Full Version : Making a Linktracker



SDP2006
01-26-2004, 04:57 PM
What I am going to do is pass information through the HTTP Addess. I am using variables $des,$cat, and $link

$des = name of site
$link = address of site
$cat = equals category of site

I am wanting to see if field des has something that matches $des and if it does, add one to field hits

If field des has nothing like $des, then enter the info and set it to one, where if clicked again, it will add one.

Here is the code I have so far


<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db");

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$des = $sql['des'];

if($des){
$result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'");
}
elseif(!des){
$result = MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')");

}
?>

Thanks for any help....

coffeedemon
01-26-2004, 05:09 PM
<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db");

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_numrows($result) # returns number of results

# if resultsNum is greater or equal to 1
if($resultsNum >= 1){
$result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'")or die(mysql_error)); # if query fails return error
}
else
{
# if resultsNum is not greater then or equal to 1 then it must be 0
MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')")or die(mysql_error)); # if query fails return error

}
?>

fimi
01-26-2004, 05:13 PM
i don't have your answer but i would make another field called link_id and assign an id to the link so that
when you do:
$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

it would be:
$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE link_id='$l_id'");


matching numbers are the easiest and fastest, and using your method if and only if one char is diff it will be added into mysql...

its just a sugesstion,
fimi

SDP2006
01-26-2004, 06:02 PM
Here is my code, thanks to coffeedemon


<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
$result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());

}
?>


Now, it is inserting the proper data, but its not adding one to hits. Its creating the row over and over again not adding 1 to 'hits' if it is more than one. If you dont understand, see this - http://www.net-riches.com/pics/db.JPG -

Thanks for all help...

raf
01-26-2004, 08:46 PM
You could just as well replace all this


$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
$result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());

}

with


$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error());

Replace will check if the record exists and update it or insert a new record. 'des' should then have a unique index or should be the primary key
http://www.mysql.com/doc/en/REPLACE.html

fimi
01-26-2004, 08:50 PM
Originally posted by raf
You could just as well replace all this


$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
$result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());

}

with


$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error());

Replace will check if the record exists and update it or insert a new record. 'des' should then have a unique index or should be the primary key
http://www.mysql.com/doc/en/REPLACE.html

LOL, i really like this.... i didn't know about REPLACE. 6 lines turned into 1 :thumbsup:
thx for the tip

SDP2006
01-26-2004, 08:50 PM
In my table 'id' is my primary key.

I'm kinda confused raf, can you show me the complete code and not just what to replace?

Thanks

raf
01-26-2004, 09:08 PM
$cat = $_GET['cat'];
$des = $_GET['des']; // no need for this intermediate variable
$link = $_GET['link'];

/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);
/*to here */
$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error());

You see, it could actualy be just 2 lines of code (one for the include and one for the query.)

Now, replace need to know which record to look for to decide if it exists.
If all values from 'des' are unique, then you can create a unique index on it. Or else you need to use the id (so the querystring should contain the id value then)
Since each link can only point to one site, there shoukd be at least one 'no duplicate values' column inside your table. No ?

SDP2006
01-26-2004, 09:31 PM
Now, I'm really confuses.

The replace function throws me for a loop.

All I want to do is -> Check for matching value ($des), if matching value exists add one to 'hits' if matching value does not exist, then create the field.

Errr, sorry if I am being a pain, buts thats kinda confusing.

Thanks for the help...

bcarl314
01-26-2004, 09:37 PM
Not that I want to alarm anyone, but shouldn't we NOT give out our username and password???

SDP2006
01-26-2004, 09:48 PM
Yes, but my host is pretty secure. You have to login into the control panel, then login to database management, and then login to phpmyadmin, (all three have different usernames and passwords)

raf
01-26-2004, 09:55 PM
Originally posted by SDP2006
Now, I'm really confuses.

The replace function throws me for a loop.

:confused: How could that result in a loop? It will just execute the query once. If you get a loop, then it must be some othr code around the one you posted

Originally posted by SDP2006
All I want to do is -> Check for matching value ($des), if matching value exists add one to 'hits' if matching value does not exist, then create the field.
Check the link in my previous post and you'll see it does exactly that.

And bcarl314 is right about the user and pwd: All we need to do is take an account on the same host and we can manipulate your db. I don't see why we would need phpmyadmin.

fimi
01-26-2004, 09:57 PM
Originally posted by bcarl314
Not that I want to alarm anyone, but shouldn't we NOT give out our username and password???

test it in your server, see if you can use it ;) :
/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

lol, that point is that it dosent matter, they do it on their own risk

SDP2006
01-26-2004, 09:59 PM
So....
$cat = $_GET['cat'];
$des = $_GET['des']; // no need for this intermediate variable
$link = $_GET['link'];

/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);
/*to here */
$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error());
will do all i need it to do?

fimi
01-26-2004, 10:06 PM
AND...


Originally posted by raf
Now, replace need to know which record to look for to decide if it exists.
If all values from 'des' are unique, then you can create a unique index on it. Or else you need to use the id (so the querystring should contain the id value then)
Since each link can only point to one site, there shoukd be at least one 'no duplicate values' column inside your table. No ?

SDP2006
01-26-2004, 11:24 PM
English and some code please?

I'm very sorry, we have around 1 1/2 inches of ice on the ground and I havent left my room in 30 hours, my brain is shot.

Thanks....

raf
01-27-2004, 12:09 AM
I posted all the code you should need for your update/insert operation. You only need to make sure 'des' has its index
--> ALTER TABLE linktracker ADD UNIQUE desind (des)
+ that your other code on that page is correct

Maybe you should take another swing at it when you have a clearer mind :)

SDP2006
01-27-2004, 12:39 AM
Would that mean I need to remove my PRIMARY KEY from field 'id' ?

raf
01-27-2004, 01:06 AM
Originally posted by SDP2006
Would that mean I need to remove my PRIMARY KEY from field 'id' ?
No.

You can create a seperate unique index on each column. But you can only have one PK and only one auto-number column inside a table.
All mySQL needs to execute 'replace' is a column to uniquely identify the record it should look for. And there are then just 2 options: either include the auto-number value inside the where-clause, or include a column with a 'unique index' inthere.
Since both have unique values for each record, mySQL then knows which record it should look for.
But you could have a dozen other columns in that table, that all also have a unique index ...

SDP2006
01-27-2004, 01:59 AM
Okay that is done.

Now my structure looks like this


CREATE TABLE linktracker (
id mediumint(9) NOT NULL auto_increment,
cat varchar(255) NOT NULL default '0',
href varchar(255) NOT NULL default '0',
des varchar(255) NOT NULL default '0',
hits int(9) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY desind (des)
) TYPE=MyISAM;


Now, do I just need to run the script above?

raf
01-27-2004, 02:11 AM
Yes indeed. Try it out.

SDP2006
01-27-2004, 03:35 AM
Okay, using the code above, and this link

http://www.net-riches.com/linkt.php?cat=Computers&des=Kim%20Komando&link=http://www.komando.com/

cat=Computers -> category from which link is coming from
des=Site name -> name of site
link=url to site

I get this error

You have an error in your SQL syntax near 'WHERE `des`='Kim Komando'' at line 1

Don't worry about the contents of the variables in the string, they have to do with my site.

Thanks for the continued assistance.

- Stevie

fimi
01-27-2004, 05:15 AM
Try this:

$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='$des'") or die(mysql_error());

if you still have problems then print the query to see if it generates as you want it. Also use phpmyadmin to run the query until you get it right and then put the query in the script(that's what i do).

SDP2006
01-27-2004, 02:14 PM
Thanks, but I get the same error in the file and in phpmyadmin.

- Stevie

raf
01-27-2004, 02:45 PM
try


$result = mysql_query("REPLACE linktracker SET hits=hits+1 WHERE des='" . $des . "'") or die(mysql_error());

There is no need for bakticks.

Or maybe print the executed statement

If i can find the time, i'll try it out this evening, but it should work just fine like that (unleas the columns don't exist)

SDP2006
01-27-2004, 05:05 PM
Still produces the same error:confused:

raf
01-27-2004, 08:44 PM
My bad. Sorry :o

It seems that you can't use replace for this:
"In other words, you can't access the values of the old row from a REPLACE statement. In some old MySQL versions it appeared that you could do this, but that was a bug that has been corrected."
from http://www.mysql.com/doc/en/REPLACE.html

The older row with the same PK value or unique index value is first deleted and then the record is inserted. So 'hits' will be 0 and you'll always get 1 as hit-value after each hit.
+ the PK value will be incremented with one which makes it useless because you then no longer can use it to join other tables.

The syntax i wrote was also wrong because replace doesn't have a where clause. It just checks for the unique value. So your query should have been
$result = mysql_query("REPLACE linktracker SET hits=hits+1, des='" . $des . "'")
to be syntactically correct, but it wount give you what you need.

So it's back t the beginning. Try


<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

/*include !! */
$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db");
/* till her*/
$result = MYSQL_QUERY("SELECT id FROM linktracker WHERE des='$des'") or die ('Queryproblem1 :' . mysql_error());
if (!$result){
echo ('DB-problem');
} else {
if (mysql_num_rows($result) == 1){
$sql = mysql_fetch_assoc($result);
$theid = $sql['id'];
$result = MYSQL_QUERY("UPDATE linktracker SET hits=hits+1 WHERE id=" . $theid) or die ('Queryproblem1 :' . mysql_error());
} elseif (mysql_num_rows($result) == 0){
$result = MYSQL_QUERY("INSERT INTO `linktracker`(`des`,`cat`,`href`,`hits`) VALUES('$des', '$cat', '$link', 1)") or die ('Queryproblem1 :' . mysql_error());
}
}
?>


So semect the id first + the insertquery need to be a bit different then what you wrote

SDP2006
01-27-2004, 09:48 PM
Raf, I have three words for you

I LOVE YOU

:D

Thanks so much

raf
01-27-2004, 10:12 PM
No problem.

After all, it was my fault you wasted some time looking in the wrong direction ... I took until i tried it myself that i saw the obvious problems.

Anyway, i suppose it's solved now, so happy coding !

SDP2006
01-27-2004, 10:28 PM
Yes, thank you again, but a friend of mine has discovered a vulnerability

If you will see, on http://www.net-riches.com/newnr/index.php?temp=info&cat=computers, the links are linked to track them. So

http://www.net-riches.com/newnr/linkt.php?cat=computers&des=Some Site&link=http://www.somesite.com/
In the code, it adds the category, if not there, or adds on to hits and redirects to $link.

The problem is, anyone can manipulate the string and add stuff to the db.
so


http://www.net-riches.com/newnr/linkt.php?cat=Don't Want This&des=Don't Want this&link=Don't Want This
Is there a way of authentication where I people cannot do stuff like that?

Thanks

SDP2006
01-28-2004, 04:15 AM
raf, where art thou?:)

raf
01-28-2004, 09:17 AM
Well, that is just allways so for querystringvalues. Manipulating the querystrings is about the oldest trick in the book. Anyone can just replace these values with something else.

You can not avoid it, unless you use a mod_rewrite (there have
been two recent threads about it + it's kinda getting a lot of attention lately).
Or you can encode and decode them and then check for specific data somewhere inside the decoded string.

But i woul just use my db to store these values in. I only use the querystring when i can check if the posted value is valid + if the client has permission for this item. It's a bit of extra work (on your first pages), but it's the only secure way in my opinion.

So instead of having the actual category descriptions or url's there, i would have ID's there from a categorytable and a site-table.
And then you can check if the values from the querystring are all numeric and don't have more then 4 positions or so. Like


foreach($_GET as $varName => $value){
if (!eregi('[0-9]{1,4}',_$value)){
echo 'Invalid querystringvalue' ;
die();
}
}

So they can still manipulate it, but they can not compromise your db. It will even speed up selects because you now have numeric values you can index and which is filtered a lot quicker on, then on string values.
You just need to make sure that you have good 'error'-trapping when you do your select for the countercode (or any other code that uses the querystring-values).
So if you select on the linktracker table and there is no record returned, then you need to run a select on the category or site tables to check if there is a category/site with that id. If no record is found in these tables, then either your code to build the links is wrong (so you should spot this when debugging your app) or the user changed the querystring, and then you print a message that he is a bad bod/girl.
I always keep a sessiontable inside my apps, and i then register an illegal action for that user. 3 illegal actions and they are banned.

But the querystring wil always be the least secure and i try to only use them for navigatiopurpuses (where i don't mind if the client changes them and get a 'Page can not be displayed etc') OR if i can check on them. For instance, to show a list of the items they have access to, and then in the page they request, i first use the regex to check the ID format and then i run a select to see if that client had access to that item, and only then, i select the actual data for that item and build the page. If the client then changes the querystring to a value of another item he has access to, well, then that is just fine by me.

SDP2006
01-28-2004, 02:59 PM
See, my string is going to have all alphabetical characters.
?cat == the category of this site that will be entered into the db
?des == the name of the site
?link == the http:// to the site

For ?link I could do a preg_match to find the http://, and if It doesnt exist, display error and not enter into db.

For ?des & ?cat, I'm not really sure what I could do to validate them.

I was thinking HTTP_REFERRER. Like, if($_HTTP_REFFER != "http://www.net-riches.com/index.php"){

Anymore ideas?

Thanks

raf
01-28-2004, 08:11 PM
Anymore ideas?
No. I already told you what i would do, which is the only way to be sure that the users hasn't manipulated it into an invalid value.

SDP2006
01-28-2004, 10:11 PM
I fixed it.

The only way a link will be tracked is if it is linked on one of my pages. If they type it in, there will be no HTTP_REFERER so thereforer
$ref = $_SERVER['HTTP_REFERER'];
if(empty($ref)){
echo "Invalid";
else it tracks the link. Crude, but it works.

Thanks.

raf
01-29-2004, 12:43 AM
Nope. Wount work.

If i put a link in one of my own pages, to your file with whatever querystring value in it, then your check
if(empty($ref)){
will retrurn True.
I just entres a new description with this link:
http://www.net-riches.com/newnr/linkt.php?cat=expert&des=RemoveMeStevie&link=http://www.sinceyouask.com/
Your check only checks if the client requests that page by clicking a link, but doesn't tell you absolutely nothing about the querystringcontent and certainly not that it is valid.

A first optimalisation would be to check what the referer-value was, and that should be one of your pages --> so that would stop the link above.
But referers can easely be spoofed so that isn't bulletproof either.
referers are not considerd to be safe to check a requests origin.
And you can't do anything against it. Even if you would store the last page they visited inside a session-variavble or database and check the referervalue against it, then your check would still be passed.

Trust me. The only bulletproof ways are mod_rewrite OR using ID's that refer to the actual categorys, descriptions or sites + run the check i posted + select the records that correspond with tese ID's and check if they exist and if the client is permitted to use them

SDP2006
01-29-2004, 02:54 AM
:( I knew it sounded too easy.......

It really doesn't matter to me if a one person adds something, the average web user won't understand what that link means, trust me I live with them, lol :)

Okay, thanks.

SDP2006
01-29-2004, 03:53 AM
How about this?

I put all of my categories in a table and if in the querystring ?cat=$category there is not a match for one of my categories, it does not go through? No?

raf
01-29-2004, 08:53 AM
Well, that would be part of the sollution as i described it a few posts back. But you lght as well take an extra step and onlu-y include the categorys ID instead of the label, and do the same with the tilte and url...

SDP2006
01-29-2004, 10:46 PM
One more question ...
I am wanting to log the ips of the users so,
<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];
$ip = $_SERVER['REMOTE_ADDR'];

$conn = mysql_connect("localhost","sdpeele","8590481");
$db = mysql_select_db("db",$conn);
$reesult = MYSQL_QUERY("SELECT cat FROM categories WHERE cat='$cat'") or die(mysql_error());

if(mysql_num_rows($reesult) != 1){
echo "Invalid Link. Please <a href='contact.html'>Contact</a> the site administrator.";
exit;
}

$result = MYSQL_QUERY("SELECT id FROM linktracker WHERE des='$des'") or die ('Queryproblem1 :' . mysql_error());

if (!$result){
echo ('DB-problem');
}

else {
if (mysql_num_rows($result) == 1){
$sql = mysql_fetch_assoc($result);
$theid = $sql['id'];
$result = MYSQL_QUERY("UPDATE linktracker SET hits=hits+1 WHERE id=" . $theid) or die ('Queryproblem1 :' . mysql_error());
$rresult = MYSQL_QUERY("UPDATE linktracker SET ip=ip+'$ip' WHERE id=" . $theid) or die('Problem :' . mysql_error());
} elseif (mysql_num_rows($result) == 0){
$result = MYSQL_QUERY("INSERT INTO `linktracker`(`des`,`cat`,`href`,`hits`,`ip`) VALUES('$des', '$cat', '$link', 1,'$ip')") or die ('Queryproblem1 :' . mysql_error());
}
}
Header("Location: $link");
?>


I don't think I am doing it right and I want it to add the IP for every click of every user, even duplicates. Thanks

raf
01-30-2004, 09:03 AM
I don't think I am doing it right and I want it to add the IP for every click of every user, even duplicates. Thanks
If you want to record each IP, and if you later want to use it, then you need to insert a new record for each click.


Note: you can update more then 1 field in one updatestatement, so


$result = MYSQL_QUERY("UPDATE linktracker SET hits=hits+1 WHERE id=" . $theid) or die ('Queryproblem1 :' . mysql_error());
$rresult = MYSQL_QUERY("UPDATE linktracker SET ip=ip+'$ip' WHERE id=" . $theid) or die('Problem :' . mysql_error());

should be

$result = MYSQL_QUERY("UPDATE linktracker SET hits=hits+1, ip=CONCAT(ip,', '," . $ip . ") WHERE id=" . $theid) or die ('Queryproblem1 :' . mysql_error());

But this way, you will get a giant, commaseperated, IP string where it's almost impossible to run a performant search on. After some fair amoun tof hits, the update will also slowdown ...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum