Best way to escape array of data on MysQl nodeJS query

Question

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.

Curious, why do you uppercase only the Q in MySQL?

Blue
– Blue

2018-10-19 12:34:55 +00:00
Commented Oct 19, 2018 at 12:34 — Blue
– Blue, Commented Oct 19, 2018 at 12:34

Blue · Accepted Answer · 2018-10-19 12:33:06Z

2

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.

edited Oct 19, 2018 at 12:33

answered Oct 19, 2018 at 11:55

Blue

23k7 gold badges69 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Blue Over a year ago

@Nick If you read the attached link: Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'. I am missing the first set of parenthesis, though.

Himad Over a year ago

I see. So the question mark is gonna be replaced by the whole list of values? Is each one of them gonna be escaped? Also could you show me an example of SQL injection with the non-escaped code? Thanks for your time!!

Blue Over a year ago

There is no such thing as "Escaping" when you're binding variables. You tell MySQL: This is my query, the ? is where you put the variables I send you, and the second parameter is the values I want to send. Just add a single quote to any of your variables, and you'll see the query break in your code.

Himad Over a year ago

Wouldn't the code still be vulnerable to SQL injection in that case?

Blue Over a year ago

No, because you're explicitly telling MySQL that these (The array in my example) are variables, and they should be placed into the query at the ?

Matthi · Accepted Answer · 2018-10-19 11:54:55Z

1

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

answered Oct 19, 2018 at 11:54

Matthi

1,0738 silver badges19 bronze badges

1 Comment

altruios Over a year ago

to add to this. there is a ? for value escaping, and ?? for column escaping.

Collectives™ on Stack Overflow

Best way to escape array of data on MysQl nodeJS query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related