Security & Utilities

/**
 * Escape a value for SQL
 * @param {*} value - The value to escape
 * @param {boolean} [stringifyObjects=false] - Setting if objects should be stringified
 * @param {string} [timeZone=local] - Setting for time zone to use for Date conversion
 * @returns {string} Escaped string value
 */
function escape(value, stringifyObjects, timeZone);

// Available on connections and pools too
connection.escape(value);
pool.escape(value);

const mysql = require('mysql');

// Basic value escaping
const escapedString = mysql.escape("That's a string"); 
// Result: '\'That\\\'s a string\''

const escapedNumber = mysql.escape(123);
// Result: '123'

const escapedNull = mysql.escape(null);
// Result: 'NULL'

const escapedBoolean = mysql.escape(true);
// Result: 'true'

// Array escaping (for IN clauses)
const escapedArray = mysql.escape([1, 2, 3, 'hello']);
// Result: '1, 2, 3, \'hello\''

// Date escaping
const escapedDate = mysql.escape(new Date('2023-01-01'));
// Result: '\'2023-01-01 00:00:00\''

// Object escaping with stringifyObjects
const obj = {name: 'John', age: 30};
const escapedObj = mysql.escape(obj, true);
// Result: '\'{"name":"John","age":30}\''

// Timezone-aware date escaping
const date = new Date('2023-01-01T12:00:00Z');
const escapedUTC = mysql.escape(date, false, 'UTC');
const escapedLocal = mysql.escape(date, false, 'local');

// Object escaping for JSON storage
const userData = {
  preferences: {theme: 'dark', language: 'en'},
  lastLogin: new Date()
};
const escapedJSON = mysql.escape(userData, true);

// Buffer escaping for binary data
const buffer = Buffer.from('binary data');
const escapedBuffer = mysql.escape(buffer);
// Result: X'62696E617279206461746127' (hex string)

/**
 * Escape an identifier for SQL
 * @param {*} value - The value to escape
 * @param {boolean} [forbidQualified=false] - Setting to treat '.' as part of identifier
 * @returns {string} Escaped string value
 */
function escapeId(value, forbidQualified);

// Available on connections and pools too
connection.escapeId(value);
pool.escapeId(value);

// Basic identifier escaping
const tableName = mysql.escapeId('user_table');
// Result: '`user_table`'

const columnName = mysql.escapeId('user-name');
// Result: '`user-name`'

// Qualified identifier escaping (database.table.column)
const qualifiedId = mysql.escapeId('mydb.users.name');
// Result: '`mydb`.`users`.`name`'

// Forbid qualified identifiers
const simpleId = mysql.escapeId('mydb.users', true);
// Result: '`mydb.users`' (treats dot as part of identifier)

// Array of identifiers
const columns = ['id', 'name', 'email'].map(mysql.escapeId).join(', ');
// Result: '`id`, `name`, `email`'

function buildSelectQuery(table, columns, conditions) {
  const tableName = mysql.escapeId(table);
  const columnList = columns.map(mysql.escapeId).join(', ');
  
  let query = `SELECT ${columnList} FROM ${tableName}`;
  
  if (conditions && Object.keys(conditions).length > 0) {
    const whereClause = Object.keys(conditions)
      .map(key => `${mysql.escapeId(key)} = ${mysql.escape(conditions[key])}`)
      .join(' AND ');
    query += ` WHERE ${whereClause}`;
  }
  
  return query;
}

// Usage
const query = buildSelectQuery('users', ['id', 'name', 'email'], {active: true, role: 'admin'});
// Result: SELECT `id`, `name`, `email` FROM `users` WHERE `active` = true AND `role` = 'admin'

/**
 * Format SQL and replacement values into a SQL string
 * @param {string} sql - The SQL for the query
 * @param {array} [values] - Any values to insert into placeholders in sql
 * @param {boolean} [stringifyObjects=false] - Setting if objects should be stringified
 * @param {string} [timeZone=local] - Setting for time zone to use for Date conversion
 * @returns {string} Formatted SQL string
 */
function format(sql, values, stringifyObjects, timeZone);

// Available on connections and pools too
connection.format(sql, values);
pool.format(sql, values);

// Basic formatting with positional parameters
const sql1 = mysql.format('SELECT * FROM users WHERE id = ?', [123]);
// Result: SELECT * FROM users WHERE id = 123

// Multiple parameters
const sql2 = mysql.format('SELECT * FROM users WHERE age > ? AND city = ?', [25, 'New York']);  
// Result: SELECT * FROM users WHERE age > 25 AND city = 'New York'

// Object parameter formatting
const sql3 = mysql.format('INSERT INTO users SET ?', [{name: 'John', email: 'john@example.com'}]);
// Result: INSERT INTO users SET `name` = 'John', `email` = 'john@example.com'

// Mixed object and value parameters
const sql4 = mysql.format('UPDATE users SET ? WHERE id = ?', [{name: 'Jane'}, 123]);
// Result: UPDATE users SET `name` = 'Jane' WHERE id = 123

