audit_log json is not escaping characters properly

[Alle]

Hello,

On logstash reading the audit_log, I receive this error message with some audit_log entries:

{:timestamp=>"2016-02-22T06:37:55.645000+0000", :message=>"JSON parse failure. Falling back to plain-text", :error=>#<LogStash::Json::ParserError: Illegal unquoted character ((CTRL-CHAR, code 9)): has to be escaped using backslash to be included in string value
 at [Source: [B@53033860; line: 1, column: 344]>, :data=>"{\"audit_record\":{\"name\":\"Query\",\"record\":\"289492725_2016-02-17T11:18:32\",\"timestamp\":\"2016-02-22T06:37:54 UTC\",\"command_class\":\"insert\",\"connection_id\":\"97602\",\"status\":0,\"sqltext\":\"INSERT INTO XXXXX (ID, MESSAGE_ID, SERVER_ID, RECEIVED_DATE, STATUS, CONNECTOR_NAME, SEND_ATTEMPTS, SEND_DATE, RESPONSE_DATE, ERROR_CODE, CHAIN_ID, ORDER_ID)\\n\t\tVALUES (0, 742570, 'f8d013f6-1a85-41ea-a54d-79a216a62141', '2016-02-22 06:37:54.691', 'R', 'Source', 0, null, null, 0, 0, 0)\",\"user\":\"user[user] @ host.com\",\"host\":\"host.com\",\"os_user\":\"\",\"ip\":\"10.0.1.203\"}}", :level=>:error}

This is running on mysql 5.7.7 and the latest audit_log plugin.

mysqld Ver 5.7.11 for Linux on x86_64 (MySQL Community Server (GPL))
Linux db5 3.13.0-76-generic #120-Ubuntu SMP Mon Jan 18 15:59:10 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Regards
Alessandro

[eroomydna (andrew-2-moore)]

Alessandro, the Percona Server Audit Plugin is designed to work with Percona Server rather than MySQL Community Server. There is no guarantee of compatibility. Also there is no GA release of 5.7 for Percona Server so there would not be any tested version of the audit plugin with 5.7.

[Alle (alessandro-demaria)]

Hello Andrew,

I understand that, but I would imagine you are still interested in hearing bugs that are affecting this product.
It is a minor issue I am sure I can work around that, but I was applying due diligence and reporting a bug especially because there is no GA yet.

[Sergei Glushchenko (sergei.glushchenko)]

Symbols which aren't escaped are tabs in this case

List of special characters in JSON:

\"
\\
\/
\b
\f
\n
\r
\t

[Alle (alessandro-demaria)]

Any update on this bug?

[Matthew B (utdrmac)]

Hey Alle, if you are ok with compiling the plugin yourself, you can use my patch to fix the issue.

https://bugs.launchpad.net/percona-server/+bug/1642375

You don't have to compile MySQL completely. You can run cmake on the root source and then run make only inside plugin/audit_log/

-Matthew

[Alle (alessandro-demaria)]

~utdrmac

I tried to compile it myself but I end up with this:

```
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/audit_log.so' (errno: 2 /usr/lib/mysql/plugin/audit_log.so: undefined symbol: plugin_thdvar_safe_update)
```

[Alle (alessandro-demaria)]

Server version: 5.7.16 MySQL Community Server (GPL)

dpkg -l | grep mysql
ii libmysqlclient-dev 5.7.16-1ubuntu14.04 amd64 MySQL development headers
ii libmysqlclient20:amd64 5.7.16-1ubuntu14.04 amd64 MySQL shared client libraries
ii mysql-client 5.7.16-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version
ii mysql-common 5.7.16-1ubuntu14.04 amd64 MySQL Common
ii mysql-community-client 5.7.16-1ubuntu14.04 amd64 MySQL Client
ii mysql-community-server 5.7.16-1ubuntu14.04 amd64 MySQL Server

[Matthew B (utdrmac)]

Hi Alessandro,
You must be missing some library either during the compile phase or on the host to which you are installing the plugin.

Do a search for that missing library and see which package provides it.

Can you provide your compile steps?

-Matthew

Sent from my iPhone 6S! Please excuse shorthnd and typoes.

> On Dec 1, 2016, at 8:06 AM, Alle <email address hidden> wrote:
>
> Server version: 5.7.16 MySQL Community Server (GPL)
>
> dpkg -l | grep mysql
> ii libmysqlclient-dev 5.7.16-1ubuntu14.04 amd64 MySQL development headers
> ii libmysqlclient20:amd64 5.7.16-1ubuntu14.04 amd64 MySQL shared client libraries
> ii mysql-client 5.7.16-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version
> ii mysql-common 5.7.16-1ubuntu14.04 amd64 MySQL Common
> ii mysql-community-client 5.7.16-1ubuntu14.04 amd64 MySQL Client
> ii mysql-community-server 5.7.16-1ubuntu14.04 amd64 MySQL Server
>
> --
> You received this bug notification because you are subscribed to a
> duplicate bug report (1642375).
> https://bugs.launchpad.net/bugs/1548745
>
> Title:
> audit_log json is not escaping characters properly
>
> Status in Percona Server:
> Triaged
> Status in Percona Server 5.5 series:
> Triaged
> Status in Percona Server 5.6 series:
> Triaged
> Status in Percona Server 5.7 series:
> Triaged
>
> Bug description:
> Hello,
>
> On logstash reading the audit_log, I receive this error message with
> some audit_log entries:
>
> {:timestamp=>"2016-02-22T06:37:55.645000+0000", :message=>"JSON parse failure. Falling back to plain-text", :error=>#<LogStash::Json::ParserError: Illegal unquoted character ((CTRL-CHAR, code 9)): has to be escaped using backslash to be included in string value
> at [Source: [B@53033860; line: 1, column: 344]>, :data=>"{\"audit_record\":{\"name\":\"Query\",\"record\":\"289492725_2016-02-17T11:18:32\",\"timestamp\":\"2016-02-22T06:37:54 UTC\",\"command_class\":\"insert\",\"connection_id\":\"97602\",\"status\":0,\"sqltext\":\"INSERT INTO XXXXX (ID, MESSAGE_ID, SERVER_ID, RECEIVED_DATE, STATUS, CONNECTOR_NAME, SEND_ATTEMPTS, SEND_DATE, RESPONSE_DATE, ERROR_CODE, CHAIN_ID, ORDER_ID)\\n\t\tVALUES (0, 742570, 'f8d013f6-1a85-41ea-a54d-79a216a62141', '2016-02-22 06:37:54.691', 'R', 'Source', 0, null, null, 0, 0, 0)\",\"user\":\"user[user] @ host.com\",\"host\":\"host.com\",\"os_user\":\"\",\"ip\":\"10.0.1.203\"}}", :level=>:error}
>
>
> This is running on mysql 5.7.7 and the latest audit_log plugin.
>
> mysqld Ver 5.7.11 for Linux on x86_64 (MySQL Community Server (GPL))
> Linux db5 3.13.0-76-generic #120-Ubuntu SMP Mon Jan 18 15:59:10 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
>
>
> Regards
> Alessandro
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/1548745/+subscriptions

[Alle (alessandro-demaria)]

Hi Matthew,

sure I can. I have wrapped the compile process into a docker image (Dockerfile attached)

[Matthew B (utdrmac)]

I'm not sure how to view that. I've never used docker. Can you just give me the cmake command you used?

Sent from my iPhone 6S! Please excuse shorthnd and typoes.

> On Dec 1, 2016, at 9:35 AM, Alle <email address hidden> wrote:
>
> Hi Matthew,
>
> sure I can. I have wrapped the compile process into a docker image
> (Dockerfile attached)
>
>
>
> ** Attachment added: "docker.tar.gz"
> https://bugs.launchpad.net/percona-server/+bug/1548745/+attachment/4785766/+files/docker.tar.gz
>
> --
> You received this bug notification because you are subscribed to a
> duplicate bug report (1642375).
> https://bugs.launchpad.net/bugs/1548745
>
> Title:
> audit_log json is not escaping characters properly
>
> Status in Percona Server:
> Triaged
> Status in Percona Server 5.5 series:
> Triaged
> Status in Percona Server 5.6 series:
> Triaged
> Status in Percona Server 5.7 series:
> Triaged
>
> Bug description:
> Hello,
>
> On logstash reading the audit_log, I receive this error message with
> some audit_log entries:
>
> {:timestamp=>"2016-02-22T06:37:55.645000+0000", :message=>"JSON parse failure. Falling back to plain-text", :error=>#<LogStash::Json::ParserError: Illegal unquoted character ((CTRL-CHAR, code 9)): has to be escaped using backslash to be included in string value
> at [Source: [B@53033860; line: 1, column: 344]>, :data=>"{\"audit_record\":{\"name\":\"Query\",\"record\":\"289492725_2016-02-17T11:18:32\",\"timestamp\":\"2016-02-22T06:37:54 UTC\",\"command_class\":\"insert\",\"connection_id\":\"97602\",\"status\":0,\"sqltext\":\"INSERT INTO XXXXX (ID, MESSAGE_ID, SERVER_ID, RECEIVED_DATE, STATUS, CONNECTOR_NAME, SEND_ATTEMPTS, SEND_DATE, RESPONSE_DATE, ERROR_CODE, CHAIN_ID, ORDER_ID)\\n\t\tVALUES (0, 742570, 'f8d013f6-1a85-41ea-a54d-79a216a62141', '2016-02-22 06:37:54.691', 'R', 'Source', 0, null, null, 0, 0, 0)\",\"user\":\"user[user] @ host.com\",\"host\":\"host.com\",\"os_user\":\"\",\"ip\":\"10.0.1.203\"}}", :level=>:error}
>
>
> This is running on mysql 5.7.7 and the latest audit_log plugin.
>
> mysqld Ver 5.7.11 for Linux on x86_64 (MySQL Community Server (GPL))
> Linux db5 3.13.0-76-generic #120-Ubuntu SMP Mon Jan 18 15:59:10 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
>
>
> Regards
> Alessandro
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-server/+bug/1548745/+subscriptions

[Alle (alessandro-demaria)]

Sure:
On clean ubuntu trusty.
```
apt-key adv --keyserver pgp.mit.edu --recv-keys 5072E1F5
cp mysql.list /etc/apt/sources.list.d/
apt-get -y update && apt-get install -y libmysqlclient-dev build-essential curl cmake libncurses5-dev libreadline-dev zlib1g-dev

tar xvzf percona-server-5.7.16-10.tar.gz
cd /percona-server-5.7.16-10
cmake -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/tmp .
cd /percona/plugin/audit_log
make
```

[Alle (alessandro-demaria)]

Btw, I also get the same error when using the binary version straight from the release tarball, so I don't think it is the way I build it.

[Matthew B (utdrmac)]

Alle,
I want to confirm you are trying to use the Percona Audit Plugin on Percona Server 5.7. I saw you were running community in one of your posts. There is no guarantee of compatibility between them with our plugin.

A quick google search reveals plugin_thdvar_safe_update is only in MySQL 8 Community.

I'll fire up a VM and give this a fresh test tomorrow.

[Alle (alessandro-demaria)]

Hi Matthew, thank you for looking into it.

Yes, I confirm I am using the community edition.

I was under the impression that the plugin was compatible with mysql community as well.
Giving that I cannot move to Percona for reasons I cannot explain, I will have to look elsewhere then.. :(

It it unfortunately because until a couple of versions ago it was working perfectly.
I hope Percona starts supporting this plugin for MySQL community as well.

Regards.

[Laurynas Biveinis (laurynas-biveinis)]

Unfortunately the audit log plugin depends on some server-side patches for its features and stability. The compilation error above shows a missing dependency on one such patch (which happened to be backported from MySQL 8.0.0).

This particular dependency has been introduced with a fix for https://bugs.launchpad.net/percona-server/+bug/1626519, which was released in 5.6.34-79.1. You could try taking the plugin source from a previous release (5.6.33-79.0), but still there would be no guarantee whether it will work correctly with Oracle MySQL.

Currently we are not aware of any ways to keep the current feature set, avoid server bugs, and remove server-side patches at the same time. Thus compiling/loading audit plugin is only supported on the Percona Server.

[Matthew B (utdrmac)]

Alle,
If you do need audit capabilities, I believe the McAfee audit plugin
supports all versions and different vendors of MySQL. You should be able
to clone this repo and compile for Community.

https://github.com/mcafee/mysql-audit

Cheers,
Matthew

[Alle (alessandro-demaria)]

Hi Matthew.

thank you. I have already migrated to that plugin thank you very much for your support.

Regards
Alessandro

[Sveta Smirnova (svetasmirnova)]

Test case, demonstrating the issue. Load it, then check content of audit log:

sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 test <test1.sql
foobar
t\t
sveta@Thinkie:~/build/ps-5.7/mysql-test$ tail var/mysqld.1/data/audit.log
...
{"audit_record":{"name":"Query","record":"277737_2016-12-19T21:39:32","timestamp":"2016-12-19T21:47:34 UTC","command_class":"select","connection_id":"169","status":0,"sqltext":"select 't ' as foobar","user":"root[root] @ localhost [127.0.0.1]","host":"localhost","os_user":"","ip":"127.0.0.1","db":"test"}}

Bug is repeatable with Percona Server

[Sergei Glushchenko (sergei.glushchenko)]

https://github.com/percona/percona-server/pull/1257
https://github.com/percona/percona-server/pull/1258
https://github.com/percona/percona-server/pull/1259

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1698