Save IP in database

Question

When a user logs in I want to save their IP in the database. How would I do that? What type is best to use for the MySQL field? How would the PHP code to get IP look like?

I'm thinking of using it as an extra security feature for the login/session stuff. I'm thinking of checking the IP the user logged in with from the DB with the IP the user have now as addition to checking the session. So that it first check the session, and then check if you have a valid IP.

Is that a good extra feature? And what other things can I do to make it more secure?

William Leader · Accepted Answer · 2010-06-06T18:54:42.800

9

Have a look at the INET_NTOA and INET_ATON functions in mysql. They convert between dotted notation IP address to 32 bit integers. This allows you to store the IP in just 4 bytes rather than a 15 bytes. Make sure you use an unsigned int and not a signed int.

edited Jun 06 '10 at 18:54

answered Jun 06 '10 at 18:49

William Leader

814
6
20

Those functions also don't seem to support IPv6. I think the best solution is a blob. I'm surprised MySQL doesn't have a dedicated data type like postgres' inet type. – Artefacto Jun 06 '10 at 18:53
They do support IPv6, but will return an 8 byte integer instead of a 4 byte integer. – William Leader Jun 06 '10 at 18:55
2

as for IPv6 - http://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database/420688#420688 – dev-null-dweller Jun 06 '10 at 18:58
2

I doubt it, since addresses in IPv6 are 128-bit long they don't fit in an 8 byte integer. – Artefacto Jun 06 '10 at 19:02

score 2 · Answer 2 · answered Jun 06 '10 at 18:58

2

In regards to William Leader's suggestion, PHP has two functions ip2long and long2ip that do exactly what he's talking about.

answered Jun 06 '10 at 18:58

Cody Baker

21
1

score 0 · Answer 3 · answered Jun 06 '10 at 19:07

as Lauri Lehtinen said,you use the $_SERVER['REMOTE_ADDR'] to get the IP-Address, for a simple PHP Code look below ...

<?php
$conn = mysql_connect("server","username","password");//server, username and password are your server address and access details
if(!$conn)
    die("cannot connect to mysql server" . mysql_error());
mysql_select_db("database Name", $conn);
$sql = "INSERT INTO table_name (IP_Address) VALUES(" . $_SERVER['REMOTE_ADDR'] . ")";
if(!mysql_query($sql,$conn))
    die("ERROR: " .mysql_error());
mysql_close($con);
?>

This is just a sample just modify it to suite your needs ...

Philip Petrov · Answer 4 · 2020-05-24T04:09:35.407

TLDR; Use VARBINARY(16) and use the INET6_ATON and INET6_NTOA functions to write/read $_SERVER['REMOTE_ADDR'] stored in the DB.

Before answering the question, it's good to discuss how to fetch the IP address. If you will use it for something important (eg. security checks), then you should use only $_SERVER['REMOTE_ADDR'] as it is the only trusted source (generated by the web server and guaranteed to be good by the three way TCP/IP handshake)... except if you are using the app in local network... etc. If it is a website, use $_SERVER['REMOTE_ADDR'] to take the user IP.

This IP can be a proxy server one. You can eventually get the original user IP if he is behind a proxy by fetching the headers that the proxy servers (sometimes) add. This is a 101 function for that:

     $ip = filter_var(
            array_map("trim", 
                explode("," ,
                    $_SERVER['HTTP_CLIENT_IP']??
                    $_SERVER['HTTP_X_FORWARDED_FOR']??
                    $_SERVER['HTTP_X_FORWARDED']??
                    $_SERVER['HTTP_FORWARDED_FOR']??
                    $_SERVER['HTTP_FORWARDED']??
                    $_SERVER['REMOTE_ADDR']
                )
            )[0],
          FILTER_VALIDATE_IP);
    $ip = $ip!=""?$ip:"Invalid IP";
    echo $ip;

HOWEVER note that any header starting with HTTP_* can be faked by the user (he can write there whatever he want. So this is NOT to be trusted.

Having said that, if you are using PHP with MySQL/MariaDB, you can store the IP the following way (in the example below I store the IP and the number of attempts to login to the system):

    CREATE TABLE login_logs_hash(
         ip VARBINARY(16) NOT NULL,
         PRIMARY KEY(ip) USING HASH,
         attempts INT UNSIGNED NOT NULL DEFAULT 1
    )ENGINE = MEMORY;

Somewhere in the script when the login is unsuccessful (invalid username and password):

    ...
    $sql = "INSERT INTO login_logs_hash (ip) 
            VALUES (INET6_ATON('".$_SERVER['REMOTE_ADDR']."'))
            ON DUPLICATE KEY UPDATE attempts = attempts+1";
    $result = mysqli_query($link, $sql);
    ...

By that example I finally show you the answer - the best column is VARBINARY(16). It can store both IPv6 and IPv4.

IMPORANT: do no change it to BINARY(16) even you will be eventually thinking that fixed column width is better. It will not store the IPv4 addresses correctly if you follow my example.

Save IP in database

4 Answers4

Linked