0

I am preparing the query using mysqli prepare but this is giving an error check the manual that corresponds to your MySQL server version for the right syntax to use near '? ) AND ( user_fullname ? )'

  $query="DELETE FROM table WHERE  (  quiz_answer ?  ) AND ( user_fullname ? )";
  $stmt = $mysqli->prepare($query);


  $stmt->bind_param('ss',array('IS NULL','IS NULL'));
  $stmt->execute();

Please help me finding the problem in query Thanks in advance

pratim_b
  • 1,160
  • 10
  • 29
Mohammad Arshi
  • 386
  • 2
  • 9
  • I don't understand why you'd want to bind parameters to MySQL syntax? – BenM Jan 15 '14 at 11:16
  • check http://www.php.net/manual/en/mysqli.prepare.php – Damodaran Jan 15 '14 at 11:19
  • I want to use bind parameter to prevent the sql injection – Mohammad Arshi Jan 15 '14 at 11:26
  • The latter restriction is necessary because it would be impossible to determine the parameter type. It's not allowed to compare marker with NULL by ? IS NULL too. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements – Mohammad Arshi Jan 15 '14 at 11:29

1 Answers1

0

If we assume, that $answer and $fullname are your quiz_answer and user_fullname params. This might work:

$answerString = 'IS NULL';
$userString = 'IS NULL';

if ( trim( $answer ) && $answer != 'null' ) $answerString = '= ?';
if ( trim( $user ) && $user != 'null') $userString = '= ?';

$query="DELETE FROM table WHERE  (  quiz_answer $answerString  ) AND ( user_fullname $userString )";

$stmt = $mysqli->prepare($query);

if ( $answer && ! $fullname ) $stmt->bind_param('s',array($answer));
if ( $answer && $fullname ) $stmt->bind_param('ss',array($answer, $fullname));
if ( ! $answer && $fullname ) $stmt->bind_param('s',array($fullname));

$stmt->execute();

But i also suggest additional param validation to be executed before inserting them into sql query, even by parameter-binding.

Xardas
  • 142
  • 9
  • I checked php.net there is clearly mentioned that we cant use NULL and IS NULL in bind params – Mohammad Arshi Jan 15 '14 at 11:36
  • It is strange, this http://stackoverflow.com/questions/5214574/php-bind-params-with-null shows that it should work. – Xardas Jan 15 '14 at 11:41
  • Yeah, you should build your query string part-by-part then. If answer is empty then quiz_answer IS NULL, and if it is not empty then use param binding. I advice using PDO then, you can use named params there. – Xardas Jan 15 '14 at 11:49