Table des matières

sqlite ipv4 extension

sqlite ipv4 extension

date this document was last edited : Mon Apr 16 2018. and minor edition (links to sqlite.org) on 2020/12/28

date the C source was last edited : 2011-06-19

You should find the latest version of this file here : http://schplurtz.free.fr/wiki/schplurtziel/sqlite3-ipv4-ext

What is it ?

This is an extension for sqlite3 that adds two simple functions named isinnet and isinrange to the set of SQL functions. those functions deal with IP v4 addresses.

With the isinnet function, you can test if an ip address is within a network, or even if two ips are in the same subnet, just by using SQL commands like these :

try-net.sql

SELECT ISINNET( '172.16.1.193', '172.16.1.0', 24 );
SELECT ISINNET( '172.16.1.193', '172.16.1.0/24' );
-- 	==> 1
SELECT ISINNET( '172.16.1.193', '172.16.1.0', 25 );
SELECT ISINNET( '172.16.1.193', '172.16.1.0/25' );
-- 	==> 0
SELECT ISINNET( '172.16.1.193', '172.16.1.0', '255.255.255.0' );
SELECT ISINNET( '172.16.1.193', '172.16.1.0/255.255.255.0' );
-- 	==> 1
SELECT ISINNET( '172.16.1.193', '172.16.1.0', '255.255.255.128' );
SELECT ISINNET( '172.16.1.193', '172.16.1.0/255.255.255.128' );
-- 	==> 0
 
CREATE TABLE ip_add (
        ip      VARCHAR( 15 )
);
INSERT INTO ip_add VALUES('172.16.1.40');
INSERT INTO ip_add VALUES('172.16.1.93');
INSERT INTO ip_add VALUES('172.16.1.204');
INSERT INTO ip_add VALUES('172.16.4.203');
INSERT INTO ip_add VALUES('172.16.4.205');
INSERT INTO ip_add VALUES('172.16.4.69');
INSERT INTO ip_add VALUES('10.0.1.204');
INSERT INTO ip_add VALUES('10.0.1.16');
INSERT INTO ip_add VALUES('10.1.0.16');
INSERT INTO ip_add VALUES('192.168.1.5');
INSERT INTO ip_add VALUES('192.168.1.7');
INSERT INTO ip_add VALUES('192.168.1.19');
 
SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0', 16 );
SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0/16' );
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
--	172.16.4.203
--	172.16.4.205
--	172.16.4.69
 
SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0', 24 );
SELECT ip FROM ip_add WHERE ISINNET( ip, '172.16.1.0/24' );
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
 
SELECT * FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0', 1 );
SELECT * FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0/1' );
--	10.0.1.204
--	10.0.1.16
--	10.1.0.16
 
DELETE FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0', 1 );
DELETE FROM ip_add WHERE NOT ISINNET( ip, '128.0.0.0/1' );
 
SELECT * FROM ip_add;
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
--	172.16.4.203
--	172.16.4.205
--	172.16.4.69
--	192.168.1.5
--	192.168.1.7
--	192.168.1.19

With the isinrange function, you can also test if an address is within a certain range. A range is defined by a start address and a stop address. Note that the start address may be smaller or greater than the stop address.

try-range.sql

SELECT ISINRANGE( '172.16.1.132', '172.16.1.19', '172.16.1.154' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.19-172.16.1.154' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.154', '172.16.1.19' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.154-172.16.1.19' );
-- 	==> 1
SELECT ISINRANGE( '172.16.1.132', '172.16.1.19', '172.16.1.132' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.19-172.16.1.132' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.132', '172.16.1.19' );
SELECT ISINRANGE( '172.16.1.132', '172.16.1.132-172.16.1.19' );
-- 	==> 1
SELECT ISINRANGE( '172.16.1.132', '10.0.0.19', '10.255.254.132' );
SELECT ISINRANGE( '172.16.1.132', '10.0.0.19-10.255.254.132' );
SELECT ISINRANGE( '172.16.1.132', '10.0.0.132', '10.255.254.19' );
SELECT ISINRANGE( '172.16.1.132', '10.0.0.132-10.255.254.19' );
-- 	==> 0
SELECT ISINRANGE( '172.16.1.193', '172.14.1.0', '198.23.43.17' );
SELECT ISINRANGE( '172.16.1.193', '172.14.1.0-198.23.43.17' );
SELECT ISINRANGE( '172.16.1.193', '198.23.43.17', '172.14.1.0' );
SELECT ISINRANGE( '172.16.1.193', '198.23.43.17-172.14.1.0' );
-- 	==> 1
 
CREATE TABLE ip_add (
        ip      VARCHAR( 15 )
);
INSERT INTO ip_add VALUES('172.16.1.40');
INSERT INTO ip_add VALUES('172.16.1.93');
INSERT INTO ip_add VALUES('172.16.1.204');
INSERT INTO ip_add VALUES('172.16.4.203');
INSERT INTO ip_add VALUES('172.16.4.205');
INSERT INTO ip_add VALUES('172.16.4.69');
INSERT INTO ip_add VALUES('10.0.1.204');
INSERT INTO ip_add VALUES('10.0.1.16');
INSERT INTO ip_add VALUES('10.1.0.16');
INSERT INTO ip_add VALUES('192.168.1.5');
INSERT INTO ip_add VALUES('192.168.1.7');
INSERT INTO ip_add VALUES('192.168.1.19');
 
SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0', '172.16.255.255' );
SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0-172.16.255.255' );
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
--	172.16.4.203
--	172.16.4.205
--	172.16.4.69
 
SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0', '172.16.1.230' );
SELECT ip FROM ip_add WHERE ISINRANGE( ip, '172.16.1.0-172.16.1.230' );
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
 
SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, '0.0.0.0', '172.16.1.255' );
SELECT * FROM ip_add WHERE NOT ISINRANGE( ip, '0.0.0.0-172.16.1.255' );
-- 172.16.4.203
-- 172.16.4.205
-- 172.16.4.69
-- 192.168.1.5
-- 192.168.1.7
-- 192.168.1.19
 
