0
# SQL Utilities
1

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

4
## Capabilities
5

6
### Escape Function
7

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

10
```javascript { .api }
11
/**
12
 * Escape SQL value to prevent injection
13
 * @param value - Value to escape (any type)
14
 * @param stringifyObjects - Convert objects to JSON strings
15
 * @param timeZone - Timezone for date formatting
16
 * @returns Safely escaped SQL value string
17
 */
18
function escape(value: any, stringifyObjects?: boolean, timeZone?: string): string;
19
```
20

21
**Usage Examples:**
22

23
```javascript
24
const mysql = require('mysql2');
25

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

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

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

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

49
### Escape Identifier Function
50

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

53
```javascript { .api }
54
/**
55
 * Escape SQL identifier
56
 * @param value - Identifier to escape (string or array of strings)
57
 * @param forbidQualified - Disallow qualified identifiers (table.column)
58
 * @returns Safely escaped SQL identifier
59
 */
60
function escapeId(value: string | string[], forbidQualified?: boolean): string;
61
```
62

63
**Usage Examples:**
64

65
```javascript
66
// Escape table and column names
67
console.log(mysql.escapeId('user'));                // '`user`'
68
console.log(mysql.escapeId('user_profile'));        // '`user_profile`'
69
console.log(mysql.escapeId('select'));              // '`select`' (reserved word)
70
console.log(mysql.escapeId('table.column'));        // '`table`.`column`'
71

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

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

78
// Handle special characters
79
console.log(mysql.escapeId('my-table'));            // '`my-table`'
80
console.log(mysql.escapeId('table with spaces'));   // '`table with spaces`'
81
```
82

83
### Format Function
84

85
Formats SQL queries by replacing placeholders with escaped values.
86

87
```javascript { .api }
88
/**
89
 * Format SQL query with values
90
 * @param sql - SQL query string with placeholders
91
 * @param values - Values to replace placeholders (optional)
92
 * @param stringifyObjects - Convert objects to JSON strings
93
 * @param timeZone - Timezone for date formatting
94
 * @returns Formatted SQL query string
95
 */
96
function format(sql: string): string;
97
function format(sql: string, values: any | any[], stringifyObjects?: boolean, timeZone?: string): string;
98
```
99

100
**Usage Examples:**
101

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

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

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

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

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

129
### Raw Function
130

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

133
```javascript { .api }
134
/**
135
 * Create raw SQL object that won't be escaped
136
 * @param sql - Raw SQL string
137
 * @returns Object with toSqlString method
138
 */
139
function raw(sql: string): { toSqlString: () => string };
140
```
141

142
**Usage Examples:**
143

144
```javascript
145
// Use raw SQL for functions and expressions
146
const query = mysql.format('SELECT * FROM users WHERE created_at > ? AND status = ?', [
147
  mysql.raw('DATE_SUB(NOW(), INTERVAL 1 DAY)'),
148
  'active'
149
]);
150
console.log(query);
151
// 'SELECT * FROM users WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) AND status = "active"'
152

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

161
// Raw SQL for subqueries
162
const subquery = mysql.format('SELECT * FROM orders WHERE user_id IN (?)', [
163
  mysql.raw('SELECT id FROM users WHERE active = 1')
164
]);
165
console.log(subquery);
166
// 'SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1)'
167
```
168

169
## Advanced Usage Patterns
170

171
### Dynamic Query Building
172

173
```javascript
174
function buildSelectQuery(table, conditions, orderBy, limit) {
175
  let sql = 'SELECT * FROM ??';
176
  const params = [table];
177
  
178
  if (conditions && Object.keys(conditions).length > 0) {
179
    const whereClause = Object.keys(conditions).map(key => '?? = ?').join(' AND ');
180
    sql += ' WHERE ' + whereClause;
181
    
182
    Object.keys(conditions).forEach(key => {
183
      params.push(key, conditions[key]);
184
    });
185
  }
186
  
187
  if (orderBy) {
188
    sql += ' ORDER BY ??';
189
    params.push(orderBy);
190
  }
191
  
192
  if (limit) {
193
    sql += ' LIMIT ?';
194
    params.push(limit);
195
  }
196
  
197
  return mysql.format(sql, params);
198
}
199

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

206
### Bulk Insert Helper
207

208
```javascript
209
function buildBulkInsert(table, columns, rows) {
210
  const sql = 'INSERT INTO ?? (??) VALUES ?';
211
  return mysql.format(sql, [table, columns, rows]);
212
}
213

214
// Usage
215
const insertQuery = buildBulkInsert('users', ['name', 'email'], [
216
  ['John', 'john@example.com'],
217
  ['Jane', 'jane@example.com'],
218
  ['Bob', 'bob@example.com']
219
]);
220

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

225
### Search Query Builder
226

227
```javascript
228
function buildSearchQuery(table, searchTerm, searchColumns) {
229
  const conditions = searchColumns.map(() => '?? LIKE ?').join(' OR ');
230
  const sql = `SELECT * FROM ?? WHERE ${conditions}`;
231
  
232
  const params = [table];
233
  searchColumns.forEach(column => {
234
    params.push(column, `%${searchTerm}%`);
235
  });
236
  
237
  return mysql.format(sql, params);
238
}
239

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

246
## Security Best Practices
247

248
### Always Escape User Input
249

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

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

258
### Use Identifiers Escaping for Dynamic Table/Column Names
259

260
```javascript
261
// Safe dynamic table queries
262
function queryTable(tableName, columnName, value) {
263
  return mysql.format('SELECT * FROM ?? WHERE ?? = ?', [tableName, columnName, value]);
264
}
265
```
266

267
### Validate Input Before Escaping
268

269
```javascript
270
function safeQuery(table, column, value) {
271
  // Validate table and column names against whitelist
272
  const allowedTables = ['users', 'products', 'orders'];
273
  const allowedColumns = ['id', 'name', 'email', 'status'];
274
  
275
  if (!allowedTables.includes(table)) {
276
    throw new Error('Invalid table name');
277
  }
278
  
279
  if (!allowedColumns.includes(column)) {
280
    throw new Error('Invalid column name');
281
  }
282
  
283
  return mysql.format('SELECT * FROM ?? WHERE ?? = ?', [table, column, value]);
284
}
285
```
286

287
## Type-Specific Escaping
288

289
### Date Handling
290

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

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

303
### Buffer Handling
304

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

312
### Array and Object Handling
313

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

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