CODEWITHSUNDEEP
phpmysqlip
Knarf
Knarf

Reputation: 1273

Save IP in database

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?

Upvotes: 2

Views: 9562

Answers (4)

Philip Petrov
Philip Petrov

Reputation: 970

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.

Upvotes: 0

sikas
sikas

Reputation: 5523

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 ...

Upvotes: 0

Cody Baker
Cody Baker

Reputation: 21

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

Upvotes: 2

William Leader
William Leader

Reputation: 844

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.

Upvotes: 9

Related Questions