Mailing List Archive: exim: users

Connecting to mysql

Index | Next | Previous | View Threaded

addw at phcomp

Mar 4, 2010, 7:15 AM

Post #1 of 3 (403 views)
Permalink

Connecting to mysql

I am doing a mysql lookup something like this:

${lookup mysql{SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}

It works -- in isolation. The problem is that I have more than one different database that
exim may play with (I am running the greylisting code that I put out a few weeks ago).

These are different databases with a different username/password. How to handle in exim:

${lookup mysql{servers=localhost(/var/lib/mysql/mysql.sock)/imap/exim_auth/YYYYY; SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}

this works, but it puts a username/password in the exim config file - that I want to avoid.

I have in a file included:

hide mysql_servers = localhost/exim_db/exim_user/XXXXXX:\
localhost/imap/exim_auth/YYYYY

So I tried:

${lookup mysql{servers=localhost/imap; SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}

but that doesn't work since the argument to servers is either the name of the server, or all 4 parts.

hide mysql_servers = localhost/exim_db/exim_user/XXXXXX:\
localhost2(/var/lib/mysql/mysql.sock)/imap/exim_auth/YYYYY

I need the socketname since localhost is recognised specially.

${lookup mysql{servers=localhost2; SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}

Doesn't work.

OK: how about a fix where the servers= could have a varying number of parameters, if it
has not got the full 4 it tries to fill them in from what it finds in mysql_servers
where the number of paramaters that it has got matches.
In short: my first attempt where I specify ''servers=localhost/imap;'' would work as I
had hoped.

The other way of doing it would be to give the generic exim user read access to the 'users' table and specify the table as imap.users:

${lookup mysql{SELECT home, uid, gid FROM imap.users WHERE userid = '$local_part' and domain = '$domain'}}

If this is thought a good idea I will do the coding work.

Regards

--
Alain Williams
Linux/GNU Consultant - Mail systems, Web sites, Networking, Programmer, IT Lecturer.
+44 (0) 787 668 0256 http://www.phcomp.co.uk/
Parliament Hill Computers Ltd. Registration Information: http://www.phcomp.co.uk/contact.php
Past chairman of UKUUG: http://www.ukuug.org/
#include <std_disclaimer.h>

--
## List details at http://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

exim-users at spodhuis

Mar 4, 2010, 10:41 AM

Post #2 of 3 (388 views)
Permalink

Re: Connecting to mysql [In reply to]

On 2010-03-04 at 15:15 +0000, Alain Williams wrote:
> I am doing a mysql lookup something like this:
>
> ${lookup mysql{SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}

You're missing the use of the quote_mysql expansion operator, to protect
you from SQL injection attacks in the local-part -- do remember that
this is a perfectly valid email address, designed to reach me (by
configuration, not catchall):

"X'); DROP TABLE domains; DROP TABLE passwords; --"@spodhuis.org

So that should be:

${lookup mysql{SELECT home, uid, gid FROM users WHERE userid = '${quote_mysql:$local_part}' and domain = '${quote_mysql:$domain}'}}

> These are different databases with a different username/password. How to handle in exim:

Hrm. Shame that my.cnf doesn't let you define defaults for multiple
hosts. You can get around that, using multiple groups though ...

What you might look at is using mysql_options() with
MYSQL_READ_DEFAULT_GROUP on the mysql_handle immediately after the
mysql_init() call in src/lookups/mysql.c and supply a user-specifiable
group-name. Then you can define your hosts, passwords etc in
/etc/my.cnf and just refer to them with a group.

You might do something hacky like say "if the host part is empty (ie, it
starts with a /) then parse as key=value in each / section". That would
let you expand the syntax to supply as much or as little as wanted and
supply alternatives too, so:

${lookup mysql{servers=/group=db1; SELECT ...}}

Another option, with less coding, is to set up CNAMEs for localhost and
use a different one for each DB. Hacky, but gets you something quickly.

-Phil

--
## List details at http://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

addw at phcomp

Mar 4, 2010, 3:51 PM

Post #3 of 3 (383 views)
Permalink

Re: Connecting to mysql [In reply to]

On Thu, Mar 04, 2010 at 10:41:05AM -0800, Phil Pennock wrote:
> On 2010-03-04 at 15:15 +0000, Alain Williams wrote:
> > I am doing a mysql lookup something like this:
> >
> > ${lookup mysql{SELECT home, uid, gid FROM users WHERE userid = '$local_part' and domain = '$domain'}}
>
> You're missing the use of the quote_mysql expansion operator, to protect
> you from SQL injection attacks in the local-part -- do remember that
> this is a perfectly valid email address, designed to reach me (by
> configuration, not catchall):

Yes: that is in my 'production' version - wasn't in the test.

> "X'); DROP TABLE domains; DROP TABLE passwords; --"@spodhuis.org
>
> So that should be:
>
> ${lookup mysql{SELECT home, uid, gid FROM users WHERE userid = '${quote_mysql:$local_part}' and domain = '${quote_mysql:$domain}'}}
>
> > These are different databases with a different username/password. How to handle in exim:

> You might do something hacky like say "if the host part is empty (ie, it
> starts with a /) then parse as key=value in each / section". That would
> let you expand the syntax to supply as much or as little as wanted and
> supply alternatives too, so:
>
> ${lookup mysql{servers=/group=db1; SELECT ...}}

> Another option, with less coding, is to set up CNAMEs for localhost and
> use a different one for each DB. Hacky, but gets you something quickly.

Yes I tried that, didn't work as I wanted it, exim understands 'localhost' specially,
and it means hacking /etc/hosts.

--
Alain Williams
Linux/GNU Consultant - Mail systems, Web sites, Networking, Programmer, IT Lecturer.
+44 (0) 787 668 0256 http://www.phcomp.co.uk/
Parliament Hill Computers Ltd. Registration Information: http://www.phcomp.co.uk/contact.php
Past chairman of UKUUG: http://www.ukuug.org/
#include <std_disclaimer.h>

--
## List details at http://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads