Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.
- support multiple sql statement seperate by semicolon
- support select, delete, update and insert type
- support drop, truncate and rename command
- output the table and column list that the sql visited with the corresponding authority
- support various databases engine
From npmjs
npm install node-sql-parser --save
or
yarn add node-sql-parsernpm 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<scriptsrc="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script>// or you can import specified database parser only, it's about 150K<scriptsrc="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script><scriptsrc="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>NodeSQLParserobject is onwindow
<!DOCTYPE html><htmllang="en" ><head><title>node-sql-parser</title><metacharset="utf-8" /></head><body><p><em>Check console to see the output</em></p><scriptsrc="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script><script>window.onload=function(){// Example parserconstparser=newNodeSQLParser.Parser()constast=parser.astify("select id, name from students where age < 18")console.log(ast)constsql=parser.sqlify(ast)console.log(sql)}</script></body></html>- BigQuery
- DB2
- Hive
- MariaDB
- Mysql
- PostgresQL
- TransactSQL
- FlinkSQL
- New issue could be made for other new database.
// import Parser for all databasesconst{ Parser }=require('node-sql-parser');constparser=newParser();constast=parser.astify('SELECT * FROM t');// mysql sql grammer parsed by defaultconsole.log(ast);astforSELECT * 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
}constopt={database: 'MySQL'// MySQL is the default database}// import mysql parser onlyconst{ Parser }=require('node-sql-parser/build/mysql');constparser=newParser()// opt is optionalconstast=parser.astify('SELECT * FROM t',opt);constsql=parse.sqlify(ast,opt);console.log(sql);// SELECT * FROM `t`constopt={database: 'MariaDB'// MySQL is the default database}const{ Parser }=require('node-sql-parser/build/mariadb');constparser=newParser()// opt is optionalconst{ tableList, columnList, ast }=parser.parse('SELECT * FROM t',opt);- get the table list that the sql visited
- the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert
constopt={database: 'MySQL'}const{ Parser }=require('node-sql-parser/build/mysql');constparser=newParser();// opt is optionalconsttableList=parser.tableList('SELECT * FROM t',opt);console.log(tableList);// ["select::null::t"]- get the column list that the sql visited
- the format is {type}::{tableName}::{columnName} // type could be select, update, delete or insert
- for
select *,deleteandinsert into tableName values()without specified columns, the.*column authority regex is required
constopt={database: 'MySQL'}const{ Parser }=require('node-sql-parser/build/mysql');constparser=newParser();// opt is optionalconstcolumnList=parser.columnList('SELECT t.id FROM t',opt);console.log(columnList);// ["select::t::id"]- check table authority
whiteListCheckfunction check ontablemode andMySQLdatabase by default
const{ Parser }=require('node-sql-parser');constparser=newParser();constsql='UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'constwhiteTableList=['(select|update)::(.*)::(a|b)']// array that contain multiple authoritiesconstopt={database: 'MySQL',type: 'table',}// opt is optionalparser.whiteListCheck(sql,whiteTableList,opt)// if check failed, an error would be thrown with relevant error message, if passed it would return undefined- check column authority
const{ Parser }=require('node-sql-parser');constparser=newParser();constsql='UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'constwhiteColumnList=['select::null::name','update::a::id']// array that contain multiple authoritiesconstopt={database: 'MySQL',type: 'column',}// opt is optionalparser.whiteListCheck(sql,whiteColumnList,opt)// if check failed, an error would be thrown with relevant error message, if passed it would return undefinedThis 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 taozhi8833998@163.com
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.