Check whether an IP address is stored in database

Question

I have a database with a table named 'IP' in it. It has 2 columns Id(int11) and ip(varchar15), and a row with values: id 1, ip 127.0.0.1

Now back in PHP I have the following:

$ip = $_SERVER['REMOTE_ADDR'];
$query = "SELECT * FROM ip WHERE ip='$ip'";

if(mysql_query($query)) {
    echo "Ip is already in database";   
}
else {
    echo "Ip is not in database";
}

Now the problem I get is that the if-statement still turns TRUE if I change the IP to say: 125.0.0.1

I have been trying to fix it for 2 hours now but still can't figure out what I'm doing wrong.

As an aside, this can be made much more efficient by storing the IP address as an integer. Use the MySQL functions [INET_ATON](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton) and INET_NTOA to convert between an address and integer, and use an UNSIGNED INT column for ip. Benefits are a fixed row size, faster indexing and therefore faster lookup. — David Snabel-Caunt, Nov 16 '11 at 11:37

Clive · Accepted Answer · 2011-11-15T19:11:57.717

5

mysql_query() will not return false if the query executed successfully, even if there are no results. Try using mysql_num_rows() instead:

$ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
$query = "SELECT * FROM ip WHERE ip='$ip'";
$result = mysql_query($query);

if (mysql_num_rows($result) > 0) {
  echo "Ip is already in database";
}
else { 
  echo "Ip is not in database";
}

By the way I added mysql_real_escape_string around the REMOTE_ADDR var, you should always sanitise your input :)

edited Nov 15 '11 at 19:11

answered Nov 15 '11 at 19:02

Clive

36,918
8
87
113

Thank you for the fast reply it works like a charm! About the mysql_real_escape_string. Can someone manipulate that too? – Seltjoek Nov 15 '11 at 19:27
I'm not totally sure to be honest, my instinct says it would probably be possible for someone to inject a malicious value into that variable but I'm not certain. I always err on the side of caution if I haven't explicitly set the var myself :) – Clive Nov 15 '11 at 19:30

score 4 · Answer 2 · answered Nov 15 '11 at 19:03

4

Use mysql_num_rows to check for number of records!

$query = mysql_query("SELECT * FROM ip WHERE ip='". $_SERVER['REMOTE_ADDR'] ."'");
$num = mysql_num_rows($query);

if($num > 0) {
  echo "Exists";
}
else {
  echo "Does not exist";
}

answered Nov 15 '11 at 19:03

Wesley

2,190
17
26

Thanks Wesso for the fast reply too! – Seltjoek Nov 15 '11 at 19:28

Check whether an IP address is stored in database

2 Answers2

Linked