httpd-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From KaiGai Kohei <kai...@kaigai.gr.jp>
Subject Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)
Date Sun, 17 May 2009 13:55:59 GMT
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 <kaigai@kaigai.gr.jp>

Mime
View raw message