Topic: Error logging Fail2Ban into MySQL
Hope this is the right forum as this is where the link sent me after reading how to get Fail2Ban to log to a MySQL database.
Have followed all the instructions from here : docs.iredmail.org/fail2ban.sql.html#troubleshooting
Running all the commands manually on a Command Line from the TEST section works perfectly.
So for example if I run: fail2ban-client set dovecot banip 220.127.116.11 it does insert the row into MySQL
And if I run mysql fail2ban -e "UPDATE banned SET remove=1 WHERE ip='18.104.22.168'" it marks the row as needing to be unbanned by pacing the 1 in the remove field.
Lastly if I run /usr/local/bin/fail2ban_banned_db unban_db it removes the row.
So pretty confident there is no issue with permissions or config files.
However, for some reason the action = settings in my jails aren't putting anything into MySQL, and I am getting the following errors in the logfile
2023-05-06 16:21:36,544 fail2ban.utils : ERROR 7fbf16735a80 -- stderr: "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'root'@'22.214.171.124' (using password: NO) 2023-05-06 7:15:55 37021 [Warning...' at line 8" 2023-05-06 16:21:36,544 fail2ban.utils : ERROR 7fbf16735a80 -- returned 1
Here is my jail settings for mysqld-auth
[mysqld-auth] enabled = true port = 3306 filter = mysqld-auth action = sendmail-whois-lines[name=RC, logpath="%(logpath)s"] banned_db[name=mysqld-auth, port="3306", protocol=tcp] logpath = /var/log/mysql/mysql-bin.err maxretry = 3 bantime = 3600
Server is running Ubuntu 22.04, Apache 2.4.52, PHP 8.1.2, MariaDB 10.6.12
Hoping it's just something really simple I have missed. Just can't understand when I run the commands manually they do everything they are supposed to do. But the action automations don't work.
Really appreciate some guidance and to be pointed in the right direction.
----Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.