Skip to content

Instantly share code, notes, and snippets.

@brad-anton
Created January 24, 2022 16:06
Show Gist options
  • Save brad-anton/efbe23fb3550e70897e9e158e41cf423 to your computer and use it in GitHub Desktop.
Save brad-anton/efbe23fb3550e70897e9e158e41cf423 to your computer and use it in GitHub Desktop.
Simple BigQuery UDF to identify if a IP address is RFC1918.
CREATE TEMP FUNCTION
is_rfc1918(ip INT64 ) AS (
IF
( ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("192.168.0.0"))
AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("192.168.255.255") ) )
OR ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("10.0.0.0"))
AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("10.255.255.255") ) )
OR ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("172.16.0.0"))
AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("172.31.255.255") ) ),
TRUE,
FALSE ) );
/*
Inovacation Example for testing
*/
WITH
tests AS (
SELECT
'192.168.1.1' AS ip
UNION ALL
SELECT
'172.16.100.1' AS ip
UNION ALL
SELECT
'10.1.1.1' AS ip
UNION ALL
SELECT
'72.14.192.2' AS ip )
SELECT
ip,
is_rfc1918(NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(ip))) AS rfc_1918
FROM
tests
/*
Results:
[
{
"ip": "192.168.1.1",
"rfc_1918": true
},
{
"ip": "172.16.100.1",
"rfc_1918": true
},
{
"ip": "10.1.1.1",
"rfc_1918": true
},
{
"ip": "72.14.192.2",
"rfc_1918": false
}
]
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment