Gathering detailed insights and metrics for @lucianbuzzo/node-sql-parser
Gathering detailed insights and metrics for @lucianbuzzo/node-sql-parser
Installations
npm install @lucianbuzzo/node-sql-parserDeveloper Guide
BETA
Typescript
Yes
Module System
CommonJS
Min. Node Version
>=8
Node Version
16.18.0
NPM Version
8.19.2
Score
78.1
Supply Chain
96.5
Quality
75.3
Maintenance
100
Vulnerability
80.9
License
Releases
19
Contributors
39
Languages
3
PEG.js
JavaScript
TypeScript
PEG.js (67.46%)
JavaScript (30.06%)
TypeScript (2.48%)
Developer
Download Statistics
Total Downloads
10,060
Last Day
3
Last Week
133
Last Month
177
Last Year
750
GitHub Statistics
834 Stars
3,014 Commits
185 Forks
13 Watching
6 Branches
39 Contributors
Bundle Size
1.18 MB
Minified
212.66 kB
Minified + Gzipped
Maintainers
1
Package Meta Information
Latest Version
4.6.5
Package Id
@lucianbuzzo/node-sql-parser@4.6.5
Unpacked Size
42.53 MB
Size
5.28 MB
File Count
97
NPM Version
8.19.2
Node Version
16.18.0
Publised On
13 Feb 2023
Total Downloads
Cumulative downloads
Total Downloads
10,060
Last day
-92.3%
3
Compared to previous day
Last week
224.4%
133
Compared to previous week
Last month
1,670%
177
Compared to previous month
Last year
-91.9%
750
Compared to previous year
Daily Downloads
Weekly Downloads
Monthly Downloads
Yearly Downloads
Dependencies
1
Dev Dependencies
33
@babel/cli@babel/core@babel/plugin-proposal-object-rest-spread@babel/preset-env@babel/register@types/chai@types/mochababel-loaderbrowserifychaicopy-webpack-plugincoverallscross-enveslinteslint-config-airbnb-baseeslint-config-stricteslint-plugin-filenameseslint-plugin-importfilemanager-webpack-pluginistanbul-instrumenter-loadermochamochapacknycpegjspegjs-loaderpre-commitrimrafsource-map-supporttinyifyvscode-mocha-hmrwebpackwebpack-cliwebpack-node-externals
Versions
Nodejs SQL Parser
Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.
:star: 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 various databases engine
:tada: Install
From npmjs
1npm install node-sql-parser --save 2 3or 4 5yarn add node-sql-parser
From GitHub Package Registry
1npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/
From Browser
Import the JS file in your page:
1// support all database parser, but file size is about 750K 2<script src="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script> 3 4// or you can import specified database parser only, it's about 150K 5 6<script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script> 7 8<script src="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>
NodeSQLParserobject is onwindow
1<!DOCTYPE html> 2<html lang="en" > 3 <head> 4 <title>node-sql-parser</title> 5 <meta charset="utf-8" /> 6 </head> 7 <body> 8 <p><em>Check console to see the output</em></p> 9 <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script> 10 <script> 11 window.onload = function () { 12 // Example parser 13 const parser = new NodeSQLParser.Parser() 14 const ast = parser.astify("select id, name from students where age < 18") 15 console.log(ast) 16 const sql = parser.sqlify(ast) 17 console.log(sql) 18 } 19 </script> 20 </body> 21</html>
:rocket: Usage
Supported Database SQL Syntax
- Athena
- BigQuery
- DB2
- Hive
- MariaDB
- MySQL
- PostgresQL
- Redshift
- Sqlite
- TransactSQL
- FlinkSQL
- Snowflake(alpha)
- Noql
- New issue could be made for other new database.
Create AST for SQL statement
1// import Parser for all databases 2const { Parser } = require('node-sql-parser'); 3const parser = new Parser(); 4const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default 5 6console.log(ast);
astforSELECT * FROM t
1{ 2 "with": null, 3 "type": "select", 4 "options": null, 5 "distinct": null, 6 "columns": "*", 7 "from": [ 8 { 9 "db": null, 10 "table": "t", 11 "as": null 12 } 13 ], 14 "where": null, 15 "groupby": null, 16 "having": null, 17 "orderby": null, 18 "limit": null 19}
Get node location in the AST
1const { Parser } = require('node-sql-parser'); 2const parser = new Parser(); 3const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } }); 4 5console.log(ast);
astforSELECT * FROM twith thelocproperty indicating locations and ranges
1{ 2 "with": null, 3 "type": "select", 4 "options": null, 5 "distinct": null, 6 "columns": [ 7 { 8 "expr": { 9 "type": "column_ref", 10 "table": null, 11 "column": "*" 12 }, 13 "as": null, 14 "loc": { 15 "start": { 16 "offset": 7, 17 "line": 1, 18 "column": 8 19 }, 20 "end": { 21 "offset": 8, 22 "line": 1, 23 "column": 9 24 } 25 } 26 } 27 ], 28 "into": { 29 "position": null 30 }, 31 "from": [ 32 { 33 "db": null, 34 "table": "t", 35 "as": null, 36 "loc": { 37 "start": { 38 "offset": 14, 39 "line": 1, 40 "column": 15 41 }, 42 "end": { 43 "offset": 15, 44 "line": 1, 45 "column": 16 46 } 47 } 48 } 49 ], 50 "where": null, 51 "groupby": null, 52 "having": null, 53 "orderby": null, 54 "limit": null, 55 "locking_read": null, 56 "window": null, 57 "loc": { 58 "start": { 59 "offset": 0, 60 "line": 1, 61 "column": 1 62 }, 63 "end": { 64 "offset": 15, 65 "line": 1, 66 "column": 16 67 } 68 } 69}
Convert AST back to SQL
1const opt = { 2 database: 'MySQL' // MySQL is the default database 3} 4// import mysql parser only 5const { Parser } = require('node-sql-parser'); 6const parser = new Parser() 7// opt is optional 8const ast = parser.astify('SELECT * FROM t', opt); 9const sql = parser.sqlify(ast, opt); 10 11console.log(sql); // SELECT * FROM `t`
Parse specified Database
There two ways to parser the specified database.
import Parser from the specified database path node-sql-parser/build/{database}
1// import transactsql parser only 2const { Parser } = require('node-sql-parser/build/transactsql') 3const parser = new Parser() 4const sql = `SELECT id FROM test AS result` 5const ast = parser.astify(sql) 6console.log(parser.sqlify(ast)) // SELECT [id] FROM [test] AS [result]
OR you can pass a options object to the parser, and specify the database property.
1const opt = { 2 database: 'Postgresql' 3} 4// import all databases parser 5const { Parser } = require('node-sql-parser') 6const parser = new Parser() 7// pass the opt config to the corresponding methods 8const ast = parser.astify('SELECT * FROM t', opt) 9const sql = parser.sqlify(ast, opt) 10console.log(sql); // SELECT * FROM "t"
Get TableList, ColumnList, Ast by parse function
1const opt = { 2 database: 'MariaDB' // MySQL is the default database 3} 4const { Parser } = require('node-sql-parser/build/mariadb'); 5const parser = new Parser() 6// opt is optional 7const { 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
1const opt = { 2 database: 'MySQL' 3} 4const { Parser } = require('node-sql-parser/build/mysql'); 5const parser = new Parser(); 6// opt is optional 7const tableList = parser.tableList('SELECT * FROM t', opt); 8 9console.log(tableList); // ["select::null::t"]
- if the table name is prefixed with database name, the table name will be parsed as dbName::tableName
- if the table name is prefixed with database and schema name, the table name will be parsed as dbName.schemaName::tableName
- if the table name is prefixed with server name in TransactSQL, the table name will be parsed as serverName.dbName.schemaName::tableName
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 *,deleteandinsert into tableName values()without specified columns, the.*column authority regex is required
1const opt = { 2 database: 'MySQL' 3} 4const { Parser } = require('node-sql-parser/build/mysql'); 5const parser = new Parser(); 6// opt is optional 7const columnList = parser.columnList('SELECT t.id FROM t', opt); 8 9console.log(columnList); // ["select::t::id"]
Check the SQL with Authority List
- check table authority
whiteListCheckfunction check ontablemode andMySQLdatabase by default
1const { Parser } = require('node-sql-parser'); 2const parser = new Parser(); 3const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' 4const whiteTableList = ['(select|update)::(.*)::(a|b)'] // array that contain multiple authorities 5const opt = { 6 database: 'MySQL', 7 type: 'table', 8} 9// opt is optional 10parser.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
1const { Parser } = require('node-sql-parser'); 2const parser = new Parser(); 3const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' 4const whiteColumnList = ['select::null::name', 'update::a::id'] // array that contain multiple authorities 5const opt = { 6 database: 'MySQL', 7 type: 'column', 8} 9// opt is optional 10parser.whiteListCheck(sql, whiteColumnList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined
:kissing_heart: Acknowledgement
This project is inspired by the SQL parser flora-sql-parser module.
License
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 donate 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.
Donation list
Star History
No vulnerabilities found.
Reason
30 commit(s) and 6 issue activity found in the last 90 days -- score normalized to 10
Reason
no binaries found in the repo
Reason
no dangerous workflow patterns detected
Reason
license file detected
Details
- Info: project has a license file: LICENSE:0
- Info: FSF or OSI recognized license: Apache License 2.0: LICENSE:0
Reason
8 existing vulnerabilities detected
Details
- Warn: Project is vulnerable to: GHSA-v88g-cgmw-v5xw
- Warn: Project is vulnerable to: GHSA-67hx-6x53-jw92
- Warn: Project is vulnerable to: GHSA-grv7-fg5c-xmjg
- Warn: Project is vulnerable to: GHSA-3xgq-45jj-v275
- Warn: Project is vulnerable to: GHSA-952p-6rrq-rcjv
- Warn: Project is vulnerable to: GHSA-p8p7-x288-28g6
- Warn: Project is vulnerable to: GHSA-4wf5-vphf-c2xc
- Warn: Project is vulnerable to: GHSA-72xf-g2v4-qvf3
Reason
Found 2/14 approved changesets -- score normalized to 1
Reason
detected GitHub workflow tokens with excessive permissions
Details
- Info: jobLevel 'contents' permission set to 'read': .github/workflows/npm-publish-github-packages.yml:14
- Warn: jobLevel 'packages' permission set to 'write': .github/workflows/npm-publish-github-packages.yml:16
- Warn: no topLevel permission defined: .github/workflows/npm-publish-github-packages.yml:1
Reason
no effort to earn an OpenSSF best practices badge detected
Reason
dependency not pinned by hash detected -- score normalized to 0
Details
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:19: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:20: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:32: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:33: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:50: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: GitHub-owned GitHubAction not pinned by hash: .github/workflows/npm-publish-github-packages.yml:51: update your workflow using https://app.stepsecurity.io/secureworkflow/taozhi8833998/node-sql-parser/npm-publish-github-packages.yml/master?enable=pin
- Warn: npmCommand not pinned by hash: .github/workflows/npm-publish-github-packages.yml:26
- Warn: npmCommand not pinned by hash: .github/workflows/npm-publish-github-packages.yml:39
- Warn: npmCommand not pinned by hash: .github/workflows/npm-publish-github-packages.yml:58
- Info: 0 out of 6 GitHub-owned GitHubAction dependencies pinned
- Info: 0 out of 3 npmCommand dependencies pinned
Reason
security policy file not detected
Details
- Warn: no security policy file detected
- Warn: no security file to analyze
- Warn: no security file to analyze
- Warn: no security file to analyze
Reason
project is not fuzzed
Details
- Warn: no fuzzer integrations found
Reason
SAST tool is not run on all commits -- score normalized to 0
Details
- Warn: 0 commits out of 30 are checked with a SAST tool
Score
4.1
/10
Last Scanned on 2024-12-16
The Open Source Security Foundation is a cross-industry collaboration to improve the security of open source software (OSS). The Scorecard provides security health metrics for open source projects.
Learn More