Jump to content

Archived

This topic is now archived and is closed to further replies.

BadGoat

IP Validation and ip2long, long2ip

Recommended Posts

Hello!

I've written a script which is supposed to search my MySQL db for a matching IP address. When I search for a matching IP I am always getting the error "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource" which refers to my query, which is:
[code]$data = mysql_query("SELECT * FROM ips WHERE find = ".long2ip($sip)."");  [/code]

As you can see, I am using ip2long to convet the IP address string  to the proper address and long2ip to convert it back to the IP address string. I'm sure I can't see the forest for the trees, as I've been banging my head for the whole weekend. Can someone more experienced see the error in my script?

[code]<tr>
    <td>IP Address:</td>
    <td>
    <form name="search" method="post" action="<?=$PHP_SELF?>">
    <input type="text" name="find" />
    <input type="hidden" name="searching" value="yes" />
    <input type="submit" name="search" value="Search" />
    </form>    </td>
</tr>
</TABLE>
</BODY>
</HTML>

<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
// filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

$x = split("\.",$find);
$find = (256*256*256*$x[0]) + (256*256*$x[1]) + (256*$x[2]) + ($x[3]);

// search for term
$data = mysql_query("SELECT * FROM ips WHERE find > ".long2ip($sip)." AND < ".long2ip($eip)."");

// display the results
while($result = mysql_fetch_array( $data ))
{
echo $result['ip'];
echo "<br>";
}

// counts the number or results
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, no entry matched your query<br><br>";
}

// string searched for
echo "<b>Searched For:</b> " .long2ip($find);
}
?>[/code]

Share this post


Link to post
Share on other sites
Maybe I am being dumb,...but you could try this

[code]$data = mysql_query("SELECT * FROM `ips` WHERE `find` = '.long2ip($sip).'"); [/code] 

instead of this:

[code]$data = mysql_query("SELECT * FROM ips WHERE find = ".long2ip($sip)."");[/code] 

Share this post


Link to post
Share on other sites
change this:
[code]
// search for term
$data = mysql_query("SELECT * FROM ips WHERE find > ".long2ip($sip)." AND < ".long2ip($eip)."");
[/code]
to this:
[code]
// search for term
$s = long2ip($sip);
$e = long2ip($eip);
$sql = "SELECT * FROM ips WHERE find BETWEEN '$s' AND '$e'";
$data = mysql_query($sql) or die(mysql_error());
[/code]

Share this post


Link to post
Share on other sites
That message usually comes up when you you don't use the proper format or syntax. As crayon suggested, his would be the proper way to find data between variables.

Ray

Share this post


Link to post
Share on other sites
Hello Crayon,

Thank you for that! I am closer now. Now I get a new error: Unknown column 'find' in 'where clause', so I changed 'find' to '$find' and I search for an IP I know is in the db, but it says that it can find no matches. Am I doing something wrong in this part of the code which prevents me from finding a known IP?
[code]// filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

$x = split("\.",$find);
$find = (256*256*256*$x[0]) + (256*256*$x[1]) + (256*$x[2]) + ($x[3]);[/code]

Share this post


Link to post
Share on other sites
The error message means that you don't have a database field named find. Change it in the query so that you are testing for the correct field name.

[code]$sql = "SELECT * FROM ips WHERE name_of_field_containing_ips BETWEEN '$s' AND '$e'";[/code]

Share this post


Link to post
Share on other sites
Andy, in your example, 'name_of_field_containing_ips' is the IP I am entering into the search field, and '$s' AND '$e' reference the starting IP and the ending IP of fields in my database.  I am guessing I am constructing it incorrectly from the start?

Share this post


Link to post
Share on other sites
No it isn't, name_of_field_containing_ips is the name of the field in your database not the name of some program variable.  Since the query error tells you the database table fieldname isn't named 'find', it must be named something else.

Share this post


Link to post
Share on other sites
My pardons, I wanted to say that what I was hoping to do was to search for the IP address I entered in my search field and match it as being between my two fields in my database, $sip and $eip, which Crayon helped convert correctly with long2ip to $s and $e. With what I am trying to do in mind, how do I match the entered IP address to a range of IP addresses between $s and $e?

