nodejs中查询mysql防止SQL注入

ゝ一世哀愁。 2022-05-17 00:10 338阅读 0赞

## Performing queries ##

The most basic way to perform a query is to call the `.query()` method on an object (like a `Connection`, `Pool`, or `PoolNamespace` instance).

The simplest form of .`query()` is `.query(sqlString, callback)`, where a SQL string is the first argument and the second is a callback:

connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
      // error will be an Error if one occurred during the query
      // results will contain the results of the query
      // fields will contain information about the returned results fields (if any)
    });

The second form `.query(sqlString, values, callback)` comes when using placeholder values (see [escaping query values][]):

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
      // error will be an Error if one occurred during the query
      // results will contain the results of the query
      // fields will contain information about the returned results fields (if any)
    });

The third form `.query(options, callback)` comes when using various advanced options on the query, like [escaping query values][], [joins with overlapping column names][], [timeouts][], and [type casting][].

connection.query({
      sql: 'SELECT * FROM `books` WHERE `author` = ?',
      timeout: 40000, // 40s
      values: ['David']
    }, function (error, results, fields) {
      // error will be an Error if one occurred during the query
      // results will contain the results of the query
      // fields will contain information about the returned results fields (if any)
    });

Note that a combination of the second and third forms can be used where the placeholder values are passed as an argument and not in the options object. The `values` argument will override the `values` in the option object.

connection.query({
        sql: 'SELECT * FROM `books` WHERE `author` = ?',
        timeout: 40000, // 40s
      },
      ['David'],
      function (error, results, fields) {
        // error will be an Error if one occurred during the query
        // results will contain the results of the query
        // fields will contain information about the returned results fields (if any)
      }
    );

If the query only has a single replacement character (`?`), and the value is not `null`, `undefined`, or an array, it can be passed directly as the second argument to `.query`:

connection.query(
      'SELECT * FROM `books` WHERE `author` = ?',
      'David',
      function (error, results, fields) {
        // error will be an Error if one occurred during the query
        // results will contain the results of the query
        // fields will contain information about the returned results fields (if any)
      }
    );

## Escaping query values ##

**Caution** These methods of escaping values only works when the [NO\_BACKSLASH\_ESCAPES][NO_BACKSLASH_ESCAPES] SQL mode is disabled (which is the default state for MySQL servers).

In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query. You can do so using the `mysql.escape()`, `connection.escape()` or `pool.escape()` methods:

var userId = 'some user provided value';
    var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
    connection.query(sql, function (error, results, fields) {
      if (error) throw error;
      // ...
    });

Alternatively, you can use `?` characters as placeholders for values you would like to have escaped like this:

connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });

Multiple placeholders are mapped to values in the same order as passed. For example, in the following query `foo` equals `a`, `bar` equals `b`, `baz` equals `c`, and `id` will be `userId`:

connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });

This looks similar to prepared statements in MySQL, however it really just uses the same `connection.escape()` method internally.

**Caution** This also differs from prepared statements in that all `?` are replaced, even those contained in comments and strings.

Different value types are escaped differently, here is how:

*  Numbers are left untouched
 *  Booleans are converted to `true` / `false`
 *  Date objects are converted to `'YYYY-mm-dd HH:ii:ss'` strings
 *  Buffers are converted to hex strings, e.g. `X'0fa5'`
 *  Strings are safely escaped
 *  Arrays are turned into list, e.g. `['a', 'b']` turns into `'a', 'b'`
 *  Nested arrays are turned into grouped lists (for bulk inserts), e.g. `[['a', 'b'], ['c', 'd']]` turns into `('a', 'b'), ('c', 'd')`
 *  Objects that have a `toSqlString` method will have `.toSqlString()` called and the returned value is used as the raw SQL.
 *  Objects are turned into `key = 'val'` pairs for each enumerable property on the object. If the property's value is a function, it is skipped; if the property's value is an object, toString() is called on it and the returned value is used.
 *  `undefined` / `null` are converted to `NULL`
 *  `NaN` / `Infinity` are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