// Array parameters for IN clauses
const sql5 = mysql.format('SELECT * FROM users WHERE id IN (?)', [[1, 2, 3, 4]]);
// Result: SELECT * FROM users WHERE id IN (1, 2, 3, 4)

// Named placeholders with object values
const sql = 'SELECT * FROM users WHERE name = :name AND age > :minAge';
const values = {name: 'John', minAge: 18};

// Manual replacement (mysql doesn't support named placeholders directly)
function formatNamed(sql, values) {
  return sql.replace(/:(\w+)/g, (match, key) => {
    return mysql.escape(values[key]);
  });
}

const formattedSQL = formatNamed(sql, values);
// Result: SELECT * FROM users WHERE name = 'John' AND age > 18

// Complex formatting with timezone
const sql6 = mysql.format(
  'INSERT INTO logs SET created_at = ?, message = ?',
  [new Date(), 'User login'],
  false,
  'UTC'
);

/**
 * Wrap raw SQL strings from escape overriding
 * @param {string} sql - The raw SQL
 * @returns {object} Wrapped object
 */
function raw(sql);

// Raw SQL for functions and expressions
const sql1 = mysql.format('INSERT INTO users SET created_at = ?, name = ?', [
  mysql.raw('NOW()'),  // Don't escape this - use as raw SQL
  'John Doe'           // This will be escaped normally
]);
// Result: INSERT INTO users SET created_at = NOW(), name = 'John Doe'

// Raw SQL for complex expressions
const sql2 = mysql.format('UPDATE users SET login_count = ? WHERE id = ?', [
  mysql.raw('login_count + 1'),  // Increment expression
  userId
]);
// Result: UPDATE users SET login_count = login_count + 1 WHERE id = 123

// Raw SQL for subqueries
const subquery = mysql.raw('(SELECT MAX(created_at) FROM posts WHERE user_id = ?)');
const sql3 = mysql.format('SELECT * FROM users WHERE last_post_date = ?', [subquery]);
// Note: The subquery itself would need separate parameter handling

// Good: Use raw SQL for MySQL functions
const goodExample = mysql.format('INSERT INTO users SET created_at = ?, updated_at = ?', [
  mysql.raw('NOW()'),
  mysql.raw('NOW()')
]);

// Bad: Don't use raw SQL for user input (SQL injection risk)
// const badExample = mysql.format('SELECT * FROM users WHERE name = ?', [
//   mysql.raw(userInput)  // NEVER do this!
// ]);

// Good: Combine raw SQL with escaped parameters
function incrementCounter(userId, tableName) {
  return mysql.format('UPDATE ?? SET counter = counter + 1 WHERE id = ?', [
    mysql.raw(mysql.escapeId(tableName)), // Escape identifier first, then mark as raw
    userId
  ]);
}

// SECURE: Always use parameterized queries
function getUser(userId) {
  return new Promise((resolve, reject) => {
    connection.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
      if (error) reject(error);
      else resolve(results[0]);
    });
  });
}

