CODEWITHSUNDEEP
mysqlsqlnode.jsif-statementsyntax-error
HKK
HKK

Reputation: 187

how to use IF EXISTS statement in node mysql?

I am using NodeJs mysql module and I want to check account existence before actually querying the password.

Here is the sql:

let sql = `IF EXISTS (SELECT name FROM users WHERE name='${data.name}') SELECT password FROM users WHERE name='${data.name}'`;

It works fine when no if statement and once IF EXISTS part added in it, no matter what I adjust, always get the error like:

code: 'ER_PARSE_ERROR',

errno: 1064,

sqlMessage:
   '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...,

sqlState: '42000'

I will appreciate it if anyone can help me!

Upvotes: 1

Views: 2060

Answers (2)

Qamer Sayeed
Qamer Sayeed

Reputation: 111

You are not using the correct syntax of SQL, Use this one.

SELECT IF( EXISTS(SELECT name FROM users WHERE name='${data.name}'), (SELECT password FROM users WHERE name='${data.name}') , NULL)

Upvotes: 1

Aleks G
Aleks G

Reputation: 57306

First thing first, your SQL is open sql injection. Use prepared statements or at least do some attempts of cleaning up your input data.

That out of the way, this is not the correct way of doing things. You should simply check the password - and verify whether you got any rows back:

let sql = 'SELECT password FROM users WHERE name='${data.name}';

Then execute the SQL and check whether you got any rows back. If yes, then read the password, otherwise that username doesn't exist.

Upvotes: 2

Related Questions