PHP Fatal error: 1406:Data too long for column 'ip_address' at row 1

[mvstudio]

Hello everyone.

I'm encountering a really weird issue at the moment I'm going to need some help with.

I'm running v1.5.6a on one of my stores and getting this intermittent issue whenever some customers pay using SquareUp.

This is what I keep getting in the log file:

[10-Jul-2019 01:42:39 America/Detroit] Request URI: /, IP address: e:21:DaabaeDe:3::2:fc:17:eeFac:0::21:000dcecadea:1::0a:2::0:9:ee:5::8:ae:20:DaabaeDe:0::8:feed:56:ded5DECEAAFac::eCfe:19:caceaefc:6:ae:5:caceb:1:11:caceca:20:DaabaeDe:0::1:4::13:000cecb:1
#1  trigger_error() called at [/xxxxx/includes/classes/db/mysql/query_factory.php:171]
#2  queryFactory->show_error() called at [/xxxxx/includes/classes/db/mysql/query_factory.php:143]
#3  queryFactory->set_error() called at [/xxxxx/includes/classes/db/mysql/query_factory.php:270]
#4  queryFactory->Execute() called at [/xxxxx/includes/functions/whos_online.php:93]
#5  zen_update_whos_online() called at [/xxxxx/includes/init_includes/init_special_funcs.php:19]
#6  require(/xxxxx/includes/init_includes/init_special_funcs.php) called at [/xxxxx/includes/autoload_func.php:48]
#7  require(/xxxxx/includes/autoload_func.php) called at [/xxxxx/includes/application_top.php:170]
#8  require(/xxxxx/includes/application_top.php) called at [/xxxxx/index.php:26]
--> PHP Fatal error: 1406:Data too long for column 'ip_address' at row 1 :: insert into whos_online
                (customer_id, full_name, session_id, ip_address, time_entry,
                 time_last_click, last_page_url, host_address, user_agent)
              values ('0', '&yen;Guest', 'ee7ea5fb75af673eead6ef4183e7c17e', 'e:21:DaabaeDe:3::2:fc:17:eeFac:0::21:000dcecadea:1::0a:2::0:9:ee:5::8:ae:20:DaabaeDe:0::8:feed:56:ded5DECEAAFac::eCfe:19:caceaefc:6:ae:5:caceb:1:11:caceca:20:DaabaeDe:0::1:4::13:000cecb:1', '1562737359', '1562737359', '/', '', 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Win64; x64; Trident/4.0)') ==> (as called by) /xxxxx/includes/functions/whos_online.php on line 93 <== in /xxxxx/includes/classes/db/mysql/query_factory.php on line 171.

Right now the issue is happening to only one client that I'm aware of as other orders paid using the SquareUp module seem to go through just fine. Whenever that client tries to make a purchase with her credit card if the order goes through which I don't know how she manages, I end up with an order and no SquareUp segment on the orders.php page to approve/process the credit card at all, which we've had to decline several times in the past couple of weeks.

I have the SquareUp payment module set up to Authorize as we need to verify the order before capturing the payment.

I've tried looking for answers on the forum but couldn't locate any threads discussing this issue.

Any help would be appreciated.

[lat9]

Hmm, that IP address is a kind-of-sort-of-not-exactly IPv6 format.

[mvstudio]

Hmm, that IP address is a kind-of-sort-of-not-exactly IPv6 format.

Hello Lat9. That was exactly my thought too. Could I be in the presence of a hacker? Perhaps someone forcing the system to place an order with a stolen CC?

[mc12345678]

Looks suspicious to me too. Don't readily see how that ip address could be morphed to be a valid ipv6 address and there doesn't seem to be any information that some newer ipv has been developed. As to the CC validity, seems that anything could be possible, but the process is indicating that at least a portion of the transaction is invalid.

[lat9]

It certainly looks like "script kiddies" having fun. Perhaps an update should be made to the zen_get_ip_address function, present in /includes/functions/functions_general.php and, unfortunately, also in the /admin/includes/functions/general.php, changing

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }

    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = preg_replace('~[^a-fA-F0-9.:%/,]~', '', $ip);

    /**
     *  if it's still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';

    return $ip;
  }

to use the built-in PHP function to perform that address validation:

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }

    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6 | FILTER_FLAG_IPV4);

    /**
     *  if it's not a valid IP address or still blank, set to a single dot
     */
    if ($ip === false || trim($ip) == '') $ip = '.';

    return $ip;
  }

[mc12345678]

