Storing IP as binary type in DB

Storing IP as binary type in DB - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Storing IP as binary type in DB (/showthread.php?tid=67876)

Storing IP as binary type in DB - neuron - 04-19-2017

Hi,

I have a table which keeps failed logins.

IP stored in VARBINARY(16) column.

I don't have any experience with BINARY types in MySql.

Problem is when I want to do query by comparing by IP sometimes it throws db error:

SELECT MAX(attempted_at) as last_failed_attempt
FROM failed_login
WHERE attempted_at > DATE_SUB('2017-04-19 17:26:18', INTERVAL 60 MINUTE) AND (ip_address = 'Xç'e' OR 1 = 0)

My model:

get max attempts:

Code:
$now = date($this->config->item('log_date_format'));

    $ip = inet_pton($_SERVER['REMOTE_ADDR']);

    $sql = "SELECT MAX(attempted_at) as last_failed_attempt

    FROM failed_login

    WHERE attempted_at > DATE_SUB('$now', INTERVAL $delay_minutes MINUTE)

    AND (ip_address = '$ip' OR ";

    if($username != null){

        $sql .= "username = '$username')";

    }else $sql .= " 1 = 0) ";

    $result = $this->db->query($sql)

    ->row_array();

what is wrong in my query?

Insert query:

Code:
   $insert_data = array(

        'username' => $username,

        'ip_address' => inet_pton($_SERVER['REMOTE_ADDR']),

        'attempted_at' => date($this->config->item('log_date_format'))

        );

    $this->db->insert('failed_login', $insert_data);

RE: Storing IP as binary type in DB - xenomorph1030 - 04-20-2017

You are trying to compare a string against binary data which just doesn't work. Trying doing some research on how to select binary data.