IP address and the timestamp storage

Question

I'm trying to store IP address and a PHP session id to uniquely identify a user, to manage a user queue to control a (one) hardware device through internet with a token. When a user has the token, has permission to control the device for 2 minutes. Then I also need a timestamp, the time on which the user asked for the token.

What is the correct field type for the IP address and the timestamp in SQLite? I need an easy way to retrieve a queue from the database matching a "text" cookie session id and an IP, using timestamp to order and filter. Should I use an integer or text field? Are there functions to work with those types?

Answer 1

Here are two sqlite C function extensions to convert strings of form 128.1.42.15 into 32-bit integers and back, the boilerplate was vaguely adapted from the sample extension: https://www.sqlite.org/src/file/ext/misc/rot13.c

ip.c

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>
#include <stdint.h>

#include <arpa/inet.h> /* pton, ntop */

/** ipv4 String to Integer */
static void ips2i(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *zIn;
  struct in_addr addr;
  int s;

  assert( argc==1 );
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  zIn = (const unsigned char*)sqlite3_value_text(argv[0]);
  s = inet_pton(AF_INET, (const char * restrict)zIn, &addr);
  if (s <= 0) {
    /* TODO report error nicely. */
  }
  /* Need int64 otherwise overflows int32_t. There is no unsigned version apparently. */
  sqlite3_result_int64(context, ntohl(addr.s_addr));
}

/** ipv4 Integer to String */
static void ipi2s(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  struct in_addr addr;
  char zOut[INET_ADDRSTRLEN];

  assert( argc==1 );
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  addr.s_addr = ntohl(sqlite3_value_int64(argv[0]));
  inet_ntop(AF_INET, &addr, zOut, INET_ADDRSTRLEN);
  /* TODO any way to avoid the strlen here? inet_ntop does not seem to return it. */
  sqlite3_result_text(context, (char*)zOut, strlen(zOut), SQLITE_TRANSIENT);
}

int sqlite3_ip_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;
  rc = sqlite3_create_function(db, "ips2i", 1,
                   SQLITE_UTF8|SQLITE_INNOCUOUS|SQLITE_DETERMINISTIC,
                   0, ips2i, 0, 0);
  if (rc == 0) {
    rc = sqlite3_create_function(db, "ipi2s", 1,
                    SQLITE_UTF8|SQLITE_INNOCUOUS|SQLITE_DETERMINISTIC,
                    0, ipi2s, 0, 0);
  }
  return rc;
}

Compile:

gcc -shared -fPIC -ggdb3 -O0 -pedantic-errors -std=c99 -Wall -Wextra -o 'ip.so' 'ip.c'

Use ips2i from ip.so:

sqlite3 :memory: '.load ./ip' "SELECT printf('%08x', ips2i('1.128.2.10'))"

Output:

0180020a

where:

• 1 = 0x01
• 128 = 0x80
• 2 = 0x02
• 10 = 0x0a

Use ipi2s to convert back:

sqlite3 :memory: '.load ./ip' "SELECT ipi2s(0x0180020a)"

output:

1.128.2.10

IPv6 support would require:

• AF_INET -> AF_INET6
• INET_ADDRSTRLEN -> INET6_ADDRSTRLEN
• learn how to use integers larger than 64 bits, since IPv6 has 128 bits

Then we should learn how to report errors in SQLite, and merge this into SQLean: https://github.com/nalgeon/sqlean/issues/86

Answer 2

SQLite is weak type, but supports type affinity. At all SQLite only support a small range of "column types" ("type affinities")

INTEGER
REAL
NUMERIC
TEXT
BLOB
NONE

However, in your case you can choose: You can store a timestamp as UNIX-timestamp in INTEGER, or as datetime formated string in TEXT. See the section "1.2 Date and Time Datatype" in the document provided by the link above. There are Date And Time Functions to help you handle this kind of data.

The IP can be stored as INTEGER after converting it into one: ip2long(). Or you store it as TEXT too. I suggest to use the former one.

Update: If you choose to use INTEGER, you will be limited to storing IPv4 addresses only, because SQLite can only store 64 bit integers, whereas IPv6 are 128 bit. However, because ip2long only works with IPv4 the range is not the only issue.