从PHP存储MySQL中的IP

Question

After many hours of searching, I can only find "obsolete" and/or "incomplete" answers. (Apparently they predate PDO.) I'm writing PHP code (version 7.0.15), and using MySQL version 5.7.17-0 (both on a KUbuntu "virtual machine"). Though I've been working with computers for more than 45 years, I'm fairly new to PHP and MySQL.

I can get the visitor's IP address in PHP. I then want to check the "try_ur_table" to see if it already has an entry, and if not, then insert an entry and look it up, so I can then use the "ur_index" in other parts of the program. The ur_index is an int(11), and the ur_ip is binary(16).

The problem is that every time I run the code, the first select fails, so a new entry is made, and then the second select also fails to find a match!

Here's the relevant code snippet:

  try
  {
    $pdc = new PDO('mysql:host=localhost;dbname=toy_database', 'xxxxx', 'xxxxx' );
    $pdc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdc->exec('SET NAMES "utf8"');
  }
  catch (PDOException $e)
  {
    $output = 'Unable to connect tothe databaseserver. ' . $e->getMessage();
    include 'errout.html.php';
    exit();
  }
  // Find the caller data...
  if ( isset( $_SERVER[ 'REMOTE_ADDR' ] ) )
  {
    $cd = inet_pton( $_SERVER[ 'REMOTE_ADDR' ] );
    if ( $cd )
    {
      // inet_pton thinks it succeeded...
      try
      {
        $sql = "SELECT * FROM try_ur_table WHERE ur_ip = ? ";
        $rt = $pdc->prepare($sql);
        $rt->execute(array($cd));
        $u_list = $rt->fetchAll();
      }
      catch (PDOException $e)
      {
        $output = 'Problem looking for ur_ip.  ' .  $e->getMessage();
        include 'errout.html.php';
        exit();
      }
      if ( $u_list == NULL )
      {
        // New user!
        try
        {
          $sqm = "INSERT INTO try_ur_table SET ur_ip=?";
          $rs = $pdc->prepare($sqm);
          $rs->execute(array($cd));
        }
        catch (PDOException $e)
        {
          $output = 'Problem inserting new ur_ip. ' . $e->getMessage();
          include 'errout.html.php';
          exit();
        }
        // Now go find the new entry...
        try
        {
          $sql = "SELECT * FROM try_ur_table WHERE ur_ip = ? ";
          $rt = $pdc->prepare($sql);
          $rt->execute(array($cd));
          $u_list = $rt->fetchAll();
        }
        catch (PDOException $e)
        {
          $output = 'Problem looking for new ur_ip.  ' . $e->getMessage();
          include 'errout.html.php';
          exit();
        }
      } // $u_list == NULL
      // At this point there should be exactly one row in $u_list...
    } // $cd != false
  }
  else
  {
    // ! isset( $_SERVER[ 'REMOTE_ADDR' ]
    $cd = false;
  }

Other testing has shown that $_SERVER[ 'REMOTE_ADDR' ] is returning 127.0.0.1 (which makes sense, as this is "local host"). However, for each time I've run the above code, phpMyAdmin says that ur_ip is 0x7f00000001000000000000000000000 (hopefully I've counted the zeros correctly -- can't seem to copy & paste from phpMyAdmin, but that's minor). Also, since I have ur_index, I've tried a select based on it, and when I tried to run the ur_ip through inet_ntop() I get garbage, neither a valid IPv4 nor an IPv6 address.

Answer 1

Your problem stems from the binary type. To match a stored value you should pad the data to be compared, as it shown in the documentation.

Or you can simply use VARBINARY to avoid the hassle.

Besides, your code is awfully duplicated and thus hard as hell to read and maintain. The very idea of exceptions is that you are catching them all once. Also some conditions are useless and you should use not fetchAll() but the fetch method that is appropriate for your task.

And of course, trying to decode not the actual binary value, but its hexdecimal representation displayed by phpmyadmin wouldn't give you any sensible result indeed.

Below is the Minimal, Complete, and Verifiable example you've been asked for. It represents the actual problem and works for anyone who care to run it, as long as correct credentials are provided.

try {
    $pdc = new PDO('mysql:host=localhost;dbname=toy_database;charset=utf8', 'xxxxx', 'xxxxx' );
    $pdc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // let's create a temporary table for the example purpose
    // change varbinary for binary and it goes astray
    $pdc->query("CREATE temporary TABLE try_ur_table 
                 (ur_index int primary key auto_increment,ur_ip varbinary(16))");

    $cd = inet_pton( $_SERVER["REMOTE_ADDR"] );

    $sql = "SELECT ur_index FROM try_ur_table WHERE ur_ip = ? ";
    $rt = $pdc->prepare($sql);
    $rt->execute(array($cd));
    $ur_index = $rt->fetchColumn();

    if ( !$ur_index )
    {
        $sqm = "INSERT INTO try_ur_table SET ur_ip=?";
        $pdc->prepare($sqm)->execute(array($cd));
        // here: that's all you need to get the index
        $ur_index = $pdc->lastInsertId();
    }

    // OK, let's verify
    $sql = "SELECT ur_ip FROM try_ur_table WHERE ur_ip = ? ";
    $rt = $pdc->prepare($sql);
    $rt->execute(array($cd));
    $ur_ip = $rt->fetchColumn();
    var_dump($ur_ip === $cd, bin2hex($ur_ip));

} catch (PDOException $e) {
    error_log($e);
    $output = ini_get('display_errors') ? $e : "There is a temporary problem. Try again later";
    include 'errout.html.php';
    exit();
}