Azure: PHP SQL Webjob not working. Why does it get

Question

In efforts to run a PHP webjob on Azure to insert a record into a SQL database, my code kept breaking on the $query->execute(); line.

Here's what I did. First I went into my SQL database and created a login under the master db:

CREATE LOGIN username WITH password='userpassword'

Then, I added this login as a user in my current database (not under master, but under mydb ):

CREATE USER username FROM LOGIN username

Then I gave this user write privileges with this command:

EXEC sp_addrolemember N'db_datawriter', N'username'

Having done all this, I then went into portal.azure, and opened my App Service, navigated to WebJobs and uploaded a .zip file with two files inside;

A scheduler, settings.job:

{
    "schedule": "0 */5 * * * *"
}

And my main PHP code, updateRecord.php:

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

try {
    $conn = new PDO ( "sqlsrv:server = myazuresite.database.windows.net,1433; Database = mydb", "username", "userpassword");
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch ( PDOException $e ) {
    print( "Error connecting to SQL Server." );
}

$cm = 'cm';
$span = '1day';

$stf = $conn->prepare("INSERT INTO mydbtable
VALUES ( $cm, $span, 1, 2, 3, 4, 12.34 );");
$stf->execute();

echo $stf;

unset($conn);
unset($stmt);

?>

As I launched the WebJob, it went from Running status to Pending restart.

[09/10/2016 21:03:14 > 82e662: SYS INFO] Detected WebJob file/s were updated, refreshing WebJob
[09/10/2016 21:03:14 > 82e662: SYS INFO] Status changed to Starting
[09/10/2016 21:03:14 > 82e662: SYS INFO] Run script 'updateRecord.php' with script host - 'PhpScriptHost'
[09/10/2016 21:03:14 > 82e662: SYS INFO] Status changed to Running
[09/10/2016 21:03:15 > 82e662: INFO]     
[09/10/2016 21:03:15 > 82e662: INFO] Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'day'.' in D:\local\Temp\jobs\continuous\myjobname\uf5ls33g.42d\myjobzipfolder\updateRecord.php:20
[09/10/2016 21:03:15 > 82e662: INFO] Stack trace:
[09/10/2016 21:03:15 > 82e662: INFO] #0 D:\local\Temp\jobs\continuous\gAnaytics\uf5ls33g.42d\myjobzipfolder\updateRecord.php(20): PDOStatement->execute()
[09/10/2016 21:03:15 > 82e662: INFO] #1 {main}
[09/10/2016 21:03:15 > 82e662: INFO]   thrown in D:\local\Temp\jobs\continuous\jobs\uf5ls33g.42d\myjobzipfolder\updateRecord.php on line 20
[09/10/2016 21:03:15 > 82e662: SYS ERR ] Job failed due to exit code 255
[09/10/2016 21:03:15 > 82e662: SYS INFO] Process went down, waiting for 60 seconds
[09/10/2016 21:03:15 > 82e662: SYS INFO] Status changed to PendingRestart

The most relevant of the entire WebJob Log and most concerning being:

[09/10/2016 21:03:15 > 82e662: INFO] Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'day'.' in D:\local\Temp\jobs\continuous\myjobname\uf5ls33g.42d\myjobzipfolder\updateRecord.php:20

Can anyone diagnose how to solve this problem? I'm not seeing anything wrong with how I prepare my sql statement, or execute it.

Answer 1

You are using prepared statements incorrectly and this is resulting in invalid SQL.

$stf = $conn->prepare("INSERT INTO mydbtable
VALUES (?, ?, 1, 2, 3, 4, 12.34 );");
$stf->execute(array($cm, $span));

You could quote the $cm and $span and this also would work. The above usage is the way prepared statements are meant to be used though. The driver handles the quoting.

When your query arrived at the DB it was:

INSERT INTO mydbtable
VALUES (1day, cm, 1, 2, 3, 4, 12.34 );

so the 1day and cm needed to be quoted. Using the placeholders and binding allows the driver to do this for you, and escaping values in those variables isn't needed.