Removes string literals, number constants, comments and excess whitespace from an SQL in one pass. The removed string literals and number constants are replaced with '?'
and ?
respectively.
The goal is to reduce a query to a unique (common) form which could be used, for example, to keep performance counters per query (just like MySQL Performance Reports does).
This project was written as a support library for sql-performance-counters because the MySQL Performance Schema is disabled in Google Cloud SQL.
npm i unparametrize-sql-query
(if you don't have a build system):
<!-- NEVER serve static content directly from node_modules! Just illustrating the file paths here. -->
<script src="/node_modules/unparametrize-sql-query/dist/browser/unparametrize-sql-query.js"></script>
See index.js for options.
const unparametrize_sql_query = require("unparametrize-sql-query");
const unparametrizedSQL = unparametrize_sql_query("SELECT 123 FROM table");
console.log(unparametrizedSQL);
<script>
// The function is exported on window.
console.log(unparametrize_sql_query("SELECT 123 FROM table"));
</script>
SELECT
*
FROM
(
SELECT
-- One line comment with /* stream comment inside of it */ and another right after -- I want my independence
DISTINCT unique_random_numbers_anyway,
(
SELECT
COUNT(*),
*
FROM whatever
FORCE INDEX (bigger_than_the_table_itself)
),
@var_Name := CONCAT('''', "'abc'", "/*' -- Hello'*/", '\r\n\t -- line comment inside string? It can''t be. /* block comment */ '''' '),
'"/*''\r\n\t*/"', -- Confusing start of block comment: /*
-3 + (-9) - - 3 + ( - 99 + 2 % -5 | 333 ~999&111111111 * -0.00 * + 33 >> +123 << ++321.22) ^ 0xAFe0 AS `Computer error 123`
FROM users /* Why do we have stream comments? */
LEFT JOIN something ON something.user_id = users.user_id
WHERE
-- We joined you to kill you. TROJAN JOIN
something.user_id IS NULL
AND -1=-+-+-++-1
AND -1=-+-+-++1
AND `quoted_name`='2019-12-12'
OR`quoted_name` >= '2019-12-12T12:34:56Z'
-- NOT IN and IN may vary in number of elements dynamically for the same query, usually when they don't contain any subqueries.
OR xxx NOT IN ( - 999, 'AAA', -59)
AND zzz not in ('a', -12.9999, 0xAFED13, (select name from cache limit 1), 0, column_name)
AND -user_id = -123 -- That minus sign must be gone. Only questions must remain. Except for the name which must keep the minus sign.
AND wage > 0.1
AND name LIKE '%I will become a question mark% start of block comment: /*'
ORDER BY
user_date_created DESC /*
Multi-line comment means importance!
ortance...
tance...
sss...
*/
LIMIT 1460 /* LIMIT HERE? */ -- Noooo!
UNION -- ALL
SELECT
`database name with spaces`./*What am I?*/`012345799`
FROM xxxx
)
ORDER BY FIELD(user_phone_call_uniqueid, 'abc', 'def', 'xxx', 1, -1)
SELECT * FROM ( SELECT DISTINCT unique_random_numbers_anyway, ( SELECT COUNT(*), * FROM whatever FORCE INDEX (bigger_than_the_table_itself) ), @var_Name := CONCAT(?, ?, ?, ?), ?, ? + (?) - ? + ( ? + ? % ? | ? ~?&? * ? * + ? >> +? << ++?) ^ ? AS `Computer error 123` FROM users LEFT JOIN something ON something.user_id = users.user_id WHERE something.user_id IS NULL AND ?=-+-+-++? AND ?=-+-+-++? AND `quoted_name`=? OR`quoted_name` >= ? OR xxx NOT IN (?) AND zzz not in (?, ?, ?, (select name from cache limit ?), ?, column_name) AND -user_id = ? AND wage > ? AND name LIKE ? ORDER BY user_date_created DESC LIMIT ? UNION SELECT `database name with spaces`.`012345799` FROM xxxx ) ORDER BY FIELD(user_phone_call_uniqueid, ?)
SELECT
*
FROM
(
SELECT
DISTINCT unique_random_numbers_anyway,
(
SELECT
COUNT(*),
*
FROM whatever
FORCE INDEX (bigger_than_the_table_itself)
),
@var_Name := CONCAT(?, ?, ?, ?),
?,
? + (?) - ? + ( ? + ? % ? | ? ~?&? * ? * + ? >> +? << ++?) ^ ? AS `Computer error 123`
FROM users
LEFT JOIN something ON something.user_id = users.user_id
WHERE
something.user_id IS NULL
AND ?=-+-+-++?
AND ?=-+-+-++?
AND `quoted_name`=?
OR`quoted_name` >= ?
OR xxx NOT IN (?)
AND zzz not in (?, ?, ?, (select name from cache limit ?), ?, column_name)
AND -user_id = ?
AND wage > ?
AND name LIKE ?
ORDER BY
user_date_created DESC
LIMIT ?
UNION
SELECT
`database name with spaces`.`012345799`
FROM xxxx
)
ORDER BY FIELD(user_phone_call_uniqueid, ?)