-1

I'm working on logging queries from a database and I only need the general form of the query. For that reason I would like to remove the specific data from the query and log it. Then I can get all of the queries in their generalized form.

Something like:

select * from table where key_field = 12

Would log as:

select * from table where key_field = ?

I've seen this done in Newrelic and in other forms of MySQL logging but I can't find a library or other well tested source of information for how to do this. Any helpful code or leads available?

I've considered regex and simple forms of parsing, but the resulting solution seems brittle. Especially when subqueries and constructs like "with" and subqueries are considered. Which is why I am looking for a library or code that has some battle testing on it.

Stephen Johnston
  • 1
  • 1
  • 2
    If you're using mysqli or pdo with prepared statements, then you get get the parameterized queries by using their libraries. See [here for PDO](https://stackoverflow.com/questions/2243177/get-query-back-from-pdo-prepared-statement) and [here for mysqli](https://stackoverflow.com/questions/962986/how-to-echo-a-mysqli-prepared-statement) – aynber Jul 22 '19 at 18:43
  • @aynber this is post query, I have the query in text form and I'm moving it to another system, so I need a solution for raw processing. – Stephen Johnston Jul 22 '19 at 19:14
  • What version of MySQL are you running? – Rick James Jul 22 '19 at 20:07

1 Answers1

0

IF you are OK with logging it via PHP then use prepared statements and wrap it in a function or class. In that way you can log the prepared statement without any "real data"

It could look something like this:

public function prepare($sqlStatement)
{
    $stmt = $this->mysqli->prepare($sqlStatement);
    if (!$stmt) {

        // Log an error here

        return false;
    } else {

        // Log your statement here

        return $stmt;
    }
}
SeeQue
  • 51
  • 6