0
# Security & Utilities
1

2
SQL injection protection and utility functions for safe database interactions. Includes escaping functions, raw SQL wrappers, and SQL formatting utilities essential for secure database operations.
3

4
## Capabilities
5

6
### SQL Value Escaping
7

8
Escapes values to prevent SQL injection attacks and ensure proper data handling.
9

10
```javascript { .api }
11
/**
12
 * Escape a value for SQL
13
 * @param {*} value - The value to escape
14
 * @param {boolean} [stringifyObjects=false] - Setting if objects should be stringified
15
 * @param {string} [timeZone=local] - Setting for time zone to use for Date conversion
16
 * @returns {string} Escaped string value
17
 */
18
function escape(value, stringifyObjects, timeZone);
19

20
// Available on connections and pools too
21
connection.escape(value);
22
pool.escape(value);
23
```
24

25
**Usage Examples:**
26

27
```javascript
28
const mysql = require('mysql');
29

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

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

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

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

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

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

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

57
**Advanced Escaping Examples:**
58

59
```javascript
60
// Timezone-aware date escaping
61
const date = new Date('2023-01-01T12:00:00Z');
62
const escapedUTC = mysql.escape(date, false, 'UTC');
63
const escapedLocal = mysql.escape(date, false, 'local');
64

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

72
// Buffer escaping for binary data
73
const buffer = Buffer.from('binary data');
74
const escapedBuffer = mysql.escape(buffer);
75
// Result: X'62696E617279206461746127' (hex string)
76
```
77

78
### SQL Identifier Escaping
79

80
Escapes identifiers (table names, column names) for safe dynamic SQL construction.
81

82
```javascript { .api }
83
/**
84
 * Escape an identifier for SQL
85
 * @param {*} value - The value to escape
86
 * @param {boolean} [forbidQualified=false] - Setting to treat '.' as part of identifier
87
 * @returns {string} Escaped string value
88
 */
89
function escapeId(value, forbidQualified);
90

91
// Available on connections and pools too
92
connection.escapeId(value);
93
pool.escapeId(value);
94
```
95

96
**Usage Examples:**
97

98
```javascript
99
// Basic identifier escaping
100
const tableName = mysql.escapeId('user_table');
101
// Result: '`user_table`'
102

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

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

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

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

119
**Dynamic Query Building:**
120

121
```javascript
122
function buildSelectQuery(table, columns, conditions) {
123
  const tableName = mysql.escapeId(table);
124
  const columnList = columns.map(mysql.escapeId).join(', ');
125
  
126
  let query = `SELECT ${columnList} FROM ${tableName}`;
127
  
128
  if (conditions && Object.keys(conditions).length > 0) {
129
    const whereClause = Object.keys(conditions)
130
      .map(key => `${mysql.escapeId(key)} = ${mysql.escape(conditions[key])}`)
131
      .join(' AND ');
132
    query += ` WHERE ${whereClause}`;
133
  }
134
  
135
  return query;
136
}
137

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

143
### SQL Formatting
144

145
Formats SQL strings with parameter values for complete query construction.
146

147
```javascript { .api }
148
/**
149
 * Format SQL and replacement values into a SQL string
150
 * @param {string} sql - The SQL for the query
151
 * @param {array} [values] - Any values to insert into placeholders in sql
152
 * @param {boolean} [stringifyObjects=false] - Setting if objects should be stringified
153
 * @param {string} [timeZone=local] - Setting for time zone to use for Date conversion
154
 * @returns {string} Formatted SQL string
155
 */
156
function format(sql, values, stringifyObjects, timeZone);
157

158
// Available on connections and pools too
159
connection.format(sql, values);
160
pool.format(sql, values);
161
```
162

163
**Usage Examples:**
164

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

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

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

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

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

187
**Advanced Formatting:**
188

189
```javascript
190
// Named placeholders with object values
191
const sql = 'SELECT * FROM users WHERE name = :name AND age > :minAge';
192
const values = {name: 'John', minAge: 18};
193

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

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

204
// Complex formatting with timezone
205
const sql6 = mysql.format(
206
  'INSERT INTO logs SET created_at = ?, message = ?',
207
  [new Date(), 'User login'],
208
  false,
209
  'UTC'
210
);
211
```
212

213
### Raw SQL Wrapper
214

215
Wraps raw SQL strings to prevent escape processing when needed.
216

217
```javascript { .api }
218
/**
219
 * Wrap raw SQL strings from escape overriding
220
 * @param {string} sql - The raw SQL
221
 * @returns {object} Wrapped object
222
 */
