PHP PDO-dblib MSSQL-在查询中使用变量不起作用 [英] PHP PDO - dblib MSSQL - using variable in query not working

问题描述

我有一个相当简单的查询，我想通过与远程MSSQL服务器的PDO连接来运行.

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.

如果我将$ vid更改为"451"，则会得到我正在寻找的结果.

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";

任何帮助将不胜感激.

推荐答案

这是由于您的查询是在单引号字符串中定义的.变量$vid不会插在单个带引号的字符串中，而是作为文字$vid传递，因为未引用双引号会导致查询语法错误.反向引用，在外面使用双引号.

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");

尽管如此，应该使用准备好的语句和bindParam()来表示VehicleID.

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();
}

开始阅读 PDO准备好的语句.如果您使用的是PDO，则应确保获得其安全益处.

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

这篇关于PHP PDO-dblib MSSQL-在查询中使用变量不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！