tessl/npm-mysql2

Fast MySQL client for Node.js with support for prepared statements, SSL, compression, and promise-based APIs

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Pending

The risk profile of this skill

Overview

Eval results

Files

SQL Utilities

Name: tessl/npm-mysql2
Author: tessl

SQL string manipulation and escaping utilities for safe query construction, SQL injection prevention, and dynamic query building with proper value formatting.

Capabilities

Escape Function

Escapes SQL values to prevent SQL injection attacks and ensure proper value formatting.

/**
 * Escape SQL value to prevent injection
 * @param value - Value to escape (any type)
 * @param stringifyObjects - Convert objects to JSON strings
 * @param timeZone - Timezone for date formatting
 * @returns Safely escaped SQL value string
 */
function escape(value: any, stringifyObjects?: boolean, timeZone?: string): string;

Usage Examples:

const mysql = require('mysql2');

// Escape different value types
console.log(mysql.escape('Hello "World"'));        // '"Hello \"World\""'
console.log(mysql.escape(123));                     // '123'
console.log(mysql.escape(true));                    // 'true'
console.log(mysql.escape(false));                   // 'false'
console.log(mysql.escape(null));                    // 'NULL'
console.log(mysql.escape(undefined));               // 'NULL'
console.log(mysql.escape(new Date()));              // '"2023-12-01 10:30:45"'
console.log(mysql.escape(['a', 'b', 'c']));        // '"a", "b", "c"'
console.log(mysql.escape([1, 2, 3]));              // '1, 2, 3'

// Escape objects (stringified as JSON)
console.log(mysql.escape({name: 'John', age: 30})); // '"{\"name\":\"John\",\"age\":30}"'

// With stringifyObjects option
console.log(mysql.escape({id: 1, active: true}, true)); // '"{\"id\":1,\"active\":true}"'

// With timezone for dates
const date = new Date('2023-12-01T10:30:45.000Z');
console.log(mysql.escape(date, false, 'UTC'));      // '"2023-12-01 10:30:45"'
console.log(mysql.escape(date, false, 'local'));    // '"2023-12-01 05:30:45"'

Escape Identifier Function

Escapes SQL identifiers (table names, column names) to handle reserved words and special characters.

/**
 * Escape SQL identifier
 * @param value - Identifier to escape (string or array of strings)
 * @param forbidQualified - Disallow qualified identifiers (table.column)
 * @returns Safely escaped SQL identifier
 */
function escapeId(value: string | string[], forbidQualified?: boolean): string;

Usage Examples:

// Escape table and column names
console.log(mysql.escapeId('user'));                // '`user`'
console.log(mysql.escapeId('user_profile'));        // '`user_profile`'
console.log(mysql.escapeId('select'));              // '`select`' (reserved word)
console.log(mysql.escapeId('table.column'));        // '`table`.`column`'

// Escape array of identifiers
console.log(mysql.escapeId(['table', 'column']));   // '`table`.`column`'

// Forbid qualified identifiers
console.log(mysql.escapeId('table.column', true));  // Throws error

// Handle special characters
console.log(mysql.escapeId('my-table'));            // '`my-table`'
console.log(mysql.escapeId('table with spaces'));   // '`table with spaces`'

Format Function

Formats SQL queries by replacing placeholders with escaped values.

/**
 * Format SQL query with values
 * @param sql - SQL query string with placeholders
 * @param values - Values to replace placeholders (optional)
 * @param stringifyObjects - Convert objects to JSON strings
 * @param timeZone - Timezone for date formatting
 * @returns Formatted SQL query string
 */
function format(sql: string): string;
function format(sql: string, values: any | any[], stringifyObjects?: boolean, timeZone?: string): string;

Usage Examples:

// Format with positional placeholders
const sql1 = mysql.format('SELECT * FROM users WHERE id = ?', [123]);
console.log(sql1); // 'SELECT * FROM users WHERE id = 123'

const sql2 = mysql.format('SELECT * FROM users WHERE name = ? AND age > ?', ['John', 25]);
console.log(sql2); // 'SELECT * FROM users WHERE name = "John" AND age > 25'

// Format with named placeholders
const sql3 = mysql.format('SELECT * FROM users WHERE name = :name AND age > :age', {
  name: 'John',
  age: 25
});
console.log(sql3); // 'SELECT * FROM users WHERE name = "John" AND age > 25'

// Format identifiers with ??
const sql4 = mysql.format('SELECT ?? FROM ?? WHERE ?? = ?', ['name', 'users', 'id', 123]);
console.log(sql4); // 'SELECT `name` FROM `users` WHERE `id` = 123'

// Complex formatting
const sql5 = mysql.format(
  'INSERT INTO ?? (??) VALUES ?',
  ['users', ['name', 'email', 'age'], [['John', 'john@example.com', 30]]]
);
console.log(sql5); // 'INSERT INTO `users` (`name`, `email`, `age`) VALUES ("John", "john@example.com", 30)'

Raw Function

Creates raw SQL objects that bypass escaping for trusted SQL fragments.

/**
 * Create raw SQL object that won't be escaped
 * @param sql - Raw SQL string
 * @returns Object with toSqlString method
 */
function raw(sql: string): { toSqlString: () => string };

Usage Examples:

// Use raw SQL for functions and expressions
const query = mysql.format('SELECT * FROM users WHERE created_at > ? AND status = ?', [
  mysql.raw('DATE_SUB(NOW(), INTERVAL 1 DAY)'),
  'active'
]);
console.log(query);
// 'SELECT * FROM users WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) AND status = "active"'