223
function raw(sql);
224
```
225

226
**Usage Examples:**
227

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

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

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

249
**Raw SQL Best Practices:**
250

251
```javascript
252
// Good: Use raw SQL for MySQL functions
253
const goodExample = mysql.format('INSERT INTO users SET created_at = ?, updated_at = ?', [
254
  mysql.raw('NOW()'),
255
  mysql.raw('NOW()')
256
]);
257

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

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

272
### Security Best Practices
273

274
#### SQL Injection Prevention
275

276
```javascript { .api }
277
// SECURE: Always use parameterized queries
278
function getUser(userId) {
279
  return new Promise((resolve, reject) => {
280
    connection.query('SELECT * FROM users WHERE id = ?', [userId], (error, results) => {
281
      if (error) reject(error);
282
      else resolve(results[0]);
283
    });
284
  });
285
}
286

287
// SECURE: Use format function for dynamic queries
288
function searchUsers(filters) {
289
  const conditions = Object.keys(filters)
290
    .map(key => `${mysql.escapeId(key)} = ${mysql.escape(filters[key])}`)
291
    .join(' AND ');
292
  
293
  const sql = `SELECT * FROM users WHERE ${conditions}`;
294
  
295
  return new Promise((resolve, reject) => {
296
    connection.query(sql, (error, results) => {
297
      if (error) reject(error);
298
      else resolve(results);
299
    });
300
  });
301
}
302

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

310
#### Input Validation
311

312
```javascript { .api }
313
function validateAndEscapeInput(input, type) {
314
  switch (type) {
315
    case 'email':
316
      if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(input)) {
317
        throw new Error('Invalid email format');
318
      }
319
      return mysql.escape(input);
320
      
321
    case 'integer':
322
      const num = parseInt(input, 10);
323
      if (isNaN(num)) {
324
        throw new Error('Invalid integer');
325
      }
326
      return mysql.escape(num);
327
      
328
    case 'string':
329
      if (typeof input !== 'string') {
330
        throw new Error('Input must be a string');
331
      }
332
      return mysql.escape(input.trim());
333
      
334
    default:
335
      return mysql.escape(input);
336
  }
337
}
338

339
// Usage with validation
340
function createUser(userData) {
341
  const sql = mysql.format('INSERT INTO users SET ?', [{
342
    name: validateAndEscapeInput(userData.name, 'string'),
343
    email: validateAndEscapeInput(userData.email, 'email'),
344
    age: validateAndEscapeInput(userData.age, 'integer')
345
  }]);
346
  
347
  return new Promise((resolve, reject) => {
348
    connection.query(sql, (error, results) => {
349
      if (error) reject(error);
350
      else resolve(results);
351
    });
352
  });
353
}
354
```
355

356
### Utility Helper Functions
357

358
#### Query Builder Helper
359

360
```javascript { .api }
361
class QueryBuilder {
362
  constructor() {
363
    this.reset();
364
  }
365
  
366
  reset() {
367
    this._select = '*';
368
    this._from = '';
369
    this._where = [];
370
    this._orderBy = [];
371
    this._limit = null;
372
    return this;
373
  }
374
  
375
  select(columns) {
376
    this._select = Array.isArray(columns) 
377
      ? columns.map(mysql.escapeId).join(', ')
378
      : mysql.escapeId(columns);
379
    return this;
380
  }
381
  
382
  from(table) {
383
    this._from = mysql.escapeId(table);
384
    return this;
385
  }
386
  
387
  where(column, operator, value) {
388
    this._where.push(`${mysql.escapeId(column)} ${operator} ${mysql.escape(value)}`);
389
    return this;
390
  }
391
  
392
  orderBy(column, direction = 'ASC') {
393
    this._orderBy.push(`${mysql.escapeId(column)} ${direction}`);
394
    return this;
395
  }
396
  
397
  limit(count) {
398
    this._limit = parseInt(count, 10);
399
    return this;
400
  }
401
  
402
  build() {
403
    let sql = `SELECT ${this._select} FROM ${this._from}`;
404
    
405
    if (this._where.length > 0) {
406
      sql += ` WHERE ${this._where.join(' AND ')}`;
407
    }
408
    
409
    if (this._orderBy.length > 0) {
410
      sql += ` ORDER BY ${this._orderBy.join(', ')}`;
411
    }
412
    
413
    if (this._limit) {
414
      sql += ` LIMIT ${this._limit}`;
415
    }
416
    
417
    return sql;
418
  }
419
}
420

421
// Usage
422
const query = new QueryBuilder()
423
  .select(['id', 'name', 'email'])
424
  .from('users')
425
  .where('active', '=', true)
426
  .where('age', '>', 18)
427
  .orderBy('name')
