sqlgrey-users

[Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

Hi List,

Truly impressive what sqlgrey does to the amount of spam I receive
(not). However I've identified a bug in both 1.1.3 and 1.3.0 which
causes sqlgrey to crash (and hence the mail server to reject all
incoming mail!): 

Some spammers nowadays use very bogus sender names, such as file names &
paths to documents including backslashes, etc. If sqlgrey encounters one
of these, it crashes with this log (I've replaced the "@" with an "-at-"
so that the list mailer will not hide the address):

Nov 16 00:29:38 sun sqlgrey[9064]: new: 219.254.35.115:
c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt-at-mail.epelle.co.kr -> [my e-mail address]
Nov 16 00:29:38 sun sqlgrey[9064]: Warning: couldn't do query: INSERT
INTO connect (sender_name, sender_domain, ip_addr, rcpt, first_seen)
VALUES('c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt', 'mail.epelle.co.kr', '219.254.35.115', '[my e-mail address]', NOW()): , sleeping and reconnecting to DB
Nov 16 00:29:48 sun sqlgrey[9064]: warning: Database handle destroyed
without explicit disconnect at /usr/bin/sqlgrey line 178.
Nov 16 00:29:48 sun sqlgrey[9064]: fatal: Error: db reconnection
failed:  at /usr/bin/sqlgrey line 77.
Nov 16 00:29:48 sun postfix/smtpd[5045]: warning: premature end-of-input
on 127.0.0.1:2501 while reading input attribute name

I'm getting these mails about twice a day, so my mail server is
meanwhile more down than up...

I believe sqlgrey would need some (more -- I understand some has been
introduced since 1.2.0) sanity checks about values it wants to insert
into the data base. I smell targeted exploits being possible here...

HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 12:36 :

>[...]
>c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt-at-mail.epelle.co.kr -> [my e-mail address]
>Nov 16 00:29:38 sun sqlgrey[9064]: Warning: couldn't do query: INSERT
>INTO connect (sender_name, sender_domain, ip_addr, rcpt, first_seen)
>VALUES('c:documentsandsettingsadministratordesktopgevaliagevaliafroms.txt', 'mail.epelle.co.kr', '219.254.35.115', '[my e-mail address]', NOW()): , sleeping and reconnecting to DB
>Nov 16 00:29:48 sun sqlgrey[9064]: warning: Database handle destroyed
>without explicit disconnect at /usr/bin/sqlgrey line 178.
>Nov 16 00:29:48 sun sqlgrey[9064]: fatal: Error: db reconnection
>failed:  at /usr/bin/sqlgrey line 77.
>Nov 16 00:29:48 sun postfix/smtpd[5045]: warning: premature end-of-input
>on 127.0.0.1:2501 while reading input attribute name
>[...]
>  
>


If the INSERT can't be done due to some malformed value, this is 
expected. What isn't expected in 1.2.0 or 1.3.x is a malformed valued 
hitting the database.
What surprises me is that your sample shows a 65 characters sender_name 
which :
- is illegal per RFC (but this is a spammer OK);
- is taken into account by sqlgrey-1.2.0 and 1.3.x : there's code 
explicitly trucating sender_name to 64 chars before putting it in database.

I must confess I didn't test this case after coding the protection, but 
I just did and it worked :

c:doc...@ma...
is put in the connect table as :
'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
(notice the missing 't')

My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.


>I'm getting these mails about twice a day, so my mail server is
>meanwhile more down than up...
>
>I believe sqlgrey would need some (more -- I understand some has been
>introduced since 1.2.0) sanity checks about values it wants to insert
>into the data base. I smell targeted exploits being possible here...
>  
>

In this particular case, only a DoS (killing sqlgrey at will) would have 
been possible, properly quoting values (which is done in 1.2.0) solves 
the '; DELETE ...' exploits.

Best regards,

Lionel.

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

On Wed, 2004-11-17 at 21:09, Lionel Bouton wrote:

> I must confess I didn't test this case after coding the protection, but 
> I just did and it worked :
> 
> c:doc...@ma...
> is put in the connect table as :
> 'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
> (notice the missing 't')

I suspect the sender contains "illegal" characters such as the
backslash. Did you consider this? I'm wondering what an <INSERT ...
("C:\DocumentsAndSettings...")..> would do.

> 
> My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.
Nack. I only have one version here which is (well, says through "sqlgrey
--version") 1.3.0. I had the crash a few times with 1.1.3 upon which I
upgraded to 1.3.0. There's a remote possibility that I didn't restart
sqlgrey after upgrading, but I strongly doubt it, as I have a log entry
(sqlgrey starting) which is younger than the mtime of the (new)
/etc/sqlgrey/ directory.

Anyway, I'm sure that now the correct version is running. I'll observe
it and report back if it crashes again.

HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

Lionel,

Pls don't dig deep into the long sender name issue, it seems I have
screwed up while going through my maillogs. The "sender too long" is in
fact from the old version of sqlgrey. I have now identified that 1.3.0
crashed differently:

Nov 16 19:16:55 sun sqlgrey[18895]: Error: couldn't access domain_awl
table: FATAL:  This connection has been terminated by the administrator.
Nov 16 19:16:57 sun sqlgrey[18895]: fatal: Can't connect to DB: could
not connect to server: Connection refused at /usr/bin/sqlgrey line 336.
Nov 16 19:16:58 sun sqlgrey[18895]: warning: Database handle destroyed
without explicit disconnect at /usr/bin/sqlgrey line 336.

I'm not sure why the "administrator terminated the connection", but I'm
trying to trace this back. Is it unavoidable that sqlgrey terminates if
there's an error with the DB connection? Maybe a setting in the config
file could determine if in such a case the mail is considered either
pass or fail and a warning being issued to syslog or postmaster. The
connection could then be re-tried on the next mail-RX, no?

Sorry for the trouble,
HaJo

On Wed, 2004-11-17 at 23:12, HaJo Schatz wrote:
> On Wed, 2004-11-17 at 21:09, Lionel Bouton wrote:
> 
> > I must confess I didn't test this case after coding the protection, but 
> > I just did and it worked :
> > 
> > c:doc...@ma...
> > is put in the connect table as :
> > 'c:documentsandsettingsadministratordesktopgevaliagevaliafroms.tx' | 'mail.eppele.co.kr'
> > (notice the missing 't')
> 
> I suspect the sender contains "illegal" characters such as the
> backslash. Did you consider this? I'm wondering what an <INSERT ...
> ("C:\DocumentsAndSettings...")..> would do.
> 
> > 
> > My best guess is that you have an old (1.1.3 wasn't protected against this) sqlgrey release in your $PATH.
> Nack. I only have one version here which is (well, says through "sqlgrey
> --version") 1.3.0. I had the crash a few times with 1.1.3 upon which I
> upgraded to 1.3.0. There's a remote possibility that I didn't restart
> sqlgrey after upgrading, but I strongly doubt it, as I have a log entry
> (sqlgrey starting) which is younger than the mtime of the (new)
> /etc/sqlgrey/ directory.
> 
> Anyway, I'm sure that now the correct version is running. I'll observe
> it and report back if it crashes again.
> 
> HaJo
-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 16:12 :

>I suspect the sender contains "illegal" characters such as the
>backslash. Did you consider this? I'm wondering what an <INSERT ...
>("C:\DocumentsAndSettings...")..> would do.
>  
>

This SQL can't hit the database with 1.2.0 and later: the '\' is 
properly quoted by code looking like :
$dbh->quote($sender_name)
Perl DBI makes it easy to protect yourself against SQL injections by 
providing the quote() method.

SQL injections work in the following way :
you run the concatenation "SELECT col FROM table WHERE key = " and a 
value provided by a user you can't trust. If the value is something like 
"iownyou; DELETE FROM another_table" you end up sending "DELETE FROM 
another_table" to your database. Not a good thing...

What you want to do is let the database know that nothing in the 
"iownyou; DELETE FROM another_table" string should be interpreted as the 
end of the value against which key is compared. $dbh->quote("iownyou; 
DELETE FROM another_table") makes sure it doesn't happen by properly 
escaping the ';' character and whatever other character the database 
would treat specially.

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[Lionel B. <lio...@bo...>]

HaJo Schatz wrote the following on 17.11.2004 16:40 :

>Lionel,
>
>Pls don't dig deep into the long sender name issue, it seems I have
>screwed up while going through my maillogs. The "sender too long" is in
>fact from the old version of sqlgrey. I have now identified that 1.3.0
>crashed differently:
>  
>

OK.

>Nov 16 19:16:55 sun sqlgrey[18895]: Error: couldn't access domain_awl
>table: FATAL:  This connection has been terminated by the administrator.
>Nov 16 19:16:57 sun sqlgrey[18895]: fatal: Can't connect to DB: could
>not connect to server: Connection refused at /usr/bin/sqlgrey line 336.
>Nov 16 19:16:58 sun sqlgrey[18895]: warning: Database handle destroyed
>without explicit disconnect at /usr/bin/sqlgrey line 336.
>
>I'm not sure why the "administrator terminated the connection", but I'm
>trying to trace this back. Is it unavoidable that sqlgrey terminates if
>there's an error with the DB connection?
>

When disconnected SQLgrey tries to reconnect to the database but if it 
fails it will exit. 1.1.3 was more robust in this respect because it 
never retried immediately. But it had a bug which made it stop filtering 
for as much as 24hours before trying to reconnect to the database.
I consider this a bug in 1.2.0 and 1.3.x, what I'll code is a more 
tolerant behaviour in 1.2.1 to fix the problem (never exit) and redesign 
1.3 to sleep a little instead of trying to reconnect immediately (in 
order to allow a temporary connection problem to go away) and refuse to 
exit on DB errors too.

> Maybe a setting in the config
>file could determine if in such a case the mail is considered either
>pass or fail
>

Fail could be nasty !

> and a warning being issued to syslog or postmaster.
>

syslog is already ready. Postmaster could be handy.

> The
>connection could then be re-tried on the next mail-RX, no?
>
>Sorry for the trouble,
>
>  
>

Don't be sorry, this report is quite useful :-)

Best regards,

Lionel.

Re: [Sqlgrey-users] Periodic crashes of sqlgrey

[HaJo S. <ha...@ha...>]

On Thu, 2004-11-18 at 00:53, Lionel Bouton wrote:

> When disconnected SQLgrey tries to reconnect to the database but if it 
> fails it will exit. 1.1.3 was more robust in this respect because it 
> never retried immediately. But it had a bug which made it stop filtering 
> for as much as 24hours before trying to reconnect to the database.
> I consider this a bug in 1.2.0 and 1.3.x, what I'll code is a more 
> tolerant behaviour in 1.2.1 to fix the problem (never exit) and redesign 
> 1.3 to sleep a little instead of trying to reconnect immediately (in 
> order to allow a temporary connection problem to go away) and refuse to 
> exit on DB errors too.

Shame on me, I just discovered that sqlgrey is actually Perl and not
binary -- that's what's happening in the RPM-age, I'm getting close to
WIN... Just went briefly over the code and I also think that "or die" on
DBI-Errors is probably not the thing one wants in a daemon. 

Maybe a sanity-connect&check when starting the daemon should be allowed
to die, but after that I'd expect a daemon to stay alive whenever
possible. I'm really worried about the situation where I'll bring
postgresql down for a DB backup, upgrade or something. It'll take me
min. a day to figure that sqlgrey has crashed and I'll likely loose mail
that way.

HaJo

-- 
HaJo Schatz <ha...@ha...>
http://www.HaJo.Net

PGP-Key:  http://www.hajo.net/hajonet/keys/pgpkey_hajo.txt