2

Today in my bootcamp we started constructing and running SQL queries in Node.js. Once we covered the basics my instructor showed us how to use "?" as an escape character, which is supposed to somehow prevent SQL injections. I think I get the gist of this concept, but am very confused as to why one would do this instead of putting whatever variable they're inserting inside a template literal. For example:

db.query("DELETE FROM foo WHERE id = ?", [bar], function (err, result){})

vs.

db.query(`DELETE FROM foo WHERE id = ${bar}`, function (err, result){})

My instructor isn't always the best at uh, instructing, but he's proven to be very knowledgeable so I trust this is for a reason. In the past we've noticed that he often uses older techniques that have been largely replaced by more recent additions to the language; is this maybe a case of him doing that?

Mason Morris
  • 23
  • 3
  • Consider if `bar` was a string. Even better, imagine it's a string like `"don't create SQL injections"`. FYI it's a _"parameter"_ or _"placeholder"_, not an _"escape character"_ – Phil Apr 28 '22 at 01:34
  • To directly answer your question, see [the documentation](https://www.npmjs.com/package/mysql#escaping-query-values) – Phil Apr 28 '22 at 01:36

2 Answers2

2

Direct interpolation like

`DELETE FROM foo WHERE id = ${bar}`

is a bad idea because if bar is dynamically generated, such as taken from the user, this could lead to SQL injection. See Bobby Tables.

While it would be possible in theory to take an input string and make your own attempt to sanitize it properly and then interpolate the result directly yourself - if you mess up, you'll be in trouble. As a result, database drivers often provide a different way of passing in possibly-untrusted input, so that all of that can be abstracted away to the database driver - which makes it a lot easier for developers. That way, instead of each individual user having to implement it (and hope that they do it correctly), the database driver can implement it once and have everyone use that instead.

For this particular case, mysql will analyze the string you pass in and replace ?s with properly escaped versions of the second parameter.

In the past we've noticed that he often uses older techniques that have been largely replaced by more recent additions to the language; is this maybe a case of him doing that?

No, the approach you're showing in your question is very normal and modern.

CertainPerformance
  • 356,069
  • 52
  • 309
  • 320
  • 1
    FYI the node `mysql` library (sadly) doesn't use prepared statements. It simply replaces the placeholders with the escaped values, wrapped in quotes if necessary – Phil Apr 28 '22 at 01:42
  • At least it does string-replacement with well-tested code, to ensure escaping is done. But I agree, this is not the same as real query parameters. The point of query parameters is to keep the dynamic values _separated_ from the SQL string until after the query has been parsed. That way weird characters in the parameter value cannot cause syntax problems. – Bill Karwin Apr 28 '22 at 01:55
  • So essentially, using a `?` placeholder makes the database regard whatever it's standing in for purely as data instead of potentially viewing it as code? Thank you, by the way, for the extremely helpful answer and for that link to Bobby Tables! It looks like a pretty useful resource along with being a great comic. – Mason Morris Apr 28 '22 at 02:05
-1

The first parameter maps to the first question mark. This minimal change ensures that the input values are escaped and, as such, prevents a SQL injection attack here.

check this post why we use ? in queries.
What is the question mark's significance in MySQL at "WHERE column = ?"?

nekrofxk
  • 61
  • 8