DELETE FROM ip_add WHERE NOT ISINRANGE( ip, '30.0.0.0', '200.0.0.0' );
DELETE FROM ip_add WHERE NOT ISINRANGE( ip, '30.0.0.0-200.0.0.0' );
 
SELECT * FROM ip_add;
--	172.16.1.40
--	172.16.1.93
--	172.16.1.204
--	172.16.4.203
--	172.16.4.205
--	172.16.4.69
--	192.168.1.5
--	192.168.1.7
--	192.168.1.19

What is the license for this small code ?

This extension is public domain.

what version of sqlite is it for ?

This extension was first written for sqlite 3.5.9, and it works for me with sqlite3 3.23.1 (current brew sqlite3 version as of Mon Apr 16 2018).

Now, there are 2 things in the archive file :

a dynamic extension that you can load from within your programs or from within the CLI sqlite3 itself (if it has been compiled with the «load extension» feature enabled).
- It should work with any sqlite3 version that supports loadable extensions.
some patches for sqlite source files. When the patch is applied, the functions isinnet and isinrange are added to the core functions that sqlite understands. I don't update these patches very often because I don't really need them (the loadable extension suits my need). So, there are only a few patches available. To use this, you will have to download the entire sqlite source archive and completely rebuild it. But once it is done, there is no need to load an extension. The patches are against the autoconf amalgamation source code.

Please, note that in either case, this little extension is only distributed as source code. You will have to compile it.

How does it work internally ?

ip addresses are simply stored as strings
netmasks are stored as strings, too
netmask, if specified as a number of bits, may be stored as strings OR integer (sqlite does not really care anyway).
each time the function isinnet or isinrange is called it tries to parse the ips and netmask, into 32 bits numbers
If there is an error : numbers are too big, or the format is not correct, then the functions return NULL
Otherwise
- isinnet returns 1 if (address & mask) = (network & mask),
  it returns 0 (zero) if not.
- isinrange returns 1 if (start <= address && address <= stop ) or (stop <= address && address <= start),
  it returns 0 (zero) if not.

since isinnet computes (address & mask) = (network & mask), it is possible to check if two addresses are in the same subnet with this function.

for example, to check if 172.16.132.4 is in the same /25 subnet as 172.16.192.45, you can use :

SELECT isinnet( '172.16.132.4', '172.16.192.45', 25 )

To find all ip in the same /16 subnet as 172.16.132.4 you can use something like :

SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', 16 );
SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip, 16 );

or even

SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', '16' );
SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4', '255.255.0.0' );
SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip, '255.255.0.0' );
SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4/16' );
SELECT ip FROM TABLE WHERE isinnet( ip, '172.16.132.4/255.255.0.0' );
SELECT ip FROM TABLE WHERE isinnet( '172.16.132.4', ip || '/16' );

The latter is of course a bad idea, but it should work (not tested).

the first two are the fastest because they require less parsing than the others.

What to download ?

Just download ipv4-ext.tar.bz2

What are all those files in the archive and how to use them ?

First you have to decide wether you want to build the extension, or if you want to entirely re build sqlite, modified to include the isinnet and isinrange functions.

Once you have decided, read the list below and do whatever you think is appropriate.

Makefile.MacOSX

This is a makefile for building the dynamic extension under MacOSX (tested under 10.5 and 10.6 10.9 and recently 10.13.4)

