Quote problem in query syntax in Mysql

Question

I have a class, and within this class, I have a function to update a data in the Mysql database. Follow the function:

public function set_detail($id, $detail, $value) {
  $query = "UPDATE `contacts` SET $detail = '$value' WHERE `contact`.`id` = $id";
  if (parent::do_sql($query)) {
    return true;
  } else {
    return false;
  }
}

So far, so good. But the problem is in the variable '$value'. If the value received via $_POST for NULL, '$value' takes an empty string '' and so it is sent to the database.

I assumed this happened because of the single quotes around the variable. So I removed the quotes and while trying to perform the query again, I get the syntax error:

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 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 'WHERE `contacts`.`id` = 0000000839'

I also assumed it was because I removed the quotation marks. However I have already written queries in Mysql without quotation marks and worked normally.

What I don’t want is to leave empty strings in the database. How do I resolve this? What’s the right way?

Answer 1

I believe that the best way to solve this is by setting the quotes of the variable only if it is not empty. Also, the null has to be string for the term "null" to appear in your SQL code.

public function set_detail($id, $detail, $value) {
	if(!empty($value)){
		$value = "'".$value."'";
	}
	else{
  		$value = "NULL";
  	}


	$query = "UPDATE contacts SET ".$detail." = ".$value." WHERE contact.id = ".$id;
	if (parent::do_sql($query)) {
		return true;
	} else {
		return false;
	}
}

Answer 2

If you take the simple quotes from SET $detail = '$value' WHERE... and $value is null or empty, it causes this error because SQL will be mounted like this SET $detail = WHERE....

The simplest way to solve this problem is to treat when $value is null or empty using the function empty.

The code would look like this:

if(empty($value)){
    // Código que lida quando for nulo ou vazio.
    // Você pode colocar um simples return false, ou lançar um throw.
    // Ou colocar setar um valor padrão para $value.
}