CodeIgniter Forums

Full Version: Storing IP as binary type in DB
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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);
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.