Fisrt edit this file and set the INCLUDE=-I/… line to a directory that contains the file sqlite3ext.h. For example, if you installed sqlite3 via brew, the line could read INCLUDE = -I/usr/local/opt/sqlite/include

Then, run

make -f Makefile.MacOSX

and you should obtain a file named libsqliteipv4.dylib. This file is the extension you are going to load. Feel free to copy this file wherever you see fit.

Makefile.linux

This is a makefile for building the dynamic extension under GNU/Linux

Fisrt edit the file and set the INCLUDE=-I/… line to a directory that contains the file sqlite3ext.h

Then, run

make -f Makefile.linux

and you should obtain a file named libsqliteipv4.so. This file is the extension you are going to load.

ipv4-ext.c

This is the source file of the extension.

ipv4-sqlite-3.7.10.0-amalgamation.patch

This file outdated and probably completely unusable

This is a patch for the file sqlite-autoconf-3071000/sqlite3.c of sqlite-3.7.10.0. Once applied, it allows to build sqlite with the isinnet and isinrange functions. There is then no need to load an extension. If you want to incorporate SQLite code into a wider program, then the functions isinnet and isinrange will also go into you wider program, provided you #define SQLITE_IPV4_EXT_FUNC 1 somewhere in your source.

Here are the steps you must follow to build sqlite with builtin isinnet and isinrange SQL functions.

download sqlite-autoconf-3071000.tar.gz from http://sqlite.org/download.html

untar the archive with something like

gzip -cd sqlite-autoconf-3071000.tar.gz | tar xf -

put the patch file in the newly created directory sqlite-autoconf-3071000

then cd into the sqlite-autoconf-3071000 directory and apply the patch.

cd sqlite-autoconf-3071000
patch -p1 <ipv4-sqlite-3.7.10.0-amalgamation.patch

Configure using -DSQLITE_IPV4_EXT_FUNC=1, make, and install the software as you would normally. for example:

env CFLAGS=-DSQLITE_IPV4_EXT_FUNC=1 ./configure --prefix=/usr/local --enable-readline
make
sudo make install

or, something like this

env LDFLAGS=-ldl CFLAGS='-DSQLITE_IPV4_EXT_FUNC=1 -DSQLITE_ENABLE_RTREE=1'\
' -DSQLITE_ENABLE_FTS3=1' \
./configure --prefix=/usr/local --enable-readline --enable-threadsafe \
--enable-load-extension
make
sudo make install

ipv4-sqlite-3.7.6.3-amalgamation.patch

This file more outdated and probably completely unusable

This is a patch for the file sqlite3.c of sqlite-3.7.6.3. Once applied, it allows to build sqlite with the isinnet and isinrange functions…

See description for version 3.7.10.0 for details and substitute 3.7.10.0 with 3.7.6.3 .

ipv4-sqlite-3.6.23.1-amalgamation.patch

This file really outdated and probably definitely completely unusable

This is a patch for the file sqlite3.c of sqlite-3.6.23.1. Once applied, it allows to build sqlite with the isinnet and isinrange functions…

See description for version 3.7.10.0 for details and substitute 3.7.10.0 with 3.6.23.1 .

sqlite3-ipv4-ext.txt

the file you are reading now, with dokuwiki markup

How to load the extension

Please, see https://www.sqlite.org/loadext.html. But, here follow some brief explanations

In your application,
call sqlite3_enable_load_extension(db,1) to allow loading external libraries. Then load the library libsqliteipv4 using sqlite3_load_extension; the third argument should be 0.
from within the sqlite3 CLI, if it has been built with load extension enabled, then try this :
```
sqlite> .load './libsqliteipv4.so'
```
or
```
sqlite> SELECT load_extension('/path/to/libsqliteipv4.so');
```
On a Macintosh, you would use .dylib instead of .so, on windows, I guess you would use .dll instead of .so.

BUGS

isinrange probably works only on little endian machines (x86 and the like).

Questions and answers

Can I use the patch for other versions ?

Yes, probably. Well in fact, this true back in 2012. nowadays (2018) this is probably wrong. but giveit a try, you may be lucky.

Since the extension is such a simple and small change to sqlite, it is reasonable to try to patch a version with a patch made for another version.

For example, if you try to apply patch for 3.6.23.1 to 3.7.6.3, it will work. patch indicates that there is an offset, but apart from this, this is OK.

sqlite-autoconf-3070603i $ patch -p1 </../ipv4-sqlite-3.6.23.1-amalgamation.patch
patching file sqlite3.c
Hunk #1 succeeded at 82777 (offset 9423 lines).
Hunk #2 succeeded at 83273 (offset 9413 lines).
sqlite-autoconf-3070603i $