Share this post


Link to post
Share on other sites
so...what exactly is $s and $e then ($sip and $eip)?

basically your query says this:

select everything from your table called ips where one of your fields (your ip address field, presumably) is between $s and $e. 

this will potentially give you more than one answer.  are you expecting to get the rest of the information (like username, etc..) based on the ip address, and expect one result?

i think you are going to have to be more specific on what you are trying to accomplish here. what are you expecting the user to enter in the field, and what are you expecting the query to do with it?

edit (cuz you posted when i was writing) okay if $s and $e are ip ranges you have set somewhere else, presumably all you need to do is change the "find" in the query to the name of the column that holds your ip addresses

Share this post


Link to post
Share on other sites
.. or select everything from the database table where what the user entered is less than one database field and more than another database field ...

Share this post


Link to post
Share on other sites
I'm a noob :)  I'll try to explain what I am trying to accomplish.

So I have a db with IP ranges. $sip is the starting IP of the range. $eip is the ending IP of the range. I am trying to create a searcxh script which will allow me to type in an IP address and when it finds a matching range, it will echo back some cursory information about the IP range. For example, 'Bob' is the 'IP Admin' for the IP Range between 10.10.10.0 ($sip) and 10.10.11.255($eip) and 'Mary' is the 'Tech Support' for the IP Range between 10.0.0.0($sip) and 10.0.255.255($eip).

My hopes was to be able to type in an IP address into the search field and whem it falls into the IP range, the script gives me the info I am seeking.

Share this post


Link to post
Share on other sites
[quote author=AndyB link=topic=105937.msg423373#msg423373 date=1156777900]
.. or select everything from the database table where what the user entered is less than one database field and more than another database field ...
[/quote]
Aye, that's what I aim to do, but I am unsure how to do it. Can you point me in the right direction or cite an example please?

Share this post


Link to post
Share on other sites
Approximately ...

SELECT * from your_database_table WHERE sip > '$user_input' AND eip < '$user_input'

Share this post


Link to post
Share on other sites
Hi Andy,

I tried that on my own yesterday but I thought it was wrong since it gave me an odd error, which 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 '> '184615169' AND < '184615169'' at line 1"

Share this post


Link to post
Share on other sites
Notice the slight difference between what I suggested and what you tried - there's a variable in EACH test in mine.  That style of query should work for you.

Share this post


Link to post
Share on other sites
The error reported above was given with this MySQL query:

$sql = "SELECT * FROM ips WHERE $sip > $find AND $eip < $find";

Share this post


Link to post
Share on other sites
Punctuation is very important.  The code I've suggested doesn't seem to be one you've tried exactly yet.

[code]$query = "SELECT * FROM ips WHERE sip > '$find' AND eip < '$find'"; // exactly like this
$result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // explain what's wrong[/code]

Give that a shot and post the precise output you get.

Share this post


Link to post
Share on other sites
Andy,
That got rid of the mysql error. Punctuation is gonna be my friend, kicking and screaming. ;)
As for the output: When I type an IP I know is within a range in my db, I get the error: "Sorry, but we can not find an entry to match your query" as if it does not recognize.

Share this post


Link to post
Share on other sites
OK, so we're making progress.  The reason why you see that message is .... either the ip really is not in the database or the logic for generating that message is flawed.

Step 1 would be to echo the query (that results in finding nothing).  So, modify the code I gave you to add:

[code]echo $query. "<br/>";[/code]

