0

I have a function which adds varhcars and integers into a database row

public function addItem($id, $site, $price, $quantity, $condition, 
   $sellerName, $sellerRating, $sellerLocation, $description, $link){

   $q = "INSERT INTO tbl_items VALUES(
      '$id',
      '$site',
      $price, 
      $quantity, 
      '$condition',
      '$sellerName',
      $sellerRating,
     '$sellerLocation',
     '$description',
     '$link',
     ".time().")";

  return mysql_query($q, $this->connection);    
}

There may be situations where I may decide that I want to set a varchar value to NULL, but the problem is if I send the string NULL as a parameter it will always be treated as a string.

e.g.

addItem("id1", "site1", 100, NULL, "NULL", "NULL", "NULL", "NULL", "NULL", "NULL",);

How do I avoid NULL being treated a string in my query?

mk_89
  • 2,692
  • 7
  • 44
  • 62
  • 1
    a PHP null will be converted to an empty string when you try to insert it into the query string. Since your code is using `'` everywhere, there is no way to insert an actual sql 'null' into that string, without redoing how you build the query string. – Marc B Sep 26 '12 at 18:17

1 Answers1

6

Easy. Don't quote it, as 'NULL' is always a string and NULL is .. well, NULL.

How? Use placeholders aka prepare statements.

This will take care of any quoting (as required) and prevent SQL injection attacks. Win.

See How can I prevent SQL injection in PHP? which contains information on writing safe queries and has examples for both mysqli and PDO approaches.

Community
  • 1
  • 1
  • Ok I just skimmed through a few article on the web, what happens when I have a duplicate key and im using placeholders? – mk_89 Sep 26 '12 at 18:21
  • @mk_89 Same thing as without placeholders :) However, while I stand by my answer as the general solution, it is likely that the variables, e.g. $condition already contain the string value `NULL` (e.g. `$condition = "NULL"`). Make sure to address this as well. –  Sep 26 '12 at 18:25