1

Method 1: Pipe Log

Recently I've read an article about how to save Apache log in MySQL database. Briefly, the idea is to pipe each log to MySQL:

# Format log as a MySQL query
LogFormat "INSERT INTO apache_logs \
set ip='%h',\
datetime='%{%Y-%m-%d %H:%M:%S}t',\
status='%>s',\
bytes_sent='%B',\
content_type='%{Content-Type}o',\
url_requested='%r',\
user_agent='%{User-Agent}i',\
referer='%{Referer}i';" \
mysql_custom_log

# execute queries
CustomLog "|/usr/bin/mysql -h 127.0.0.1 -u log_user -plog_pass apache_logs" mysql_custom_log

# save queries to log file
CustomLog logs/mysql_custom_log mysql_custom_log

 

Question

It seems that untreated user inputs (ie: user_agent & referer) would be passed directly to MySQL.
Therefore, is this method vulnerable to SQL injection? If so, is it possible to harden it?

 

Method 2: Apache module

mod_log_sql is an Apache module that seems to do something similar, ie: "logs all requests to a database". According to the documentation, such module has several advantages:

  • power of data extraction with SQL-based log
  • more configurable and flexible than the standard module [mod_log_config]
  • links are kept alive in between queries to save speed and overhead
  • any failed INSERT commands are preserved to a local file
  • no more tasks like log rotation
  • no need to collate/interleave the many separate logfiles

However, despite all this advantages, mod_log_sql doesn't seem to be popular:

  • the documentation doesn't mention one production level user
  • few discussions through the web
  • several periods without a maintainer

Which sounds like a warning to me (although I might be wrong).

 

Questions

  1. Any known reason why this module doesn't seem to be popular?
    Is it vulnerable to SQL injection? If so, is it possible to harden it?
  2. Which method should have better performance?
Mark Messa
  • 440
  • 4
  • 22

1 Answers1

1

Pipe Log method is better because it creates a stream between your log and your database, this can reflect directly on time performance in insertion/searching. Another point about pipe log is the possibility to use a NoSQL database which is optmized for searching or insertion via specific queries, one example is the ELK Stack, Elasticsearch + Logstash(Log Parser + Stream) and Kibana.

Would recommend any reading related to that: https://www.guru99.com/elk-stack-tutorial.html

Related to your question about SQL Injection, it deppends on how you are communicating with your database, despite the type of database or method to store your log. You need to secure it by using tokens as a example.

Related to apache module, the intention was to made a pipe log but the last commented part it's from 2006, and the documentation is not user friendly.