// Raw SQL for complex expressions
const updateQuery = mysql.format('UPDATE users SET login_count = ? WHERE id = ?', [
  mysql.raw('login_count + 1'),
  123
]);
console.log(updateQuery);
// 'UPDATE users SET login_count = login_count + 1 WHERE id = 123'

// Raw SQL for subqueries
const subquery = mysql.format('SELECT * FROM orders WHERE user_id IN (?)', [
  mysql.raw('SELECT id FROM users WHERE active = 1')
]);
console.log(subquery);
// 'SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1)'

Advanced Usage Patterns

Dynamic Query Building

function buildSelectQuery(table, conditions, orderBy, limit) {
  let sql = 'SELECT * FROM ??';
  const params = [table];
  
  if (conditions && Object.keys(conditions).length > 0) {
    const whereClause = Object.keys(conditions).map(key => '?? = ?').join(' AND ');
    sql += ' WHERE ' + whereClause;
    
    Object.keys(conditions).forEach(key => {
      params.push(key, conditions[key]);
    });
  }
  
  if (orderBy) {
    sql += ' ORDER BY ??';
    params.push(orderBy);
  }
  
  if (limit) {
    sql += ' LIMIT ?';
    params.push(limit);
  }
  
  return mysql.format(sql, params);
}

// Usage
const query = buildSelectQuery('users', { active: true, role: 'admin' }, 'created_at', 10);
console.log(query);
// 'SELECT * FROM `users` WHERE `active` = true AND `role` = "admin" ORDER BY `created_at` LIMIT 10'

Bulk Insert Helper

function buildBulkInsert(table, columns, rows) {
  const sql = 'INSERT INTO ?? (??) VALUES ?';
  return mysql.format(sql, [table, columns, rows]);
}

// Usage
const insertQuery = buildBulkInsert('users', ['name', 'email'], [
  ['John', 'john@example.com'],
  ['Jane', 'jane@example.com'],
  ['Bob', 'bob@example.com']
]);

console.log(insertQuery);
// 'INSERT INTO `users` (`name`, `email`) VALUES ("John", "john@example.com"), ("Jane", "jane@example.com"), ("Bob", "bob@example.com")'

Search Query Builder

function buildSearchQuery(table, searchTerm, searchColumns) {
  const conditions = searchColumns.map(() => '?? LIKE ?').join(' OR ');
  const sql = `SELECT * FROM ?? WHERE ${conditions}`;
  
  const params = [table];
  searchColumns.forEach(column => {
    params.push(column, `%${searchTerm}%`);
  });
  
  return mysql.format(sql, params);
}

// Usage
const searchQuery = buildSearchQuery('products', 'laptop', ['name', 'description', 'tags']);
console.log(searchQuery);
// 'SELECT * FROM `products` WHERE `name` LIKE "%laptop%" OR `description` LIKE "%laptop%" OR `tags` LIKE "%laptop%"'

Security Best Practices

Always Escape User Input

// NEVER do this (vulnerable to SQL injection)
const badQuery = `SELECT * FROM users WHERE name = '${userInput}'`;

// Always do this instead
const goodQuery = mysql.format('SELECT * FROM users WHERE name = ?', [userInput]);

Use Identifiers Escaping for Dynamic Table/Column Names

// Safe dynamic table queries
function queryTable(tableName, columnName, value) {
  return mysql.format('SELECT * FROM ?? WHERE ?? = ?', [tableName, columnName, value]);
}

Validate Input Before Escaping

function safeQuery(table, column, value) {
  // Validate table and column names against whitelist
  const allowedTables = ['users', 'products', 'orders'];
  const allowedColumns = ['id', 'name', 'email', 'status'];
  
  if (!allowedTables.includes(table)) {
    throw new Error('Invalid table name');
  }
  
  if (!allowedColumns.includes(column)) {
    throw new Error('Invalid column name');
  }
  
  return mysql.format('SELECT * FROM ?? WHERE ?? = ?', [table, column, value]);
}

Type-Specific Escaping

Date Handling

// Dates are automatically formatted
const date = new Date('2023-12-01T10:30:45.000Z');
const query = mysql.format('SELECT * FROM events WHERE created_at > ?', [date]);
// 'SELECT * FROM events WHERE created_at > "2023-12-01 10:30:45"'

// Custom date formatting
const customDate = mysql.format('SELECT * FROM events WHERE created_at > ?', [
  mysql.raw(`'${date.toISOString().slice(0, 19).replace('T', ' ')}'`)
]);

Buffer Handling

// Buffers are escaped as hex strings
const buffer = Buffer.from('Hello World', 'utf8');
const query = mysql.format('INSERT INTO files (name, content) VALUES (?, ?)', ['test.txt', buffer]);
// 'INSERT INTO files (name, content) VALUES ("test.txt", 0x48656c6c6f20576f726c64)'

Array and Object Handling

// Arrays are expanded for IN clauses
const ids = [1, 2, 3, 4, 5];
const query = mysql.format('SELECT * FROM users WHERE id IN (?)', [ids]);
// 'SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5)'

// Objects are JSON stringified
const metadata = { tags: ['admin', 'user'], preferences: { theme: 'dark' } };
const updateQuery = mysql.format('UPDATE users SET metadata = ? WHERE id = ?', [metadata, 123]);
// 'UPDATE users SET metadata = "{"tags":["admin","user"],"preferences":{"theme":"dark"}}" WHERE id = 123'