// SECURE: Use format function for dynamic queries
function searchUsers(filters) {
  const conditions = Object.keys(filters)
    .map(key => `${mysql.escapeId(key)} = ${mysql.escape(filters[key])}`)
    .join(' AND ');
  
  const sql = `SELECT * FROM users WHERE ${conditions}`;
  
  return new Promise((resolve, reject) => {
    connection.query(sql, (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
}

// INSECURE: Never concatenate user input directly
// function badGetUser(userId) {
//   const sql = 'SELECT * FROM users WHERE id = ' + userId; // DON'T DO THIS!
//   connection.query(sql, callback);
// }

function validateAndEscapeInput(input, type) {
  switch (type) {
    case 'email':
      if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(input)) {
        throw new Error('Invalid email format');
      }
      return mysql.escape(input);
      
    case 'integer':
      const num = parseInt(input, 10);
      if (isNaN(num)) {
        throw new Error('Invalid integer');
      }
      return mysql.escape(num);
      
    case 'string':
      if (typeof input !== 'string') {
        throw new Error('Input must be a string');
      }
      return mysql.escape(input.trim());
      
    default:
      return mysql.escape(input);
  }
}

// Usage with validation
function createUser(userData) {
  const sql = mysql.format('INSERT INTO users SET ?', [{
    name: validateAndEscapeInput(userData.name, 'string'),
    email: validateAndEscapeInput(userData.email, 'email'),
    age: validateAndEscapeInput(userData.age, 'integer')
  }]);
  
  return new Promise((resolve, reject) => {
    connection.query(sql, (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
}

class QueryBuilder {
  constructor() {
    this.reset();
  }
  
  reset() {
    this._select = '*';
    this._from = '';
    this._where = [];
    this._orderBy = [];
    this._limit = null;
    return this;
  }
  
  select(columns) {
    this._select = Array.isArray(columns) 
      ? columns.map(mysql.escapeId).join(', ')
      : mysql.escapeId(columns);
    return this;
  }
  
  from(table) {
    this._from = mysql.escapeId(table);
    return this;
  }
  
  where(column, operator, value) {
    this._where.push(`${mysql.escapeId(column)} ${operator} ${mysql.escape(value)}`);
    return this;
  }
  
  orderBy(column, direction = 'ASC') {
    this._orderBy.push(`${mysql.escapeId(column)} ${direction}`);
    return this;
  }
  
  limit(count) {
    this._limit = parseInt(count, 10);
    return this;
  }
  
  build() {
    let sql = `SELECT ${this._select} FROM ${this._from}`;
    
    if (this._where.length > 0) {
      sql += ` WHERE ${this._where.join(' AND ')}`;
    }
    
    if (this._orderBy.length > 0) {
      sql += ` ORDER BY ${this._orderBy.join(', ')}`;
    }
    
    if (this._limit) {
      sql += ` LIMIT ${this._limit}`;
    }
    
    return sql;
  }
}

// Usage
const query = new QueryBuilder()
  .select(['id', 'name', 'email'])
  .from('users')
  .where('active', '=', true)
  .where('age', '>', 18)
  .orderBy('name')
  .limit(10)
  .build();

// Common MySQL error codes
const MYSQL_ERROR_CODES = {
  // Connection errors
  'PROTOCOL_CONNECTION_LOST': 'Database connection was closed',
  'PROTOCOL_ENQUEUE_AFTER_QUIT': 'Cannot enqueue after invoking quit',
  'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR': 'Cannot enqueue after fatal error',
  'PROTOCOL_ENQUEUE_HANDSHAKE_TWICE': 'Cannot enqueue handshake twice',
  'ECONNREFUSED': 'Connection refused: MySQL server not reachable',
  'ENOTFOUND': 'Host not found',
  'ETIMEDOUT': 'Connection timed out',
  
  // Authentication errors
  'ER_ACCESS_DENIED_ERROR': 'Access denied for user',
  'ER_NOT_SUPPORTED_AUTH_MODE': 'Authentication method not supported',
  'ER_MUST_CHANGE_PASSWORD': 'Password must be changed',
  
  // Database/table errors
  'ER_BAD_DB_ERROR': 'Unknown database',
  'ER_NO_SUCH_TABLE': 'Table does not exist',
  'ER_BAD_TABLE_ERROR': 'Unknown table',
  'ER_NON_UNIQ_ERROR': 'Column is ambiguous',
  'ER_BAD_FIELD_ERROR': 'Unknown column',
  
  // Data integrity errors
  'ER_DUP_ENTRY': 'Duplicate entry for key',
  'ER_NO_REFERENCED_ROW': 'Foreign key constraint fails (parent missing)',
  'ER_ROW_IS_REFERENCED': 'Foreign key constraint fails (child exists)',
  'ER_DATA_TOO_LONG': 'Data too long for column',
  'ER_TRUNCATED_WRONG_VALUE': 'Incorrect value',
  'ER_BAD_NULL_ERROR': 'Column cannot be null',
  
  // SQL syntax errors
  'ER_PARSE_ERROR': 'SQL syntax error',
  'ER_NO_DEFAULT_FOR_FIELD': 'Field has no default value',
  'ER_WRONG_VALUE_COUNT_ON_ROW': 'Column count does not match value count',
  
  // Transaction errors
  'ER_LOCK_WAIT_TIMEOUT': 'Lock wait timeout exceeded',
  'ER_LOCK_DEADLOCK': 'Deadlock found when trying to get lock',
  
  // Server capacity errors
  'ER_CON_COUNT_ERROR': 'Too many connections',
  'ER_OUT_OF_RESOURCES': 'Out of memory',
  'ER_DISK_FULL': 'Disk full',
  
  // Query timeout/limits
  'ER_QUERY_INTERRUPTED': 'Query execution was interrupted',
  'PROTOCOL_SEQUENCE_TIMEOUT': 'Query timeout'
};

// MySQL error codes and handling
function handleMySQLError(error) {
  const message = MYSQL_ERROR_CODES[error.code];
  if (message) {
    return `${message}: ${error.message}`;
  }
  
  switch (error.code) {
    case 'ER_DUP_ENTRY':
      return 'Duplicate entry: Record already exists';
    case 'ER_NO_SUCH_TABLE':
      return 'Table does not exist';
    case 'ER_ACCESS_DENIED_ERROR':
      return 'Access denied: Check credentials';
    case 'ER_BAD_DB_ERROR':
      return 'Unknown database';
    case 'PROTOCOL_CONNECTION_LOST':
      return 'Connection lost: Attempting to reconnect';
    case 'ECONNREFUSED':
      return 'Connection refused: MySQL server not available';
    default:
      return `MySQL Error (${error.code}): ${error.message}`;
  }
}

// Secure error reporting (don't expose internal details)
function safeErrorHandler(error, res) {
  console.error('Database error:', error); // Log full error internally
  
  const userMessage = handleMySQLError(error);
  res.status(500).json({
    error: 'Database operation failed',
    message: userMessage // Safe message for users
  });
}