Unparametrize SQL query

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.

Installation:

Node.js

npm i unparametrize-sql-query

Browser

(if you don't have a build system):

<script src="/node_modules/unparametrize-sql-query/dist/browser/unparametrize-sql-query.js"></script>

Usage

See index.js for options.

Node.js

const unparametrize_sql_query = require("unparametrize-sql-query");

const unparametrizedSQL = unparametrize_sql_query("SELECT 123 FROM table");

console.log(unparametrizedSQL);

Browser

<script>
// The function is exported on window.
console.log(unparametrize_sql_query("SELECT 123 FROM table"));
</script>

Example

Input

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)

Output

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, ?)

Output with whitespace preserved

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, ?)