SQL tracing: Support safely capturing parameterized SQL queries

[grobmeier]

I am running a Spring webapp with this configuration:

dataSource.setJdbcInterceptors("com.amazonaws.xray.sql.mysql.TracingInterceptor;");

Debugging confirms I am running into the TracingInterceptor and inside that, I can even see the sql inside the TracingInterceptor.

However, in the AWS XRay Console I cannot see the SQL commandos fired, just some meta data (like connection information).

Am I supposed to see the raw SQL running against a database? If yes, is there any additional step I missed?

[awssandra]

Hi grobmeier,

Originally, we had a concern raised around the fact the query may contain sensitive data, and how recording this automatically to X-Ray could leak this data. We had experimented in making our own sanitizer (as seen in the commented out SanitizeTest) but this was deemed insufficient, and the sanitizer and 'sanitized_query' parameter were removed, pending investigation on a better solution.

You can see the MySQL parameters captured here.

aws-xray-sdk-java/aws-xray-recorder-sdk-sql-mysql/src/main/java/com/amazonaws/xray/sql/mysql/TracingInterceptor.java

Line 157 in 3e0b21c

subsegment.putAllSql(additionalParams);

Other SDKs do have the 'sanitized_query' parameter for specific drivers that have built-in support for sanitizing/filtering queries (such as SQLAlchemy for Python). But, we do not yet have a solution for the MySQL driver on Java.

Similar question posted in the Node SDK GitHub: aws/aws-xray-sdk-node#9

Let me know if you have any questions. Thanks!
Sandra

[svetozar02]

Any updates on this issue?
From my perspective, there isn't much value in tracing sql queries if you don't see the actual sql statement. At very least it should be mentioned at https://docs.aws.amazon.com/xray/latest/devguide/xray-sdk-java-sqlclients.html so users know what to expect.

[willarmiros]

Hi @svetozar02,
Unfortunately it is still a security concern to capture arbitrary SQL query information in subsegments, and we cannot add this information at this time. I've put in a request to explicitly mention this on the documentation you linked.

[svetozar02]

Would it be possible for have an option of enabling it if users actually want it?

Other option is to look at how Datadog agent is obfuscating sql...

[softprops]

if not automatic, is there any guidance on a hook consumers can do in code to provide the instrumentation the sql manually?

[softprops]

The docs for the node sdk provided a tip that also works for the Java sdk https://docs.aws.amazon.com/xray/latest/devguide/xray-sdk-nodejs-sqlclients.html

[willarmiros]

Hi all,

Sorry for the delay in response here. You can capture SQL queries automatically with the X-Ray Java Agent. You just have to opt-in by setting the collectSqlQueries parameter in the agent config file to true.

In the meantime, this small feature is in our backlog to add to the Java SDK, and we are also always open to pull requests. We need to make this opt in via an environment variable like AWS_XRAY_COLLECT_SQL_QUERIES.