关于mysqljs/mysql中默认sanitization、Escape()/EscapeId()及?/??的疑问

Hey there, let's unpack your questions about the mysqljs/mysql library clearly—this stuff can trip up even experienced devs at first!

First: Default Sanitization Behavior #

The library does automatic sanitization only when you use its built-in safe patterns—like placeholder syntax or passing objects to .query(). If you manually concatenate user input into SQL strings, you're on your own (and at risk of SQL injection).

.escape() vs .escapeId(): What's the Difference? #

These two methods serve completely distinct purposes:

• .escape() is for values: Use this when you need to sanitize user-provided values (strings, numbers, dates, etc.). It adds proper quoting (like single quotes for strings) and escapes special characters to prevent injection. For example:

  mysql.escape("O'Neil") // Returns: 'O''Neil'
  mysql.escape(123)      // Returns: 123 (no quotes needed for numbers)
  

• .escapeId() is for identifiers: This is for sanitizing SQL identifiers—things like table names, column names, or alias names. MySQL uses backticks for identifiers, so this method wraps the input in backticks and escapes any internal backticks. For example:

  mysql.escapeId("user-table") // Returns: `user-table`
  mysql.escapeId("column`name") // Returns: `column``name`
  

? and ??: The Placeholder Shortcuts #

These are just syntactic sugar for the escape methods above, and they're the recommended way to write safe queries:

• ? = .escape() for values: When you use ? in your query string and pass an array of values to .query(), each ? gets replaced with the escaped value. Example:

  connection.query(
    'UPDATE table SET updated_at = ? WHERE name = ?',
    [userInputDate, userInputName],
    (err, results) => { /* ... */ }
  )
  

  Both userInputDate and userInputName are automatically escaped here.
• ?? = .escapeId() for identifiers: Use this when you need to dynamically set table or column names. Example:

  connection.query(
    'SELECT ?? FROM ?? WHERE id = ?',
    ['username', 'users', userId],
    (err, results) => { /* ... */ }
  )
  

  Here, username becomes `username` and users becomes `users`—safe even if those names have special characters.

Your Specific Question: The UPDATE Query #

For your example UPDATE table SET updated_at = userInput WHERE name = userInput:

• If you're concatenating strings manually: Yes, you need to manually escape both userInput values (using .escape())—but don't do this! Manual concatenation is risky and error-prone.
• If you use placeholders: No manual escape needed. Use the ? syntax as shown above, and the library handles sanitization for you.
• If you pass an object: You can also write it like this, which is even cleaner:

  connection.query(
    'UPDATE table SET ? WHERE ?',
    [{ updated_at: userInputDate }, { name: userInputName }],
    (err, results) => { /* ... */ }
  )
  

  Here, the object keys (updated_at, name) are automatically escaped with .escapeId(), and the values are escaped with .escape()—double safety!

Quick Recap of the Docs Note #

When you pass an Object to .escape() or .query(), the library uses .escapeId() on the object's keys (since those are SQL identifiers) and .escape() on the values. This only applies to objects, not standalone strings or numbers—so if you're not using objects or placeholders, you have to handle escaping yourself.

内容的提问来源于stack exchange，提问作者Yasin Yaqoobi