Roberto Gonçalves
  • 3,186
  • 4
  • 13
  • 27
  • _"Another point about pipe log is the use of a NoSQL database"_ In both methods it would be used MySQL, instead of a NoSQL ... – Mark Messa Aug 13 '19 at 21:43
  • I've already edited, added the word "possibility", because it can be implemented in both ways. And MySQL would not be a good choice. – Roberto Gonçalves Aug 13 '19 at 21:57
  • _"Another point about pipe log is the possibility to use a NoSQL database"_ Indeed this is an interesting point. Method 2 is a dead end, while method 1 later could be expanded into a centralized log system. – Mark Messa Aug 13 '19 at 22:09
  • 1
    _"one example is the ELK Stack, Elasticsearch + Logstash(Log Parser + Stream) and Kibana."_ I don't discard the possibility of moving forward into a centralized log system. However, first I would like to get as much as possible from what I'm already familiar with (ie: LAMP). – Mark Messa Aug 13 '19 at 22:11
  • 1
    _"it deppends on how you are communicating with your database"_ At least in method 1 the communication is very straight forward, ie, INSERT query without treating the user input. – Mark Messa Aug 13 '19 at 22:13
  • _"possible from what I'm already familiar with (ie: LAMP)."_ Sure, starting with everything that you are familiar is pretty good. So, basically you will have to parse your log and than structure your database as you mentioned on your question. – Roberto Gonçalves Aug 13 '19 at 22:17
  • _"At least in method 1 the communication is very straight forward, ie, INSERT query without treating the user input"_ - Yes, you're right, first you would use a insert(no possibility of SQL Injection here, eg: JDBC) and then you have to query it (possibility of Injection depending on how you are doing it, eg: HTTP Post) – Roberto Gonçalves Aug 13 '19 at 22:20
  • _"basically you will have to parse your log and than structure your database"_ In PHP this would be easy. However, I'm not sure if this is possible in Apache. Moreover, even if it would be possible, I'm not sure if this is efficient or safe ... – Mark Messa Aug 13 '19 at 22:20
  • _"possibility of Injection depending on how you are doing it"_ I'm not sure what you mean. The code is very crystal clear: 'INSERT query without treating the user input'. Shouldn't this be enough to know whether the code is vulnerable or not to SQL injection? What else does SQL injection depends on? What do you mean by 'how you are doing it'? – Mark Messa Aug 13 '19 at 22:25
  • _"What do you mean by 'how you are doing it'?"_ You can for example use a HTTP request parameter as a variable for inserting in your database (even not using user input) maybe to identify which instance is, it was the pattern a long time ago. So, I was talking about that. – Roberto Gonçalves Aug 13 '19 at 22:32
  • _"You can for example use a HTTP request parameter as a variable for inserting in your database"_ AFAIU from the code, there won't be any HTTP request, instead only the direct call: `mysql -h 127.0.0.1 -u log_user -plog_pass apache_logs INSERT INTO apache_logs \ set ip= ...` – Mark Messa Aug 13 '19 at 22:42
  • _from the code, there won't be any HTTP request, instead only the direct call:_ - Yes, now it is injection safe. If you do the queries the same way, both of them will be. – Roberto Gonçalves Aug 13 '19 at 22:45
  • 2
    _"now it is injection safe"_ Is it possible to set user-agent to `'; DROP TABLE mysql_custom_log` ? – Mark Messa Aug 13 '19 at 22:47
  • Good question, yes, it is possible, but your application must execute the user-agent parameter on your code, [like this](https://sechow.com/bricks/docs/content-page-4.html) and [here](https://securityonline.info/sql-injection-user-agent-injection-attack/) so, you need as I said before, have a secure code, even for the basic requests. – Roberto Gonçalves Aug 13 '19 at 22:56
  • _"your application must execute the user-agent parameter on your code"_ AFAIK, MySQL will execute whatever Apache submit, including such malicious code. Any idea how to 'clean' this field or mitigate such issue? – Mark Messa Aug 13 '19 at 23:10
  • _Any idea how to 'clean' this field or mitigate such issue?_ I don't think you can "clean it" since it's on HTTP standards, but you know something about OWASP? You can configure in your apache [here](https://modsecurity.org/crs/) – Roberto Gonçalves Aug 13 '19 at 23:18
  • Modsecurity is above my current level. Any simpler idea? Ex: LOAD DATA INFILE instead of INSERT INTO? – Mark Messa Aug 13 '19 at 23:23
  • Both of them is the same looking for the perspective that both are valid mysql commands. As I said before, the user-agent will be executed only if you are using it on your query, there's no possibility for MySQL execute it, look: `INSERT INTO visits (ua, dt) VALUES ('%s', '%s')", $_SERVER["HTTP_USER_AGENT"]` – Roberto Gonçalves Aug 13 '19 at 23:29
  • _"INSERT INTO visits (ua, dt) VALUES ('%s', '%s')", $_SERVER["HTTP_USER_AGENT"]"_ Where/How this Php code come from? The issue is about Apache + MySQL ... – Mark Messa Aug 13 '19 at 23:36
  • Yes, my point is, even on Apache, it will not execute user-agent unless you tell him to, the PHP is a example of it. You can test for yourself, try to build the setup we talked and try to inject using cURL, it will not work. – Roberto Gonçalves Aug 13 '19 at 23:39