This escaping allows you to do neat things like this:

var post  = {id: 1, title: 'Hello MySQL'};
    var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
      if (error) throw error;
      // Neat!
    });
    console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

And the `toSqlString` method allows you to form complex queries with functions:

var CURRENT_TIMESTAMP = { toSqlString: function() { return 'CURRENT_TIMESTAMP()'; } };
    var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
    console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42

To generate objects with a `toSqlString` method, the `mysql.raw()` method can be used. This creates an object that will be left un-touched when using in a `?` placeholder, useful for using functions as dynamic values:

**Caution** The string provided to `mysql.raw()` will skip all escaping functions when used, so be careful when passing in unvalidated input.

var CURRENT_TIMESTAMP = mysql.raw('CURRENT_TIMESTAMP()');
    var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
    console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42

If you feel the need to escape queries by yourself, you can also use the escaping function directly:

var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");
    
    console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'

## Escaping query identifiers ##

If you can't trust an SQL identifier (database / table / column name) because it is provided by a user, you should escape it with `mysql.escapeId(identifier)`, `connection.escapeId(identifier)` or `pool.escapeId(identifier)` like this:

var sorter = 'date';
    var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
    connection.query(sql, function (error, results, fields) {
      if (error) throw error;
      // ...
    });

It also supports adding qualified identifiers. It will escape both parts.

var sorter = 'date';
    var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
    // -> SELECT * FROM posts ORDER BY `posts`.`date`

If you do not want to treat `.` as qualified identifiers, you can set the second argument to `true` in order to keep the string as a literal identifier:

var sorter = 'date.2';
    var sql    = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter, true);
    // -> SELECT * FROM posts ORDER BY `date.2`

Alternatively, you can use `??` characters as placeholders for identifiers you would like to have escaped like this:

var userId = 1;
    var columns = ['username', 'email'];
    var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
      if (error) throw error;
      // ...
    });
    
    console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

**Please note that this last character sequence is experimental and syntax might change**

When you pass an Object to `.escape()` or `.query()`, `.escapeId()` is used to avoid SQL injection in object keys.

## Preparing Queries ##

You can use mysql.format to prepare a query with multiple insertion points, utilizing the proper escaping for ids and values. A simple example of this follows:

var sql = "SELECT * FROM ?? WHERE ?? = ?";
    var inserts = ['users', 'id', userId];
    sql = mysql.format(sql, inserts);

Following this you then have a valid, escaped query that you can then send to the database safely. This is useful if you are looking to prepare the query before actually sending it to the database. As mysql.format is exposed from SqlString.format you also have the option (but are not required) to pass in stringifyObject and timezone, allowing you provide a custom means of turning objects into strings, as well as a location-specific/timezone-aware Date.

## Custom format ##

If you prefer to have another type of query escape format, there's a connection configuration option you can use to define a custom format function. You can access the connection object if you want to use the built-in `.escape()` or any other connection function.

Here's an example of how to implement another format:

connection.config.queryFormat = function (query, values) {
      if (!values) return query;
      return query.replace(/\:(\w+)/g, function (txt, key) {
        if (values.hasOwnProperty(key)) {
          return this.escape(values[key]);
        }
        return txt;
      }.bind(this));
    };
    
    connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

[https://github.com/mysqljs/mysql\#preparing-queries][https_github.com_mysqljs_mysql_preparing-queries]

[escaping query values]: https://github.com/mysqljs/mysql#escaping-query-values
[joins with overlapping column names]: https://github.com/mysqljs/mysql#joins-with-overlapping-column-names
[timeouts]: https://github.com/mysqljs/mysql#timeout
[type casting]: https://github.com/mysqljs/mysql#type-casting
[NO_BACKSLASH_ESCAPES]: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_backslash_escapes
[https_github.com_mysqljs_mysql_preparing-queries]: https://github.com/mysqljs/mysql#preparing-queries