Problem with quotation marks in query syntax in MySQL

Question

Problem with quotation marks in query syntax in MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a class, and within that class, I have a function to update a data in the MySQL database. Here is 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 '$value' variable. If the value received via $_POST is NULL , '$value' receives an empty string '' and thus is sent to the database.

I assumed this happened because of the single quotes around the variable. So I removed the quotation marks and when trying to 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 that it was for removing the quotation marks. But I've written MySQL queries without quotation marks and they worked fine.

What I do not want is to leave empty strings in the database. How to solve this? Which way is right?

php mysql

asked by anonymous 16.09.2018 / 22:35

2 answers

0

I think the best way to resolve this is to set the variable's quotes only if it is not empty. In addition, null must be string so that the term "null" appears 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;
	}
}

18.09.2018 / 17:28

How to get a QByteArray video using the QML version of CameraRecorder? Business Intelligence - How and where to group school results?

score 0 · Accepted Answer

If you take the single quotation mark from SET $detail = '$value' WHERE... and $value to null or empty, it causes this error because SQL will thus be mounted SET $detail = WHERE... .

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

The code looks 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.
}