Help with MTG's IP Finder

[gurpreet]

I finally decided to start up my game again, and am fixing all of the bugs before it goes live again. However on the IP finder page the query seems to fail and I can't see why.

 

$sql = (
"SELECT `username`,`lastip`, COUNT(`userid`) AS `numusers`, `userid`, CAST(GROUP_CONCAT(`userid`) AS CHAR) AS `userlist`, fedjail " .
"FROM `users` " .
"WHERE (`lastip` != '') " .
"GROUP BY `lastip` " .
"HAVING (`numusers` > 1) " .
"ORDER BY `lastip`");
$mysql = mysql_query($sql);

while($z = mysql_fetch_array($mysql))
{


$ipcheck = mysql_query("SELECT * FROM ipfinder
WHERE ip = ".$z['lastip']."") or die(mysql_error()); //Error is here



$ip = mysql_fetch_array($ipcheck) or die(mysql_error());

 

The output is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 2

I tried to echo out $mysql and it came up with Resource ID#13

If you need some more code let me know, thanks

[Djkanna]

$ipcheck = mysql_query("SELECT * FROM ipfinder WHERE ip = '".$z['lastip']."'") or die(mysql_error()); //Error is here

[gurpreet]

Hi Djkanna, thanks for the reply. Now this is coming up, even though user_level isn't being called?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_level='2'' at line 1.

I've removed all of the custom functions and made it as default as I can, so I can't see why it's not working or calling the user_level field

Here's the script, it was a free mod so I think I can post it:

 

<?php
include "sglobals.php";
/*
Created by Magictallguy
*/
error_reporting(E_ALL);
if($ir['user_level'] != 2 && $ir['user_level'] != 3)
{
die("You can't access this");
}
//-----------
$_GET['action'] = isset($_GET['action']) && is_string($_GET['action']) ? strtolower(trim($_GET['action'])) : "";
//-----------
switch($_GET['action'])
{
case 'index': index(); break;
case 'status': status(); break; 
case 'status2': status_edit(); break;

default: index(); break;
}
function index()
{
echo ("10.0.0.0 and 127.0.0.1 are default IPs. Pay no attention to those.


<table border='1' class='table' cellpadding='2' cellspacing='2' width='100%'>
<tr>
<th>IP</th>
<th>How Many</th>
<th>ID's</th>
<th>Usernames</th>
<th>Status</th>
<th>Cleared By</th>
</tr>");
$sql = (
"SELECT `username`,`lastip`, COUNT(`userid`) AS `numusers`, `userid`, CAST(GROUP_CONCAT(`userid`) AS CHAR) AS `userlist`, fedjail " .
"FROM `users` " .
"WHERE (`lastip` != '') " .
"GROUP BY `lastip` " .
"HAVING (`numusers` > 1) " .
"ORDER BY `lastip`");
$mysql = mysql_query($sql);

while($z = mysql_fetch_array($mysql))
{


$ipcheck = mysql_query("SELECT * FROM ipfinder WHERE ip = '".$z['lastip']."'") or die(mysql_error()); //Error is here



$ip = mysql_fetch_array($ipcheck) or die(mysql_error());
if($ip['status'] == "" || !$ip['status'])
{ $status = "<font color=blue>Needs Checking</font>"; }
##
else
{ $status = stripslashes($ip['status']); }
##
if(!$ip['cleared_by'])
{ $cleared = "No-one"; }
##
else
{ $cleared = $ip['cleared_by']; }
echo ("<tr>");
echo sprintf("<td style='text-align:center;'><a href='ipfinder.php?action=status&ip=%s' target='_blank'>%s</a></td>", $z['lastip'], $z['lastip']);
echo sprintf("<td style='text-align:center;'>%s</td>", $z['numusers']);
echo sprintf("<td>%s</td>", wordwrap($z['userlist'], 50, "
\n", true));
echo sprintf("<td style='text-align:center;'><a href='/viewuser.php?u=%s'>%s</td>", $z['userid'], $z['username']);
echo sprintf("<td style='text-align:center;'>%s</td>", $status);
echo sprintf("<td style='text-align:center;'>%s</td>", $cleared);

echo ("</tr>");
}
echo ("</table>");
}                                                                                     

function status()
{
echo sprintf("Editing the status of IP: %s", $_GET['ip']);
$GetIPDetails   = sprintf("SELECT * FROM <span class='highlight'>ipfinder</span> WHERE (ip = '%s')", $_GET['ip']);
$sql            = mysql_query($GetIPDetails);
$a              = mysql_fetch_array($sql);
echo ("
");
echo ("You can use code, but limit it to font colours only (<font color=colour>Text</font>)
");
echo ("<font color=red>Multi</font> - <font color=green>Federal Jailed</font>
");
echo ("<font color=green>Library IP</font>
");
echo ("
<font color=orange>Side Note:</font>
");
echo ("<form action='<span class='highlight'>ipfinder</span>.php?action=status2' method='post'>");
echo sprintf("<input type='hidden' name='ip' value='%s' />", $_GET['ip']);
echo sprintf("<textarea rows='10' cols='50' name='status'>".$a['status']."</textarea>
");
echo ("<input type='submit' value='Change Status'>");
echo ("</form>");
}
function status_edit()
{
$Count = sprintf("SELECT ip FROM <span class='highlight'>ipfinder</span> WHERE (ip = '%s')", $_POST['ip']);
$mq = mysql_query($Count);
#
$Name = sprintf("SELECT userid, username FROM users WHERE (userid = %u)", $_SESSION['userid']);
$SN = mysql_query($Name);
$fluff = mysql_fetch_array($SN);
if(mysql_num_rows($mq) == 0)
{
$Update = sprintf("INSERT INTO <span class='highlight'>ipfinder</span> VALUES ('%s', '%s', '%s')", $_POST['ip'], mysql_real_escape_string($_POST['status']), mysql_real_escape_string($fluff['username']));
mysql_query($Update) OR DIE(mysql_error());
echo ("<b>Status Updated!</b><hr /><a href='ipfinder.php' target='_blank'>Back</a>");
}
else
{
$Insert = sprintf("UPDATE <span class='highlight'>ipfinder</span> SET status = '%s', cleared_by = '%s' WHERE (ip = '%s')", $_POST['status'], mysql_real_escape_string($fluff['username']), $_POST['ip']);
echo ("<b>Status Updated!</b><hr /><a href='ipfinder.php' target='_blank'>Back</a>");
mysql_query($Insert) OR DIE(mysql_error());
}
stafflog_add(sprintf("Edited the status of IP %s", $_POST['ip']));
}
?>

Edited February 16, 2013 by gurpreet

[Djkanna]

Hi Djkanna, thanks for the reply. Now this is coming up, even though user_level isn't being called?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_level='2'' at line 1.

I've removed all of the custom functions and made it as default as I can, so I can't see why it's not working or calling the user_level field

Here's the script, it was a free mod so I think I can post it:

<?php
include "sglobals.php";
/*
Created by Magictallguy
*/
error_reporting(E_ALL);
if($ir['user_level'] != 2 && $ir['user_level'] != 3)
{
die("You can't access this");
}
//-----------
$_GET['action'] = isset($_GET['action']) && is_string($_GET['action']) ? strtolower(trim($_GET['action'])) : "";
//-----------
switch($_GET['action'])
{
case 'index': index(); break;
case 'status': status(); break; 
case 'status2': status_edit(); break;

default: index(); break;
}
function index()
{
echo ("10.0.0.0 and 127.0.0.1 are default IPs. Pay no attention to those.


<table border='1' class='table' cellpadding='2' cellspacing='2' width='100%'>
<tr>
<th>IP</th>
<th>How Many</th>
<th>ID's</th>
<th>Usernames</th>
<th>Status</th>
<th>Cleared By</th>
</tr>");
$sql = (
"SELECT `username`,`lastip`, COUNT(`userid`) AS `numusers`, `userid`, CAST(GROUP_CONCAT(`userid`) AS CHAR) AS `userlist`, fedjail " .
"FROM `users` " .
"WHERE (`lastip` != '') " .
"GROUP BY `lastip` " .
"HAVING (`numusers` > 1) " .
"ORDER BY `lastip`");
$mysql = mysql_query($sql);

while($z = mysql_fetch_array($mysql))
{


$ipcheck = mysql_query("SELECT * FROM ipfinder WHERE ip = '".$z['lastip']."'") or die(mysql_error()); //Error is here



$ip = mysql_fetch_array($ipcheck) or die(mysql_error());
if($ip['status'] == "" || !$ip['status'])
{ $status = "<font color=blue>Needs Checking</font>"; }
##
else
{ $status = stripslashes($ip['status']); }
##
if(!$ip['cleared_by'])
{ $cleared = "No-one"; }
##
else
{ $cleared = $ip['cleared_by']; }
echo ("<tr>");
echo sprintf("<td style='text-align:center;'><a href='ipfinder.php?action=status&ip=%s' target='_blank'>%s</a></td>", $z['lastip'], $z['lastip']);
echo sprintf("<td style='text-align:center;'>%s</td>", $z['numusers']);
echo sprintf("<td>%s</td>", wordwrap($z['userlist'], 50, "
\n", true));
echo sprintf("<td style='text-align:center;'><a href='/viewuser.php?u=%s'>%s</td>", $z['userid'], $z['username']);
echo sprintf("<td style='text-align:center;'>%s</td>", $status);
echo sprintf("<td style='text-align:center;'>%s</td>", $cleared);

echo ("</tr>");
}
echo ("</table>");
}                                                                                     

function status()
{
echo sprintf("Editing the status of IP: %s", $_GET['ip']);
$GetIPDetails   = sprintf("SELECT * FROM <span class='highlight'>ipfinder</span> WHERE (ip = '%s')", $_GET['ip']);
$sql            = mysql_query($GetIPDetails);
$a              = mysql_fetch_array($sql);
echo ("
");
echo ("You can use code, but limit it to font colours only (<font color=colour>Text</font>)
");
echo ("<font color=red>Multi</font> - <font color=green>Federal Jailed</font>
");
echo ("<font color=green>Library IP</font>
");
echo ("
<font color=orange>Side Note:</font>
");
echo ("<form action='<span class='highlight'>ipfinder</span>.php?action=status2' method='post'>");
echo sprintf("<input type='hidden' name='ip' value='%s' />", $_GET['ip']);
echo sprintf("<textarea rows='10' cols='50' name='status'>".$a['status']."</textarea>
");
echo ("<input type='submit' value='Change Status'>");
echo ("</form>");
}
function status_edit()
{
$Count = sprintf("SELECT ip FROM <span class='highlight'>ipfinder</span> WHERE (ip = '%s')", $_POST['ip']);
$mq = mysql_query($Count);
#
$Name = sprintf("SELECT userid, username FROM users WHERE (userid = %u)", $_SESSION['userid']);
$SN = mysql_query($Name);
$fluff = mysql_fetch_array($SN);
if(mysql_num_rows($mq) == 0)
{
$Update = sprintf("INSERT INTO <span class='highlight'>ipfinder</span> VALUES ('%s', '%s', '%s')", $_POST['ip'], mysql_real_escape_string($_POST['status']), mysql_real_escape_string($fluff['username']));
mysql_query($Update) OR DIE(mysql_error());
echo ("<b>Status Updated!</b><hr /><a href='ipfinder.php' target='_blank'>Back</a>");
}
else
{
$Insert = sprintf("UPDATE <span class='highlight'>ipfinder</span> SET status = '%s', cleared_by = '%s' WHERE (ip = '%s')", $_POST['status'], mysql_real_escape_string($fluff['username']), $_POST['ip']);
echo ("<b>Status Updated!</b><hr /><a href='ipfinder.php' target='_blank'>Back</a>");
mysql_query($Insert) OR DIE(mysql_error());
}
stafflog_add(sprintf("Edited the status of IP %s", $_POST['ip']));
}
?>

Are you sure the error relates to the file itself?

[Octarine]

As a piece of code, it cannot be relied upon for a multitude of reasons, but it served a purpose in its days. Perhaps however, it would be wise to understand the underlying principal of the code, rather than relying on third party (ie MTG) so called "claimants".

With some engineering, it can be made to work passably well, assuming you ignore the fact the IP's are not a decent mechanism for detecting multi's - consider for example the small country pub with wifi... "A" likes the game, takes his phone out to the pub, shows "B", "B" registers, instant ban... Why?

The solution is relatively simple, and in fact has been deployed in a number of projects including the venerable McCodes, and more recently in an NWE project.

Look at a) how you are detecting the IP address, I can almost certainly guarantee you are doing it incorrectly, b) how you store the address, again, it's almost bound to be wrong, and c) how you examine the database for duplicates; that is unfortunately very wrong.

It has merit as a bit of code, it does work, up to a point, but making to work properly is a challenge that few have mastered.

[Spudinski]

I agree with Octarine, IPs just aren't a very efficient way to track visitors on a website.

There's quite a few things one can do to track a visitor, i.e. cookies, but in reality if someone does not want to be tracked, you're not going to.

A lot of browsers is focused on privacy, some to the extent that it's easier to open up an "anonymous" tab environment rather than a normal one.

If you really want to track someone, tell then so and tell them to install a daemon on their computer and follow a approach similar to DynDNS. *sarcasm*

Like someone once said, you should make your website so such that users do not need tocreate multiple account.

About the SQL itself:

A lot of people prefer to store IP addresses in heximal format, and I would suggest it here as well.

IP addreses should only be used in the event that something goes wrong.

[gurpreet]

Are you sure the error relates to the file itself?

I thought it could be a function in my global_func, header or another file, but again this issue only pops up here. I'll keep looking around in other files to see if there's an outstanding issue.

 

As a piece of code, it cannot be relied upon for a multitude of reasons, but it served a purpose in its days. Perhaps however, it would be wise to understand the underlying principal of the code, rather than relying on third party (ie MTG) so called "claimants".

With some engineering, it can be made to work passably well, assuming you ignore the fact the IP's are not a decent mechanism for detecting multi's - consider for example the small country pub with wifi... "A" likes the game, takes his phone out to the pub, shows "B", "B" registers, instant ban... Why?

The solution is relatively simple, and in fact has been deployed in a number of projects including the venerable McCodes, and more recently in an NWE project.

Look at a) how you are detecting the IP address, I can almost certainly guarantee you are doing it incorrectly, b) how you store the address, again, it's almost bound to be wrong, and c) how you examine the database for duplicates; that is unfortunately very wrong.

It has merit as a bit of code, it does work, up to a point, but making to work properly is a challenge that few have mastered.

 

I agree with Octarine, IPs just aren't a very efficient way to track visitors on a website.

There's quite a few things one can do to track a visitor, i.e. cookies, but in reality if someone does not want to be tracked, you're not going to.

A lot of browsers is focused on privacy, some to the extent that it's easier to open up an "anonymous" tab environment rather than a normal one.

If you really want to track someone, tell then so and tell them to install a daemon on their computer and follow a approach similar to DynDNS. *sarcasm*

Like someone once said, you should make your website so such that users do not need tocreate multiple account.

About the SQL itself:

A lot of people prefer to store IP addresses in heximal format, and I would suggest it here as well.

IP addreses should only be used in the event that something goes wrong.

Both of you make very good points. However, with my skill level I wouldn't be able to accomplish the above. This page is just being used to see multiple users on a single IP so they can be monitored, to make sure they aren't transferring money and items between eachother. I've made it so people can only on the same IP but have advised that transfers will be monitored.