Error in your SQL syntax: SET Syntax (Lambda function)

Question

While on the Lambda function, I'm trying to run the following query, but it throws an ER_PARSE_ERROR error.

{

"errorType": "Error",

"errorMessage": "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 'SELECT DISTINCT A.j FROM ...' at line 1",

"trace": [

const vandium = require('vandium');

var mysql = require('mysql');
var connection = mysql.createConnection({ multipleStatements: true });

exports.handler = vandium.generic()
    .handler((event, context, callback) => {

        var connection = mysql.createConnection({
            host: 'xjxj',
            user: 'xjxj',
            password: 'xjxj',
            database: 'xjxj'
        });
        var x = connection.escape(event.x);
        var y= connection.escape(event.y);
        var z = connection.escape(event.z);
  connection.query("SET @"+y+"=2; SELECT DISTINCT A.j FROM A INNER JOIN B, C WHERE A.j = B.j and C.x= " + x + " and C.y="+y+"  and C.z= " + z + "", function (error, results, fields) {
            if (error) {
                connection.destroy();
                throw error;
            }
            else {
                console.log(results);
                callback(null, results);
            }
        });
    });

I also thought of something like: ... "SET @y=2;..." but negative

I'm trying to implement something on similar lines:

SET @y := @k :=79;SELECT DISTINCT A.j FROM A INNER JOIN B, C WHERE A.j = B.j and C.x= " + x + " and C.y="+y+"  and C.z= " + z + "

Of course, there's no k in the first code block, but here's the rough idea.

What went wrong?

please add thencomplete error message and also show the connection string — nbk, Dec 06 '21 at 13:29
update your question and add the comple sql code fo your query .. (also the code after where.....) — ScaisEdge, Dec 06 '21 at 14:04
the join of the tables is wrong you really should read about it, further this is qa multiquery and i can't see why yo you use a SET @id when you don't use it, but basically read about n nodejs and multi query and as last https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js which you also should read urgently — nbk, Dec 07 '21 at 12:40

score 0 · Answer 1 · edited Dec 28 '21 at 21:10

0

Your code seems wrong you are mixing implicit join based tablename separated by comma and where condition and explicit join based on JOIN and ON clause and you are referring to a table name something non included in your query..

You should use a proper, explicit join clause and use proper tablename for column

"SELECT DISTINCT A.j 
FROM A
INNER JOIN B on A.j = B.j
INNER JOIN C ON c.X= " + x +" and c.y = " +y + " and c.z="+z+";

edited Dec 28 '21 at 21:10

marc_s

732,580
175
1,330
1,459

answered Dec 06 '21 at 14:09

ScaisEdge

131,976
10
91
107

But what about `SET @id =2;` which is essential? – ddb Dec 06 '21 at 16:08
if your db allow multiple command .. you can add the SET @id =2; before the other sql command.... .. – ScaisEdge Dec 06 '21 at 16:11
It's not working that way, that's the problem – ddb Dec 06 '21 at 16:16
If you have not sql error without the SET @id =2; .. but have error with this command .. then you should check if your db env allow multiple sql command.. otherwise if yuo have error in sql you should update your question and add the complete sql code ...not only a bief part – ScaisEdge Dec 06 '21 at 17:24
edited, please check – ddb Dec 07 '21 at 12:27
@user17603370 seems don' want understand .. you can use the sintax .. FROM A INNER JOIN B, C .. you can't use JOIN and tablename separated by comma you must use or the or tablename separated by commea and where condition but not JOIN Or JOIN with the condition in the ON clause .. answer updated .. – ScaisEdge Dec 07 '21 at 12:35

Error in your SQL syntax: SET Syntax (Lambda function)

1 Answers1