Thread: Storing PHP sessions into a PostgreSQL database

Hi all,
I'm trying to store PHP sessions into a database, but I'm having problems with
sess_write() function.

I created the table 'sessions' this way:

CREATE TABLE sessions (
    sesskey  CHAR(32)        PRIMARY KEY,
   expiry   NUMERIC(11, 0)  NOT NULL,
   value    TEXT
);

The function sess_write() looks like:

function sess_write($key, $val) {
        global $SESS_DBH, $SESS_LIFE;

        $expiry = time() + $SESS_LIFE;
        $value = addslashes($val);

        $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')";
        $qid = pg_exec($SESS_DBH, $qry);

        if (! $qid) {
                $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'";
                $qid = pg_exec($SESS_DBH, $qry);
        }

        return $qid;
}

The first time I browse the test page, PHP calls the session handling function
in the right way, i.e.:

sess_open()
sess_read(...)  /* here the select returns 0 rows, i.e. no state variables */
sess_write(...) /* here the initial value is inserted into database with the
                 * INSERT INTO query
                 */
sess_close(...)

If I reload the test page with the session variable $count, this is what
happens:

sess_open()
sess_read(...)  /* here the select returns 'count|i:0;' */
sess_write(...) /* the INSERT INTO fails because sesskey is a primary key */
boooom!

The script ends without doing the UPDATE inside sess_write() at the point
marked with the arrow:

function sess_write($key, $val) {
        global $SESS_DBH, $SESS_LIFE;

        $expiry = time() + $SESS_LIFE;
        $value = addslashes($val);

        $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')";
        $qid = pg_exec($SESS_DBH, $qry);
------>>
        if (! $qid) {
                $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'";
                $qid = pg_exec($SESS_DBH, $qry);
        }

        return $qid;
}

Even an fwrite($fp, "before if\n") doesn't get executed!!

This solved the problem:

function sess_write($key, $val) {
        global $SESS_DBH, $SESS_LIFE;

        $expiry = time() + $SESS_LIFE;
        $value = addslashes($val);

        $qry = "SELECT 1 FROM sessions WHERE sesskey = '$key'";
        $qid = pg_exec($SESS_DBH, $qry);

        if (pg_numrows($qid) > 0) {
                $qry = "UPDATE sessions SET expiry = $expiry, value = '$value' WHERE sesskey = '$key'";
                $qid = pg_exec($SESS_DBH, $qry);
        } else {
                $qry = "INSERT INTO sessions VALUES ('$key', $expiry, '$value')";
                $qid = pg_exec($SESS_DBH, $qry);
        }

        return $qid;
}

This works, but I think it's very ugly. I don't want to do an extra SELECT for
each PHP page served.

Does anybody have a solution for this problem?

    Francesco Casadei