1

I have code, which should execute if the table is not in mysql or "NULL" or empty.

mysqlConnection.query('SELECT `something` FROM `here` WHERE `dog` = \'' +info+ '\'', function(err, row, fields) {
  if(err) {
    console.log('Error1');
    return;
  }
  else if (!row.length) {                                                   
    console.log('Error2');
    return;
  }
  else if (row[0].something == 'NULL' || row[0].something == '') {
    console.log('Error3');
    return;
  }
  console.log('Works');
});

So the thing is, if "something" is not in mysql, console shows Error2, but if "something" is in mysql, but if its NULL, console shows Works, so whats the problem? Im checking if something is NULL, but it wont show Error3. If table is empty, it shows Error3. Thanks for help.

Improve this question
4
  • If something is NULL then it is not 'NULL' (is a string containing NULL) or '' (is an empty string), but null or undefined (can't test it right now). So you need to test for row[0].something === null or row[0].something === undefined Commented Aug 23, 2015 at 18:43
  • An additional note: you should't use constructs like \'' +info+ '\'' because they might result in potential sql injections or at least not working queries if info contains '. Take a look at Escaping query values. Commented Aug 23, 2015 at 18:46
  • Hey, thanks it works, but there is another problem. If I add console.log('Hello'); after this code, Sometimes Hello appears first and then Error1 or etc.? Commented Aug 23, 2015 at 18:59
  • If you add console.log('Hello'); that way mysqlConnection.query( ... console.log('Works'); }); console.log('Hello'); ? Then Hello should always appear before Works or one of the Error messages, otherwise it would (imho) a bug in the mysql lib. Commented Aug 23, 2015 at 19:09

3 Answers 3

Reset to default
6

I would try something like this:

mysqlConnection.query('SELECT `something` FROM `here` WHERE `dog` = ?', [info] function(err, row, fields) {
  if(err) {
    return console.log('Error1');
  } else if (!row.length) {                                                   
    return console.log('Error2');
  } else if (!row[0].something) {
    return console.log('Error3');
  }

  console.log('Works');
});

It's using a "falsy" check for row[0].something which will return false if the value is undefined, null or an empty string. It also fixes the injection attack vector that t.niese mentioned.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Note the OP was checking for the string 'NULL' which you are not doing.
Hey thanks for code, but the thing is it shouldnt be vulnerable to sql injection because +info+ is not accessible to user.
@ArnasMarnas even if it is not accessible by the user you should avoid this. You might do changes to your code later which might result in a potential mysql injection or you right now have one already even if you think that it won't be one. Just use the escaping when ever possible.
@jfriend00 I may be wrong, but I believe the OPs intent was to check for a null value, not the string 'NULL', hence the entire reason to post the question on StackOverflow.
1

I am aware that I am 5 years and 9 months late, but for those of you struggling with this, here's a solution. The table's value when empty is not NULL. I was having a similar problem in which I wanted to reset AUTO_INCREMENT to 1 when the table is empty. To detect when it's empty, we have to see if it has any element with the index 0. If it has an element, it would return something like: RowDataPacket { // data }. If it doesn't, it would return undefined. See where I'm going with this? Just add a conditional to see if the result[0] is undefined or not. Want some code to better understand it? Sure! Here it is:

  db.query("SELECT * FROM tablename", (err, result) => {
    if (err) throw err;
    else {
      // If the first element does not exist
      if (result[0] == undefined) {
        db.query("yourquery", (err) => {
          if (err) throw err;
        });
      } else {
        res.send(result);
      }
    }
  });
Improve this answer

Comments

0

If you think in a scenario when you receive an Array<any> when you run a SQL like select name from employee there are three concerns you should have:

  1. If your statement did return something
  2. If the property you are looking for exist
  3. If the content of the property is null and you are expecting a null

As these concerns will occur hundreds of time, I use the following approach (in TypeScript):

let ret: Array<any> = connection.query('select name from employee',...);

for (let r of ret) {
   name = getValueColumn(r,'name','This will be thrown if content is null');
};

export function getValueColumn(obj: any, fieldName: string, messageIfNull: string = null): any {
    fieldName = fieldName.toLocaleLowerCase();
    if (!obj) {
        throw new CustomError(errorCodes.connection.rowNull, 'Linha nula e sem campos');
    } else if (!obj.hasOwnProperty(fieldName)) {
        throw new CustomError(errorCodes.connection.fieldDoesNotExist, 'Campo não existe -> ' + fieldName);
    } else {
        if (!obj[fieldName]) {
            if (messageIfNull) {
                throw new CustomError(errorCodes.connection.fieldWithNullValue, messageIfNull + '\n' + fieldName +
                ' com valores nulos ou campo invalido\n' + obj);
            };
            return null;
        }; 
        return obj[fieldName];
    };
};

If you were to check the results with just if (!ret) {...}, it would be always false as an empty array is not null. So you would have to check if(!ret[0]) {..} So all three concerns are handled and you don't need to be worried every time you want to parse the query.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.