I'm using an Ubuntu server with SQL Server installed and fail2ban for securing my services from brute force with some along securities.
So the problem is this: I created a bash script that takes the arguments from fail2ban IP, jail-name etc and creates a record to SQL Server via sqlcmd. When I'm testing the script it works perfectly.
But when it comes to real values and more it takes a wrong turn.
The bash script is this:
${sqlcmd} = full path of sqlcmd
${CMD_SQL} = "${sqlcmd} -S localhost -U randomuser -P randompassword(reading it from a file using grep) -Q "
#...
#code for checking some stuff and creating variables
#...
${CMD_SQL} <<EOF "INSERT INTO ${DB_TABLE} (ip, ports, protocol, jail,hostname, country, rdns, timestamp,failures, loglines) VALUES ('${_ip}', '${_ports}', '${_protocol}', '${_jail}','${_hostname}', '${_country}', '${_rdns}', CURRENT_TIMESTAMP,'${_failures}', '${_loglines}');"
EOF
When the script run from fail2ban service i saw on the logs the real values which it didnt insert them into sql. I tried to run them by myself.
sudo /opt/mssql-tools/bin/sqlcmd -S localhost -U randomuser -P randomPass -Q INSERT INTO dbo.banned (ip, ports, protocol, jail,hostname, country, rdns, timestamp,failures, loglines) VALUES ('12.12.12.12', 'All-ports', 'tcp', 'mssqld','djasserver', 'SA, Saudi Arabia', '', CURRENT_TIMESTAMP,'5', '2022-03-12 04:17:10.20 Logon Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 12.12.12.12] 2022-03-12 04:17:10.54 Logon Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 12.12.12.12] 2022-03-12 04:17:10.89 Logon Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 12.12.12.12] 2022-03-12 04:17:11.22 Logon Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 12.12.12.12] 2022-03-12 04:17:11.56 Logon Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 12.12.12.12]');
So at first I got an error:
-bash: syntax error near unexpected token `('
and I thought to myself that the query must be in quotation marks and I added this:
${sqlcmd} = full path of sqlcmd
${CMD_SQL} = "${sqlcmd} -S localhost -U randomuser -P randompassword(reading it from a file using grep) -Q "
#...
#code for checking some stuff and creating variables
#...
${CMD_SQL} <<EOF "\"INSERT INTO ${DB_TABLE} (ip, ports, protocol, jail,hostname, country, rdns, timestamp,failures, loglines) VALUES ('${_ip}', '${_ports}', '${_protocol}', '${_jail}','${_hostname}', '${_country}', '${_rdns}', CURRENT_TIMESTAMP,'${_failures}', '${_loglines}');\""
EOF
I run again the above real values and got this error from SQL:
Msg 102, Level 15, State 1, Server myserver, Line 1
Incorrect syntax near 'sa'.
And I thought okay it probably cannot pass some escape characters it contains.
The question is how do I fix this SQL error? Thanks for any answer.
PS: of course username, password and ips aren't real I changed them for protecting attackers privacy and mine.