428
  .limit(10)
429
  .build();
430
```
431

432
### Error Handling
433

434
```javascript { .api }
435
// Common MySQL error codes
436
const MYSQL_ERROR_CODES = {
437
  // Connection errors
438
  'PROTOCOL_CONNECTION_LOST': 'Database connection was closed',
439
  'PROTOCOL_ENQUEUE_AFTER_QUIT': 'Cannot enqueue after invoking quit',
440
  'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR': 'Cannot enqueue after fatal error',
441
  'PROTOCOL_ENQUEUE_HANDSHAKE_TWICE': 'Cannot enqueue handshake twice',
442
  'ECONNREFUSED': 'Connection refused: MySQL server not reachable',
443
  'ENOTFOUND': 'Host not found',
444
  'ETIMEDOUT': 'Connection timed out',
445
  
446
  // Authentication errors
447
  'ER_ACCESS_DENIED_ERROR': 'Access denied for user',
448
  'ER_NOT_SUPPORTED_AUTH_MODE': 'Authentication method not supported',
449
  'ER_MUST_CHANGE_PASSWORD': 'Password must be changed',
450
  
451
  // Database/table errors
452
  'ER_BAD_DB_ERROR': 'Unknown database',
453
  'ER_NO_SUCH_TABLE': 'Table does not exist',
454
  'ER_BAD_TABLE_ERROR': 'Unknown table',
455
  'ER_NON_UNIQ_ERROR': 'Column is ambiguous',
456
  'ER_BAD_FIELD_ERROR': 'Unknown column',
457
  
458
  // Data integrity errors
459
  'ER_DUP_ENTRY': 'Duplicate entry for key',
460
  'ER_NO_REFERENCED_ROW': 'Foreign key constraint fails (parent missing)',
461
  'ER_ROW_IS_REFERENCED': 'Foreign key constraint fails (child exists)',
462
  'ER_DATA_TOO_LONG': 'Data too long for column',
463
  'ER_TRUNCATED_WRONG_VALUE': 'Incorrect value',
464
  'ER_BAD_NULL_ERROR': 'Column cannot be null',
465
  
466
  // SQL syntax errors
467
  'ER_PARSE_ERROR': 'SQL syntax error',
468
  'ER_NO_DEFAULT_FOR_FIELD': 'Field has no default value',
469
  'ER_WRONG_VALUE_COUNT_ON_ROW': 'Column count does not match value count',
470
  
471
  // Transaction errors
472
  'ER_LOCK_WAIT_TIMEOUT': 'Lock wait timeout exceeded',
473
  'ER_LOCK_DEADLOCK': 'Deadlock found when trying to get lock',
474
  
475
  // Server capacity errors
476
  'ER_CON_COUNT_ERROR': 'Too many connections',
477
  'ER_OUT_OF_RESOURCES': 'Out of memory',
478
  'ER_DISK_FULL': 'Disk full',
479
  
480
  // Query timeout/limits
481
  'ER_QUERY_INTERRUPTED': 'Query execution was interrupted',
482
  'PROTOCOL_SEQUENCE_TIMEOUT': 'Query timeout'
483
};
484

485
// MySQL error codes and handling
486
function handleMySQLError(error) {
487
  const message = MYSQL_ERROR_CODES[error.code];
488
  if (message) {
489
    return `${message}: ${error.message}`;
490
  }
491
  
492
  switch (error.code) {
493
    case 'ER_DUP_ENTRY':
494
      return 'Duplicate entry: Record already exists';
495
    case 'ER_NO_SUCH_TABLE':
496
      return 'Table does not exist';
497
    case 'ER_ACCESS_DENIED_ERROR':
498
      return 'Access denied: Check credentials';
499
    case 'ER_BAD_DB_ERROR':
500
      return 'Unknown database';
501
    case 'PROTOCOL_CONNECTION_LOST':
502
      return 'Connection lost: Attempting to reconnect';
503
    case 'ECONNREFUSED':
504
      return 'Connection refused: MySQL server not available';
505
    default:
506
      return `MySQL Error (${error.code}): ${error.message}`;
507
  }
508
}
509

510
// Secure error reporting (don't expose internal details)
511
function safeErrorHandler(error, res) {
512
  console.error('Database error:', error); // Log full error internally
513
  
514
  const userMessage = handleMySQLError(error);
515
  res.status(500).json({
516
    error: 'Database operation failed',
517
    message: userMessage // Safe message for users
518
  });
519
}
520
```
521

522
Security utilities provide essential protection against SQL injection attacks and ensure safe database interactions. Always use parameterized queries and proper escaping functions when constructing dynamic SQL statements.