Philip Hazel writes:
> I now have a MYSQL lookup implemented which works as follows:
>
> . mysql_servers must be set to a list of host/database/user/password
> tuples. They are tried in order until a connection succeeds. No
> database need be supplied - it must then be given in the queries.
>
> . connections to the database are cached, based on mysql_servers
> strings.
>
> . The format of the query is an SQL statement, so an example might be
>
> ${lookup mysql{select mailbox from users where id='ph10'}{$value}fail}
>
> . If the result of the query contains more than one field, the data for
> each field in the row is returned, preceded by its name, so the result
> of a lookup such as
>
> ${lookup mysql{select home,name from users where id='ph10'}{$value}fail}
>
> might be
>
> home=/home/ph10 name="Philip Hazel"
>
> Values containing spaces and empty values are double quoted, with
> embedded quotes escaped by \.
>
> . If the result of the query contains just one field, the value is
> passed back verbatim, without a field name, e.g.
>
> Philip Hazel
>
> . If the result of the query yields more than one row, it is all
> concatenated, with a newline between the data for each row.
>
> Comments?
When Tim Bunce designed and wrote the DBI spec and code for Perl, it
took a long time and a lot of hard work before it settled down into a
fairly complete and stable standard. Although it has to cover more
than just lookups, I suspect you'll be running into many of the same
problems over time.
For example, you need some sort of quoting function so that
"select ... where foo = '$bar'" won't bomb out or allow an attacker
to sneak in arbitrary SQL code if $bar contains something like a
single quote. Unfortunately, it's not sufficient just to do the SQL
standard of doubling single quotes since some databases allow/need
further escape sequences. That makes the quoting function dependent
on which database you're connecting to. Perl DBI does it via the
method "$dbh->quote($foo)" but in Exim you may have to have
mysql_quote(), pg_quote, ora_quote() and so on unless you can come
up with a nicer solution.
Then you have to worry about what's in the select statement. Yes,
most queries are going to be simple "select colname from ..." but if
that's all your parser can cope with then you're in for a surprise
when people start trying to do
select foo.name, count(*)
from foo, bar
where foo2.namekey=$namekey and foo.id = bar.id
group by foo.name
or
select quota + priority from foo where name=...
or
select somefunction(foo, bar, baz) from ...
In order to find how many fields you get back, you'll need
database-specific code (unless you want to include your own SQL parser
in Exim :-O). People with some of the big name databases may well ask
for a way to prepare queries in advance (since some tend to be slow
unless you take advantage of such preparation). And then then they'll
want placeholders and a way to fill them in with arguments.
If you don't want to rewrite the relevant Exim internals twenty times
over or have non-portable database lookups (as happened to Perl before
DBI became widespread) then I think you'll want to look before you
leap. Once you release an Exim with a particular API for one database
it may turn out to be very difficult to reengineer for other databases
without breaking the API (both user-visible functions in the config
file and whatever underlying C API you've chosen) or giving
portability problems ("this config file only works for CoolDB").
--Malcolm
--
Malcolm Beattie <mbeattie@???>
Unix Systems Programmer
Oxford University Computing Services
