Raw SQL query reports syntax error when there seems to be no error

[anthozep]

Sails version: "^0.12.9"
Node version: "4.x"
NPM version: "4.x"
DB adapter name: sails-mysql
DB adapter version: "^0.11.5"
Operating system: Windows

---

I posted this on SO here: https://stackoverflow.com/questions/46229400/sails-js-waterline-raw-sql-query-reports-syntax-error-when-there-seems-to-be-no

I have the following query so I can copy some rows into the same table:

START TRANSACTION;
CREATE TEMPORARY TABLE IF NOT EXISTS copy AS SELECT * FROM table WHERE table.field= 'foo';
UPDATE copy SET copy.field = REPLACE(copy.field, 'foo', 'bar');
SELECT @id := MAX(table.uid) AS uid FROM table;
UPDATE copy SET copy.uid = (@id:=@id+1);
INSERT INTO table SELECT * FROM copy;
COMMIT;

This works perfectly in MySQL workbench. I then ran the query through Sails.js (using the sails-mysql adapter) but I get the error:

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE IF NOT EXISTS copy AS SELECT * FROM table WH' at line 1

I've logged the query to the console and it looks fine.

Here's the sails code:

  copyTo: function (req, res) {
    var from = req.param('from', '');
    var to = req.param('to', '');
    if (from === '' || to === '') {
      res.send('from and to are required');
    }

    var query = 'START TRANSACTION; CREATE TEMPORARY TABLE IF NOT EXISTS copy AS SELECT * FROM table WHERE table.field = \'' + from + '\'; UPDATE copy SET copy.field = REPLACE(copy.field, \'' + from + '\', \'' + to + '\'); SELECT @id := MAX(table.uid) AS uid FROM table; UPDATE copy SET copy.uid = (@id:=@id+1); INSERT INTO table SELECT * FROM copy; COMMIT;';

    Model.query( query, function (err, items) {
      if (err) {
        console.log(err);
        res.send(400);
      } else {
        res.send('ok');
      }
    });
  },  

The result is essentially a copy of the temporary table reinserted into the original table, with the field property changed.

I've also tried specifying \n as the delimiter, to no avail

[sailsbot]

Hi @anthozep! It looks like you missed a step or two when you created your issue. Please edit your comment (use the pencil icon at the top-right corner of the comment box) and fix the following:

• Verify "I am experiencing a concrete technical issue (aka a bug) with Sails (ideas and feature proposals should follow the guide for proposing features and enhancements (http://bit.ly/sails-feature-guide), which involves making a pull request). If you're not 100% certain whether it's a bug or not, that's okay--you may continue. The worst that can happen is that the issue will be closed and we'll point you in the right direction."
• Verify "I am not asking a question about how to use Sails or about whether or not Sails has a certain feature (please refer to the documentation(http://sailsjs.com), or post on http://stackoverflow.com, our Google Group (http://bit.ly/sails-google-group) or our live chat (https://gitter.im/balderdashy/sails)."
• Verify "I have already searched for related issues, and found none open (if you found a related closed issue, please link to it in your post)."
• Verify "My issue title is concise, on-topic and polite ("jst.js being removed from layout.ejs on lift" is good; "templates dont work" or "why is sails dumb" are not so good)."
• Verify "I have tried all the following (if relevant) and my issue remains:"
• Verify "I can provide steps to reproduce this issue that others can follow."

As soon as those items are rectified, post a new comment (e.g. “Ok, fixed!”) below and we'll take a look. Thanks!

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[anthozep]

Fixed the verification steps!

[sailsbot]

@anthozep Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[sgress454]

@anthozep The issue here is that by default, the Node MySQL driver doesn't allow running multiple queries at once. This is to guard against SQL injection.

You have a couple of options here. If you don't need connection pooling for your app, you can turn it off and then run each of those statements individually. Because all the statements would use the same connection, it should allow the transaction to work, and potentially the @id := statements although we've never tested that programmatically (you could also rewrite the UPDATE copy query to use subqueries). You can turn off pooling by setting pool: false in your connection config. This could cause some serious bottlenecks in your app if you expect it to have a decent amount of traffic, as all requests will have to share one database connection.

As an alternative, you could try setting multipleStatements: true in the connection config and running your code as-is. I've never tried it, but since in Sails v0.12.x the connection settings are passed directly into the adapter, in theory it should work. Just keep in mind that this opens your app up to SQL injection attacks, so you'll have to be very careful in crafting queries, especially ones that involve untrusted input.

Sails 1.0 is stricter as far as what config properties are allowed, and I don't think multipleStatements is whitelisted for MySQL -- but it also has built-in support for transactions, so you would be able to run the statements individually in code and still have them be part of the same transaction.

Closing this as it's not a bug in the Sails core, but feel free to post back here if either of those suggestions works out or if you find another solution!

[Cazaimi]

@sgress454 , this concept was super hard to find. Is there a reason why this is not present in the documentation?