CODEWITHSUNDEEP
javascriptmysqlnode.jssql-injection
Himad
Himad

Reputation: 337

Best way to escape array of data on MysQl nodeJS query

So I have the following object with is made by data submitted by the user in order to sign up:

  var dataToInsert = { 
  userName: 'Wilson J',
  userEmail: '[email protected]',
  userAddress: '2020 St.',
  userCellPhone: '95587412',
  }

And I'm using the following query to insert it: 

 var insertQuery = `INSERT INTO users ( ${Object.keys(dataToInsert).toString()} ) VALUES( '${Object.values(dataToInsert).join("','")}' )`;

Which at the end is taken as: 

INSERT INTO
  users (
    userName,
    userEmail,
    userAddress,
    userCellPhone
  )
VALUES
  (
    'Wilson J',
    '[email protected]',
    '2020 St',
    95587412
  )

So far I'm having a hard time understanding how data escaping works. I'd really appreciate if someone could show me how a SQL Injection could take place with this code and how to prevent it.

I'm using the MysQl npm module and it has this method: mysql.escape() but I would like to find a more automated approach instead of escaping every single value manually.

Upvotes: 2

Views: 2001

Answers (2)

Blue
Blue

Reputation: 22921

In this day and age, it's actively discouraged to do anything other than bind variables to your query. See this for more information on other ways to escape data:

connection.query(`
    INSERT INTO users ( ${Object.keys(dataToInsert).toString()} ) VALUES (?)`, 
    Object.values(dataToInsert),
    function (error, results, fields) {
      if (error) throw error;
      // ...
    }
);

Word of caution: You wont be able to bind variables to the column names, so unfortunately that part of the query is necessary. Ensure your keys of your dataToInsert are either static, or not from any user input.

Upvotes: 2

Matthi
Matthi

Reputation: 1063

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

There is a way. https://github.com/mysqljs/mysql#user-content-escaping-query-values

Upvotes: 1

Related Questions