PHP and MSSQL dates 1

[dkemas]

I am having to update a system I built from mysql to mssql (and also from a wamp environment to IIS running windows php).

All is going well so far apart from datetime data types. I read that datetime is not supported and to use the much more forgiving datetime2 instead but I'm still getting the error

Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 241

 => 241 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. ) ) 

Removing the date fields from my insert query works fine. Here's my test script

[code]
if (isset($_POST['submit'])) {

$id = $_SESSION["user_id"]
$varcharfield1 = $_POST['varcharfield1'];
$varcharfield2 = $_POST['varcharfield2'];
$datetimefield = $_POST['datetimefield'];

$params = array($varcharfield1, $varcharfield2, $datetimefield, $id);

$query = sqlsrv_query( $conn, "update mytable set 
varcharfield1=?, 
varcharfield2=?, 
datetimefield=?
where id=?", $params);

}

Thanks for any suggestions

 

[jpadie]

first we don't know what format the POST variable is in. so it's a bit difficult to suggest a fix. Let's assume it is in yyyy-mm-dd format.

warning - i know precious little about sql server.

$date = new DateTime($_POST['datetimefield'], new DateTimeZone('UTC'));
$params = array($varcharfield1, $varcharfield2, $date->format(DateTime::ISO8601), $id);
$query = sqlsrv_query( $conn, "update mytable set 
varcharfield1=?, 
varcharfield2=?, 
datetimefield=CONVERT(datetime, ?,126)
where id=?", $params);

possibly you could bypass the conversion by outputting the date in the format

$date->format("Y-m-d\TH:i:s");

 

[dkemas]

Thanks for the suggestions, I ended up using format when populating the text field value when printed to the page and

date("Y-m-j", strtotime(str_replace('/', '-', $_POST['datetimefield'])));

on inserting data.

Another question, is that safe to use the above code in my query, for other fields I prevent injection by running through a function?

Thanks

 

[jpadie]

if you use the placeholder syntax I used above then the engine does the escaping transparently. that said you should always validate incoming data before you use it.

so:

1. check that the date string looks like a date and is within an acceptable range.
2. and that the two other fields are as you expect.

for the datetime field, you can make the code a bit neater

try{
$param[2] = datetime::createFromFormat('Y/m/j',$_POST['datetimefield'])->format('Y-m-d\TH:i:s');
} catch {Exception $e) {
 print_r($e);
}

the reason you need a try ... catch block is if the format of the incoming date is altered then the code will fail with a fatal error.

This code does not validate that a date is well formed. e.g. if you provide '2015/01/70' php will gladly take that and make it into 11th March (being 70 days after jan 1st). so even with the improvement in date handling through the use of the built-in classes - you are still left having to use some other code to validate the types of incoming fields.

I typically do all of that in my class model. then you end up with a nice encapsulation.