Package Exports

node-sql-parser

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (node-sql-parser) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

Nodejs SQL Parser

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.

⭐ Features

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

🎉 Install

npm install node-sql-parser --save

or

yarn add node-sql-parser

(Deprecated)Install the following type module for typescript usage

npm install @types/node-sql-parser --save-dev

or

yarn add @types/node-sql-parser --dev

🚀 Usage

Supported Database SQL Syntax

Hive
Mysql
MariaDB
PostgresQL

Create AST for SQL statement

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default

console.log(ast);

ast for 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
}

Convert AST back to SQL

const opt = {
  database: 'MySQL' // MySQL is the default database
}
const { Parser } = require('node-sql-parser');
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`

Get TableList, ColumnList, Ast by `parse` function

const opt = {
  database: 'MariaDB' // MySQL is the default database
}
const { Parser } = require('node-sql-parser');
const parser = new Parser()
// opt is optional
const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt);

Get the SQL visited tables

get the table list that the sql visited
the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert

const opt = {
  database: 'MySQL'
}
const { Parser } = require('node-sql-parser');
const parser = new Parser();
// opt is optional
const tableList = parser.tableList('SELECT * FROM t', opt);

console.log(tableList); // ["select::null::t"]

Get the SQL visited columns

get the column list that the sql visited
the format is {type}::{tableName}::{columnName} // type could be select, update, delete or insert
for select *, delete and insert into tableName values() without specified columns, the .* column authority regex is required

const opt = {
  database: 'MySQL'
}
const { Parser } = require('node-sql-parser');
const parser = new Parser();
// opt is optional
const columnList = parser.columnList('SELECT t.id FROM t', opt);

console.log(columnList); // ["select::t::id"]

Check the SQL with Authority List

check table authority
whiteListCheck function check on table mode and MySQL database 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

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:🅰️: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

😘 Acknowledgement

This project is based on the SQL parser extracted from flora-sql-parser module.

License

GPLv2

Buy me a Coffee

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 donation to Author.

Paypal

Donate money by paypal to my account taozhi8833998@163.com

AliPay(支付宝)

Wechat(微信)

Explain

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.

node-sql-parser