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 typescript
npm install node-sql-parser --save or yarn add node-sql-parser
Install the following type module for typescript usage
npm install @types/node-sql-parser --save-dev or yarn add @types/node-sql-parser --dev
const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t'); console.log(ast);
astforSELECT * FROM t
{
"tableList": [
"select::null::t"
],
"columnList": [
"select::null::(.*)"
],
"ast": {
"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 { Parser } = require('node-sql-parser'); const parser = new Parser() const ast = parser.astify('SELECT * FROM t'); const sql = parse.sqlify(ast); console.log(sql); // SELECT * FROM `t`
const { Parser } = require('node-sql-parser'); const parser = new Parser() const { tableList, columnList, ast } = parser.parse('SELECT * FROM t');
- get the table list that the sql visited
- the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert
const { Parser } = require('node-sql-parser'); const parser = new Parser(); const tableList = parser.tableList('SELECT * FROM t'); 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
const { Parser } = require('node-sql-parser'); const parser = new Parser(); const columnList = parser.columnList('SELECT t.id FROM t'); console.log(columnList); // ["select::t::id"]
- check table authority
whiteListCheckfunction check ontablemode 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 parser.whiteListCheck(sql, whiteTableList, 'table') // 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'); 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 parser.whiteListCheck(sql, whiteColumnList, 'column') // 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.