Strip/delete all values from MySQL query using PHP regex?

Question

How to strip/delete all values from MySQL queries using PHP Regex?

Example:

original: SELECT id, abc FROM mytable WHERE id IN (12, 15)
striped: SELECT id, abc FROM mytable WHERE id IN ()

original:

SELECT ut.id, IF(stagiaire_devis.id IS NOT NULL, CONCAT(ut.prenom, ' (my lovely)', ut.prenom) AS value FROM quality AS uq, avocate, utility AS ut LEFT JOIN stagiaire_devis ON (stagiaire_devis.id_devis='1293049' AND stagiaire_devis.id_utilisateur=ut.id) WHERE uq.id_utilisateur = ut.id AND avocate.id_utilisateur = ut.id AND avocate.deleted = 0 AND avocate.id_avocat_liste_cabinet = 7 AND uq.id_liste_qualite IN (5,2,9) AND uq.actif = 2 ORDER BY ut.prenom

striped:

SELECT ut.id, IF(stagiaire_devis.id IS NOT NULL, CONCAT(ut.prenom, '', ut.prenom) AS value FROM quality AS uq, avocate, utility AS ut LEFT JOIN stagiaire_devis ON (stagiaire_devis.id_devis='' AND stagiaire_devis.id_utilisateur=ut.id) WHERE uq.id_utilisateur = ut.id AND avocate.id_utilisateur = ut.id AND avocate.deleted = AND avocate.id_avocat_liste_cabinet = AND uq.id_liste_qualite IN () AND uq.actif = ORDER BY ut.prenom

I cannot find right regex :(

Answer 1

You can't do this with any regex because SQL is not a regular language.

To do this task, you need to use an SQL parser.

---

Edit regarding your comment:

Okay, you need to establish a kind of 'fingerprint' for the query so that you can group queries together when they differ only in constant values.

The pt-query-digest tool can do this. It works with the slow-query log or the general log, but not the error log. But it's probably not hard to use awk or something to convert your error log into a format that pt-query-digest can read.