2

Hello I'm developing server with node.js and mysql. When I read some documents about mysql library, I found out that Turning multiple statements option on would increase the scope of the SQL injection attacks.

I know that if I use SQL syntax with '?' would escape the SQL syntax. So I thought that if I use the ? with using multiple query, SQL injection attack cannot be done. Is it right?

For example,

let sql = "UPDATE auth_user SET last_login=now() WHERE username=?; SELECT id, nickname FROM auth_user WHERE username = ? LIMIT 1"; let params = [username, username]; pool.getConnection(function(err, conn){ conn.query(sql, params, (err, datas, fields)=>{..}

If somebody gives "foo'; DROP TABLE auth_user;--" username params to do SQL injection, doesn't the mysql library automatically read the params as string with escaped one? Then I guessed the possibility of arising SQL injection attack would be same as the single statement is.

Why multiple statements option increase the scope of the SQL injection? How can I use multiple statements securely?

dizwe
  • 35
  • 1
  • 3
  • 1
    IIRC MySQL has true parameterized queries, but you can’t use them with multiple statements, which opens up [a vulnerability of no practical impact](https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218), but there’s no difference in this case because the mysql package for Node.js never uses true parameterized queries anyway. IIRC. Anyway, could you quote what you read? – Ry- May 11 '18 at 04:05

2 Answers2

3

SQL injection of bad'); DROP TABLE users -- could lead to the following SQL:

INSERT INTO users (firstname, lastname) VALUES ('bob', 'bad'); DROP TABLE users --')

If you disable multiple statements in a single query, this scenario is impossible, because only the first statement would be executed.

By enabling multiple statements, you open yourself up to SQL injection attacks like this. This "increases the scope" for SQL injection attacks, but if you are already using prepared statements, you should be protected from SQL injection.

Kyle
  • 564
  • 4
  • 4
0

From official mysqljs github

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;
  // ...
});

Get used to check your libraries documentation.

Community
  • 1
  • 1
Galih Akbar Moerbayaksa
  • 122
  • 3
  • The document you mentioned below, They said that "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; // ... });" . So I thought ? has same role with connection.escape(userId). – dizwe May 11 '18 at 05:05
  • what I really want to ask is that If I write SQL statement with connection.escape(userId) or “?”, Are all of SQL injection issues resolved **even if I use multiple statements**? then WHY the documents mentioned _Allow multiple mysql statements per query. Be careful with this, it could increase the scope of SQL injection attacks. (Default: false)_ – dizwe May 11 '18 at 05:05