It certainly looks like "script kiddies" having fun. Perhaps an update should be made to the zen_get_ip_address function, present in /includes/functions/functions_general.php and, unfortunately, also in the /admin/includes/functions/general.php, changing

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }

    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = preg_replace('~[^a-fA-F0-9.:%/,]~', '', $ip);

    /**
     *  if it's still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';

    return $ip;
  }

to use the built-in PHP function to perform that address validation:

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }

    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6 | FILTER_FLAG_IPV4);

    /**
     *  if it's not a valid IP address or still blank, set to a single dot
     */
    if ($ip === false || trim($ip) == '') $ip = '.';

    return $ip;
  }

Actually:

    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6 | FILTER_FLAG_IPV4);

Would need to exclude both of those FILTER_FLAGS to validate against both ipv4 and ipv6. Inclusion of one omits the other and bitwise orring them excludes both. By adding no flag, then private and reserved ip addresses would be allowed. If desired to exclude one of them (which I can't say as a whole that they should be) then either or both FILTER_FLAG_NO_PRIV_RANGE or FILTER_FLAG_NO_RES_RANGE would be included. If both then they are bitwise orred (|).

So long and short, to just simply validate that the ip address is "acceptable" it would be:

    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP);

Further, if there is no need to identify whether it was really a false response or an empty field, then could use a default response such that if it is invalid that the result is a set of empty quotes and the if below could be left as it was:

   $options = array('options' => array('default' => ''));
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, FILTER_DEFAULT, $options);

I originally wrote array information into the one line, but the php manual reads as if $options needs to be its own variable outside of the call to filter_var.

[mvstudio]

Thanks guys!
Ok, so what should I change $ip to? I'm confused now.

[mc12345678]

Below is what I would recommend built off of what lat9 initially identified.

in /includes/functions/functions_general.php and, unfortunately, also in the /admin/includes/functions/general.php, changing

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }
    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = preg_replace('~[^a-fA-F0-9.:%/,]~', '', $ip);
    /**
     *  if it's still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';
    return $ip;
  }

to use the built-in PHP function to perform that address validation:

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }
    /**
     * sanitize for validity as an IPv4, IPv6, private or reserved address
     */
    $options = array('options' => array('default' => ''));
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, FILTER_DEFAULT, $options);
    /**
     *  if it's not a valid IP address or still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';
    return $ip;
  }

[mc12345678]

CORRECTION!!!
Below is what I would recommend built off of what lat9 initially identified and I have now been able to test.
I have one modification from my previous posting which is to remove the FILTER_DEFAULT option.

in /includes/functions/functions_general.php and, unfortunately, also in the /admin/includes/functions/general.php, changing

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }
    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = preg_replace('~[^a-fA-F0-9.:%/,]~', '', $ip);
    /**
     *  if it's still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';
    return $ip;
  }

to use the built-in PHP function to perform that address validation:

  function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }
    /**
     * sanitize for validity as an IPv4, IPv6, private or reserved address
     */
    $options = array('options' => array('default' => ''));
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP, $options);
    /**
     *  if it's not a valid IP address or still blank, set to a single dot
     */
    if (trim($ip) == '') $ip = '.';
    return $ip;
  }

[lat9]

@mc12345678, the documentation for filter_val indicates that the options are either an array or a bitwise disjunction of values (as I supplied). I'll bite that all the default values should be used, so the change should be

 function zen_get_ip_address() {
    $ip = '';
    /**
     * resolve any proxies
     */
    if (isset($_SERVER)) {
      if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_X_FORWARDED'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED'];
      } elseif (isset($_SERVER['HTTP_X_CLUSTER_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
      } elseif (isset($_SERVER['HTTP_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_FORWARDED_FOR'];
      } elseif (isset($_SERVER['HTTP_FORWARDED'])) {
        $ip = $_SERVER['HTTP_FORWARDED'];
      } else {
        $ip = $_SERVER['REMOTE_ADDR'];
      }
    }
    if (trim($ip) == '') {
      if (getenv('HTTP_X_FORWARDED_FOR')) {
        $ip = getenv('HTTP_X_FORWARDED_FOR');
      } elseif (getenv('HTTP_CLIENT_IP')) {
        $ip = getenv('HTTP_CLIENT_IP');
      } else {
        $ip = getenv('REMOTE_ADDR');
      }
    }

    /**
     * sanitize for validity as an IPv4 or IPv6 address
     */
    $ip = filter_var((string)$ip, FILTER_VALIDATE_IP);

    /**
     *  if it's not a valid IP address or still blank, set to a single dot
     */
    if ($ip === false || trim($ip) == '') $ip = '.';

    return $ip;
  }

Note also that your update 'lost' the check for $ip exactly equal to false, which is the filter_var return value and will result in a PHP Notice from trim since (bool)false is not a string.