title: How to work around PHP odbc_execute parameters needing single quotes for datetime conversion?
body: <p>I’m trying to convert some code to use PHP’s odbc_prepare and odbc_execute to insert the data into a SQL Server table using parameters for security but I can't get past errors due to the way PHP handles single quotes in parameters and SQL Server's need for single quotes around text to be cast to datetime.</p>
  
  <p>I have a web form that accepts a datetime from the user amongst other data.<br>
  The output from the datetime input box (missdt below) is in the format YY-MM-DDTHH:MM:SS (e.g. 2019-10-09T10:14:00)</p>
  
  <p>Here’s my code (shortened for this post, yes I clean up the post data in my real code):</p>
  
  <pre><code>$missdt = $_POST["missdt"];  // results in 2019-10-09T10:14:00  stored as datetime in SQL Server
  $branch = intval($_POST["branch"]);  // stored as integer in SQL Server
  $name = $_POST["name"];              // stored as varchar in SQL Server
  
  $params = array($branch, 
          $missdt,               // results in SQL error “Invalid character value for cast specification”
          $name, );
  $sql = "insert into my_table (branch, submitted, miss_date, name) 
              values (?, getdate(), ?, ?) ";
  
  $res = odbc_prepare($conn, $sql);
  $stmt = odbc_execute($res, $params);
  </code></pre>
  
  <p>The problem is that the $missdt parameter results in a SQL error “Invalid character value for cast specification”</p>
  
  <p>I’ve tried the following variations for $missdt in the $params array and received the indicated errors:</p>
  
  <p>"'" .  $missdt . "'",  // (added quotes) PHP error can't open file</p>
  
  <p>"' " . $missdt . " '", // (added quotes and spaces) PHP error can't open file</p>
  
  <p>"'" .  $missdt . "' ", // (space after) SQL error Invalid character value for cast specification</p>
  
  <p>'"' . $missdt . '"',   // (double quotes used with set quoted_identifier off) results in “invalid parameter number” &amp; “odbc_execute() expects parameter 1 to be resource”</p>
  
  <p>This variation on the sql query works fine but could be abused:</p>
  
  <p>$sql = "insert into my_table (branch, submitted, miss_date, name)       values (?, getdate(), ‘$missdt’, ?) ";</p>
  
  <p>According to the PHP documentation using single quotes before and after a parameter results in the parameter being treated as a filename and a workaround is to add a space to the beginning or end of the parameter.  You can see from the variations I’ve tried above that doesn’t work in this case.  I think this is because SQL Server needs the quotes in order to cast the character data to datetime.</p>
  
  <p>Any ideas on how to get around this?</p>
  
  <p>I’m using PHP 7.1, SQL Server 2014, and Apache on Windows 2012</p>