between the query definition and the $result = line. If you then copy/paste that same query into your database administration tool (phpMyAdmin if you're lucky) and see what results.  If you find nothing then the next step should be obvious. If you get one or more results, then your logic is flawed.

Perhaps as we're so close to success (although it might not feel like it), now is the time to post a fairly complete copy of your code as it exists right now. The input form as well as the database query script.

Share this post


Link to post
Share on other sites
The query, as echoed, when searching for 11.0.0.1 (the range for which I added for S&Gs): SELECT * FROM ips WHERE sip > '184615169' AND eip < '184615169'

Using PHPMyAdmin, I go to the range and check, and within, sip is '184549376'  and eip is '201326591'.

And by looking at this, I figured that the query should be
[code]$query = "SELECT * FROM ips WHERE sip < '$find' AND eip > '$find'"; // exactly like this[/code]

And with this, I do not get the "Sorry, but we can not find an entry to match your query" error anymore...

Even more progress!

One of my final problems is figuring out how to echo other information which is stored in the same record... Such as the contact name ($contact) phone #($phone) and email address($e1).

***EDIT***


I just fixed that problem. Without help.. I surprised myself... hehehe

There is only one problem left... When I edit an IP range the edited IP range is listed out of numerical order once I touch one of the IP addresses. Here's my edit.php script:
[code]<?PHP

session_start();
if(!session_is_registered(user)){
header("location:login.php");
}
include  ("config.php");
echo'
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Edit IP Block</title>
<link rel=stylesheet href="css.css" type="text/css">
</head>
<body>';

// $string is just a placeholder
function escapeSingleQuotes($string){
//escapse single quotes
$singQuotePattern = "'";
$singQuoteReplace = "''";
return(stripslashes(eregi_replace($singQuotePattern, $singQuoteReplace, $string)));
}

$x = split("\.",$newstart);
$sip = (256*256*256*$x[0]) + (256*256*$x[1]) + (256*$x[2]) + ($x[3]);

$y = split("\.",$newend);
$eip = (256*256*256*$y[0]) + (256*256*$y[1]) + (256*$y[2]) + ($y[3]);

if(!empty($_REQUEST['action']) && $_REQUEST['action'] == 'update')
{
        $id = $_REQUEST['id'];
        $_GET['id'] = $id;
        $query = "UPDATE ips SET sip = '".ip2long($_REQUEST['newstart'])."', eip = '".ip2long($_REQUEST['newend'])."', nic = '".escapeSingleQuotes($_REQUEST['newnic'])."', contact = '".escapeSingleQuotes($_REQUEST['newcontact'])."', phone = '".escapeSingleQuotes($_REQUEST['newphone'])."', e1 = '".escapeSingleQuotes($_REQUEST['newe1'])."' WHERE id='$id'";
        $result = mysql_query($query) or die("<b>mySQL Error:</b>");
        if(!$result)
        {
            echo 'Error processing request.';
        }
        else
        {
            echo '<B>The Record has been successfully updated!</B>';
        }
    }

    $id = $_GET['id'];
// The ID is passed through the URL to specify the row,
    // Or it is set in the previous script.

    $query = "SELECT * FROM ips WHERE id = '$id'";
    $result = mysql_query($query);
    $row = mysql_fetch_array($result);

echo '
<form name="update1" method="post">
        <input type="hidden" value="update" name="action">
        <input type="hidden" name="id" value="'.$id.'">

<table class="menu" align="center">

<tr>
    <th colspan="2">Edit IP Block</th>
</tr>

<tr>
    <td class="selcol1">Starting IP Address:</td>
<td class="selcol2"><input class="white" type="text" name="newstart" size="50" value="'.long2ip($row['sip']).'" tabindex="10"></td>
</tr>

<tr>
    <td class="selcol1">Ending IP Address:</td>
<td class="selcol2"><input class="white" type="text" name="newend" size="50" value="'.long2ip($row['eip']).'" tabindex="20"></td>
</tr>

<tr>
    <td class="selcol1">Contact:</td>
<td class="selcol2"><input class="white" type="text" name="newcontact" size="50" value="'.$row['contact'].'" tabindex="60"></td>
</tr>

<tr>
    <td class="selcol1">Phone:</td>
<td class="selcol2"><input class="white" type="text" name="newphone" size="50" value="'.$row['phone'].'" tabindex="70"></td>
</tr>

<tr>
    <td class="selcol1"> Email:</td>
<td class="selcol2"><input class="white" type="text" name="newe1" size="50" value="'.$row['e1'].'" tabindex="80"></td>
</tr>

<tr>
    <td class="selcol1">Alternate Email:</td>
<td class="selcol2"><input class="white" type="text" name="newe2" size="50" value="'.$row['e2'].'" tabindex="90"></td>
</tr>

<tr>
    <td class="selcol1">Email Verified:</td>
    <td class="selcol2"><select name="newe_ver" tabindex="30">
        <option value="'.$row['e_ver'].'">'.$row['e_ver'].'</option>
        <option value="Yes">Yes</option>
        <option value="No">No</option>

    </select></td>
</tr>    ';
?>

<input type="hidden" name="newupdated" value="<? date('M j Y')?>">

<tr>
    <td class="header" colspan="2"><center><input class="blue" type="submit" value="Update" action="required" tabindex="110"><br><br>
        </form>
        </td>
</tr>

<tr>
    <td class="spacer" colspan="2">&nbsp;</td>
</tr>

</TABLE>
</BODY>
</HTML>[/code]

And the script which lists the IP ranges in numerical order:
[code]
<?PHP

session_start();
if(!session_is_registered(user)){
header("location:login.php");
}
include  ("config.php");
echo'
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>List IP Blocks</title>
<link rel=stylesheet href="css.css" type="text/css">
</head>
<body>';

$sqlquery = "SELECT id, sip, eip, nic, e1, contact, phone FROM  ips ORDER BY sip";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query !");
$row = mysql_fetch_row($queryresult);

$id = $row[0];
$sip = $row[1];
$eip = $row[2];
$contact = $row[3];
$phone = $row[4];
$e1 = $row[5];

$tdcount = 1;
$numtd = 1; // number of cells per row
echo '

<table class="menu" align="center">

<tr>
    <th colspan="6">List IP Blocks</th>
</tr>

<tr>
    <td class="selcol3">ID</td>
    <td class="selcol3">IP Block Start</td>
    <td class="selcol3">IP Block End</td>
    <td class="selcol3">Contact</td>
    <td class="selcol3">Phone</td>
    <td class="selcol3">Email</td>
</tr>

<TR>  ';
  mysql_data_seek($queryresult,0)  ;
while($row = mysql_fetch_array($queryresult)) {

    if ($tdcount == 1) echo "<tr>";
echo '
    <td class="selcol2"><a class="style1" href="ip_block_view.php?id='.$row['id'].'"> '.$row['id'],'</a></td>
    <td class="selcol2"> '.long2ip($row['sip']),'</td>
    <td class="selcol2"> '.long2ip($row['eip']),'</td>
    <td class="selcol2"> '.$row['contact'],'</td>
    <td class="selcol2"> '.$row['phone'],'</td>
    <td class="selcol2"> '.$row['e1'],'</td>
    ';
if ($tdcount == $numtd) {
echo "</tr>";
$tdcount = 1;
} else {
$tdcount++;
}

echo "</tr>";
}
echo '

</table> ';
?>

</BODY>
</HTML>[/code]


Share this post


Link to post
Share on other sites
doh! That was my fault for getting the start and end reversed.

as to how you output other data from the results, add more output in the while loop ...

[code]echo $result['contact']; // show contact name[/code]

See you've worked that one out.

Share this post


Link to post
Share on other sites
Andy: A sincere THANK YOU for putting up with my goatheadedness and assisting me through my issues!

Getting the rest of the record turned out to be pretty easy:
[code]
    $query = "SELECT * FROM ips WHERE sip < '$find' AND eip > '$find'";
    $result = mysql_query($query);
    $row = mysql_fetch_array($result);
echo'
<br>
<tr>
    <td class="selcol1">Contact</td>
    <td class="selcol2">'.$row['contact'].'</td>
</tr>

<tr>
    <td class="selcol1">Phone</td>
    <td class="selcol2">'.$row['phone'].'</td>
</tr>

<tr>
    <td class="selcol1">Email</td>
    <td class="selcol2">'.$row['e1'].'</td>
</tr>[/code]

The weird anomaly where an edited record is put out of numerical order once an IP address is changed is my next (and last) problem..

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.