Hello,
Le 22 juil. 08 à 20:17, Alexey Pechnikov a écrit :
>
> Hello!
>
> Can I found functions for ip address/mask operations?
>
> Best regards, Alexey.
I am a new user, and was looking for ip network/mask function too.
I found none, so I just wrote an extension (tested on linux and
MacOSX 10.5, with sqlite 3.5.9). this was both an exercise and
something useful to me. I was wondering whether to share, so your
question is an asnwer for me.
It defines one single function for ip v4 only :
isinnet( 'ip', 'network', mask ) or
isinnet( 'ip', 'network/mask' )
I don't know how fast it is, though, I just have a few hundreds ip
to manage. I suspect it is possible to write something that runs
really, really faster by storing ip as integer. As it is, it scans
and converts every ip in IP.IP.IP.IP notation.
Hope this helps
schplurtz
file ipv4-ext.c :
/*
This library will provide the ipv4 ISINNET function in
SQL queries.
The author claims no copyright on this library
ISINNET( ip, network, mask )
mask may be specified the CIDR way as a number of bits,
or as a standard 4 bytes notation.
if CIDR notation is used, mask may be a string ('13' for
example) or a number (13 for example)
ISINNET returns NULL if there is any kind of error, mainly :
- strings are not valid IPV4 addresses or
- number of bits is not a number or is out of range
ISINNET returns 1 if (ip & mask) = (net & mask)
ISINNET returns 0 otherwise
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( 16 )
);
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
programm template was taken from
http://sqlite.org/contrib/
http://sqlite.org/contrib/download/extension-functions.c?get=25
Mar 22 jul 2008 22:06:37 CEST
Schplurtz le deboulonne.
Instructions (mostly from extension-functions.c):
1) Compile with
Linux:
gcc -fPIC -lm -shared ipv4-ext -o libsqliteipv4.so
Mac OS X:
gcc -fno-common -dynamiclib ipv4-ext.c -o libsqliteipv4.dylib
(You may need to add flags
-I /opt/local/include/
if your sqlite3 is installed from Mac ports, or
-I /sw/include/
if installed with Fink.)
Please, note that sqlite3 from macport 1.6.0 is not
compiled with
--enable-load-extension. So you cannot try this extension
from
within the sqlite3 shell.
The same applies to leopard's /usr/bin/sqlite3
2) In your application, call sqlite3_enable_load_extension(db,1) to
allow loading external libraries. Then load the library
libsqlitefunctions
using sqlite3_load_extension; the third argument should be 0.
See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
3) Use, for example:
SELECT ISINNET( '10.0.0.1', '10.0.0.0', 8 );
Note: Loading extensions is by default prohibited as a
security measure; see "Security Considerations" in
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
If the sqlite3 program and library are built this
way, you cannot use these functions from the program, you
must write your own program using the sqlite3 API, and call
sqlite3_enable_load_extension as described above.
If the program is built so that loading extensions is permitted,
the following will work:
sqlite> SELECT load_extension('./libsqliteipv4.dylib');
sqlite> select isinnet( '123.234.210.109', '123.123.23.18',
'255.248.0.0' );
0
Alterations:
The instructions are for Linux or Mac OS X; users of other OSes may
need to modify this procedure. If you do not
wish to make a loadable module, #define COMPILE_SQLITE_STATIC_EXTENSION
Liam Healy (with little modifications by Schplurtz le deboulonne)
*/
#ifdef COMPILE_SQLITE_STATIC_EXTENSION
# include "sqlite3.h"
#else
# include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#endif
#include <stdlib.h>
#include <sys/types.h>
#include <string.h>
#define NUMTOMASK( x ) ( ~ ( (((u_int32_t)1) << (32 - x)) -1 ) )
static int iptonum( const char *, u_int32_t *, int );
/*
* The isinnet() SQL function returns true if ip is in network/netmask.
* isinnet( '172.16.1.23', '172.16.1.0', 18 )
* isinnet( '172.16.1.23', '172.16.1.0', '18' )
* isinnet( '172.16.1.23', '172.16.1.0', '255.255.192.0' )
*/
static void isinnet3Func(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
u_int32_t ad, net, mask;
int64_t i;
if( sqlite3_value_type(argv[0]) == SQLITE_NULL ||
sqlite3_value_type(argv[1]) == SQLITE_NULL ||
sqlite3_value_type(argv[2]) == SQLITE_NULL ){
sqlite3_result_null(context);
} else {
if( iptonum( (char*)sqlite3_value_text(argv[0]), &ad, 0 ) ||
iptonum( (char*)sqlite3_value_text(argv[1]), &net, 0 )
) {
sqlite3_result_null(context);
return;
}
if( SQLITE_INTEGER == sqlite3_value_type(argv[2]) ) {
i = sqlite3_value_int64(argv[2]);
if( i < 0 || i > 32 ) {
sqlite3_result_null(context);
return;
}
mask = NUMTOMASK( i );
} else {
if( iptonum( (char*)sqlite3_value_text(argv[2]), &mask,
1 )) {
sqlite3_result_null(context);
return;
}
}
sqlite3_result_int( context, (ad & mask) == (net & mask ) );
}
}
/*
* The isinnet() SQL function returns true if ip is in network/netmask.
* isinnet( '172.16.1.23', '172.16.1.0/18' )
* isinnet( '172.16.1.23', '172.16.1.0/255.255.192.0' )
*/
static void isinnet2Func(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
u_int32_t ad, net, mask;
int64_t i;
char snet[32], *smask;
if( sqlite3_value_type(argv[0]) == SQLITE_NULL ||
sqlite3_value_type(argv[1]) == SQLITE_NULL ){
sqlite3_result_null(context);
return;
}
if( iptonum( (char*)sqlite3_value_text(argv[0]), &ad, 0 ) ) {
sqlite3_result_null(context);
return;
}
snet[31]=0;
strncpy( snet, (char*)sqlite3_value_text(argv[1]), 31 );
if( ! (smask = strchr( snet, '/' )) ) {
sqlite3_result_null(context);
return;
}
*smask++=0;
if( iptonum( snet, &net, 0 ) || iptonum( smask, &mask, 1 )) {
sqlite3_result_null(context);
return;
}
sqlite3_result_int( context, (ad & mask) == (net & mask) );
}
/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here. This is usually the only exported symbol in
** the shared library.
*/
int sqlite3_extension_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "isinnet", 3, SQLITE_ANY, 0,
isinnet3Func, 0, 0);
sqlite3_create_function(db, "isinnet", 2, SQLITE_ANY, 0,
isinnet2Func, 0, 0);
return 0;
}
static int iptonum( const char *s, u_int32_t *ad, int allowcidrmask ) {
char *la;
u_int32_t tmp;
tmp=strtoul( s, &la, 10 );
if( '\0' == *la && allowcidrmask && tmp <= 32 ) {
*ad=NUMTOMASK( tmp );
return 0;
}
if( '.' != *la || tmp > 255 )
return 1;
*ad=tmp;
tmp=strtoul( la + 1, &la, 10 );
if( '.' != *la || tmp > 255 )
return 2;
*ad <<=8;
*ad |= tmp;
tmp=strtoul( la + 1, &la, 10 );
if( '.' != *la || tmp > 255 )
return 3;
*ad <<=8;
*ad |= tmp;
tmp=strtoul( la + 1, &la, 10 );
if( '\0' != *la || tmp > 255 )
return 4;
*ad <<=8;
*ad |= tmp;
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
