PHP PDO - dblib MSSQL - using variable in query not working

Question

I have a reasonably simple query that i want to run with my PDO connection to a remote MSSQL server.

SELECT BookingID, DriverID 
FROM dbo.VehicleJobHistory 
WHERE TimeJobRequired > "02/03/2013" AND VehicleID = $vid

when i write the query without any variables it works perfectly, but as soon as i try to add a variable to the query i get nothing returned. I think its a type mismatch, but i cant be sure.

If i change $vid to "451" i get the results im looking for.

$vid = '451';    
$myServer = "X";
$myUser = "X";
$myPass = "X";
$myDB = "X";

try {
  # MS SQL Server and Sybase with PDO_DBLIB

$DBH = new PDO("dblib:host=$myServer;dbname=$myDB", $myUser, $myPass);

# creating the statement
$STH = $DBH->query('SELECT BookingID, DriverID FROM dbo.VehicleJobHistory WHERE TimeJobRequired > "02/03/2013" AND VehicleID = $vid');

# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_OBJ);

# showing the results
while($row = $STH->fetch()) {
    echo $row->BookingID . "/";
    echo $row->DriverID  ;
echo "<br>";

}

}
catch(PDOException $e) {
    echo $e->getMessage();
}

# close the connection
$DBH = null;
echo "connection closed";

Any help would be most appreciated.

Michael Berkowski · Accepted Answer · 2013-03-26 02:12:44Z

2

This is due to the fact that your query is defined in a single-quoted string. The variable $vid doesn't get interpolated in a single quoted string, and is passed as the literal $vid, causing a query syntax error since it's unquoted. Reverse the quotes, using double quotes on the outside.

$STH = $DBH->query("SELECT BookingID, DriverID FROM dbo.VehicleJobHistory WHERE TimeJobRequired > '02/03/2013' AND VehicleID = $vid");

Really though, this should be done with a prepared statement and bindParam() for VehicleID.

$stmt = $DBH->prepare("SELECT BookingID, DriverID FROM dbo.VehicleJobHistory WHERE TimeJobRequired > '02/03/2013' AND VehicleID = :vid");
if ($stmt) {
  $stmt->bindParam(':vid', $vid);
  $stmt->execute();
}

Start reading up on PDO prepared statements. If you are using PDO, you ought to make sure you are getting their security benefits.

answered Mar 26, 2013 at 2:12

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP PDO - dblib MSSQL - using variable in query not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related