title: How to use a serialized or json_encoded attribute in a MySQL Stored Procedure
body: <p>I'm stumped. I want to store either a <code>serialize()</code>'d or <code>json_encode()</code>'d array in MySQL using a stored procedure. This would be to store a user's checked boxes or multiselect options.</p>
  
  <p>We are using stored procedures, and while I like to think I've gotten a good grip on them over the last few months, I'm not sure how to solve the following error:</p>
  
  <blockquote>
    <p>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 'One","Three","Two"]")' at line 1 (SQL: CALL CategoryFieldValues_UpdateAttributeByID("3077", "1", "1234", "field_value", "["One","Three","Two"]"))</p>
  </blockquote>
  
  <p>Here is my stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE `CategoryFieldValues_UpdateAttributeByID`(
    IN siteID INT UNSIGNED,
    IN ID INT UNSIGNED,
    IN modifiedByID INT UNSIGNED,
    IN attribute VARCHAR(255),
    IN attrValue VARCHAR(255)
  )
  BEGIN
  
      SET @d = IHaveAFunctionHere( siteID );
      SET @n = 'null';
  
     IF attribute = "category_field_id" THEN SET @n = 0; END IF;
  
      SET @s = CONCAT(
      'UPDATE ', @d,'.category_field_values cfv
         SET
          ', attribute, ' =  ', QUOTE(NULLIF(attrValue, @n)), ',
          modified_by = ', QUOTE(modifiedByID), '
      WHERE cfv.id = ', ID, ';'   );
  
  PREPARE statement FROM @s;
  EXECUTE statement;
  SELECT ROW_COUNT() AS ROW_COUNT;
  DEALLOCATE PREPARE statement;
  
    END$$
  </code></pre>
  
  <p>This procedure takes information like the siteID, which it uses to find the right database, and an <code>attrValue</code> that is the value I want to set. It works fine, but now when I need to drop a json_encoded or serialized array at it, it chokes.</p>
  
  <p>The json_encoded array ends up looking like <code>["One","Three","Two"]</code>.</p>
  
  <p>I'm guessing it's bugging out because of unescaped quotes or square brackets, but I'm not totally sure. </p>
  
  <p>Also, I tried a slightly different syntax (below) to no avail:</p>
  
  <pre><code>    BEGIN
  
  SET @d = IHaveAFunctionHere( siteID );
  SET @n = 'null';
  SET @attrValue = attrValue;
  
  IF attribute = "category_field_id" THEN SET @n = 0; END IF;
  
  SET @s = CONCAT(
      'UPDATE ', @d,'.category_field_values cfv
         SET
          ', attribute, ' =  ?,
          modified_by = ', QUOTE(modifiedByID), '
      WHERE cfv.id = ', ID, ';'   );
  
  PREPARE statement FROM @s;
  EXECUTE statement USING @attrValue;
  SELECT ROW_COUNT() AS ROW_COUNT;
  DEALLOCATE PREPARE statement;
  
    END
  </code></pre>
  
  <p>I've also tried removing the <code>QUOTE</code>s, and some other small tweaks, and I'm just spent.</p>