Tom Donovan wrote:
Yes, SQL *functions* only return a single value - but if your database
supports SQL *stored procedures* (like the example), they return a set
of rows; including any extra values to be assigned to environment
variables. For example:
DROP PROCEDURE IF EXISTS digest;
CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
SELECT md5(concat(uname,':', realm ,':', upass)), uctx AS CONTEXT,
uexpiration AS EXPIRES
FROM uaccount WHERE uname = username;
When httpd executes the CALL statement from:
AuthDBDUserRealmQuery "CALL digest(%s, %s)"
this will authenticate the user, and if successful - it will also set
the two httpd environment variables AUTHENTICATE_CONTEXT and
AUTHENTICATE_EXPIRES to values from the database.
Stored procedures are available in MySQL, Oracle, and several other
databases - but some databases, like PostgreSQL and SQLite, do not
support them.
My target is PostgreSQL, and its function has a regional dialect. :-)
Example)
apache=# CREATE OR REPLACE FUNCTION digest_f (TEXT, TEXT) RETURNS RECORD
LANGUAGE 'sql' AS
'SELECT md5(uname || '':'' || $2 || '':'' || upass), context FROM
uaccount WHERE uname = $1';
CREATE FUNCTION
apache=# SELECT * FROM digest_f ('foo', 'sample realm') AS (hash text, context
text);
hash | context
----------------------------------+---------
29dd4bc0ed3d043849fc3efbb05876cd | *:s0:c0
(1 row)
However, I would like to consider this kind of avoidance as an independent
topic (or a workaround for a while). The fixed order parameters are really
an limitation on writing a query for mod_authn_dbd, so it should be fixed.
Thanks,
--
KaiGai Kohei <kai...@kaigai.gr.jp>
