Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.
npm install node-sql-parser --saveor
yarn add node-sql-parser
npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/
Import the JS file in your page:
// support all database parser, but file size is about 750K// or you can import specified database parser only, it's about 150K
NodeSQLParserobject is on
window
<title>node-sql-parser</title> <meta charset="utf-8"> <p><em>Check console to see the output</em></p> <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script> <script> window.onload = function () { // Example parser const parser = new NodeSQLParser.Parser() const ast = parser.astify("select id, name from students where age < 18") console.log(ast) const sql = parser.sqlify(ast) console.log(sql) } </script>
// import Parser for all databases const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by defaultconsole.log(ast);
astfor
SELECT * FROM t
{ "with": null, "type": "select", "options": null, "distinct": null, "columns": "*", "from": [ { "db": null, "table": "t", "as": null } ], "where": null, "groupby": null, "having": null, "orderby": null, "limit": null }
const opt = { database: 'MySQL' // MySQL is the default database } // import mysql parser only const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser() // opt is optional const ast = parser.astify('SELECT * FROM t', opt); const sql = parse.sqlify(ast, opt);console.log(sql); // SELECT * FROM
t
parsefunction
const opt = { database: 'MariaDB' // MySQL is the default database } const { Parser } = require('node-sql-parser/build/mariadb'); const parser = new Parser() // opt is optional const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt);
const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const tableList = parser.tableList('SELECT * FROM t', opt);console.log(tableList); // ["select::null::t"]
select *,
deleteand
insert into tableName values()without specified columns, the
.*column authority regex is required
const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const columnList = parser.columnList('SELECT t.id FROM t', opt);console.log(columnList); // ["select::t::id"]
whiteListCheckfunction check on
tablemode and
MySQLdatabase by default
const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' const whiteTableList = ['(select|update)::(.*)::(a|b)'] // array that contain multiple authorities const opt = { database: 'MySQL', type: 'table', } // opt is optional parser.whiteListCheck(sql, whiteTableList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined
const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' const whiteColumnList = ['select::null::name', 'update::a::id'] // array that contain multiple authorities const opt = { database: 'MySQL', type: 'column', } // opt is optional parser.whiteListCheck(sql, whiteColumnList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined
This project is based on the SQL parser extracted from flora-sql-parser module.
If you like my project, Star in the corresponding project right corner. Your support is my biggest encouragement! ^_^
You can also scan the qr code below or open paypal link to donate to Author.
Donate money by paypal to my account [email protected]
If you have made a donation, you can leave your name and email in the issue, your name will be written to the donation list.