Bug: whiteListCheck Accepts Column Names with Extra Characters (Substring Matching Issue)

[chengwang-cc]

Describe the bug
The whiteListCheck function in node-sql-parser incorrectly validates SQL column names when the column name contains an allowed name as a substring. For example, with a schema defined as { user: ["username"] }, the SQL statement SELECT u.usernameXXX FROM user u; passes validation even though usernameXXX is not a valid column name. In contrast, a statement with a slightly misspelled column name like SELECT u.usernam FROM user u; correctly throws an exception.

Database Engine
PostgreSQL.

To Reproduce
-the SQL that be parsed
SELECT u.usernameXXX FROM user u;
-the node-sql-parser version
5.3.6
-the node version
v20.18.2

Steps to Reproduce
const Parser = require('node-sql-parser').Parser;
const parser = new Parser();
const sql = 'SELECT u.usernameXXX FROM user u;';
parser.astify(sql, { database: 'PostgreSQL' });
// Alternatively, if using whiteListCheck:
parser.whiteListCheck(sql, ['select::user::username'], { database: 'PostgreSQL', type: 'column' });

Expected behavior
The parser should strictly match column names. In the above example, the SQL statement should throw an error indicating that usernameXXX is not an allowed column for the user table. Only an exact match for username should be considered valid.

Screenshots
N/A

Additional context

• The issue appears to be due to a non-strict matching algorithm in the whiteListCheck method, which may be using a substring match instead of enforcing word boundaries or an exact match.
• This behavior could lead to unintended SQL statements being accepted, potentially causing security or data integrity issues.
• A temporary workaround might involve manually validating SQL identifiers or patching the node-sql-parser to enforce strict matching until an official fix is released.
• Please let me know if further details or testing information is required.