KaiGai Kohei wrote:
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,
I understand why you would prefer the simpler alternative of AuthDBDRealmUserQuery, but I disagree
with your conclusion.
Your argument appears reasonable for the current set of dbd modules, where AuthDBDUserRealmQuery is
the only query specified with multiple parameters - but in future Apache versions there may be more.
For example: http://httpd.apache.org/docs/2.3/mod/mod_session_dbd.html requires five queries
(deletesession, updatesession, insertsession, selectsession and cleansession). Three of these
queries take multiple parameters. We probably won't want to introduce variations of these queries
for whenever the argument order is inconvenient to a particular database. Ditto for other future
dbd modules.
This is a common problem whenever a "SQL API" is used - and the commonly-used solution (stored
procedures, or "functions returning rows" in the case of PostgreSQL) seems like a better way to go
than adding additional options to Apache whenever a configuration requires some extra SQL coding effort.
Nevertheless, it is a matter of opinion - and this is just my 2-cents worth...
Regards,
-tom-
