title: Saving JSON to SQL
body: <p>I am storing JSON in a MsSql database, I am then passing it to Javascript via a PHP script, one of the JSON values is a format string that is passed to <a href="https://github.com/phstc/jquery-dateFormat" rel="nofollow">jQuery-dateFormat</a>, "yyyy-MM-dd" for example.</p>
  
  <p>It works great until i want to include extra text not part of the formatting string, specifically d, h, a or p characters. Fortunately there is a way to escape strings as part of jQuery-dateFormat, if I wrap the text in single quotes (') i am able to use the affected characters unsubstituted.</p>
  
  <p>When I attempt to save the JSON with this additional value through my existing PHP script back to the database It doesn't work.</p>
  
  <p>Now for some code, i use a standard <code>&lt;textarea&gt;</code> as an input for the JSON data and validate it on the server by using;</p>
  
  <pre><code>function fValidJson($input) {
      json_decode($input);
      switch (json_last_error()) {
          case JSON_ERROR_NONE:
              return $input;
              break;
          default:
              return false;
              break;
      };
  };
  </code></pre>
  
  <p>if <code>$input</code> passes the test I update the database using;</p>
  
  <pre><code>odbc_exec($conn, $update);
  </code></pre>
  
  <p>without any data for the dateFormat format <code>$update</code> looks like this;</p>
  
  <pre><code>update data set json = '{"folder": "example"}' where id = 1
  </code></pre>
  
  <p>with dateFormat data <code>$update</code> can look like this;</p>
  
  <pre><code>update data set json = '{"folder": "example", "format": "report-yyyy-MM-dd"}' where id = 1
  </code></pre>
  
  <p>Ordinarily this would be fine except i want to preserve the "p" in "report" and it gets converted to "a.m./p.m." by jQuery-dateFormat. escaping the the "p" once;</p>
  
  <pre><code>update data set json = '{"folder": "example", "format": "re\'p\'ort-yyyy-MM-dd"}' where id = 1
  </code></pre>
  
  <p>gives me an error with <code>json_last_error()</code>, while escaping it twice;</p>
  
  <pre><code>update data set json = '{"folder": "example", "format": "re\\'p\\'ort-yyyy-MM-dd"}' where id = 1
  </code></pre>
  
  <p>gives me an error updating the database table row. escaping it a third time returns the same problem with <code>json_last_error()</code>.</p>
  
  <p>This behavior is expected though. JSON can't contain single quotes (') and the single quote (') confuses SQL. So what are my other options for saving this JSON string to SQL?</p>