how to use IF EXISTS statement in node mysql?

Question

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!

Possible duplicate of [Usage of MySQL's "IF EXISTS"](https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) — Ocaso Protal, May 21 '19 at 08:32

score 2 · Accepted Answer · answered May 21 '19 at 08:31

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.

Thanks for reminding me security problem. And I think I will do it this way:) — HKK, May 21 '19 at 09:13

score 1 · Answer 2 · answered May 21 '19 at 08:38

1

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)

answered May 21 '19 at 08:38

Qamer Sayeed

111
1
4

how to use IF EXISTS statement in node mysql?

2 Answers2