PHP 使用 PDO 方式连接 sqlserver ,拼接 的 sql 命令总是报错

Question

    $sql = "Declare @v1 varchar(5000)
";
    $sql .=    "Declare @v2 varchar(5000)";
    $sql .=    "Set @v1 = '";
    $sql .=    "<?xml version = \"1.0\"?>";
    $sql .=    "<Var1Data>
";
    $sql .=    "<BodoOrder>
";
    $sql .= "<OrderNo>2016121201380</OrderNo>
";
    $sql .=    "<Bodtype>订单</BodType>
";
    $sql .=    "<xdate>2016-12-12</xdate>
";
    $sql .= "<DID>00</DID>
";
    $sql .=    "<CID>104179</CID>";
    $sql .=    "<Contacter>111</Contacter>
";
    $sql .=    "<Tel>13707165174</Tel>
";
    $sql .=    "<address>测试地址</address>
";
    $sql .= "<BodNote>测试</BodNote>
";
    $sql .=    "</BodoOrder>
";
    $sql .= "</Var1Data>'

";
    $sql .= "Set @v2 = '";
    $sql .=    "<?xml version = \"1.0\"?>";
    $sql .= "<Var2Data>
";
    $sql .= "<BodoOrderDetails>
";
    $sql .= "<PID>14241</PID>
";
    $sql .= "<Color>金色</Color>
";
    $sql .= "<Size>90C</Size>
";
    $sql .= "<nQty>1</nQty>
";
    $sql .= "<nPrice>188.00</nPrice>";
    $sql .= "</BodoOrderDetails>
";
    $sql .= "</Var2Data>'

";
    $sql .= "Exec LW2DRP @v1,@v2";
    
    try {
        $stmt = $dbh->prepare($sql);    
        $stmt->execute();  
    } catch (Exception $e) {
        echo $e->getMessage();
    }

报错：

 Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 102 [Microsoft][SQL Server Native Client 11.0][SQL Server]'
' 附近有语法错误。 in C:\phpStudy\WWW\test.php on line 51

但是我把 echo htmlspecialchars($sql) 输出的代码，直接放到数据库去执行，是可以成功的。

Declare @v1 varchar(5000)
Declare @v2 varchar(5000)Set @v1 = '<?xml version = "1.0"?><Var1Data>
<BodoOrder>
<OrderNo>2016121201380</OrderNo>
<Bodtype>订单</BodType>
<xdate>2016-12-12</xdate>
<DID>00</DID>
<CID>104179</CID><Contacter>111</Contacter>
<Tel>13707165174</Tel>
<address>测试地址</address>
<BodNote>测试</BodNote>
</BodoOrder>
</Var1Data>'

Set @v2 = '<?xml version = "1.0"?><Var2Data>
<BodoOrderDetails>
<PID>14241</PID>
<Color>金色</Color>
<Size>90C</Size>
<nQty>1</nQty>
<nPrice>188.00</nPrice></BodoOrderDetails>
</Var2Data>'

Exec LW2DRP @v1,@v2

已经解决。从 word文档粘贴过来的时候，肯定带上了特殊字符。手动重新打一遍就 OK

Answer 1

你保存的时候貌似没htmlspecialchars吧