Preventing SQL injection in Node js

Question

Is it possible to prevent SQL injections in Node js  preferably with a module  in the same way that PHP had Prepared Statements that protected against them   If so  how  If not  what are some examples that might bypass the code I ve provided  see below      Some Context   I m making a web application with a back-end stack consisting of Node js   MySql using the node-mysql module  From a usability perspective  the module is great  but it has not yet implemented something akin to PHP s Prepared Statements  though I m aware it is on the todo    From my understanding  PHP s implementation of prepared statements  among other things  helped greatly in the prevention of SQL injections  I m worried  though  that my node js app may be open to similar attacks  even with the string escaping provided by default  as in the code snippet below    node-mysql seems to be the most popular mysql connector for node js  so I was wondering what other people might be doing  if anything  to account for this issue - or if it is even an issue with node js to begin with  not sure how this wouldn t be  since user client-side input is involved    Should I switch to node-mysql-native for the time being  since it does provide prepared statements  I m hesitant to do this  because it does not seem to be as active as node-mysql  though that may just mean that it is complete    Here is a snippet of user registration code  which uses the sanitizer module  along with node-mysql s prepared statement-like syntax  which  as I mentioned above  does character escaping   to prevent cross site scripting and sql injections  respectively      Prevent xss var clean user   sanitizer sanitize username       assume password is hashed already var post    Username  clean user  Password  hash       This just uses connection escape   underneath var query   connection query  INSERT INTO users SET     post     function err  results                 Can a Sql injection happen here

User · Answer

In regards to testing if a module you are utilizing is secure or not there are several routes you can take. I will touch on the pros/cons of each so you can make a more informed decision.

Currently, there aren't any vulnerabilities for the module you are utilizing, however, this can often lead to a false sense of security as there very well could be a vulnerability currently exploiting the module/software package you are using and you wouldn't be alerted to a problem until the vendor applies a fix/patch.

To keep abreast of vulnerabilities you will need to follow mailing lists, forums, IRC & other hacking related discussions. PRO: You can often times you will become aware of potential problems within a library before a vendor has been alerted or has issued a fix/patch to remedy the potential avenue of attack on their software. CON: This can be very time consuming and resource intensive. If you do go this route a bot using RSS feeds, log parsing (IRC chat logs) and or a web scrapper using key phrases (in this case node-mysql-native) and notifications can help reduce time spent trolling these resources.
Create a fuzzer, use a fuzzer or other vulnerability framework such as metasploit, sqlMap etc. to help test for problems that the vendor may not have looked for. PRO: This can prove to be a sure fire method of ensuring to an acceptable level whether or not the module/software you are implementing is safe for public access. CON: This also becomes time consuming and costly. The other problem will stem from false positives as well as uneducated review of the results where a problem resides but is not noticed.

Really security, and application security in general can be very time consuming and resource intensive. One thing managers will always use is a formula to determine the cost effectiveness (manpower, resources, time, pay etc) of performing the above two options.

Anyways, I realize this is not a 'yes' or 'no' answer that may have been hoping for but I don't think anyone can give that to you until they perform an analysis of the software in question.

User · Answer

The library has a section in the readme about escaping  It s Javascript-native  so I do not suggest switching to node-mysql-native  The documentation states these guidelines for escaping   Edit  node-mysql-native is also a pure-Javascript solution    Numbers are left untouched Booleans are converted to true   false strings 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 are turned into key    val  pairs  Nested objects are cast to strings  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 allows for you to do things like so   var userId   5  var query   connection query  SELECT   FROM users WHERE id        userId   function err  results        query sql returns SELECT   FROM users WHERE id    5        As well as this   var post     id  1  title   Hello MySQL    var query   connection query  INSERT INTO posts SET     post  function err  result        query sql returns INSERT INTO posts SET  id    1   title     Hello MySQL        Aside from those functions  you can also use the escape functions   connection escape query   mysql escape query     To escape query identifiers   mysql escapeId identifier     And as a response to your comment on prepared statements      From a usability perspective  the module is great  but it has not yet implemented something akin to PHP s Prepared Statements    The prepared statements are on the todo list for this connector  but this module at least allows you to specify custom formats that can be very similar to prepared statements  Here s an example from the readme   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         This changes the query format of the connection so you can use queries like this   connection query  UPDATE posts SET title    title     title   Hello MySQL        equivalent to connection query  UPDATE posts SET title       mysql escape  Hello MySQL

User · Answer

The easiest way is to handle all of your database interactions in its own module that you export to your routes  If your route has no context of the database then SQL can t touch it anyway

User · Answer

Mysql-native has been outdated so it became MySQL2 that is a new module created with the help of the original MySQL module s team  This module has more features and I think it has what you want as it has prepared statements by using execute    like in PHP for more security  It s also very active the last change was from 2-1 days  I didn t try it before but I think it s what you want and more

User · Answer

The node-mysql library automatically performs escaping when used as you are already doing  See https   github com felixge node-mysql escaping-query-values

[javascript] Preventing SQL injection in Node.js

Examples related to javascript

Examples related to mysql

Examples related to node.js

Examples related to sql-injection

Examples related to node-mysql