Capture bind variables for prepared statements

[ryanrupp]

Is your feature request related to a problem?

Currently, bind variables in prepared statements are not captured e.g. spans database statements are reported as select * from my_table where my_col = ?. It would be useful to capture these in some form for additional context about the transactions taking place in the application.

Describe the solution you'd like

I would like either:

1. Bind variables to be inlined with the SQL reported to the APM server. It's possibly there could be a performance concern here so possibly this is optional by default or can be dumped upon certain scenarios e.g. only if SQL ends in error or took longer than X ms (or read vs write possibly)
2. Include the bind variables as another non-indexed field in the span - avoids having to determine where to fill in the values but isn't as user friendly potentially.

Describe alternatives you've considered

I haven't necessarily considered alternatives but have used Stagemonitor :) in the past which used p6spy to resolve the parameters. See - https://github.com/p6spy/p6spy/blob/cce314027f25c7820346209caf717fa3a6c5b2cf/src/main/java/com/p6spy/engine/common/PreparedStatementInformation.java#L44

Additional context

Another user in a forum post asking about this - https://discuss.elastic.co/t/db-statement-values-are-seen-for-some-spans-whereas-it-is-seen-as-in-others/217300

[felixbarny]

Aside from the performance impact, recording parameters may also be a security/privacy risk.

Moving to elastic/apm as this seems to be something that should be aligned across agents.

[initharrington]

+1

This is something my dev team uses in New Relic currently to troubleshoot poorly performing sql issues. They are disappointed that this is functionally loss from moving from New Relic to Elastic APM.

Paramater: record_sql URL: https://docs.newrelic.com/docs/agents/java-agent/configuration/java-agent-configuration-config-file

It has 3 options: off: Send no queries. raw: Send the query statement in its original form. obfuscated: Strips out numeric and string literals.

We use "raw" to get the sql binds. I believe it does this just for slow transactions which are above their "apdex" threshold.

[eWilliams35]

I've been chatting with elastic support on this issue, and this may turn out to be a deal breaker for our company. For us, the bind variables completely change how the queries perform in our databases. I've used NewRelic in the past, and we're using App Dynamics now. Both offer bind variable scraping. I'm a bit lost on the security implications... if the agent has a configuration option that enables / disables bind variables, and the customer turns it on, they are implicitly assuming the associated risks.

This seems like it's solvable with RBAC and field level security. If you're in the right group, you can see the bind variables in the stack traces. If you're not, you can't.

[nyp-cgranata]

+1 - having the bind variables is very much needed to debug SQL queries.

@felixbarny Have there been an updates internally on this request?

[felixbarny]

I don't have updates on this, sorry.