Referal checker

[gurpreet]

So I haven't coded in over a year and I've gone terrible at it. I'm trying to make a simple file that checks any referal multis, but I'm having a few problems.

1) I've forgotten how to get 2 columns for the referer and the refered person with ID's. I managed to get one but can't get the other, because my mind goes blank when it comes to queries. I think I've gone about the refered user the completely wrong way

Any help would be appreciated, I've checked on php.net and tizag but it isn't working for me. Here's what I got so far

 

<?php
include "globals.php";
if($ir['user_level'] != 2 && $ir['user_level'] != 3)
{
   die("You can't access this page");
}

$q=mysql_query("SELECT u.userid, u.username, u.laston, r.* FROM users u LEFT JOIN referals r ON r.refREFER=u.userid WHERE r.refREFERIP=r.refREFEDIP ORDER BY userid ASC",$c);

print "<center><br /><b><font color=white><h1>Referral Multis</h1></b><br />

<table width=75% border=1> <tr style='background:black'><th><font color=grey>Referer</font></th><th><font color=grey>Refered<th><font color=grey>IP</font></th> <th><font color=grey>Time</font></th></tr>";
while(($r=mysql_fetch_array($q) or die(mysql_error())))
{
$reftime = date('F j, Y g:i:s a', $r['refTIME']);
$refered = str_replace("{$r['refREFED']}", "{$r['username']}", "{$r['refREFED']}");
if($r['laston'] >= time()-60*60) { $on="<font color=green><b>Online</b></font>"; } else { $on="<font color=red><b>Offline</b></font>"; }
print "<tr> <td><a href='viewuser.php?u={$r['userid']}'><font color=brown><b>{$r['username']}</b></font></a> [{$r['userid']}]</td><td>$refered</td><td>{$r['refREFERIP']}</td> <td>$reftime</td></tr>";
}
print "</table>";

$h->endpage();

?>

Edited September 14, 2011 by gurpreet
Fixed 1 problem

[Spudinski]

Well, SQL is pretty unforgettable once you get it in your head.

IP checking wouldn't be as good, as it is the easiest method to fool.

I would suggest making a cookie in the user's browser, or use the new addition of either client-storage or client-database to keep record.

Most newbs won't know how to alter those, so for now it's better than simply checking their IP.

But, if you insist on IP checking, I'd suggest the introduction of a proxy blacklist. Also, CDIR might be helpful when detecting on the host.

Additionally, the HTTP_VIA or HTTP_HOST might also be worth checking.

A simple query to check problem one:

SELECT `col1`, `col2` FROM `referrals` WHERE `userid` = (SELECT `userid` FROM `users` WHERE `last_ip` = '__IP__')

[gurpreet]

Well, SQL is pretty unforgettable once you get it in your head.

IP checking wouldn't be as good, as it is the easiest method to fool.

I would suggest making a cookie in the user's browser, or use the new addition of either client-storage or client-database to keep record.

Most newbs won't know how to alter those, so for now it's better than simply checking their IP.

But, if you insist on IP checking, I'd suggest the introduction of a proxy blacklist. Also, CDIR might be helpful when detecting on the host.

Additionally, the HTTP_VIA or HTTP_HOST might also be worth checking.

A simple query to check problem one:

SELECT `col1`, `col2` FROM `referrals` WHERE `userid` = (SELECT `userid` FROM `users` WHERE `last_ip` = '__IP__')

I will look into cookies, but how would I go about that for multies?

CDIR? Do you mean CIDR or is it something different?

I can't seem to get a query working, I must be doing something really wrong. I know what I need to achieve, but I just can't get it. Here's what I'm trying to do:

 

$q=mysql_query("SELECT u.userid, u.username, u.laston, r.* 
			FROM users u 
			LEFT JOIN referals r	ON r.refREFER=u.userid 
			INNER JOIN referals rr	ON rr.refREFED=u.userid
			WHERE r.refREFERIP=r.refREFEDIP 
			ORDER BY userid ASC",$c);

 

I'm trying to make the refREFER become a userid so I can print it as a username, but I want to do that with the refREFED aswell. However when I try to do both, the refREFED becomes the refREFER userid, and I can't seem to get both to work together

Edited September 14, 2011 by gurpreet

[rulerofzu]

This has been mentioned before on the same or similiar subject. INET_NTOA() and INET_ATON() mysql is worth looking at.

As for multi's there is no foolproof way to stop it. There is too many numerous ways to get around that.

Easiest method is to keep accurate logs for transfers. The only advantage to having more than one account is to pool the resources.

[gurpreet]

This has been mentioned before on the same or similiar subject. INET_NTOA() and INET_ATON() mysql is worth looking at.

As for multi's there is no foolproof way to stop it. There is too many numerous ways to get around that.

Easiest method is to keep accurate logs for transfers. The only advantage to having more than one account is to pool the resources.

Just wondering if you could elaborate further on the INET_ATON()? I see what it does, but not sure how it helps? If it would convert it to binary, then a match would be the same as if it wasn't binary? Wouldn't ip2long() do the same function?

Edited September 15, 2011 by gurpreet

[rulerofzu]

It returns a numerical value.

If your wishing to check if its a valid IP then why dont you filter the IP which you can do with filter_validate_ip which also means you can flag for ipv4 and 6. I dont think INET covers ipv6.

There is also inet_pton and inet_ntop php functions.

[gurpreet]

My main goals were to check if it's a valid IP, then to check if there are multiple IPs referred by 1 user

I will look up on those, thanks for that.

[gurpreet]

Just wondering for performance, is there a better way to do these 2 if statements?

 

if($r['fedjail'] == 1)
{
$fed='<font color=red>(In Fed Jail)</font>';
}
else
{
$fed2='';
}
if($r['u2fedjail'] == 1)
{
$fed2='<font color=red>(In Fed Jail)</font>';
}
else
{
$fed='';
}

 

$r['u2fedjail'] is for the refered person

They are later used like this:

 

<td><a href='viewuser.php?u={$r['userid']}'><font color=brown>{$r['username']} $fed</font></a> [{$r['refREFER']}]</td>
<td><a href='viewuser.php?u={$r['u2userid']}'><font color=brown>{$r['u2username']} $fed2</font></a> [{$r['refREFED']}]</td>

Edited September 17, 2011 by gurpreet