Convert ip 66.249.75.215 to 0x249f94bd7

[larry29936]

How do I update a table and convert say, 66.249.75.215, from one column to another column as 0x249f94bd7. Both columns are varbinary. I'm using PDOConnect and this isn't working:

    $stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;");
    $stmt->execute() ;

I'm getting the following error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1411 Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet_aton in /home/larry/web/test/public_html/index.php:31 Stack trace: #0 /home/larry/web/test/public_html/index.php(31): PDOStatement->execute() #1 {main} thrown in /home/larry/web/test/public_html/index.php on line 31

[benanamen]

Quote

To store values generated by INET_ATON(), use an INT UNSIGNED column

https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet-aton

[gw1500se]

You are trying to convert the literal string ADDRESS. You need ADDRESS to be a variable, hopefully, in the form xxx.xxx.xxx.xxx.

[Barand]

$stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;");

@gw1500se Those look like backticks to me

[gw1500se]

Yep, now that I see the code properly posted with the formatter.

[larry29936]

The code now reads:

  $test = inet_aton($ip);
    echo var_dump($test);
    $stmt = $pdo->prepare("UPDATE download SET ip_address = (?) WHERE ip_address is null;");
    $stmt->execute($test) ;

It errors at "$test = inet_aton($ip);  I never see the var_dump()

Edited May 7, 2020 by larry29936

[Barand]

Of course it does - as already stated, inet_aton() is an SQL function not PHP

[Barand]

mysql> CREATE TABLE `download` (
    ->   `download_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip_address` int(10) unsigned DEFAULT NULL,
    ->   `address` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`download_id`)
    -> );

mysql> INSERT INTO download (address) VALUES ('66.249.75.215');

mysql> UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;

mysql> SELECT address
    ->      , HEX(ip_address) as ip_address
    -> FROM download;
+---------------+------------+
| address       | ip_address |
+---------------+------------+
| 66.249.75.215 | 42F94BD7   |
+---------------+------------+

 

Edited May 7, 2020 by Barand

[larry29936]

@Barand, I need the ip in a "0x249f94bd7" format.

[Barand]

mysql> CREATE TABLE `download` (
    ->   `download_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ip_address` varchar(50) DEFAULT NULL,
    ->   `address` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`download_id`)
    -> );

mysql> INSERT INTO download (address) VALUES ('66.249.75.215');

mysql> UPDATE `download` SET `ip_address` = hex(inet_aton(`ADDRESS`)) WHERE ip_address is null;

mysql> SELECT address
    ->      , ip_address
    -> FROM download;
+---------------+------------+
| address       | ip_address |
+---------------+------------+
| 66.249.75.215 | 42F94BD7   |
+---------------+------------+

 

[benanamen]

57 minutes ago, larry29936 said:

I need the ip in a "0x249f94bd7" format.

Why?

[larry29936]

@benanamen -  I'm comparing to another table has a start and end ip in that format in order to get the country, area, and city from. I also have to deal with IPv6 ip's.

Edited May 7, 2020 by larry29936

[gw1500se]

Leave it in benanamen's format in your database. Change the query result to whatever format you need when you do the compare.

[benanamen]

23 minutes ago, larry29936 said:

I also have to deal with IPv6 ip's.

The you should be using INET6_ATON

24 minutes ago, larry29936 said:

I'm comparing to another table has a start and end ip in that format

Ok, Why is it in that format?

[larry29936]

That's the way it imported into the table. I don't remember where I downloaded the .csv file from.

[benanamen]

1 minute ago, larry29936 said:

That's the way it imported into the table.

Is that the way it is in the CSV file?

[larry29936]

I just checked the csv and it's in xxx.xxx.xxx.xxx format. It's almost 5 million rows of data and the ip's were imported into varbinary(32) fields.

Edited May 7, 2020 by larry29936

[benanamen]

9 minutes ago, larry29936 said:

the ip's were imported into varbinary(32) fields.

And there is your REAL problem.....

[larry29936]

Everything I've seen has said to use varbinary fields for IPv6 ip's.

[larry29936]

16 minutes ago, benanamen said:

And there is your REAL problem.....

So, I should drop the table and recreate it with the ip fields as int(32) unsigned then re-import the .csv? 

[Barand]

1 hour ago, larry29936 said:

Everything I've seen has said to use varbinary fields for IPv6 ip's.

According to my manual...

Quote

Topic: INET6_ATON
Syntax: INET 6_ ATON (expr)

Given an IP v6 or IP v4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian). Because numeric-format IP v6 addresses require more bytes than the largest integer type, the representation returned by this function has the VARBINARY data type: VARBINARY (16) for IP v6 addresses and VARBINARY (4) for IP v4 addresses. If the argument is not a valid address, INET 6_ ATON () returns NULL .

 

[larry29936]

@Barand, so should I re-import the csv ip's into varchar(32) fields or int(32) unsigned fields?

[Barand]

VARBINARY (16) would give you a common type for IPv4 and IPv6